Create Table that is an aggregation of another table (similar to group by)

[crankbit]

Hey everyone,

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?

Kind regards,

Marcel

[TheTimeSavingCo]

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?

[IbrahimAlfaraj]

To aggregate values from different tables you'll need to checkout the "rollup" field type! It's a actual field type 😉

Hope this helps 

[crankbit]

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!

[TheTimeSavingCo · Accepted solution]

Ahh hmm, check out this base

The idea is to create a table where each record represents a single email + a step, and then you can add a rollup field there which should get you what you want I think?

[Alexey_Gusev]

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.

[crankbit]

Hey Adam, 

again thanks for your time.

That is basically what I tried so far. Question being, when I setup the Lookup Field "Client Email by Step", it is empty at first. So how can I automatically assign a value to it? Did you do this with an automation or just manually?

Cheers,

Marcel

[crankbit]

Thanks for that insight 🙂 I am looking for an automated update indeed!

[crankbit]

What I tried now is to create the key not by formula but by automation. This I can then also create in the table to do the aggregation and then include the rollup. I think that should be it 🙂