Help

This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

Base-wide Constants - how to easily use across multiple Tables?

cancel
Showing results for 
Search instead for 
Did you mean: 
John_Prior
5 - Automation Enthusiast
5 - Automation Enthusiast

Is there a way to easily setup and use “constants” across an entire Base?

For instance, the price of oil per barrel might be 40. I have a lot of formulas that depend on that price. In addition, I might have several numbers like this that need to be referred to from multiple formulas - for instance the /mile that the IRS accepts for the mileage deduction, the number of tons of gravel contained in a gravel truck, etc.

The advantage of the “constants” is that, if the price of oil changes, I need to change just one number in one place; I don’t have to hunt through tons of formulas looking for a hard-coded “40” in the formula.

However I can’t seem to figure out how to easily setup constants in AirTable. Right now I’m sticking them all in a “Constants” table, with each row having just “Name” and “Value”, where Name is something like price_of_oil_per_barrel and Value is 40.

BUUUT … referring to these constants is A PAIN …any referring Table first needs to add a link to my “Constants” table, and then tediously create a new linked Field for each value that I want to refer to in any formula in that Table. So if there are 11 constants that I’m going to use in formulas in a single Table, I’ll need to link to11 fields (then possibly hide them…).

Is there an easier way to do this?

7 Comments
IT_BeeTee
6 - Interface Innovator
6 - Interface Innovator

This would be such a useful feature.
Cannot believe how difficult it is to do this in Airtable :frowning:

Albert_Nazander
4 - Data Explorer
4 - Data Explorer

Has any better way come up? Also interested in this.

Joshua_Gilman
5 - Automation Enthusiast
5 - Automation Enthusiast

Two years later and no update? In Google Sheets I literally just have a tab with a bunch of hardcoded settings that I refer to in my formulas. Why would this be complicated to do in AirTable?

Tomer_Eldor
5 - Automation Enthusiast
5 - Automation Enthusiast

+1 to interest in this

Margaret_Picker
6 - Interface Innovator
6 - Interface Innovator

Yes I need it too. Without it I have to change abot 20 formula by hand every month. Very error prone.

kuovonne
18 - Pluto
18 - Pluto

If all of your formulas are in a single table, you can set your constant with a formula field. Then all of the other formulas can refer to that formula field. If you have several constants for that table, make a view that shows the constants for quick reference.

If you need a [Constants] table because the values are used in multiple tables, you can use automations to make sure that every record is linked. Instead of having a new row in the [Constants] table for each value, I recommend placing each value in a new field. That way there is only one record to link.

Lu
4 - Data Explorer
4 - Data Explorer

I ended up with the same solution as @kuovonne suggested, as I needed to be able to update single "constants" and have them available to all automations, whereas many other requests for this feature describe problems that are better solved by related tables.

When creating a constants table, first delete all but one record, then change the table permissions to disallow anyone, including yourself, automations, & forms, from adding or deleting records. Delete the default columns except for maybe a helpful description for the table's purpose as the first field. As you add a field and value for each constant, set its permission so that nobody can edit it, to prevent accidents. You can always unlock it later if you need to change it, then lock it again. Naming fields as if they're JS variables makes them easier to use in scripts later, e.g. someSpecialValue or GREAT_CONSTANT.

To use the constants, add a Find records action to the top of your automation, and Find records based on condition, where it will always be true, e.g. "Where someField is not empty or someField is empty". to guarantee you'll get the record. The result of that action produces results in arrays, so you can add a simple Run script action immediately after the Find, with the following contents:

Object.entries(input.config()).forEach(([k, v]) => {
output.set(k, v[0]);
});

Add each desired constant to the input variables of that script, and its output will then contain "unwrapped" constants.