Variables (comparable to using env variables) that can be set on a database and referenced in formulas (rather than putting them in another table and using lookup value or similar).
Basically if there was a page in each database, where we can set variables it would be super useful and clean. Like ENV_CONVERSION_RATE_USD_NTD, ENV_CONVERSION_RATE_USD_EUR for example with a local scope.
Then reference those values in formulas for the sheet and avoid needing to make extra tables and/or columns for single values like this.
I don’t believe it is currently possible to lookup a value from another sheet directly in a formula (as in, you would need to import the values from another sheet into the current sheet first, before they could be used in formulas). Though even if it was/is, I think having local variables for the sheet itself would be cleaner.
My use case:
The use case is a sheet that needs manual updating of applicable conversion rates for payment calculations each time it is used. The conversion rate variable used for each row would depend on the currency pair set for the row (conditional formulas), with other formulas depending on the derived calculations from that.
Hi @log , thanks a lot for the suggestion, it sounds really useful! I’ve shared it with our product team for further discussion. Feel free to keep an eye on our roadmap for updates: Bika.ai - Automate Your Work with Intelligence
In Bika.ai, the concept of local variables similar to environment variables is not directly available.
However, you can achieve a similar effect using the following strategies:
1. Using a Dedicated Configuration Table
Create a dedicated table for your conversion rates. This table would act as a configuration table where you can store your rates as records. For example:
Currency Pair
Conversion Rate
USD to NTD
30.5
USD to EUR
0.85
You can then use a lookup field or a rollup field to reference these values in your main table.
2. Using a Formula Field
If you need to keep things cleaner and avoid additional tables, you can define a formula field in your main table to set the conversion rates based on the currency pair. For example:
IF({Currency Pair} = "USD to NTD", 30.5,
IF({Currency Pair} = "USD to EUR", 0.85,
0))
This way, you can update the rates directly in the formula field whenever needed.
3. Manual Updates in a Single Field
If you prefer not to use any additional tables, you can maintain a single field in your main table where you manually enter the conversion rate for each session. This would require you to update the field before you calculate any conversions.
4. Using Scripting or Automation
If you have scripting available (via Bika.ai scripting block or automations), you can create a script that fetches the latest rates from an API or another source and updates a field in your main table automatically.
Summary
Using a combination of a configuration table, formula fields, and automations can help you achieve a similar outcome. This will allow you to streamline your conversion rate management without cluttering your main tables with extra columns.
Regarding the alternative implementations, I will likely use your suggestion @Kelly or keep the sheet in gsheets for now.
Though I received an email saying the status of this is ‘reject’ so just would like to add more info.
Bika is meant to be less free-form than other sheets/databases - where you define a strong ‘unbreakable’ schema.
Formulas I don’t think should be something that ever needs updating unless your schema changes.
Having variables would also be easier to update via automation compared to needing to regularly update the full formula, including all currency pairs in the IF logic.
If I delegate usage of the database to an employee, I’d need to give them administrative access to edit the schema of the database itself, instruct them on which formulas to update, how to update them etc. instead of simply updating some user friendly input values that have input validation. This increases the risk of undetected human error and would be more fragile.
Also, for more complex databases - it may be difficult to remember every value that needs to be updated and finding all associated formulas to update. Especially if delegated to employees. The description field could be used I suppose.
Being able to directly reference a lookup value from an external sheet in a local formula would also somewhat achieve this. Just would be more clutter and more fragile for a lot of cases.
I’m sure there are several ways this can be achieved, it’s just a matter of which is the least fragile, most simple and elegant way.