Skip to main content
Solved

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

  • May 4, 2023
  • 8 replies
  • 269 views
C
Forum|alt.badge.img+3

 

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

Best answer by TheTimeSavingCo

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!


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?

8 replies

TheTimeSavingCo
Forum|alt.badge.img+31

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?

Adam, Airtable Consultant - thetimesaving.company
IbrahimAlfaraj
Forum|alt.badge.img+3
  • IbrahimAlfaraj
  • Participating Frequently
  • May 4, 2023

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

Hope this helps 

C
Forum|alt.badge.img+3
  • crankbit
  • Author
  • New Participant
  • May 4, 2023

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!

TheTimeSavingCo
Forum|alt.badge.img+31

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!


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?

Adam, Airtable Consultant - thetimesaving.company
C
Alexey_Gusev
Forum|alt.badge.img+25

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.

 

C
Forum|alt.badge.img+3
  • crankbit
  • Author
  • New Participant
  • May 5, 2023

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?


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

C
Forum|alt.badge.img+3
  • crankbit
  • Author
  • New Participant
  • May 5, 2023

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.

 


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

C
Forum|alt.badge.img+3
  • crankbit
  • Author
  • New Participant
  • May 5, 2023

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?


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 🙂

TheTimeSavingCo
Terms & ConditionsAccessibility statement