Event Planning: Count of Guests and Calculate Prices

[Tobias_Brenner]

Hi Everyone,

I recently started using Airtable and I think the whole tool is great. I am now setting up a Base for planning a private birthday party.
Some background information: We are multiple people celebrating together. Everyone can invite people and we want to divide the costs as following: Fix costs (Location, Band, …) are divided equally to all hosts and variable costs (food, drinks,…) are allocated by the number of guests per host.

I have a set-up with the tables “Hosts”, “Guests” and “Costs”. The guest table includes all people invited with a link to who invited them. (and vice versa) Important here: Individual people can be invited by multiple persons, so the link is multi-select. The Costs table includes a column “Cost type” which groups them in fix and variable costs.

Now I need to calculate the price for each person. I know exactly how to do that in excel:

1. Sum the Fix costs and divide by the number of hosts
2. Calculate the price per person by dividing the variable costs and the number of guests
3. Calculate the number of guests per person. This includes “half” or “third” guests, if multiple Hosts invited the same person.
4. Calculate the variable cost per host by multiplying the price per guest with the number of guests.

Now this is quite a challenge for me in AirTable at the moment. I am using a rollup for the fix price, which is working fine. When it comes to the variable price though, I have now idea how to realize that. I can count the number of invitees per host by summing up the number of links in the Guests collumn, but this leads to me counting the people that have been invited by multiple persons in multiple guests which leads to a wrong result.

Does anyone have an idea how I could do that or change my approach?

I hope my problem is clear, I am happy to answer any questions you might have.

Thanks a lot in advance.

Best
Tobias

[Mohamed_Swella1]

Hey @Tobias_Brenner,

Welcome to Airtable Community and Happy birthday in advance ! :grinning_face_with_big_eyes: Sounds like a fun way to use Airtable.

Ok so here is what you will do.

1. In the Guests table, make the Hosts Field as a Link To Another Record and link it to the Hosts table. Use a Rollup Field to Count the hosts for each guest (lets call it {No. of Hosts}). Make a Formula that is 1/{No. of Hosts} , will call this {Cost Per Host}. This will calculate how much will each host be responsible for.

2. Now in the Host table, rollup the {Cost Per Host} as a Sum.

Hope this helps.

BR,
Mo

[Tobias_Brenner]

Hi @Mohamed_Swellam,

Thanks a lot for your solution. This is exactly how I did it in the end, so I am very happy that you confirmed that this is the best / only solution.

Best
Tobias

[Tobias_Brenner]

Hi @Mohamed_Swellam,

sorry, one additional question: To properly divide the costs to the hosts, I need to know the total no. of Guests in the Hosts table.

Do I really need to add an additional field in the Guest table “All hosts” and always link all hosts so I can sum up the total with a Rollup in the Hosts table or is there a better way?

Thanks in advance for your reply.

Best
Tobias

[Mohamed_Swella1]

Hi @Tobias_Brenner,

Well you can easily do that in the new rollup field that you have created. Use the Sum function in the bottom of the field.

BR,
Mo

[Tobias_Brenner]

@Mohamed_Swellam I am sorry but I don’t get it.
How can I sum-up the number of all guests using the new roll-up field that is summing up the number of guests per person?
I am sorry for missing something here…

[Tobias_Brenner]

I mean I can read the total number in the sum bar at the bottom, but I cannot use that value in a formular for calculating the costs, can I?

[Mohamed_Swella1]

Ah ok, now I get you :slightly_smiling_face:

Yes you cannot use this value to calculate the cost. (unless you use a script)

Scratch that …

I believe if I see the Base Id help you better :slightly_smiling_face:

[Tobias_Brenner]

@Mohamed_Swellam, I implemented the sum by adding a link to all guests in all the hosts (and vice versa) and then rolling up the name.
Is there a better solution then that? Also what do you mean with Base Id?

[Mohamed_Swella1]

I meant I‘d :slightly_smiling_face: not id.

Yes what you did is what I had in mind actually