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.
My name is Christiaan and blog about Coda. Since the summer of 2023 often (but not only) about how to Coda with AI to support organisations dealing with texts and templates. The latest major Coda AI update was on Dec 7, 2023. With the announcement of Snowflake as partner on April 10, I expect to see a new Coda AI logic put in place before the summer holidays. The current implementation is not sustainable.
Why I focus on Coda AI you can read here: ⤵️
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. You find my (for free) contributions to the Coda Community and on Twitter.
Coda comes with a set of building blocks ー like pages for infinite depth, tables that talk to each other, and buttons that take action inside or outside your doc ーso anyone can make a doc as powerful as an app (source).
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
