With a bit of work you can build properly punctuated lists by utilizing a combination of Concatenate, Concat, Split, CountRows, FirstN, and Last. Whew!
Others may have a more elegant way of accomplishing this, but this one definitely works for me. I actually whittled this version down as I was preparing this blog from the much clunkier version I started with. Continuous improvement! It's the name of the game!
So, for this example, I used a Collection (MyPeople) with one column (Name). I gave it 5 names to work with, but the logic is independent of list size.
We build the properly punctuated list as follows:
Let's break this down to make it easier to follow:
We are almost there! All that is left is to tack on the final punctuation and final name.
Applying this logic to the 5-item collection above yields the desired result:
John Doe, Jane Snow, Abby Normal, Slim N None & Jack Spratt
One final tweak is needed to handle scenarios with only one or two names correctly by wrapping our logic in an If statement. We'll throw in a check for an empty collection, just for good measure. (Thanks @CBroeker for asking about how to handle 1 and 2 name cases.)
In addition to using the collection demonstrated above, I have tested the logic with a Sharepoint list using both a Person column and a single line text column. Both works identically.
I hope this helps you build cleaner lists for use in labels or HTML Text output. If you have a leaner method, please share!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.