Hello,
My subject might not be entirely accurate. Bangin my head against a wall for a few days now.
I haven't been able to find the solution to my problem here, and I feel like there really must be one. I'm trying to come up with a way to quickly calculate what individual contractors should be paid for events, and display as a grid or list or interface that is easy for the person entering "payment requests" to read. This is currently done by coping over the information into an excel spreadsheet to do the calculations and then reentered into a payment request spreadsheet...but I already have all the information here in airtable
I have a table of "events", and table of "sessions" (an event might be made up of 1 or more sessions/ days) and a table of "contractors". I assign contractors to "sessions" through linked records. A contractor may work 1, 2, or all "sessions" of an "event". The sessions have a set number of hours assigned to each, and I've got a payment formula.
First I set-up a table for payment calculation where I tried to start with a link to the contractors, but I still had to then link each session (and I'm trying to make this less work than the current excel sheet). Then I changed it so I select the "event" and the "contractors assigned" is a lookup. This will return all contractors assigned, and it lists duplicates if the contractor was assigned to multiple days. Is there a way to return the number of times each contractor's record appears in an event? or maybe a totally different way to go about it?
Ultimately, I want a list that resembles
Event A
John Smith - $411
Jane Doe - $411
Harry Potter - $336
Event B
John Smith - $411
Joe Shmo - $75
Jane Doe - $75
To give to another person to enter. Where the payment has been calculated by - (# of sessions * session hours * pay rate) + (training hours * training rate), which I would like to all be "looked up" automatically from the linked sessions.
I can provide more details, but I feel like that was already a lot. I know there has to be a way
Help! Thanks!
