Specify field in another table in formula?

[Matt_C]

Hi.

I have a table containing a single row with a value column that I would like to reference in a formula in a second table. Is there a way to do this?

The background… I have a table containing household “inventory” - basically everything I need to include in my home contents insurance. Insurers set a value (that can change each year) over which an item has to specified in the insurance documentation. I have set up a table to hold this value and, for each item in my inventory, I want a formula to check the item value against the insurers threshold value and flag whether an item needs to be specified or not.

Thanks in advance.

[W_Vann_Hall]

The standard approach would be to access that value via a lookup field in your main table. You’d define that field as something like {LookedUpThreshold}; the value from your single-record table would be populated in each record in your main table; and you could use {LookedUpThreshold} your calculations. Essentially, you’d be making a dynamic copy of {Threshold} in every record of [Main].

Alternatively, you could use an aggregation formula to access the value of {Threshold} without a lookup field. To do so, you would use a rollup field instead of a formula field in [Main]. The field would follow the link to [ThresholdTable] and roll up the value of {Threshold}. In the space where you would ordinarily define an aggregation function, you instead would enter your formula — except wherever you needed the value of {Threshold}, you’d use the keyword values. For example

IF(
    {ItemValue}>=values,
    {ItemValue}
    )

That would set your rollup field to the value of the item whenever the item met or exceeded the threshold amount. You can find more about aggregation formulas in item 3 of this reply.

Either approach requires every record in your main table to be linked to the single record in [ThresholdTable]. Information on how best to achieve that can be found here.

[Matt_C]

Thank you. Really appreciate your time to explain. I’ve had a go but unless I’m doing something wrong I do have to select the single field from the Threshold table for each record in my main table in a LINKED column; otherwise the lookup does not work. Is that how it should work or should I be able to force the actual threshold value to automatically populate as soon as a new main table row is created?

[W_Vann_Hall]

You’ll have to link each newly created record to the [Threshold] table. You can do that manually (i.e., by clicking the plus sign in the linked-record field and then selecting the [Threshold] record), by copy-and-pasting a value into the linked-record field, or by using the fill handle (the small white square at the lower right-hand corner of a cell) to drag the value from a completed cell to the new cell(s). For utilitarian links such as this, I usually name the linked record — in your case, the single record in the [Threshold] table — '.' — that is to say, ‘period’. That way I can easily copy-and-paste a period into the linked-record field of a newly created record (or, even better, a number of newly created records) and create the link. Unfortunately, there is no way (as far as I know) to get Airtable to populate a default linked-record value into newly created records, other than with assistance from a SaaS integration service such as Zapier or Integromat.

[Matt_C]

Thanks again. Useful to know I’m not missing a trick. The period is a good idea.

[Griffin_Mullins]

I’ve found an okay workaround!

1. Create your formula column that outputs the exact same name as the field you’re trying to reference in the other table.
2. Create a “link to record” column next to your formula column.
3. Highlight all records in your formula column, then use the dot in the bottom right to copy content from your formula column to the linked record column. Airtable should find the referenced record and link it!

It’s not 100% automated but it gets pretty close.