Help

Re: Updating An Existing Record via Airtable Form

28363 7
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
ScottWorld
18 - Pluto
18 - Pluto

Welcome to the community, @Kayla_Higginbotham!!

Congratulations on your big success with Airtable, and thanks so much for sharing your success story in complete step-by-step detail!! 😃🙌  You are quickly becoming an Airtable guru!! 🤠 🏇

Your post can serve as a great tutorial for other people who are looking to build a similar solution for their businesses! 🌟

A few thoughts that I would like to add here:

#1. As of 2023, we can now use Fillout's advanced forms for Airtable, which is 100% free and is the easiest & most customizable & most bulletproof way to Airtable records from a form.

Fillout has become the best, quickest, cheapest (because it’s free!), and easiest way to update Airtable records with a form.

The reason that Fillout's advanced forms for Airtable are a better solution than Airtable's forms for updating records is because of the following reasons:

a) Fillout is free to use with Airtable!
b) Fillout's forms don't require any complicated prefill links!
c) Fillout’s prefill links remain static, so you don’t have to keep regenerating prefill links for the same Airtable record over & over again!
d) Fillout's forms don't require any automations at all!
e) Fillout's forms don't require you to add an extra table just to collect your form entries for updating! 😀

Fillout offers hundreds of advanced features that Airtable’s native forms don’t offer, including the ability to:
- Update Airtable records from a form 
- Display Airtable lookup fields & Airtable rollup fields & Airtable attachments & formulas on forms
- Conditionally & dynamically filter which linked records can be chosen, without any of the limits that Airtable natively imposes on this.
- Perform math or other live calculations on your forms
- Accept payments on forms
- Create multi-page forms with conditional paths
- Create new linked records on a form
- Display as many fields as you want to see in a linked record selection list (including attachment fields)
- Connect a single form to dozens of external apps simultaneously
- Limit the number of linked records that can be chosen
- Upload an unlimited amount of attachments simultaneously
- Add CAPTCHAs to your form, add expiration dates for your forms, add logins to your forms
- Add choice matrixes to your forms
- Integrate Google Maps and other apps onto your forms
- And much, much, much more!

#2. Airtable's limitations/nuances in automations

However, if you decide to stick with Airtable's native automations (instead of using Fillout) to update records from a form, there are a few limitations to be aware of with Airtable's automations:

- This shouldn't affect you, but if your "Find Records" action finds more than one record, you'll need to add a repeating group to loop through your found records & update each one of them individually. This will prevent you from getting error messages in Airtable.

- On that note, Airtable's "Find Records" action can only find a maximum of 100 records. if you need to find more than 100 records, I would recommend using Make's advanced automations for Airtable.

- Airtable's automations also have a few other limitations, such as the inability to combine repeating groups & conditional actions. In cases like that, you could also turn to Make.

- If you've never used Make before, I've assembled a bunch of Make training resources in this thread. (For example, here is how you would instantly trigger a Make automation from Airtable.)

Overall, though, you've got many great solutions at your fingertips and a variety of different ways to update records from a form! 😎🙌

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 

Thanks for the positive feedback and warm welcome to the community, @ScottWorld :slightly_smiling_face:

Hey Kayla,

Thanks for posting this. I agree with Scott, this is the best tutorial I have read thanks for shedding light on this topic. You’ve made it seem totally achievable.

Best
Isaac

I’d love to get your feedback on how to go about my project which is pretty ambitious I realise now.

I’m building a workflow from a deals table with 6 prefilled internal forms that update records in the deals table.

image

I’ve got some things right and others wrong. I can’t use emails since some customers submit multiple quote requests. I have used an autonumber and formula to create unique quote numbers. So that whether they are derived by google sheets or airtable forms they will follow the same format. Do you think this will suffice Scott?

I used a single select field in kanban view to track stages these trigger automation in zapier like creating draft quotes. I used airtable automations to sent instructions for new tasks and a link to the prefilled forms for example in the qualification call stage a message is sent to “sales channel” in slack. The message provides the first name, phone number and a link to the prefilled form. So that sales people can call the customer to confirm the details of the request. Once the qualified checkbox is ticked in this form. It will move to the design stage and “design channel” in slack will get a message with a prefilled form and so on through all the steps in our workflow.

The next step is following your tut to create those find and update record automation.

What’s your next project Kayla, looking forward to the next tutorial… If you’d like to collaborate on something I’d really love to build a content creation base.

Hi Guys, I wanted to give you an update on this because I reorganised my linked relationships today. Created an updates table by duplicating the existing table. There are so many records that need to be updated with the form that I hit an air tables automation limit felt like tearing my hair out at that point. I’ve come back to this thread now to get a refresher on how Kayla created a timestamp.
I’ll also share another tutorial I found today which has been a lot of help How to Update Airtable with a Form - YouTube

:exploding_head: Okay so this was 100x easier to build the automation with zapier which surprised me.
It’s pretty easy to setup but make sure to add a “last modified” timestamp field to your updates table then use zapier to build the same automation you planned to build with airtable automations. The advantage being that you can create 1 automation to do all these updates if you do it via zapier whereas in airtable you have to create many automations for the same effect.

PS really helps to have data inside your table so fill out a record will every field filled in before moving to zapier,pabbly,intergomat ect

Another pothole here and definitely needs a tip to overcome this one. I’m using a linked contacts table and I would like to update the contact details with the form how do I pull this off?

We dropped Zapier for Airtable after several years of dealing with Zapier’s slack integrations being super slow/buggy, constantly breaking, and their ongoing failure to support their product or maintain their documentation in any way.

I think they are pretty good and they are the only company that support an integration with xero so we had to go with them. There are plenty of other good options now that automation is more mainstream

 

If you're looking for Xero integration with Airtable, you will want to turn to Make's integrations & automations.
 
Some people turn to Zapier instead of Make, but I would not recommend using Zapier.
 
That's because of these reasons:
a) Make typically has deeper support for apps than Zapier does.
b) Make is INFINITELY more powerful & customizable than Zapier.
c) Make is SIGNIFICANTLY CHEAPER than Zapier.
 
I wrote an entire post here comparing Make vs. Zapier.
 
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