I am fairly new to Airtable so I hope it's not too nooby of a question.
I am intending to create a fact table with invoices. Now I would simply like to aggregate the value for each client. The only way I found is the "group" feature, however this appears to me as just being a visual filter. In any case I need both the individual table as well as the grouped overview.
Also I found that I can link records to each other but the actual grouping column is a formula. As soon as I select it as lookup, that is breaking the formula.
How can I possibly achieve that?
Solved! Go to Solution.
Do you have a table that contains all your clients that's linked to your "Invoices" table? If so, you can create a rollup field in your "Clients" table with "SUM(values)" to get the values for each client. If you don't have a "Clients" table, I highly recommend setting one up and trying this out
I'm not too sure what you mean by the lookup breaking the formula, sorry. Could you provide some screenshots of the problem you're facing?
Hey Adam, thanks for your response!
So this is my fact invoice table setup. As you can see here, I want to group by invoice by customer mail and by step (several invoices lead to the total spent per Step)
Here in the second tab, I tried to recreate the key with an automation. Now for the rollup field, it asks me to link the other table. Now when I link the key (mail-step) to the invoice table, this destroys the formula for concatenation of mail-step. So I am not sure how to link the customer overview to the invoice table.
Thanks for your help!
Do you need to create it as permanent and dynamically updated, or you create it as report from time to time?
The easiest way to do it 'by hand' without automation and scripts, is just duplicate group field and turn it into linked field, to a new Table. New created table will be a list of values with links to all records, and you just create desired Rollup field. SUM(values), for example.
Also, you should check Pivot table extension.
in you case, after duplicating field, you might need to turn it first to static (Single line text) then - to Linked field. But I'm not sure, it depends on value types in formula.