Linked record counting

[ReDeGarmo]

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!

[TheTimeSavingCo]

Ah, you're going to need a junction table for this I think.  Here's something I threw together:

Link to base

The idea is to create one new record link between an Event and a Contractor, resulting in that "Event <> Contractor" table.  From there you'll be able to make rollups to get the total number of hours and calculate the pay

To make sure the "Event <> Contractor" table is always updated, I made a formula field that'll basically create the record titles for us, that's the 'Text for pasting' field, and you can see it outputs "Event A - Jerry,Event A - George,Event A - Kramer"

I then have an automation that'll trigger when that field gets updated, and its action is to paste the value from that formula field into the linked field to "Event <> Contractor", which creates the records for us