How to Coda the ‘And’ in your string?
The powerful list logic applied using Slice()
In the community again an interesting question was asked.
Is it possible to write a formula so I can get a list of items from a column and have the word “and” appear before the last item regardless if there are 2 or 20 items in that column?
My contribution you find below:
This is all rather simple and straightforward. As I wrote in my contribution, the question can be rephrased into “how to glue two lists together whereby the ‘and’ should precede the last item”.
In the community the question was brought up why we no longer could find the Array group of formulas. Indeed these functions now live under the umbrella of lists, which is indeed a better way to identify them.
In Coda everything is a list and once you see that, you don’t need very complex operations to get the job done. Honestly speaking I was surprised to see that salient suboptimal suggestion of Scott, as if he — the man behind Coda School explaining all Coda functions — does not understand this.
When everything is a list, you have comma’s that separate the items in the list, they don’t show up at the end, only between items. One item does not have a comma. When you see this, you understand that when you glue a list of items together with one item, you are done.
I like the Slice() function quite a bit. It permits not only to slice between positions like 3,7 but also to Slice as of for example position 17 or to get the last items using a negative number.
There is something however that is not very well understandable. First I wrote the function below to get all the items in the list, but not the last one. As you see, that works.
However once put inside the Format() function, it does not work that way anymore. You only get one item, the first item of the list.
This is weird in in my opinion likely a bug. May Coda read this, they can follow up on it.
Anyway, to solve the issue I added List() to the end and that worked as if this function woke up the sleeping property.
The second part was the easiest of all, you only need the Last() item in the list. Glued together we had a response as asked for. Since the items lived in the display column, you get row references back and by rewriting each item as text — using lamb.Nameto reference the column value as (which is text) and not the object (which you recognize by @ — the outcome is also pleasant for the eye.
Last but not least, what if the list has only one item? In that scenario we need to take into account two scenarios and we use and If statement to have to types of outcomes:
If(thisRow.lamb.Count() = 1,
thisRow.lamb.Name,
Format("{1} and {2}",
thisRow.lamb.Name.Slice(1,thisRow.lamb.Count()-1).List(),
thisRow.lamb.Name.Last()))Extra
Once you have list of items that comes back in its full length again and again, it gets on your nerves easily. In this context it is an option to apply what I did in a document related to divorce planning. In case we deal with the complete list and there are more than two children, we note simply “all children”.
My name is Christiaan and I support SMB with calculations (budgets and planning) and I prefer using Coda to get the job done.
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.
If you enjoyed this read and would like to get more Coda related content, please consider a Medium membership. It is it only $5 a month, and you’ll have access to every article ever published on Medium. If you sign up using my referral link, I’ll earn a small commission.
