Airtable Cobuilder is here! Learn more about our new no-code app creation feature, powered by AI on the Airtable Academy

Linked record automation - prohibiting replacing the linked record

Topic Labels: Automations
379 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

I have an expense base.

It has two tables - All Expenses and Budget.

The All Expenses table contains a form that is filled out by anyone submitting an expense. It will categorize based on program, sub-program, and category within that program.

The Budget table contains every category of budget items. The overall program, then the hierarchy of sub-program and category within the program.

The automation I created triggers upon the creation of a record in the All Expenses table via the form. It is set to find records that match in program, sub-program, and category. Once found, it will update the budget item record in the Budget Table. Specifically, it will update one field titled "All Expenses" in the Budget table. This field is a lookup field to the All Expenses table. There is a connected lookup field that will show the value connected to the title of the expense attached. So, the theory is that as expenses are submitted via the expense form, the automation will fill in the lookup field by budget item category and the value will continue to grow. So, I will have a sum of all of the expenses within each category.

The issue is that the automation will replace any value in the lookup field instead of compounding it. Here is a link to a Loom video that explains it better.

1 Reply 1
12 - Earth
12 - Earth

In your update action, for the linked field "All Expenses", you should not only add the "title" field, but also the already existing value that is in the "All Expenses" field. You do that by using a comma and then also referring to the same field "All Expenses" in that "Budget" table. 

I would also suggest using a rollup field iso a lookup field.