How to use buttons to transpose in Coda. io?
In my previous article on transpose we started with the case that clients provided feedback via a form on three products. The proposed solution we discussed is this one below. It is a formula and lives outside a button.
Sequence(1,thisRow.[The List].Count()).FormulaMap(thisRow.[The List].Nth(CurrentValue).Nth(thisRow.RowIndex))In this article we focus on a different use case, one I came across with one of my clients. She wanted to follow-up on required permits. First I created a PaperForm to find out which permits her clients needed based on smart questions (are you planning this and then that or something else — branching is the term). The questions came in as yes & no via Zapier into Coda. Though Zapier makes it possible to put as many answers as you want into a single Coda column, I went for an overview: one answer is one column and the name of the column tells me something about the answer (numbers are of great use here).
In the example below we only work with a few answers, but 5 or 50 the principle is the same.
Our Next step is — as shown in the previous article — to put them together in one list applying List(). This is an easy step, maybe the easiest in this blog.
She wanted per client to follow up on these permits for a few reasons:
- the permits the client needs?
- what in our experience is the permit lead time (based on different stakeholders)
- is the planned project date aligned with the dates we forecast
- permits validated should leave the list, so be removed from the view.
An other table — not shown here — services as source table for the permit descriptions, the expected lead time much more information.
We focus on the question :
“how can she check a permit in the follow-up table”?
In our Follow-Up Table we need at least:
- The client
- The permit
- The checkbox
Required permits should show up as empty check boxes. This asks for two steps. First we translate the yes & no into a value that a checkbox can read and second we transpose the answers per client into the feedback table.
Translating values with FormulaMap
From yes & no to values as a checkbox means we have to translate them into True() & False(). And here we have to pay attention. Normally we say that a yes is True() and no is False(). However the case is that she wants to follow-up on yes answers and check them. You can only do so when a checkbox is empty, meaning the checkbox beares the value False(). The formula below does the trick. The FormulaMap() evaluates each item in The List (no need to use Sequence() )
thisRow.[The List].FormulaMap(If(CurrentValue.Contains("Yes"), False(), True()))
We need two buttons
The next step is to activate the button that creates new rows in the FollowUp Table. These rows should contain the checkbox and the name of the client, as well the kind of permit. The checkbox should be clickable, that is important.
My first idea was to use one button: AddOrModifyRows() to execute two actions at the same time. First adding the new rows and if necessary updating them when for example a spelling mistake in the name was detected and corrected. However due to the FormulaMap() this won’t work. Every time you use for a new client the AddOrModifyRows() in combination with the FormulaMap(), it iterates over the list via Sequence(1,[The List].count()) and ínstead looking for a MatchValue, it overwrites previous rows once you have a MatchValue set, this could be any value. It only adds new rows when the MatchValue is empty. I had to learn this the hard way via trial & error. In fact the combination of FormulaMap() and AddoRModifyRows() has the same outcome as AddRow() This observation then leads to the conclusion we need two buttons:
- one for adding rows :
AddRow() - one for updating rows:
AddOrModifyRows()
To avoid problems, we disable the AddRow once the rows have been added and meanwhile we activate the update button that was inactive before. We use a simple trick to make this work.
In both cases we make use of RunActions() because we have at least two actions per button. The button that adds the rows checks a (for the user hidden) checkbox and once checked, it disables the button.
ModifyRows(thisRow, thisRow.Checkbox, "true"),The second button is disabled when the checkbox is false and only becomes active after being checked. Below how it looks.
Once we have understood what is possible and what not, we can start implementing.
Add a Client
From here on it becomes easier. We apply the function AddRow() and define the rows we want to add. We also set the checkbox as active via ModifyRows()
RunActions(Sequence(1,thisRow.[The List].Count()).FormulaMap(FollowUp.AddRow(Checkbox,thisRow.[True and False].Nth(currentValue),Permits,thisRow.Permits.Nth(CurrentValue),Key,thisRow.RowIndex,Clients,thisRow.Clients)),
ModifyRows(thisRow, thisRow.Checkbox, "true"))
In the code above you see that for the checkbox and the permits we link via Nth() to the position in the respective lists. We have a list with yes & no translated as False() and True() and we have a list with the names óf the permits we bring in via a LookUp() into a SelectList (this is one of the column formats like Text, Number, Date etc.).
Update information
In our example there is little to update, but what we have should be updated on the respective rows. We need a MatchValue and this element is only directly available in AddOrModifyRows() When you implement this action in your button, you have to make sure that the MatchValue links to ‘thisRow’ as does the rest of your information. By default (if you follow the builder) you end up with ‘thisTable.Columname’ and this means you bring in all the values (thus of each row) in one single row.
The code you need in this example is:
AddOrModifyRows(FollowUp, thisRow.RowIndex, FollowUp.Clients, thisRow.Clients)Optimalisation as a next step
In this example we deal with limited data, but what if (as with my clients) each push on the button generates 30–50 rows per project (not even per client). We don’t want to end up with tables getting slower due to an overload of rows. My idea is to implement two tools to avoid a slow down. We can add a control on end dates (for this is crucial when you work with permits) and I consider to only inject permits that need to be checked.
The latter is more complicated than I assumed first. To filter out values in the button it is rather straight forward. You add a Filter() in the function that is about the checkboxes.
FollowUp.Checkbox,thisRow.[True and False].Filter(CurrentValue=false).Nth(currentValue),The result is indeed lesser rows. However the not so unimportant side effect and also problem is that the permits no longer align. I guess I have to link the answers and the permits with the virtual index we generate via Sequence() to filter out on this level the rows (via the positions in The List). That is for an other time.
With this last open point we came to the end of this blog post. I hope you enjoyed it. If you do not follow the logic, feel free to reach out. Though this article is for free, my work (including advice) won’t be.
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. I live in Ghent and serve the BeNelux market.
