Coda & Find()
Find & Replace, is that not like the horse and the carriage going together like love and marriage? It might feel that way, but once you dive into the Coda logic, you see something else.
This article is about how the function Find() operates inside Coda and aims to clarify three use cases:
- Finding something to filter on as understood in natural language
- As a detector
- As a function to refer to
thisRow()and previousRows
Finding something
I responded to a question in the community on how to filter on something predefined. The questions was:
I want to filter it to show only events that contains the words “LMA” and/or “Quarterly”.
Before Brandon could filter, he first had to find the words he looked for in the string. Below you read how this works (my response).
I created an example that looks a bit like yours, see below. The trick is that you need to find text and for this we use
Find(), this function gives us the position in a string where the item you look for starts. This position is a number greater than or equal to 1 and if this item is not found the outcome is minus one (-1). What I do below is looking for an outcome greater than or equal to 1
the function Find() is used to tell if the string is found or not.
A variation on the above is written down in the following example. The question is
Is there a way to extract the domain from a text field that contains the URL.
This is the answer:
thisRow.[the url].totext().slice(1,sum(thisRow.[the url].Find(“.com”),Length(“.com”)))
We use the real position to work with as part of a calculation for slicing something. This application of Find() is practical and you often come across it. In the above the starting point is completed with a number generated via length() This is all not too difficult, it requires a good understanding of how positions in a string can be found.
Find as a detector
An other example is given by Maria. Below you see in bold the two elements we came across previously. First the function Find() and second an outcome -1, meaning not found, further on you see again a “1” meaning found. This formula combines various functions elegantly.
- ModifyRows()
- WithName()
- If()
- Splice()
- Find()
ModifyRows(thisRow, [Companies 2].[Internal Team], WithName(thisRow.[Internal Team].Find(User()),CurrentUserIndex,If(CurrentUserIndex=-1,thisRow.[Internal Team].Splice(0,0,User()),thisRow.[Internal Team].Splice(CurrentUserIndex,1))))
The formula permits to add a user in the internal team by clicking on the button (and on top of that the name of the button changes as well). The main function is renamed via WithName() with the label CurrentUserIndex . If the outcome of this function Modifyrows () is -1 , meaning user is not found via Find() , then add the user via Splice() The latter is a smart solution to insert something on a specific position. Like Find() it works with a position in a string. It starts deleting something to replace it with something else. It is a variation on find & replace.
If the user is not found (thus -1), we look for no position (0), we delete nothing (0) and we insert the user() However if the user is found (thus 1, we look for position 1, we delete this one and the user is removed from the team.
Once you see the elegance, you only can smile ;-)
PreviousRows
In other articles I wrote quite a bit about the previousRow logic. This approach has no formal function yet, you cannot yet ask for something like thisRow.PreviousRow(1) . Instead we use Find() like below:
Standardly we use this formula to get a RowIndex, thus a list for ascending numbers: thisTable.Find(thisRow)As you can see on the left, the numbers correspond with the numbers before the table, put there by Coda. However Coda does not permit us (early April 2021) to access this information and so we generate a list of corresponding numbers using Find() The main reference in this function is the complete table. In the second screenshot (middle) we use find to create lists of numbers corresponding with row values. We needed this value as part of our calculations and decided to put into a column, instead of pointing to it via a function and using WithName(). The main reason is performance. We try to avoid that every time every row in the table has to be checked, we calculate it one time and once the value is put in the table, it can be used. The third variation checks if the value of the previousRow (the minus one) is equal to thisRow.
How the three approaches differ
In the last three cases we used Find() not to get the position in a string (on Row Level), but in a List (on column level). One of the consequences is that you should be careful with using Find() in larger tables. This function applied to a larger list, requires quite some horsepower. Coda CEO, Shishir Mehrotra, announced at the end of 2020 that the previousRow logic had his attention. I assume it will be shipped before Q4 this year and then — so is my expectation— we need this usage of Find () less than today.
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. Besides you find my contributions in the Coda Community.
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 Coda, Mailjet, Zapier & Paperform to get the job done.
