Re: Editing an existing record via an Airtable form…sorta-kinda…
Summary
People sometimes ask whether it’s possible to use an Airtable form to edit an existing record. Technically the answer is “no” because Airtable forms always create new records. However, a base can be set up so that records created by a form end up affecting records in another “mirror" table.
As with my first show-and-tell submission, this was inspired by a solution that I created in another thread. Long story short, the key to that solution was using two tables: one for collecting form data, and another that looks up that form data and fills out a parallel set of fields. Extending that to allow data from subsequent form-generated records to effectively replace older data in the “mirror” table only took a few more steps.
Table Setup
To build this system, the first table to make will be the [Data Entry] table. This is where the form data will end up. After deleting the default records, change the primary field to the Autonumber type, then set up the remaining fields for the data you want to collect. Borrowing from the thread that inspired this system, my example setup will focus around stats for football players:
Next add another table. This will become the “mirror” table, the main place to view all of the data that you collect. In my case I’ll name this [Players], but I won’t do anything with its fields just yet.
Back in the [Data Entry] table, insert a new field between the primary field and your first data field, and make it a link to your “mirror” table ([Players] in this example). Name it appropriately for your situation. In my case, I’ll call it {Player}.
Returning to the [Players] table, it’s time to start setting up the “mirror” behavior, so that the fields in this table are driven by what’s in [Data Entry]. However, not all fields will require the same setup. For example, I don’t plan on changing the first name, last name, or height of a football player once they’ve been initially entered. For those cases, I’ll create what I’m going to call a static mirror.
For each field in the [Data Entry] table that will have a static mirror setup, begin by adding a new field to the mirror table, setting its name to match the field it will mirror, and making it a Lookup field. In my case, I’m starting with the {First Name} field, so I’ll make a {First Name} lookup field in [Players]. It should find the automatically-created {Data Entry} link field that Airtable made, so all that you should need to choose is the field to look up. In my case I’ll pick the {First Name} field, then save the changes.
The {Last Name} and {Height} fields will also be static mirrors, so I’ll make them the same way.
{Weight} is the first field that I might want to “edit” via a form down the road, so for that I’ll make a dynamic mirror setup. One of the keys to this setup is consistent character length. If the data in the field that you’re going to be “editing” will vary in length, you’ll need to create a modified version to force that length to be consistent. However, in this case I can skip this step because I’m fairly confident the weight will always be three digits. :winking_face: I’ll walk through the consistent-length setup in another field. In this case, I can stay in [Players] and add a new lookup field that I’ll name {Weight Lookup}, pulling from the {Weight} field in [Data Entry].
To extract the weight from this, we’ll make a {Weight} formula field. Because the lookup may eventually pull from multiple records, they need to be whittled down to only the most recent. Again because we know the weight will always be three digits, we can use the following formula to extract only the latest weight:
RIGHT({Weight Lookup} & "", 3)
Now our fields look like this:
While it’s unlikely for a football player’s position to change over time, let’s say that we want to prepare in case that happens with the team we’re tracking. We’re using position abbreviations that can range from one to three characters, so in building this dynamic mirror setup, we need to pad those out to a consistent three-character length. For that, we’ll make a formula field in [Data Entry] named {Position Padded}. We only want this padded version if something was actually chosen in the {Position} field, so we’ll structure the formula like this:
IF({Position}, LEFT({Position} & REPT("*", 3), 3))
For any field in your base that might need this treatment, determine how many characters are needed to guarantee a consistent length for this padded string based on all the possible options for your data field, and replace each 3 in the above formula with that value.
Back in your mirror table, make a lookup field that pulls in this padded field you just created. Finally, make a formula field that matches the name of the field you’re mirroring, using this formula (changing the field name as appropriate) to pull only the last position from the list:
SUBSTITUTE(RIGHT({Position Lookup} & "", 3), "*", "")
We’ll make one last dynamic mirror setup for the {Speed (sec)} field. Because we can pretty safely assume that no player will go above 9.99 seconds (and if they do, we don’t want them on the team), we won’t need to do the extra padding steps. We’ll just make the lookup field in [Players], then a formula field with a formula similar to the {Weight} field, but based on a four-character length assumption:
IF({Speed Lookup}, VALUE(SUBSTITUTE(RIGHT({Speed Lookup} & "", 4), "*", "")))
Keep working through your [Data Entry] fields, setting up static and dynamic mirrors for each of them in the mirror table as needed.
Once that’s out of the way, what’s to be done about the mirror table’s primary field? I suggest making this a formula field that concatenates some unique and useful data from one or more of your other mirrored fields. In our case, we’re going to mash the player’s first and last names together:
IF(AND({First Name}, {Last Name}), {First Name} & " " & {Last Name})
The last thing to do in our mirror table setup is add a bunch of blank records to the mirror table. How many you add is up to you. You can add them in chunks, or add a ton all at once. As we move on, you’ll see why they need to be there.
Form Setup and Use
Now we’re ready to start talking about how a form fits into all this. Back in [Data Entry], add a form view. I named mine “Player Stats”. It should add all of the fields from the main grid view in the same order. Assuming it all looks good, click “Open form” in the form’s toolbar, which will open the form in your web browser.
Let’s say I’m entering data for a football player for the first time. Under “Player", I’ll choose the first “Unnamed record” entry that’s available in the list. I’ll then fill out the player’s first and last name, and any stats that I have. I won’t capture the full form here, but the top part will look like this:
Now I’ve got a second player to enter. Looking at the available entries under “Player”, the first entry won’t be “Unnamed record” any more, but should match the name of the player I previously entered. That’s because the name I entered gets looked up in the mirror table, mashed together via the formula into the primary field, which is now referenced by the refreshed form.
I’ll go ahead and enter the second player’s info, again choosing the next available “Unnamed record” before filling out the rest. In essence, choosing a specific “Unnamed record” entry tells Airtable which record in the mirror table I want to use to view the data that I’m entering. In other words, I’m assigning this data to be displayed in a specific record in the mirror table.
With two players entered, here’s how the tables look:
Now let’s say that it’s a month later. John Smith has dropped a few pounds and improved his speed, so I want to update his info. Opening the form again, this time I choose his existing record under “Player,” skip the name fields, and only enter data into the two fields that I want to modify: “Weight" and "Speed (sec)”. After submitting the form, the tables now look like this:
Here’s a sample base with no records that you can copy to your own workspace if you want to have a play:
Caveats
There are some key things to note about this system, some of which you may have already figured out while reading through this post:
- You can’t see the existing data for a given field before entering the new data. If that’s a deal-breaker, then you might need a more robust form system that integrates with Airtable to pull the existing data before you edit it. However, a possible workaround is to setup the formula for the mirror table’s primary field to include some of that info, so that you see it immediately when you choose the record from the list. In this football team example, I could alter the formula to make the primary field something like “John Smith: 5’8”, 160 lbs, QB, 4.60 sec"
- Because adding a “new” record via the form is actually forming a connection to an existing unnamed record from the mirror table, you need to periodically add blank records to the end of the mirror table.
- Using this on a base where the mirror table might potentially hold hundreds or thousands of records could quickly become cumbersome. There’s no shortcut for finding the next unnamed record, so you’ll do a lot of scrolling while entering new info.
Accepted Solutions
This is a very creative workaround, but it comes with some caveats as you mentioned. We came up with a solution that overcomes these limitations by utilizing the redirect feature in Airtable Form.
The main advantages in our tool:
- It allows users to see the existing data before editing.
- It automatically deletes the newly created record after updating the existing one.
- It doesn’t require setting up another table.
This is a very creative workaround, but it comes with some caveats as you mentioned. We came up with a solution that overcomes these limitations by utilizing the redirect feature in Airtable Form.
The main advantages in our tool:
- It allows users to see the existing data before editing.
- It automatically deletes the newly created record after updating the existing one.
- It doesn’t require setting up another table.
