Automation involving two tables

[Georgie_Cumming]

I’ve tried multiple times and I’m still stuck so I’m hoping this community can help.

I have a bunch of tables in a base. The main one is called Leads & Clients. There is another called Waitlist which also has a form view. LC has a field called ‘status’ and Waitlist has a question asking ‘have we worked together before?’

I have it set up where a new form submission triggers an automation that creates a new client record in LC if one doesn’t already exist for that client or it just updates one that’s already there. And the automation links the two records across both forms.

This is where I’m stuck… I want the status in LC to change to ‘lead’ if someone says no on the Waitlist form to the question asking if we’ve worked together before.

Any insight?

[TheTimeSavingCo]

Hi Georgie, I’m assuming the Status field is a single select?

If so, I would put a rollup / look field in Leads and Clients for the Have we worked together before field, and I would then create an automation that would update the Status field if the lookup field has a “No” value or something

I’ve put a version of the automation together here for you to check out

It utilizes a rollup field with the aggregation formula sum(values), as it’s rolling up a checkbox field

[Georgie_Cumming]

Thank you Adam. The lookup + automation worked.

I had been trying to do automation only. Now that you mentioned lookup/rollup, I went to try and figure out the difference between the two and then realised lookup would work.

I’m all set now. Thank you!