Use case update records via a form
  • 04 Oct 2022
  • 7 Minutes to read
  • Dark
    Light

Use case update records via a form

  • Dark
    Light

This article is meant to give you an idea of how to work around a common request we see from Airtablets: updating a record that already exists in a base via a form submission. If you often find yourself wishing external collaborators could just update a record/field here or there, then you’ve found the right place!

Prerequisites

Be sure to brush up on any of the subjects below as we will be using them in the example ahead:

Linked record field - This allows you to represent relationships between records.
Lookup field - A lookup field allows you to look up a specific field's contents via a linked record.
Pre-filled forms - The feature means that you can manipulate a shared form's URL in order to fill certain fields with predefined information.
Formulas - It's important to have a baseline understanding of the way that formulas work in Airtable as it may be slightly different than the way you may have used formulas in other database or spreadsheet software.
Creating an automation - Knowing how to construct Airtable Automations opens new doors to efficiency in your bases' workflows.
Record IDs - Similar to a primary key in a database, Record IDs represent Airtable's way of uniquely identifying each record.

Scenario

Imagine that you are on a marketing team tracking various campaigns and the ad sets that belong to those campaigns. Before your team can fully proceed with pushing out a new ad set you need approval from an outside consulting firm your team is working with. The members of this outside firm do not use Airtable and your team is unable to pay for their access at the moment. Each ad set is the single source of truth for that portion of the marketing campaign. This means you want to design a way for an external collaborator to be able to approve the ad set by essentially checking a box in Airtable.

No need to fear! Because of Airtable’s flexibility, you can create a workaround solution that will allow an external collaborator to "update" the ad set record. Follow the steps below and think about how you might translate this process to the bases that you are using.

Walkthrough

On a high level, we will create a table named "Updates" and connect it to the "Ad sets" table via a linked record field. Next, we need to create a new form view in the "Updates" table. This form will need to be prefilled with the record ID of the ad set record that requires an external collaborator's approval. Finally, we will create an automation that will update the ad set that has been approved.

TIP

If you’d like to follow along with the example below, then navigate to this template while signed-in, click "Use template," and choose a workspace you would like to add the base to.

Step 1 - Create a checkbox field for approvals

Add a checkbox field to the "Ad sets" table. We will call this field "Approval" in our base.

4402395254167approvalCheckbox6721.jpg

Step 2 - Create a record ID formula field

In the same "Ad sets" table create a formula field and use the RECORD_ID() formula function to output the unique record ID for each ad set. We will call this field "Ad set ID."

4402389308439recordIDField6721.jpg

Step 3 - Create an "Updates" table

Next, we want to add a new table where we can track updates via form submission. We will call this the “Updates” table.

4402395267863createUpdatesTable6721.gif

Step 4 - Add a linked record field

In this newly created "Updates" table, we need to add some fields to track approvals and use the information in a soon-to-be-built Automation (step 8 below). We can keep the default name field so that we know who submitted the approval, but we want to customize the other fields. The "Notes" field can be renamed as "Link to Ad sets" and changed to a linked record field that links to the "Ad sets" table.

4402389315351addLinkedField6721.png

Step 5 - Add a checkbox field

Next, we will add a checkbox field to the "Updates" table. We can customize the default "Attachments" by renaming it as "Ad set approval" and changing the field type to be a checkbox field. We will use this same field later in the Automations (Step 8) portion of this workflow.

4402389317271checkboxUpdates6721.gif

Step 6 - Create a new form view

In the "Updates" table we want to create a simple form that we can send to our external collaborators for ad set approval. The form will contain each of the fields we just set up.

4402389360407updateForm6721.gif

We also want to turn on the "Required" toggle for each field. This ensures that the external collaborator won’t miss any necessary information. Additionally, it’s a good practice to add context to the "Link to Ad sets" portion of the form in the "Add some help text" box. More on pre-filling forms in the next step.

4402389375255customizeForm6721.gif

At this point, the form would look something like this. Note the added "help text" highlighted in the green boxes.

4402389430935updateFormWithAddedHelpText.png

Step 7 - Formulate a pre-filled form URL

Now we need to create some helpful URLs for each ad set. This step will allow you to prefill the ad set needing approval by concatenating the RECORD_ID() from each ad set with the form share link we set up in the previous step.

First, we want to copy the form's share link URL from the "Updates table" by clicking on the "Share form" button from the form view.

4402389367191formShareLink6721.jpg

TIP

You may also consider restricting access to the form with a password for an added layer of security.

Next, we want to build out a new formula field in the "Ad sets" table. The general formula we are using for this example is as follows:

CONCATENATE("https://airtable.com/shr**********?","prefill_Link+to+Ad+sets=", {Ad set ID}) 

Let’s breakdown each part of this formula:

4402400247703Pre-fillURLBreakdown.png

NOTE

You'll need to use your own form's unique share URL when building out this formula.

Now, we have a unique pre-filled form link for each ad set that can be easily shared with external collaborators.

4402389374743prefillFormLinkField6721.jpg

As you can see below, the link you’ve created with the formula will now dynamically pre-fill the ad set belonging to that record.

4402389407767prefillAdSetFormLink6921.gif

Step 8 - Construct an automation

Now that we have everything needed to track ad set approvals in the base, we need a way of using those responses to update the ad set record in the "Ad sets" table. There are a couple of ways to do this, but for our example, we will use Airtable’s native Automations feature.

First, you’ll need to create a new automation, name it, and choose a trigger. The GIF below shows navigating to the Automations feature, adding a new custom automation, naming it (in this case we are naming it "Approval automation," and setting the trigger as " When a record is created " in the "Updates" table. Don’t forget to run the automation test.

4402389455511addAutomation6721.gif

Once the trigger has run successfully, we need to add an action or actions that the automation will perform. In this case, we want the automation to update an ad set’s record in the “Ad sets” table. To do this, we need to carefully select the record ID from the linked record field from the “Updates” table (Step 1 is the trigger portion of the automation). Follow along in the GIF below.

4402389463575automationRecordID6721.gif

Once we’ve inserted the dynamic record ID value, we need to choose a field or fields to update. In this case, we will be updating the “Approval” checkbox field that we set up at the beginning of this guide. We will use the “Ad set approval” checkbox field from the “Updates” table to overwrite the “Approval” checkbox field in the “Ad sets” table. Again, make sure to test the action step of the automation and check that it performed the intended operation.

4402395374487adSetApproval6721.gif

Congratulations! You can now turn the automation on and share the form link with your external collaborators so that they can approve the ad set.

4402389461015adSetApprovalFullFlow.gif

Here is an embed of the base with all of the changes made from the steps above. Feel free to copy this base for your own comparison or use.

FAQs

Are there any other workflows that I could use to update existing records?

Above is just one of the workarounds you could use to update an existing record whenever a form is submitted. You can also check out other solutions for this problem in the Airtable Community!

How do I troubleshoot the automation I’m building out?

Check out this useful support article that outlines common troubleshooting steps.

Can external collaborators make comments?

Short of adding the individual as a billable "commenter" permissions collaborator, you could follow similar steps as we did above, and create a comments section using a long text field. Consider going back to step 5 above and create a "Comments" long text field instead of the checkbox field used in the example.


Was this article helpful?