A simple Coda form
The need to transpose — for pro users only
Indeed creating something simple requires professional skills. Below a form that is easy to fill out.
You select the tea and next you check the boxes. As my oldest noticed, you cannot add multiple boxes of each box. This is on purpose to keep the exercise concise.
The back end logic you see below and by using filters in the relations we make sure only boxes related to the selected tea become available.
We relate the columns using the filter option in the relation.
This is a flexible set up permitting the user to fill out a form rapidly. We make sure that the user cannot select unrelated boxes.
The data arrives in our table on one row and as you may know, this row based approach is typical for forms but does not permit us to apply our typical Coda super powers. We need to transpose the data first.
This blog is about how to transpose this data elegantly. When you have read this blog (and understood) you are able to transpose each row and bring the data to a second table to store the data a Coda friendly manner as you see below.
What you read in this blog is party inspired on the contribution of the great Coda connaisseur Joostmineur who was so friendly to help me out when I got stuck in a related exercise. Merci Joost!
Getting started
we are going to bring the teas and the boxes together in two distinct columns, thus in two lists using the function List(). This function permits us to keep the logic as created by user. You see that clearly in the listBoxes where some lists contain two items.
We have two lists, both filled with objects. The teas and the boxes. All readers will notice that order one will result in 4 rows, like wise order two, while order three results in 5 rows (hidden by the pushy AI option).
Although this looks simple, it is not. Before we dive into the solution, first some basics.
In the example we have 3 or 4 teas and each tea can have one or two boxes.
One more step is waiting before we can take off. As said we need to count the boxes per tea. In our example we have 4 teas and the last one has two boxes. The logic is what you see below and we need this in our transpose set up.
That we need a button goes without saying and that in this button we need an AddRow() is evident. The challenge is to set up the formula in such a way that for each tea box a row is generated with the related tea.
Integration in the button
We integrate the three shown steps in our button as you see below:
We first create step one and name the outcome ‘counter’. Second we mimic step02 and name it items and we finish with replicating step03 and we name that base. These three named formulas are required to achieve our goal: distribution of teas and boxes over rows. In our example we focus on order 3 with two boxes related to the last tea (green). Desired outcome: 4 teas and 5 rows.
Adding the rows
Our starting point is the step03 named ‘base’ in our formula. This is this one: [1],[1],[1],[1,2]
It is maybe the hardest step to understand. We evaluate each tea two times, first via the [1],[1],[1],[1,2] and second via the counter (1,2,3,4) . Below how this looks like.
Distribution of boxes over the rows
Adding the rows was not easy to see, but this one felt harder until I looked again and saw the rather simple solution.
We need the ForEach() to remain open to add the 5 rows and meanwhile we don’t want to add extra rows. We only need 5 and thus we transpose via a double Nth().
thisRow.listBoxes.Nth(counter).Nth(base)Here we are. When you understand what I have written you are a Coda pro user. Although when you don’t, but you can copy — paste this solution and adapt it to your needs, you are a smart maker. There is not always the need to see through, sometimes applying a predefined logic is good enough.
In this published doc you see how the tables work.
I hope this article was informative and helpful. Did it help you to solve a problem you unlike would have solved other ways? What about a donation?
My name is Christiaan, and I regularly blog about Coda. While this article is free, my professional services (including consultations) are not, but I’m always happy to chat and explore potential solutions. You can find my free contributions in the Coda Community and on X. The Coda Community is a fantastic resource for free insights, especially when you share a sample doc.
