How to Coda an Index?
Making use of the hidden gem Find()
As you may remember in two previous articles I already spoke about Find() . In what follows there is a bit of overlap with this blog. Here I showed how to use the find function as a detector and how it can be applied to get access to a previousRow value. Well in this blog we start right here by paying attention to a basic formula:
thisTable.Find(thisRow)We often use this concise function to get the Row Index. This number is the outcome of a formula and tells the user on which row you are. It corresponds most of the time with the little grey number just before the table as you see below. It does not correspond if you apply a sorting. On the right part I applied the sorting and this alters the visual appearance quite a bit. You can play around with it here.
Once you have seen how you can set an index on table level, the question comes up if you can also set an index based on for example an item in the column (or as you wish ‘list’) like World Parts.
Group Index
Yes you can and what you need to add is a filter that takes care of the items you want to focus on.
thisTable.Filter([Line Numbers].Contains(thisRow.[Line Numbers])).
Find(thisRow)As you can see, we start with a standard filter that check each item in the list to see if it fits the current item and if it does, it shares the position via Find(). The result is a range of numbers starting at 1 and ending when the find gets the last item in that specific sub list.
I used this logic quite a lot when working with time tables. On a traject starting for example in Brussels and going to Paris I can check the total duration in a table that also contains the part back from Paris to Brussels.
Below how it looks like when you have both the Row Index and the Group Index applied. Do you notice in the GroupIndex that there are less stops on the way back?
Order Index
This formula Irecently developed to keep track of companies in a database. I had a list of about 300 companies with basic properties like name, address and so on. More importantly is however how their fleet looks like, thus how many buses they have, the kind of buses (double deck vs single deck), the bus brand (Setra, MAN, Scania, etc).
The sad part of the story is that this fleet info needed to be copied and pasted manually into a Coda table. Each time when I pasted the fleet info I had to select the correct company from the source table, using a lookUp. Since names looked alike a lot I needed an additional criteria to avoid mistakes.
I added the RowIndex to the name of the bus company, applied LeftPad(3,0) and below a bit of the result with the formula I constructed to get a list of unique values in this table. Thus a table in which I select manually the companies.
The formula is rather straightforward. We evaluate each item in the list with FormulaMap, we take out the unique once and we sort them A-Z. As you can see, we have 5 companies in this example and the first one has three buses, the second one 14 and so on.
We want to have the number 1 in front of the first three numbers 3 (the number of buses) and number 2 in in front of the next 14 buses etc.
To get there we have to complete the above formula in two steps. We first ask if the bus company we find on this row is part of this list. Second if so (and obviously it always is) where in this list do we find this company?
Step 1 is the evaluation of the list to check if this row contains this company. Again we use a FormulaMap and the output is a list of true and false statements.
The next step is translate the true and false outputs into a number, the true in row 1, 2 and 3 stands for 1, the true in row 4 is the second.
I had again a look at this solution and noticed it could be written shorter via:
thisTable.ColumnOfChoice.Unique().Sort().
FormulaMap(CurrentValue.Contains(thisRow.ColumnOfChoice)).
Find(True())There is no need for a double FormulaMap. First create a list of unique values, check if these are part of the column you focus on and next via Find() does the job.
Also this time we applied the Find function. In this case to get the position of the ‘true’ value in the created list. In the previous function we used the find to get the position of an item living in a row and therefore we could use ‘thisRow’ to evaluate. Not so in this case where we constructed a list of unique and sorted items containing true and false. All we did was looking for something ‘true’.
In this blog I showed how by using the Find Function we can construct an index in Coda. I thought it as worthwhile sharing for the simple reason I use variation one and two quite a lot. Number three not so much (yet) for it is a recent one.
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.
