Help

Rollup from Multiselect - Expense Calculator

Topic Labels: Base design Formulas
376 1
cancel
Showing results for 
Search instead for 
Did you mean: 
treverr
4 - Data Explorer
4 - Data Explorer

I am trying to create a tracker for trip expenses. I have a list of Attendees in one table, then Receipts in another with a "Charge To" field (since sometimes expenses wont be shared evenly across the group) that is a link to the Attendees table. What I would like to do is auto-calculate from the "Charge to" field on the Attendees table in a "Amount Owed" field.

So basically I am looking for a way to look at "Charge To" see if the Attendee line is there, if so, get the the length of the number of attendees and divide by it and add it to the amount. I can add some columns on the Receipts table if I need to for the amount per person but the thing I cant figure out is how to check the multi select if the Attendee line is in it. Any suggestions?

---- Tables Set up ----

Attendees:

Name (Single Line Text) | Total Owed (?) 

Receipts:

Date (date) | Total (currency) | Charge To (multi select [Attendees::Name])

1 Reply 1

Try this:
1. In "Receipts", create a "Count" field to display the number of Attendees
2. In "Receipts", create a formula field that will divide the value from "Total (currency)" by the value in the count field mentioned above
3. In "Attendees" create a "Rollup" field with the formula `SUM(values)` to add up all the values from the formula field in the previous step

If you invite me to your base I could set it up for you real quick too