I don’t think what I describe below is possible in Airtable. I’d love to be wrong. I’ve looked into junction tables, but I still don’t think that will solve this issue. If you have thoughts, please let me know. I’d be very happy to break down my description below into more digestable questions…I just don’t know where to start…
We are a company of 40+ structural engineers. Each engineer is professionally licensed in 1 or more States and needs to earn Professional Development Hours (PDH) for each State. Each State has specific requirements. To track all of this, each engineer currently has 1 unique excel file. At the top of each engineer’s excel file there is 1 unique row for each State where this engineer is professionally licensed. Each row also contains related data that is specific to that engineer and State:
At the bottom of each engineer’s excel file there is a list of seminars that he attended in order to earn PDHs. Each seminar row contains related data that is specific to that seminar.
If the engineer attended the Seminar, they add it as a row to the bottom of their excel sheet. Once added to the excel sheet, a calculation occurs: if the Seminar “Event Date” occurred in the timeframe between “Previous Renewal Date” and “Renewal Date” (from the top section of the excel file) then the “PDH” value for that Seminar is summed in the “PDH Earned” field for that specific State. The PDH value from a single Seminar can be counted multiple times (for every state that it satisfies the Date criteria), but it can only be counted once for each State.
As you can imagine, 40+ excel files leads to a lot of variations on things that should be the same. It’s also annoying i.e. copying/pasting the same seminar row to 40+ excel files.
Is there a way to have a single table for Seminars (with all related data), AND just one other table that captures ALL unique rows (engineer & state combo) while still performing the PDH calculation based on Date and engineer’s attendance? I can then group based on Engineer Name or State.
OR…do I still need 40+ tables? Am I thinking about this all wrong? Is there a simpler way?
I’m open to suggestions! Thank you very much for the help.
