When to leave a field alone - Update Record

[Deko]

Does Airtable still not have the ability to keep data when updating a record?

For example, when using automation, if the form field is left blank and is submitted it wipes data from the record that is being updated.

I have heard you can do this with scrip, but I can’t write the script and I am on a free plan.

[Kamille_Parks]

I don’t believe I understand exactly what you’re asking for. If you don’t want an Automation to adjust a record’s field value for a particular field when using the “Update record” step, just don’t “add” the field to the step’s configuration.

[Deko]

I want the user to have the option to update any field. I am creating a wiki-like database where users can contribute information, therefore all fields need to be present just in case data needs to be corrected.

[Kamille_Parks]

Are you talking about a form or an Automation? Is your form prefilled with the “original” field’s data? If not, why?

[Deko]

I am talking about forms and the “update record” automation.

I was hoping that I didn’t have to use a prefilled form because of text limits and how massive the link is going to be.

If a user left a field blank when updating data with the form and the automation ran, the automation erases all data in that field and makes it empty. Then that data would be lost.

I am wanting the automation to look at the form field and if it is blank, then preserve the data that’s already in the table instead of erasing it. In other words, don’t update the field in the table if the relevant form field was left blank.

Does that make sense?

I have a feeling I am just going to have to update everything manually still.

[Justin_Barrett]

I think I get what you’re talking about. I built a system (that isn’t actually being used, sadly, but it works) to allow records in a primary table to be edited via an Airtable form. That form saved data into a secondary table with matching fields. That secondary table also linked to the primary table (so I knew which record to update). To handle the update, I made a bunch of rollup fields that contained more-complex-than-normal aggregation formulas that would determine whether to use the form’s new data (if there was any) or the existing data. Those rollup fields were then used to update the primary table record. If someone made changes to a given field, the rollup would output those changes and overwrite the original data. If the form field was left empty, the rollup would output the original data from the linked record.

[Deko]

Hmm, I didn’t think to use rollup fields. I am still trying to figure out how to use those properly. But that is exactly what I was talking about.

[Justin_Barrett]

Rollup fields rely on linked records. In most cases they’re used when each record in [Table A] links to several records in [Table B], and you want to pull data from specific fields in [Table B] based on those linked records. For example, say you have [People] and [Companies] tables, with each company linked to several people. By adding a rollup field to the “Companies” table that rolls up the names of all of the linked people, you can see them all in one place.

Anyway, rollups can also be quite useful when there’s only a single linked record. In this situation, each record in the table populated by the form should link to the record in the primary table that you want to update. This link could be made by the user (cumbersome) or prefilled when they open the form to make their changes. To prefill the link, pass the ID of the target record using Airtable’s prefill terminology in the URL. More on that here:

Airtable Support

Prefilling a form

When making an Airtable form, you can use prefill_ parameters in the URL of the form to prefill specific fields. Note that this feature cannot be used to edit information that already exists in you...

(@kuovonne has created a slick “Prefilled Forms” app that will build the formula for you to prefill any fields that you want, but being a custom app it won’t work on your free plan. Still, keep it in mind if you’re able to upgrade later!)

With the link established, the “values” keyword in the rollup field aggregation formula refers to the array of collected data from the linked records. As an example of how to use that, say that your main table has a {Name} field, and you’ve made a similarly-named field in the table where the form data saves. Create a rollup field that points to the {Name} field from the primary table and uses this aggregation formula:

IF(Name, Name, values & "")

Concatenating “values” with an empty string is required to force-convert the array into a string (“values” will always be an array, even if there’s only a single linked record). If any of the fields that you’re updating aren’t strings, you can apply an appropriate conversion for their original types. At any rate, that formula will cause the rollup field to output the new name if one was entered and the original name otherwise. Using that rollup field in the automation update step to update the original record (which also uses the link to get the record ID) will effectively leave untouched fields as they are, and apply changes to any modified fields.