This would require a scripting action. Other actions in Airtable automations do not allow for this type of logic.

Your #1 best bet for this is to use Fillout’s advanced forms for Airtable because it communicates directly with Airtable and can easily do everything that you requested.

Fillout lets your customers update their own profiles using a form, without any of the limitations & restrictions that you were running into in Airtable.

Fillout is 100% free, and it offers hundreds of features that Airtable’s native forms don’t offer, including the ability to update Airtable records using a form, create custom PDF files from a form submission, accept payments on forms, pre-fetch dynamic data from an Airtable record, customize the style and branding of your form, customize a theme for your form, display Airtable lookup fields on forms, create new linked records on a form, add a login page to your form, perform math or other live calculations on your forms, collect signatures on a form, create multi-page forms with conditional paths, connect a single form to dozens of external apps simultaneously, add CAPTCHAs to your form, and much more.

I show how to use a few of the advanced features of Fillout on these 2 Airtable podcast episodes:

• Using Fillout to update an existing Airtable record & create an eSignature approval process with PDF file creation.
• Using Fillout to create an order entry form with line items.

Hope this helps!

If you’d like to hire the best Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld