Keeping data aligned — Coda Forms
After users submitted and updated their information
In my blog on how to Coda a Form I mentioned the importance of keeping data aligned once you present pre-filled Coda forms and users update their info via these Coda forms.
This blog is about ways to pre-fill forms and to bring the info back to the user without making use of a key value like a phone number or mail addresses.
Introduction
Below how Coda proposes to solve the issue of pre-filling a form. You have a table with data and you bring the data back to the user via an open URL. Open because as you maybe see in the screenshot, you can read in the URL the values you push into the fields.
Another solution came to me via Marius in his contribution to the community. Instead of creating an open URL, he created a form that was pre-filled based on an Id for which he used an hashed key. This results in a form that is pre-filled with values you cannot overwrite and has a second column in which you can write new values to update the ‘old’ ones. The outcome might look like this one below:
And the hashed URL behind this form:
As you can see, nothing is visible in the URL in terms of names, they only appear in the form. Before we check-out the pros and cons of this secure versus the classic solution, first a few words on how to get the secure solution.
- You need two tables. In Table One you have the values as text for address, city etc. Values you can edit. In table two you have these values by referencing a key.
- The key we generate in the first table is the bridge we use in Table Two to reference the values from the first table. The key is in the display column in Table One.
- We create a hashed value based on values in Table One
- In Table Two we have not only the referenced values as outcome of formulas, we have also columns we use to collect the new (updated) values like ‘updated_ name’ or ‘updated_address’.
- The form we create is based on Table Two
- The values we enter in the form return in Table Two
The new row with updated (and actual values) arrives in Table Two.
A few screenshots to show how it works. The hash is created via Crypto (free pack) and I base it on the RowId, that is always available and since the main function of the hash is to disguise the table logic behind it, it works fine. The RowId() would work as well, but one can easily guess that if you see number 100, you might have 101 and 99 and this makes the system vulnerable as we’ll see later on.
By the way, in the above the UD_ stands for UpDate. Below you notice I used a different phrase to ask for updated information. How this works I described in my previous blog.
Via Publish Form we get the URL via Copy Link, we add the ‘?’ and we are ready to finalise the URL using the hashed ID created like below and meanwhile permitting the form to use LookUp values.
The result is something like below
The classical and the secure way both have their advantages and face one common challenge. For the secure version you see the challenge below showing that we have two rows in Table Two about the same person first living in Brussels and now in Ghent.
How to keep data aligned?
The problem to solve
Since we have two ways of doing it (classical and secure) I split the solution in two. To keep data aligned we need a MatchValue a unique key that helps us to identify the last version. The secured solution works with the hashed RowId as MatchValue.
Keeping data aligned in the classical solution
People fill out a form, the data arrives in a table. In the classical solution an automation takes care of the email (new row → email) and the user gets a via email the URL that links back to the filled-out form. The user updates something like a date or a misspelled name and submits. A new row is added to the database and a new email gets out and offers the user again the option to review previously shared data (with the exception of shared files and images) and so on. As previously shown, this process results in a table that contains rows with outdated information.
Once we send the confirmation email with the URL that contains the information per field, we can add a hidden field. This hidden field can contain the RowId() as MatchValue, though not visible in the form, it is very well readable in the URL.
It is easy to see that this number increases. The ‘hacker’ assumes that previous numbers exists, leaves all the fields empty and replaces the number in the ULR for a lower number and submits. A new row — empty — arrives in the database with the lower number and can — once the update logic is active — empty your database row by row. With a bit of experience, the hacker automates the process and the result is an empty database. To avoid this, we need a harder to understand key. A good candidate is the earlier described hashed key that will function as MatchValue.
The challenge we face is to avoid that each new row generates a new hashed key based on the new RowId(). Instead, once the form comes back with the existing key, we should work with this one to align next updates. To make this work, we need an automation.
Get the MatchValue via an automation
The set-up is rather easy once you understand the formula language behind automations. In automations you work with steps and the outcome of these steps.
In our context step 1 happens when in Demo Table we have a row changed and any column is fine. This is the case when a new row comes in, then the values in all columns change. The result of a form filled-out is a new row.
In step 3 (we return to step 2 later on) we select the column we want to modify, it is the column MatchValue and we update it in the context of step 1 with the formula below and we push the value from the column ID:
thisRow.[Step 1 Result].ID
I had to get used to this logic and did not see it immediately. Don’t worry if you feel the same way about it. This part of the Coda formula language is not well documented, it is mainly a matter of practice.
In step 3 we take the value of column ID in the row we are working on (the new one) and we put this ID into the column MatchValue. Now we have two columns with the same values. Both ID and MatchValue have the same hashed key. The difference is that the key in the column ID is the result of a formula and that the values in the column MatchValue are in text and text can be edited. And we need a column that allows us to write a value in, you cannot overwrite a formulated value for obvious reasons. The column MatchValue — which does not contain a formula, only text — now can be filled with a value coming from the form. Only when a new row comes in (first time user) there is not yet a MatchValue in the URL (it is Blank so to say) we only need a MatchValue in case a user wants to update something.
This brings us to step 2, the If. Only when we have nothing returning — IsBlank() — we create a hashed MatchValue.
This logic, although not so obvious when you see it for the first time works fine — but a bit slow — and is key in setting up a logic that keeps data aligned.
Updating values.
Since we have a MatchValue, we can set-up a second table for the classical solution that will only receive updated values based on the hashed ID. The easiest way to make this work is by adding a button to the table you work in. This button contains the the AddOrModifyRows() as you see below:
The first line is explicit about the match value and requires the match value to be present in the target table which you see in line two. An automation runs and for every new row we push this button and if there is a match, we update. If not we add a new row to DB Archive Christiaan.
The advantage of this new target table is twofold. First, we only have the most recent information of any user. Second, it has no formulas, no automations, the table is just plain text and is fast. It is the shorter and clean version of the source table. In the source table we still have all the rows available and this enables us to look back. This might be important once we receive empty fields. A notification could be set to alert the ‘manager’ to check these rows.
Once you have done this a few times, it feels elegant and goes fast.
In the secured solution we already had two tables (Table One and Table Two) and a MatchValue. Also here we add a button in Table Two and apply the same trick to update values in Table One. Below are two issues we have a closer look at. First I turn the value in the display column into text to enable a match with the text value in Table One. Second, I have a fall back option in case a value comes in Blank (empty) via the form applying IfBlank()
One last thing → emails
So far we have two solutions to update data via a form and keep this data aligned. One is open and goes fast, the second is secure and requires two forms: one for fresh data and the second for updated data. By the way, you can create two variations of the update form, one with the actual and ‘new’ fields and one with only the ‘new fields’ you rename simply as ‘your first name’, your last name, city etc’. In my blog on Forms I applied this logic to ask the same question in different languages.
Back to the basic question, how to create a functional loop with emails and automations?
We keep it simple and use the Gmail Pack as described in this blog. The email gets out based on a new row. A page inside Coda is filled-out with the variables. In our use case we can give a summary of the main points and invite the recipient to adapt if needed. If one sees that everything is correct, no need to update, otherwise they click on the link , the browser opens and the form shows with pre-filled values (classical or secured).
The link in the email goes like this:
[Table Two].[Secured URL].Last().Hyperlink(" this link here")We update the city to Bonn, the value arrives in Table Two where we have a row change which triggers an automation to push the button. Table One gets updated and because of the linked data, Table Two is ready for a new update.
I would suggest to point the automation to push only the last button to avoid that due to an unfortunate chain of events the doc gets slow or when a row is deleted you may get a wrong update due to a correction that is missing (deleted row). Better save than sorry. I used the one below, but feel free to use the Last() function or the Created().Max() or something else.
thisTable.Count() = thisTable.Find(thisRow)The secure solution brought to us by Marius is in terms of process rather straight forward. Don’t get me wrong, it is time consuming to create the tables, the automations, the emails (lay-out, text) to test it etc etc. but you do not face difficulties other then how to Coda.
We are left with the last challenge and that is to get an email out with the right values for the classical solution. As you may remember, we noticed a delay between the creation of a new row and the creation of the hashed Id via an automation. I was a bit worried that this delay might break the process, but it did not!
I filled out an empty form (new user) and this added a new row. This new row was the trigger to create the hashed ID, it triggered also the automation to sent me an email. I got the email, used the link in the email and updated the values, they arrived in the table and the previous generated row got updated, I got a new email, after clicking on the link, I had the pre-filled form with the correct updated info.
Conclusion
We can conclude safely that both solutions work fine. I reflected upon the question when to apply ‘the classic solution’ and when ‘the secure’ one.
The secure is great once you already have a database and you need to check for updates. People see what they shared with you, understand the update logic and will appreciate it. They cannot empty the database, because not filling out does not harm the database. It is thus a secure solution in both ways. To be GDPR compliant and fair, add a phrase that if people want to have their data partly) erased, they simply reply with something like ‘please erase my data’ in the subject (or you create a link that does this for them in the email).
The classic approach is elegant when you are collecting data and you can give it back directly after the submission for review. It shows you are taking care, since they see the same form but now pre-filled; you empower the subscriber graciously. The downside is the security part. They can delete all the previous shared info and you need something in place to check this. For example by filtering the source table that only contains the clients that erased their data (using the RowId() and the hashed Key). The second concern is the visibility of the data in the URL. In my line of work we never transmit info like credit card numbers, social security numbers and so on. If you follow these basic security rules you are good to go in my opinion.
Many thanks again to Marius for his idea to use a hashed ID and a double column based structure, but also to Joostmineur who took the time to review some issues I faced while writing this blog.
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter
My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialized in serving SME in harvesting data and keeping it aligned. I am a Coda Expert and I mainly rely on Coda, Mailjet, Zapier & Paperform to get the job done.
