Coda and Find() complications
Last week I launched my first template for sale. In this template the user can type the name of the month or day in several languages. This multi language approach is possible due to the function Find().
In this blog I’ll explain how we created this multi language SearchBox and how we overcame an undocumented issue with Find().
How to ‘find’ the name?
Ás explained in my previous article on Find() it gets you the first position of a name or a number in string. This is easy part, you get a number, a position.
Next you have to understand how that position relates to the day number (1–7) or the month number (1–12). Below the table dealing with months.
The MonthName contains per month all the names in different languages. We simply used List() to bring them together. This function automatically adds a comma to separate one month from the other.
The next part is a bit more difficult, but still good to do. We want to have the first position of each row and the last position of each row that contains the month names. Using Lenght() we get the number of characters per row:
thisRow.MonthName.Length()The column From is filled based the formula you see below:
While the ‘To’ needs this one:
The logic you see coming back is that we use the complete list of Lenght() and via Sum() and the number of the month we Slice() the total list in parts, month after month via month 1, month 2, month 3 etc.
As such we get numbers telling us where the month starts and where it ends. If we find a number, we have to check in between these values. We have at least two methods of getting there:
- greater or equal to (From) combined with smaller than or equal to (To) this gives as a virtual range
Sequence()
The image below shows the outcome of Sequence().
All we need to do is to check if the number we find corresponds with a number in this column values and via a Filter() we get the month number and thus the month.
This I got rather fast, the Slicing logic took me a bit, but not very long and I thought that I was done. I tested with a few months, even with the shorter versions like Feb, fev, jan, mar and so on. This all worked fine. Then — since this was set up in May — I looked for ‘mayo’ and I got June… The next 6 hours I rewrote my basic calculations, went out for a walk, had more coffee and finally I contacted Joostmineur to ask for help. He first suggested sticking with the simple Filter(). Indeed, the complexity of all the counting in the above is not required in this set up. One needs to type the name correctly and the formula does the rest.
MonthLookup.Filter(MonthName.Contains(TextMonth.Lower())).Mn_numberHowever I wanted to understand what went wrong and on top I knew that my French speaking clients don’t write well in English. I continued my search and noticed that when I counted the sum of all the rows that contained the Lenght() of each row I had a different number than when I counted the Lenght() of the string that had all the months taken together (12 for each language). The difference was 22. The question is, why 22?
The 22 Explained
What is the logic behind the 22? Well, once you know it, not too complicated. Each string of months linked to the next string makes that the software adds two characters to separate the last month of the last string from the first month of the following one. It concerns a comma and a white space. Since a year has 12 months, this happened 11 times. 11 * 2 = 22. This Joostmineur explained me, I only noticed the 22, not the logic behind it.
So here we are. We have to add per row the characters extra. We can do this quick and dirty or via a formula. Simply adding 2 to the Length() is okay. There is no row 13, thus the last 2 extra characters are getting lost, but do no harm. An alternative is this formula below:
This formula adds 2 to every row, but not on the last one, there you get 0. So again, both functions work and do the job, though the later is the exact one.
By now we have a function that permits us to find the month of our choice. I put it at work in this template:
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 to the Coda Community and on Twitter
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.
