Specify field in another table in formula?


#1

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.


#2

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.


#3

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?


#4

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.


#5

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