Updating An Existing Record via Airtable Form

[Kayla_Higginbot]

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:

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”

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

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

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.

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

[Jeff_Sasse]

Can this be used with Multiple Select fields? I have attempted this and failed. Wondering if there is a workaround.

[Julian_E_Post]

I walk through how to do exactly what Joe is describing here, including prefilling the record ID and hiding it: How to collect wedding RSVPs with Airtable forms + automation - YouTube

[Mike_Shaw1]

Airtable would put some of those other companies out of business with some basic funtions, like forms to update a record, seems so basic. Other companies charge 2 to 10 times as much as air table itself for a basic function., Ridiculous!

[Shang1128]

Hi Kayla.  You did great job and this is exactly what I am looking for.

But I can't do like this.

See this.

I think, I did exactly same lik you. But when I try to test, I get error.

And I can't find Add Condition option in automation flow like you. Where is that?

Can you let me know what I am missing?

Thanks.

[Shang1128]

Okay, I did exactly what you did, but the submitted data is created into the original table. Not into a temporary table.

In your case, submitted data should be stored in the Form Submissions table. 

But in my case, submitted data is stored in the original table(In my base, the Candidates table is the original one, and Candidates_temp is a table for form submission)

Candidates and Candidates_temp table are with exactly the same titles and content.  I duplicated the Candidates table and named Candidates_temp for the duplicated table.

I think, when opening the URL for form submission, existing data needs to be loaded so that users can update their information properly, and it will be easily more than scratch.  So I created a form in the Candidates_temp table.  But in this case, if user submits a form, then the Candidates_temp table will have duplicated records because I didn't delete records from Candidates_temp table for loading existing data on submission form(user can edit information easily more than blank)

So I want to know exactly how you did for this case.

Did you understand what I am asking?

Thanks!

[Mike_Shaw1]

Looks like you need to add the Find Records action inbetween those first.

[Shang1128]

Ah yeah I see.

I was missing it.   Thank you so much! 😁

[tabletools]

I built out tabletools.io to solve this problem - check it out and join the Discord community if you have any feedback 🙂