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

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?

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.

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

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?

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!