Script for If/Then across multiple Tables

[Brett_Smith1]

I believe I need a script rather than a combo of filtered views/linked fields/conditional rollups to accomplish my goal:

I have 3 tables: Employees, Seminars, Registrations

I’m trying to populate a field in the Registrations table with a sum of values contained in the Seminars table IF certain conditions are met when compared against other fields in the Registrations table. Below is my best effort at writing this out…obviously not actual JavaScript or Airtable syntax, but hopefully its a start. Tables are bold, fields are italic.

Registrations PDH Earned is sum of Seminars/Webinars (PDH)

IF

Seminars/Webinars Attendees contains Registrations Employee

AND IF

Seminars/Webinars Date is (>= Registrations Previous Renewal Date AND <= Registrations Renewal Date)

[Justin_Barrett]

Welcome to the community, @Brett_Smith1! :grinning_face_with_big_eyes: I’m glad that I searched before replying to your original thread:

Base Design for Many to Many? Base design

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…

You categorized this new thread under “Formulas,” but unfortunately Airtable formulas can’t directly access other tables. In fact, they can’t even access other records. A formula can only directly pull data from other fields in the same record. Each record runs the same formula, but the output for any given record is specific to the data pulled from fields in that record.

However, what you said at the top is correct:

Brett_Smith1:

I believe I need a script rather than a combo of filtered views/linked fields/conditional rollups to accomplish my goal

Only scripts run in Airtable’s Scripting block can perform the detailed operations needed to pull off what you want. I’d be happy to help put a script together for you, but because of the time required for development and testing, I’d need to take this on as a client project. If that sounds good, message me directly and we can talk further.