Prefill a Paperform via Coda.io
In this blogpost I share tips & tricks on how to set up an automation that prefills already filled-out Paperforms. Sometimes you want to provide the (potential) client the opportunity to update their data without any hassle. Showing the information you have on them not only serves your image, makes you compliant with high GDPR standards, but also keeps data alive and aligned with reality. Maybe someone wants to change their email address or update the day and time for a RDV. Instead of sending an email with a requirement (time consuming process for all involved) you empower the user.
In this blogpost I leave aside how you get the form at the fingertips of the person you have in mind. I use MailJet and send an email that contains an URL that shows the relevant data. Sending emails is a different story. We focus in this article on the PaperForm URL.
The Paperform
We use PaperForm for the creation of our forms. These forms serve two purposes:
- Harvesting data
- Publishing data (share it back with the user)
We bring the data into Coda.io and focus on the latter: publishing data.
The URL — how to create it inside Coda?
Each form is unique due to its URL. The structure of the URL is somthing like the one below. The part before the dot in the URL is a unique ID generated by Paperform.
https://lmv4ra5u9.paperform.co/A prefilled form requires this URL and something extra. This extra is the information per field like below: the first name, the last name, the birthday and a photo.
The URL is the carrier of this information and in order to enrich the URL with the this data, you need two sorts of information:
- The ID of each field (or the key)
- The data itself (like my first name ‘Christiaan’)
The key per field you find via the settings per field in Paperform. It looks like the image below, here it is “6e2ks”. We next have to find a way to tell the form that they value related to this key is par example: ‘Christiaan’ and together we create a so called Key Value Pair and this could be as simple as:
6e2ks=Christiaan
The next thing is to create the columns inside Coda in such a way that you can start building. For each Key we create a column like below and we use the same logic everywhere: Key + name of the value. In terms of functions this is as easy as it gets; you put it as between ‘ ‘ or “ “ and you have the text value.
We use the Concatenate() function and it goes like this with the ‘ =’ in between. The EncodeForUrl() part I discuss below.
Concatenate(thisRow.[Key Firstname],'=',thisRow.Firstname.EncodeForUrl())
Each value becomes part of a key Value Pair, meaning we need at least three columns. And as you can see, the wording does matter to keep the overview.
- The value (like my firstname christiaan) in the column First name
- The key for the Firstname in the column Key First name
- The Key Value for my firstname using the Key First name with the name KV First name
We have all elements but two to create the URL
- The URL
- The ‘&’
Coda and the creation of the URL
Coda makes it easy to create a URL, again we use the Concatenate() and then you get something like below:
Concatenate(thisRow.[URL Clean],thisRow.[KV firstname],thisRow.[AND],thisRow.[KV lastname], thisRow.[AND],thisRow.[KV birthday], thisRow.[AND], thisRow.[KV Image URL], thisRow.[AND], thisRow.[KV Retour ID], thisRow.[AND], thisRow.[KV PaperformID]).Hyperlink()we link to the column URL Clean that contains the URL for the Paperform enriched with a questionmark, like the one below:
https://lmv4ra5u9.paperform.co?Last but not least, the AND stands for the ‘&’. This reads easier and will help you to avoid mistakes when you chain all the Key-Value pairs.
You end with .Hyperlink() to turn the string into a URL and off you go. We now have a URL to prefill a form and thus to publish the data we have in our table.
Longer Text fields
Once you have something simple as a space between words you want to see the two words apart and not as one cluster. Imagine you have ‘Marie Louise’ as a first name. If you send it back without proper attention it ends up as MarieLouise. An error easy to correct. But text a bit longer could become unreadable. To avoid this we add a function to each field that might contain spaces or other characters. This function translates the string into something readable, it is EncodeForUrl(). You add it to any field that might contain something special, even a name can get messed up.
Concatenate(thisRow.[Key Firstname],'=',thisRow.Firstname.EncodeForUrl())The use of the RowId and the Paperform ID.
Database wise it is important that we can group updated info about a user and when needed delete outdated values. The single point of identification is standardly the Coda RowId. If we have a Coda RowId we have an update, if we have this RowId more recently, this is the latest version. In another article I described how to filter out rows with outdated information.
We inject this Coda RowId in a hidden form field. The user won’t notice, but when submitting the updated info, we have the RowID back into Coda.
The same goes for the Paperform ID. Each form has a unique ID and we can take hold of that ID via Zapier and store into our Coda Table. We can send this ID to the also hidden field and once we see this one back in our database on a new row, we have an update. As such we have two IDs we can use to identify the most recent update. But why two Id’s if one does inside Coda the job ? The reason is simple: to avoid that someone wipes our database clean.
Although we inject this data into hidden fields, the URL contains all the information. Including the RowID and this is just a simple number between 1 and let’s say 10.000. Any creative person could copy that URL, change this ID, paste it back into the browser, submit it with the filled out data (or leave it all blank) and we have in our Coda base maybe an empty row or a row with erroneous data. If they do that again with the previous number, that row contains dirty or no info as well. RowId’s itself are easy to hack.
However if we add the more complex Paperform ID (24 characters) it becomes close to impossible to hack our database as described. Because even if the Coda RowId is altered it won’t erase data from another user because we have set up a double key: RowId + Paperform Id. If we do not have a match we consider this as a new user with a unique row. No harm is done to another user.
I hope this is rather self explanatory. If you do not follow, feel free to reach out. Though this article is for free, my work (including advice) won’t be.
Special case Date and time
Paperform wants to have dates piped in this format YYYY-MM-DD. First I thought that setting the format as required on Column level would solve the issue. It did not. I had to use a function to force the data into the right format as you can see below. This is the formula:
concatenate(thisRow.[Key Birthday],'=',formatdatetime(thisRow.Date,8,0))
The FormatDateTime() is a not officially supported one, though present and available with some explanation. Only after wrapping the — already formatted date — into this function I could force the software to pipe the date in the required format and it worked.
A date format matters quite a bit for everything related to birthdays, appointments and so on has to do with a date one way or the other. Once you know the trick, the fix is rather easy.
Images and Photos
Though images arrive perfectly well into Coda (via Zapier) and are shown as such you cannot pre fill a Paperform with the user shared with your image. I consider this as an important omission in the current Paperform approach. Something that should be high on their list to fix. The reason you find below.
The future of forms
Solutions like Paperform solve an important problem: how to gather data intelligently and nicely. Today it is both easy to set up a form and to fill one out. This frictionless process is however mostly a one-way-communication. You leave your details, you might get an email that confirms you did so and it stops. Not many organisaties have the knowledge to share the info back.
One can easily see how to bring back data to the user regardless of how it was injected into the table / database. This reversed usage of forms has the potential to become far more important than the one promoted today. All major low code databases have any kind of form. Their starting point is not harvesting data, but publishing what they already have. It is the other way around. This data is maybe a copy of a spreadsheet, manually entered, the result of a calculation or arrived via a form. Seeing the huge amount of data living in tables of all sorts, one can see that much of it could or even should be shared back with the user in a controlled way. Forms are a great way of doing so. However they should be able to show all data living in the tables, including images and attachments. In this perspective this is no longer a nice to have, but a have to have.
The future of forms won’t be fought out only between Paperform, Typeform and the likes. The battlefield will also be populated with solutions like Notion, AirTable and Coda. Companies that have many data rich clients in need of a smart publishing tool.
My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialised in serving SME in harvesting data and keeping it aligned. I mainly rely on AirTable, Coda, Mailjet, Zapier & Paperform to get the job done.
