I have two tables in my base: Table A and Table B.
Table A contains main projects (“parent projects”) for sub tasks in Table B.
Table A has a multiple select field (STATUS) with five options.
Each Status has its own sub task and is associated with the parent project in Table A. Each sub task has a START and END date [and I have a formula that determines if a sub task is “ACTIVE” (e.g., START is in the past and END is in the future); while that can also be handled using the filter option in the view itself].
Now I want Airtable to go and adapt the multiple select field STATUS in Table A based on the currently active sub task (or status respectively) but I cannot figure out how to do that in case of 40+ projects.
I thought about an automation that runs once daily or a “when record matches conditions” but I am unable to think of the correct approach afterwards since I have no programming background unfortunately.
I hope my question is comprehensible and I’d appreciate any feedback if my envisioned automation is even possible at all.
I tried to depict it here in this base and also add some screenshots for a better description:
In Table B we can see which of the subtasks are currently ACTIVE. Based on the Status of the ACTIVE subtasks, I want Airtable to change the Status in Table A accordingly (see blue text in 2nd screenshot).
It should be noted that the user can choose any Status for a new project in the beginning but this may change later on depending on the added START and END dates. So I think the LOOKUP approach would generally work, but then the user could not choose a Status for a new project in the form upfront. This might be a good fallback option for me in case it does not work the way I imagine it to work. :winking_face:
Yeah I think I found a sufficient work around but you thanks for nudging me into the right direction.
However, now if I have (by accident or for whatever reason) two or even three active “Status” (since the dates are all set in todays range) is there a way to use the formula field to tell them to only display one of the three status?