Coda — Forms — Transpose
How to deal with information in Coda that comes in via a form?
The form I write about can be any form, thus a Coda Form or a PaperForm, Google Form, TypeForm, etc. This article is for experienced Coda users and partly based on my own experiences as well as on input I came across in the Coda Community. This specific contribution of Paul Danyliuk was crucial and can be found over here.
The form responses we focus on concern a text, a number or a choice (yes / no) or a combination of these elements (like dates). Sometimes you ask via a form for files or images and it happens that a check-out is offered to handle payments. However to show the main principles we focus on incoming numbers and text, not on images and files.
How does information arrive in your Coda Doc?
The first observation is that the response to a question is shown in a column. You have one question, you have one column, 10 questions, 10 columns, each response fits its unique column. The second observation is that information linked to the question (the response in the form) is living in a different table on a row. Take for example products you offer. information as availability, location, the price, taxes, specs, images is stored per product per row. Meanwhile the answer to the question ‘are you interested in this product’ arrives for example in column one and would you like to receive a sample (yes / no) arrives in column two. If you want to relate this information to a product you need to translate horizontal information into vertical information. In Excel you would easily transpose the information that you cannot do in Coda.
The question that pops up is
how to link these two tables that are so differently structured?
Coda : everything is a list
In previous articles we wrote about how everything in Coda can be seen as a list. The list can be the outcome of combining rows using List() or ListCombine() or via a thisTable.ColumName. The difference between these two is that the column based list has the same value for every item (like dates, numbers text). Rows on the contrary can be quite different (numbers, tekst, dates, images). When you create a list using rows, you have to select alike values, like the list of all products, or all product prices or product delivery dates and so on. Below an example of the two types of information. The first part is about the client, the second part relates to a product, tea in this case.
If we want to relate the evaluation of each participant to each tea, we have to link the feedback to the tea product table or to a table that via a lookUp links to the product table. To keep it simple we work with a view of the product table and name it ‘Tea Evaluations’, see below
Once we want to have the average per tea, or the max value, a statistical deviation, etc. we need all the numbers per tea together. In this example, each one of the teas got 5 evaluations. We cannot repeat enough that it is all about lists of numbers and positions per number in any given list. Once you see this, the game is almost set. Below how we can see the list logic. The blue numbers are the results per tea. We have 5 clients (green) and in sum 15 evaluations distributed over 3 teas.
Lists and positions in a list
We start pulling the data in as a list in the product table. As the image shows you have a list with sublists. We leave it like that, it does look a bit ugly, but it helps to understand what is about to happen and even more important if you reformat The List with ListCombine() or List() the logic we deploy over here won’t work anymore for the software no longer recognises how the list are a set of subsets.
The source table contains 5 rows, one row per client. The destination table has 3 rows, because three teas. This is dynamic information meaning that if we have more clients with feedback, we have more rows, do we ask feedback on more teas, we have extra rows as well. We therefore work with references. If anything changes, the formula still provides a proper output. To keep it simple we work with a count of The List
The ingredients to split a list living in a row
By now we know that the result per tea contains 5 evaluations, one per client and that we have 3 teas. This implies that the formula has to split The List in 3 equal parts and remember leaving te ugly lists with subsets of list is important. The idea is that we have a formula that first evaluates the first number and allocates it to the first tea, then the second number and this goes to the second tea and then the 3th number and this goes to tea 3, then number 4 in The List goes again to the first tea, number 5 goes to the second tea and so on, see below.
The main ingredients we need to solve this puzzle are:
- RowIndex
- Sequence()
- Nth()
- FormulaMap()
RowIndex has been explained before. We use thisTable.Find(thisRow) to get an ascending number in every row. When you do not Filter() or Sort() this table, it works fine.
Sequence () gives you a list of numbers between the first number and the last number 1,3 -> 1,2,3 while 8,12 results in 8,9,10,11,12. Both values (start & end) can be hard coded or be the results of a formula. Since we want to develop a formula that allows for change, we go with formulas inside Sequence, though the first number is a “1”, it is for the second that we apply the logic below:
Sequence(1 , [The List].Count())Nth() is the position in a list. This is rather important to us as we need to filter out numbers that are on specific positions in The List. For the first tea we need the following positions 1, 4, 7, 10, 13. We find all of them on Row 1 and via Nth() we have a way to put them there.
FormulaMap takes a list as input and runs every item through an expression. Inside the expression, you use CurrentValue to reference the current item being evaluated( Source). What does this somewhat cryptic text mean?
Above we noted that we need 15 times something to happen and that this something concerns finding the numbers that correspond with a position in a list and that position follows a pattern. Well, this 15 times can happen because of FormulaMap, it does nothing else than bridging a list with a formula.
Below the schematic overview of the green Sequence (to tell FormulaMap to do something 5 times in this schema below, though we need in our example 15) and inside the FormulaMap the Expression / Function. The CurrentValue is each item in the list, in our case the numbers 1 -> 15 so 15 times we do something else.
Formula to find the items in the list
With the ingredients laid out, we are almost there. Let’s go back to the beginning:
Part 01 we need to evaluate a list of 15 itemsSequence(1 , [The List].Count()).FormulaMap()
What is it exactly we do have to evaluate 15 times with FormulaMap() to get the right evaluations out of the string? As said on RowIndex 1, we take the first position, on RowIndex 2, the second and on RowIndex 3, the third position. On RowIndex 1 we also take the 4 position, on RowIndex 2 the 5th position and so on. The pattern we look for is thus directly related with the RowIndex and we let the CurrentValue run over it.
Part 02: the formula (or expression) evaluated by FormulaMap:[The List].Nth(CurrentValue).Nth(RowIndex)
What does part II do?
Was it not for the instructions of Paul, I would have never considered this double Nth() solution. I assumed for quite a while that I should use Slice() inside the Nth() to find the position in The List, but this did not work.
The Nth(1).Nth(1) you can read as coordinates, thus indeed as 1.1 then 2.1 and then 3.1. Please read this carefully and note the order of the numbers. We start at Row 1, we jump to Row 2, To Row 3 and then CurrentValue number 4 (out of the 15) runs back to Row number 1, number 6 goes to Row 2 and so forth. We distribute the CurrentValue over the RowIndex by writing:
Nth(CurrentValue).Nth(RowIndex)This is a coordinate based approach and once you see this, you cannot unsee it anymore, it is crystal clear. So the final and logical solution is the following:
Sequence(1,thisRow.[The List].Count()).FormulaMap(thisRow.[The List].Nth(CurrentValue).Nth(thisRow.RowIndex))The result of this exercise is that we have per tea the evaluations we can use as starting point for further calculations as you can see below. The nice thing about this approach is that the RowIndex plays a secondary role. The software is so smart that it distributes the 15 outcomes over three rows, not over 6… And this is because we left the ugly sub sets as they came in.
Forms become increasingly important as I also laid out in this blog, not only to inject data into a database, but also to share data back. Here the circle closes. Once we have per client for example their feedback, we not only can link it to products as shown above, but also bring it back.
I hope you enjoyed this article. 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.
