Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 27, 2020 05:43 AM
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:
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
Mar 27, 2020 11:22 PM
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.
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.
Now in the Host table, rollup the {Cost Per Host} as a Sum.
Hope this helps.
BR,
Mo
Mar 30, 2020 06:42 AM
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
Mar 30, 2020 08:27 AM
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
Mar 30, 2020 08:45 AM
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
Mar 30, 2020 08:58 AM
@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…
Mar 30, 2020 08:59 AM
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?
Mar 30, 2020 09:24 AM
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:
Mar 30, 2020 10:41 AM
@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?
Mar 30, 2020 10:52 AM
I meant I‘d :slightly_smiling_face: not id.
Yes what you did is what I had in mind actually