New app in the Airtable Marketplace: Formula for a Prefilled Form

[kuovonne]

I have a new app on the Airtable Marketplace!

Have you ever wanted to prefill a form will data that already exists in your base, but found building the url to be difficult? My new marketplace app, Prefilled Forms, creates a formula that calculates a prefilled url for a form for each record in a table.

You don’t have to write any code–just point, click, copy, and paste.

Use case 1: Create prefilled links for clients You already have a table of client information, and you have an onboarding form, questionnaire, evaluation survey or other form for them to fill out. You want a to email each client a link to the form prefilled with the client’s name and other details. Use the Prefilled Forms app to build the formula to for each client.

Use case 2: Click a button to create records with defaults New records tend to have several fields that always have the same combination of values. For each combination, use a button field with the url for the form prefilled with that combination. Click the button to launch the prefilled form and have less to fill out.

You can read about my app (and several other custom apps) in Airtable’s blog post. Mine is the 3rd on the list.

You can get my app here.

---

Note about emailing prefilled links with automations If you are using Airtable automations to email the url, you must escape the underscore character or the Airtable automations will think that you are trying to include italic text. The workaround for this is to create a second formula field that escapes the underscores, then use the escaped url in the automation email:

SUBSTITUTE({Original Url}, "_", "\_")

If you have spaces in your field names, use this formula:

SUBSTITUTE(
  SUBSTITUTE(
    {Òriginal Url}, 
    "_", 
    "\\" & "\\" & "_"
  ),
  "%20",
  "\\" & "\\" & "%2520"
)

[kuovonne]

Here are links to videos on the two uses cases

• using the formula internally in a button field for creating records with defaults
• using the formula to generate a url that can be emailed to someone with prefilled content for that person, then using an automation to link newly submitted values to the original record.

[kuovonne]

After some investigating with custom support, depending on how you enter your urls in your automated email, you might not have to escape them. If you use markdown syntax [link text](unescaped_url), it looks like you do not have to escape the urls.

[itoldusoandso]

Yah, this app is gold. Same as Field List that already saved me once. Too bad nobody gave kuovonne kudos she deserves for this.

With this I was able to show the autogenerate next new SKU number for my items in the web form. SKU is for stock keeping unit.

I have table with SKU IDs for records. My SKU IDs are alphanumeric, where letter prefix depends on location and number is autogenerated. This is what I used for the formula…

Autogenerate 4 digit numbers Formulas

This is close to what I am looking for, but I’m not advanced enough to deconstruct that formula. Is there a way to customize an alphanumeric code with some static characters… Example: In Excel, I can enter “3.1-001” into a cell, then “3.1-002” in the next, then auto-populate the rest of the cells. Is there a workaround/solution for this?

Next I created a new table as per kuovonne’s instructions and aded rollup value with Max function for the {ID} and added +1 number … and concatenated with the location number to get my next available SKU number as needed.

Then using kuovonne’s app I get prefilled form with the next available SKU filled in the form. If I want I can override the SKU number if needed manually.

That saves me time as I always had to look up the next available SKU number.

Could you answer some questions please:

1. I have a field is Barcode type field and it shows up in the Airtable form no problem. I even use the iOS 3rd party barcode scanning keyboard app to scan barcodes in it and work fine. This barcode type field doesn’t show up in the list of available form fields in the Prefilled Form app. I changed the field type to text and then it shows up in the app. So I created the prefilled URL and changed the field again back to Barcode type field and yet it works fine and the field is prefilled. So I guess, it’s a matter of making the field available in the Preffiled Form app, right?

2. In the app configuration I can select “Prefill with formula result”. I am curious what formulas I can use here. I see in the demo video, the formula used is RECORD_ID(). How about other formulas? The challenge I have is the data I am currently filling into the form is not in the table yet and the data will be saved as a completely new record. So what are the formulas I could use with here? I can imagine perhaps some date and time calculations or formulas taking data that is already available in Airtable before I even submit the form. So I am curious to know what formulas are even useable at the time when entering the new record.

Much appreciated and thank you.

[kuovonne]

itoldusoandso:

Yah, this app is gold. Same as Field List that already saved me once.

Thank you. I’m glad you find the app useful.

itoldusoandso:

So I guess, it’s a matter of making the field available in the Preffiled Form app, right?

Thanks for pointing out the use case for the Barcode field type. I didn’t occur to me that someone would want to prefill a barcode. I will keep this in mind for future versions of the app.

itoldusoandso:

In the app configuration I can select “Prefill with formula result”. I am curious what formulas I can use here.

You can put any formula that would be a valid Airtable formula in the same table as the one calculating the prefilled url. The formula should fit on a single line, as it is designed for short, simple formulas. If a formula is complicated, it is better to use a separate formula field for the calculation, and then prefill based on the separate formula field. Note that the inputs for the formula will need to be inputs for the host record, not the record that will be created when the form is submitted.