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.
