Help

Re: Updating An Existing Record via Airtable Form

16771 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Kayla_Higginbot
4 - Data Explorer
4 - Data Explorer

Hi! I am brand new to Airtable (and admittedly, brand new to the tech world in general), so I wanted to share my first project and get some feedback/suggestions.

Some background on the project: My dad’s company is built almost entirely out of google drive and he is wanting to start transitioning over to Airtable to automate some processes that are currently being done tediously by his HR team. One of these tasks is keeping employees’ contact info updated regularly. Right now, this is being done by HR reaching out to employees individually to see if their info has changed and then manually updating in google sheets.

Current Project: Import current employee info into new Airtable base and set up a form to be emailed quarterly to all employees which will allow them to change any information and update their record automatically once this form has been submitted.

Step 1: I imported data straight from google sheets into Airtable to create my Employee Info base. This google sheet had a lot of information (about 50 different columns) so I created a view that would hide everything aside from the contact information that I am wanting to keep updated (name, personal email, company email, address, phone number)

Step 2: I created a form for the contact info in a separate table (I used the exact same column headings and titled this table “Form Submissions”) and used the app SendGrid to create an email template that would pull data from the base and send a unique email to all employees with a pre-filled form. I chose to have the form pre-filled for two reasons: 1) if an employee’s information has not changed, it will be easier to review their current information and submit vs having to re-type all of the same information and 2) I’ll be using their company email (the one data point that will not change and is unique to each employee) to link the new record to their existing record and I want to eliminate the possibility of typos interfering with this automation. Here’s the email I’ll be sending:

Screen Shot 2021-05-02 at 9.00.10 AM

Step 3: To have the form submission update the existing record, I set up the following automation:

  1. When a new record is created in the table “Form Submissions”

Screen Shot 2021-05-02 at 9.18.36 AM

  1. Find a record in table “Contact Info” where “Company Email” contains “Company Email” from Form Submissions

Screen Shot 2021-05-02 at 9.19.36 AM

  1. Update the matching record in “Contact Info” on all of the fields within the Contact info form

Screen Shot 2021-05-02 at 9.20.19 AM

Step 4: After excitedly demonstrating my project to my dad, he pointed out that we need an easy way to tell who submitted the form and who ignored the email. I decided to add a time stamp to the form so we could see when it was submitted, thinking I could add this as another field to update automatically in my Contact Info table and replace the timestamp from the last time they submitted. However, because this is a computed value, I wasn’t able to add it to the automation to update in the record. Instead I ended up using the “link to another record” option to link my Contact Info table to my Submissions table and added a look-up column for the time stamp (which I renamed “last updated”). From there I was able to create a view to filter the records that had not been updated within the last week so it would be easy to send a follow-up email to those employees in a few days.

Screen Shot 2021-05-02 at 9.35.49 AM

And that’s it! I thought this was a fun and tricky first project to help me learn my way around apps and automations within Airtable. I got to experience the high associated with seeing the green checkmark informing me that my test ran successfully (after many, many failed tests beforehand) and let me just say: I get it.

I’m open to any feedback/suggestions for anything that could be done differently to make this project simpler or more efficient. And additionally, any suggestions for fun projects I can do to help me build more skills within Airtable!

Thanks for taking the time to read :slightly_smiling_face:

Kayla

28 Replies 28
Bill_Hughes
4 - Data Explorer
4 - Data Explorer

To you, I say, “great job.” To Airtable. I say, what…?!?

I’m a huge Airtable fan, but I can’t believe this incredibly common use case is not supported. It’s ridiculous to require such Herculean efforts. Please address this as soon as possible.

ScottWorld
18 - Pluto
18 - Pluto

Yes, it would be great for Airtable to offer this functionality!

As I mentioned above, Fillout's advanced forms for Airtable is currently the best way to update Airtable records from a form! It's easy, quick to setup, powerful, and free! 😀

Also, if you want to get even more advanced and create a portal for your customers that lets your customers log into a website and only see their own Airtable records, there a number of great Airtable portals on the market.

Some popular Airtable portals are: Noloco, JetAdmin, Softr, Pory, and Glide.

I give a brief demo of Noloco on this Airtable podcast episode. And I gave an entire one-hour webinar on Noloco called Building a Client Portal on Noloco powered by Airtable. Also, the CEO of Noloco gave a demonstration of his product on this BuiltOnAir podcast episode.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

Hi,
Im trying to do something similair to the original creator, except in my case there is a good chance that there will be duplicate records. This is because it is a list of members that enter their name and email into a form that puts them into the “master list” of members. It is likely someone will mistakenly fill out the form twice therefore creating a duplicate record that would cause the record updating automation to fail. Do you know of any ways to prevent duplicates from being created or another work around to this issue?

Typically, the best workaround to this would be to collect your forms in another table, and then upon form submission, use Make's Airtable automations to search for pre-existing records in the original table.

If a pre-existing record is found, then it could update the original record with the newly-submitted information from the form.

You could also use Make's special "Upsert Record" module for Airtable, which either creates a new record OR updates an existing record, depending on whether or not a record was found.

Take a look at the screenshot below for how this might look in Make.

(If you’ve never used Make before, I’ve assembled a bunch of Make training resources in this thread.)

p.s. If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

Screenshot 2024-04-21 at 10.07.08 AM.png

Joe_Hewes
7 - App Architect
7 - App Architect

What i do is prefill the form with the record Id of the record that you want to update. You can hide the field in your form with the id in when creating the prefill form url. When you run the automation after submission, update the record that matches the id.

Do you have a workflow for this? Thanks.

Sure, take a look at my screenshot above in my previous post! 😃

Sean_Comerford
5 - Automation Enthusiast
5 - Automation Enthusiast

This is an incredibly smart and well-written walkthrough, thank you for this @Kayla_Higginbotham, I’m excited to try your approach.

As is so often the case with tools like these – as helpful as they otherwise are – some of the most common sense, routine practices are unaccounted for from the word go and continue to go unaddressed for years. A data-driven platform without a native function to maintain or update data is like an airplane without landing gears.

I tried that, but I don’t see any way to hide the record ID field in the form. And if I remove the field from the form but keep the prefill, that field doesn’t update on submit.

Can you show what you’re talking about?

Here’s some xtra info:
You set the hidden field in the url string - here’s the announcement post for the feature: New Feature: Hide Form Field by URL Parameters
Plus more info in the docs New Feature: Hide Form Field by URL Parameters