Use a summary value in a formula

[viger]

Hi all! :slightly_smiling_face:
I have a question, I searched a lot and it seems not possible with Airtable, but I try anyway…

Scenario: plan the wedding

• in one table all possible guest, in one field their participation to the lunch with a multiple select
• in another table all restaurants, with in one field the price per person, in another the number of participants and in the last the total amount ({price per person}*{participants})

With my knowledge at the moment, it seems impossible to calculate in an automatic way the number of participants and use this value in the formula to calculate the total amount. I’m using the summary bar to see the total participants in a dedicated field (“Count essential”) and I manual insert this summary in the lunch table.
Any possibility to do it in an automatic way?
I know that Airtable is not a spreadsheet but a relational database, but a similar function seems to me useful for a relational database, too. And not only for wedding! :smiling_face_with_sunglasses:
Here the link to the sample:

Airtable

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

[W_Vann_Hall]

Take a look at this version of your base. (This is a read-only share; duplicate it to your own workspace so you can examine field configurations.)

Airtable

wedding - Pala edit - Airtable

Explore the "wedding - Pala edit" base on Airtable.

The secret to performing a multi-record calculation whose result you can then use in another table’s formulas lies in creating all-to-one links between the records in your main table and a single record in a second, calculation table. I’ve written about this technique in more detail elsewhere; suffice to say in this instance I have named the single record in the [Calculations] table '.' — that is, the period character and have linked every record in both [Guests] and [Lunch] to this record.

In [Calculations] you will find {Name}, the primary field; reciprocal linked-record fields pointing back to [Guests] and [Lunch]; and a rollup field, {#Guests}. This last field links to [Guests] and rolls up {Count essential} with an aggregation function of COUNTA(values). This counts the number of records whose {Count essential} field is not empty.

In [Lunch], I’ve reconfigured your {Participants} field to be a lookup field retrieving the value of {Calculations::#Guests}. Now, as additional R.S.V.P.s arrive and your number of guests increases, the changes will be reflected automatically in [Lunch] —

— assuming you’ve remembered to link any new [Guests] records you add to [Calculations]. You can do this in any of several ways:

1. Through Airtable’s traditional method of linking records; that is, select the plus sign ('+') in the {Link2Count} field and select the single defined record in [Calculations].
2. By selecting the fill handle — the small white box that appears in the lower right-hand corner of the field — in the {Link2Count] cell in the bottom-most record configured to link to '.' and dragging it to the last record in the table.
3. By selecting one of the configured links to the [Calculation] table, pressing Ctrl-C to copy its value, marking [Link2Count] of all of the currently unlinked records, and pressing Ctrl-V to paste the value into each of the marked cells.

If you have questions, don’t hesitate to ask — oh, and congratulations! :winking_face:

[viger]

@W_Vann_Hall Wow, thanks! :grinning_face_with_big_eyes:
Your is a true guide, thanks for your time.
I followed (and tried) your linked post as well the explanation for the modified base: of course it works like a charm! I need to work more on it to well understand the logic because, as you say in your post, it’s very important. I understand that this process (in very raw words) is able to transform in “horizontal” what is “vertical” and Airtable works in horizontal way, so the gap with a pure spreadsheet is exceeded. I already encountered this problem and I solved with a raw workaround, but in this way the target is reached in the right way. Yesterday I spent a few hours searching a solution (of course not for the wedding but for the knowledge, we haven’t so many guests! :joy: ), now here it is.
I’m new to Airtable and it is very impressive, but it must be approached with the right attitude, and sometimes it appears hard or strange because the mind is more “Excel oriented”. Airtable is just different, but very powerful (if well used).
So, I’ll follow to study and I’ll review my wedding base and my work base with this process, thanks again.

W_Vann_Hall:

oh, and congratulations! :winking_face:

:grinning_face_with_big_eyes: :bride_with_veil: :man_in_tuxedo: :tada: :balloon: :confetti_ball: :clinking_glasses: