Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

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

Solved
Jump to Solution
3418 0
cancel
Showing results for 
Search instead for 
Did you mean: 
crankbit
5 - Automation Enthusiast
5 - Automation Enthusiast

 

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

1 Solution

Accepted Solutions

Ahh hmm, check out this base

Screenshot 2023-05-05 at 1.35.18 AM.png

Screenshot 2023-05-05 at 1.35.21 AM.png

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?

See Solution in Thread

8 Replies 8

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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

Hope this helps 

Hey Adam, thanks for your response!

crankbit_0-1683214446925.png

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)

crankbit_1-1683214547771.png

 

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

Screenshot 2023-05-05 at 1.35.18 AM.png

Screenshot 2023-05-05 at 1.35.21 AM.png

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?

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.

 

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

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

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 🙂