Blank fields in the UPDATE source table overwrite existing data in main table with Update Record

[BillGateways]

Hi All

My apologies if this is too simplistic - I’ve only got six months or so experience with Airtable…

I suspect use case doesn’t matter here, since my “issue” is at the record and field level. Here is the scenario:

- a form with some fields pre-filled is sent to a Client for additions and updates, is submitted by them, and creates a new record in an UPDATE table, separate from the main, “real” table; Clients often have no regard for “garbage in, garbage out”…

- a manual review of the two tables “side-by-side” is performed to make sure data in each field is the type of data expected - both in text and attachment fields

- an automation does an “Update Record” on the main table, using the data from the UPDATES table record, hence moving the new data to the Client's existing record in the main table

The result is that blank fields in the UPDATE table overwrite existing data in the main table. BAD RESULT! At present, ALL fields are included in the update record automation, since there is no way to know what data will be updated by the Client.

Writing a separate automation for each field is not possible, given ONE table has 50-60 fields and Airtable’s 25 automation per base limitation (seems restrictive…)

I think I have seen an “entire record to list” buried somewhere in the field selection part of the automation area, but it has always been greyed out. Not sure how/if that would help…

Is there a methodology, automation, or script that anyone has developed that they would be willing to share that would address the “don’t overwrite the field if it is blank in the source data” issue?

Thanks in advance. Again, my apologies for the “newbie-ness” of this.

[TheTimeSavingCo · Accepted solution]

Hmm, not too sure what your business case is, but if you're okay with skipping the manual review and allowing customers to see their existing data, you could solve this with Fillout pretty quickly probably (https://www.fillout.com/).  The bit you mention about only having some of the fields pre-filled makes me feel like you don't want customers to see everything they've currently got though?

---
A non-script option might be to:
1. Link the update record to the main record
2. Create a lookup field per field you want
3. Create a formula field per lookup field that outputs the lookup field value if it exists, if not it outputs the original field's value
4. An automation that runs and copies the value from the field from step 3 into your original data field

[ScottWorld]

@BillGateways 

Most people are solving your problem by using Fillout’s advanced forms for Airtable, which lets you update Airtable records from a form with no automations and no overwriting fields.

Fillout is 100% free and offers hundreds of features that Airtable’s native forms don’t offer, including the ability to update Airtable records from a form, display Airtable lookup fields & Airtable rollup fields & Airtable attachments & formulas on forms, dynamically & conditionally filter linked record fields by any values that you would like, perform math or other live calculations on your forms, accept payments on forms, create multi-page forms with conditional paths, create new linked records on a form, display as many fields as you want to see in a linked record selection list (including attachments), connect a single form to dozens of external apps simultaneously, limit the number of linked records that can be chosen, set advanced field validations & limitations, upload an unlimited amount of attachments simultaneously, add CAPTCHAs to your form, add choice matrixes to your forms, direct integration with hundreds of apps like Calendly & Google Maps on your forms, and so much more.

Hope this helps!

— ScottWorld, Expert Airtable Consultant

[BillGateways]

Hi Adam @The Time Saving Co

This looks very doable, and within my present skillset. I will do a test run on one of the smaller tables first, before I commit to literally hundreds of fields!

Thank you!

Bill

[BillGateways]

@ScottWorld

Yikes! Sounds like a lot for free - I assume they have a similar graduated use fee structure like most everyone else. I will definitely check them out.

Thank you!

Bill