Update record in another Table and change value of multiple select field

[FaH]

Hi all,

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.

Thanks!

[TheTimeSavingCo]

Hi FaH, could you provide some screenshots and examples of what you would want to happen? Once I know exactly what you’d like to happen I can try to figure out how to automate it.

Specifically, it would be super helpful to see:

1. Table B with a bunch of sub-task records
2. Table A, and exactly how you would want it to look based on the data in Table B at that point of time

---

In the meantime: I don’t really understand what we’re trying to do here, so bear with me

It sounds like you want the Parent Project record’s multiple select field to be updated to display data from its Sub-Task records.

Are the Sub-Task records linked directly to the Parent Project records via a linked field? If they are, can we use a rollup / lookup for this instead?

[FaH]

Airtable

Untitled Base - Airtable

Explore the "Untitled Base" base on Airtable.

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:

Hope this is clarifying my request a bit.

[TheTimeSavingCo]

Hey FaH, thanks for the screenshots and the example base!

I see what you mean about not being able to set the Status at the beginning if we use a rollup/lookup to display the status. Usually when faced with this issue, I end up with three fields:

1. One rollup/lookup field
2. One field where I can set the value manually
3. One formula field that will display either the field from bullet point 1 or the field from bullet point 2 which I use as the primary reference, resulting in something like this:
   

I see that you made a lookup field where you filtered it by the start and end date, and was wondering if we could filter by whether the Calculation field’s value was “Active” instead?

I’ve made a version with the changes listed above here for you to check out

Looks like you’ve basically solved this problem yourself, really

[FaH]

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?

[TheTimeSavingCo]

Hey FaH, to handle this I would recommend you change the field type from Lookup to Rollup, and use the formula ARRAYUNIQUE(values) like so:

I think this should solve your problem!