Events with multiple staff and varying costs per worker


#1

Hi, I’m loving Airtable and it’s already doing plenty for me that is good :grinning:

I’d like to take my existing Base a step further. I have two key tables: Events (rows are unique bookings, with dates and times) and Staff (rows of unique persons). I allocate varying numbers of staff to each booking in the Events table. What I’d like to do is calculate how much is spent on staff costs per booking - the tricky thing is that each staff member will have a different cost per booking. This cost is given to me by a colleague and includes pay+expenses+bonuses.

How do I do this in the most simple/efficient way, using data that is already in existing tables (the booking by ID/date and the multiple staff linked to in the Staff table)?

All thanks


#2

If the payment is on a per-event basis, you could just add a Rollup field and sum the costs. If the payment is based on number of hours for instance, you will need a junction table: https://support.airtable.com/hc/en-us/articles/218734758-A-beginner-s-guide-to-many-to-many-relationships#junction


#3

If you’d be willing to share, I’m trying to create a schedule for a theatrical production (similar), but want to consider the “conflicts” the actors have that won’t allow them to work/rehearse on specific days. Have you done something similar?


#4

This is great. I worked out how a junction table could help and I’ve added one in and can confirm that I have a solution! Thanks very much for pointing me in the right direction.


#5

Happy to share, but I haven’t got the feature you have described in my tables. I don’t have anything to represent/record availability/unavailability of personnel. I only allocate them to events after I have their confirmation of acceptance (outside of Airtable - usually by email). Good luck with your project!