https://airtable.com/apprdPguzaBCfjD56/shroyWamJGrG0yeMi
In the sample base above, I have two linked Tables. One is a list of projects, the other is information for a quarterly report. Currently, I am linking the tables to each other manually when updating project start and end dates. For example, if I add a new project scheduled to start today and end 3/1/2024, I'd add a new record with a start date of 11/30/22, the end date of 3/1/24, and then link it to the "2023 Q4" and "2024 Q1" records from the second Table. Projects don't often (if ever) end when planned, so I come back and update the end dates often which means I also have to go back to check the linked Quarter records to make sure they're still accurate. This seems like something that should be easily automatable.
I don't necessarily even NEED the Quarter Table. There is no other information about each quarter that I collect. Ultimately, I just need to be able to graph the data points out to count how many projects were in process each quarter. I can easily do this with the linked field, but I'm not sure how to do this using the "Projects copy" version that has a separate formula field for each quarter. If there is a way Airtable can do that, I can stop manually linking projects to quarters, drop the automation piece altogether, and just rely on the new-field-for-every-quarter option.
---------
You can problem stop here and answer, but if it helps, here are automation solutions I've tried:
Find Records doesn't have the ability to pull records based on dynamic dates, so given the example above, I can't say "use the start and end dates for the new record to find records from the Quarters Table that overlap and then update the project record".
I also can't use a Repeated Group block within a Conditional Logic block to first use Find Records to pull ALL quarter records and then say "for each Quarter Record..." and use conditional logic to compare the start and end dates between the project record and each quarter record and then update the project record with the matching quarter records.
I can't use a Conditional Logic block to hardcode the quarter start and end dates to match Quarters to Projects because I have to select a record to link to and can't even have it enter "2022 Q4" into the linked field (which would ideally link it to the 2024 Q1 record if Airtable allowed it).
All of that led me to what you see on the Projects copy Table in the example where I created a new field for each quarter. A formula checks to see if the Project start/end dates fall within the start end dates for the quarter that field represents. But I'm stuck at figuring out how to use those Xs to automatically link the project and quarter fields to each other...
I thought I might be able to use "When a record is updated" and "watch" all of the quarter fields in the Project record. Then Find Records from the Quarters Table that matches the same name as the field that was updated, but you can't actually use which field was updated function.
Finally, I tried using a conditional block, one for each quarter. If 2022Q1 has an X, find the Quarter record that starts on 1/1/2022, then Update the Project record that triggered the automation to include the record it just found. In theory, this does what I need. I duplicated the condition group, once for each quarter. But I think the problem is that it runs through the automation so quickly, that instead of adding the additional linked field, it replaces what was there. I keep ending up with just one linked field regardless of how many X's I have (how many quarters the project spans). Additionally, I'm not sure to handle when a record changes later. Essentially, the automation would need to first wipe out whatever links are already there and then check each quarter again to rebuild the links.
