Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 17, 2019 02:07 AM
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:
Solved! Go to Solution.
Mar 27, 2020 01:46 PM
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:
May 22, 2019 05:34 AM
Hi Justin. I too have a mirror system in place for one of my projects. Re: caveat #3 - I’ve found that the “keep sorted” feature helps with this. If you turn that on, it will keep all the empty records at the top of the drop down in the form. It’s made a huge difference for us.
Oct 04, 2019 02:46 PM
Thanks for providing this detailed work around, I got stuck implementing this solution as several of the fields I want to update are very variable length (e.g. address).
There’s a service FormNano that does more or less exactly what you want although it could use a bit of a polish
Nov 04, 2019 05:52 AM
Apologies for the delayed reply, but yes, an external form tool would also do the trick. I’ve used Jotform recently to do something similar. I posted this just to show what could be done natively within Airtable, even though it’s firmly in “hack” territory. :slightly_smiling_face:
Dec 11, 2019 01:46 PM
Hey Justin, thanks for your detailed answers. Alway super helpful. Do you know if in the future Airtable will move towards an easier way to do this? Thanks!
Dec 11, 2019 04:13 PM
No idea. I agree that it would be helpful to add editing as an option, but I’m just another user, so your guess is as good as mine re: when/if it’ll happen.
Mar 27, 2020 01:46 PM
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:
May 29, 2020 12:33 PM
I just created a similar system myself! I came to see if anyone else had and you have made a much more expansive and elegant system.
As always, good work Justin!
Mar 20, 2021 12:39 AM
This is very frustrating. Airtable team please help
Aug 04, 2024 01:08 AM - edited Aug 04, 2024 01:17 AM
2024 Update:
In my personal opinion, the quickest, easiest, cheapest (because it’s free!), and most bulletproof way to have a form that updates existing Airtable records is to simply use Fillout's advanced forms for Airtable. You wouldn't even need to prefill the form because it automatically pulls in all the record data that you'd like to see on the form.
Fillout for Airtable is 100% free and it offers over 100 advanced form features that Airtable's native forms don't offer, such as updating Airtable records from a form, displaying formulas & lookup fields & rollup fields & attachments on forms, allowing dynamic linked record filters (i.e. dynamic dropdown selections), the ability to have multi-page forms with conditional paths, the ability to create new linked records on a form, the ability to accept payments on forms, visually displaying as many fields as you want to see in a linked record selection list (including attachment fields), limiting the number of linked records that can be chosen, and much more.
I give a brief demonstration of Fillout on this episode of the BuiltOnAir Podcast.
p.s. If you have a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld