Substitute ( thelist ; "¶" ; ", " )
Substitute comma space for carriage returns
Thank you Steve.
Of course, I was not super clear on the problem.
This is an attempt to collect nicknames from child individual records for the household letter salutation.
Dear Duo and Duodo,
Dear Once, Twice and Thrice,
Dear Un, Deux, Trois and Quatre,
All work fine. Then there are people with two first names like Mary Anne and John Henry. In that case, RightValues works better than RightWords, but using RightValues messes up what works with RightWords. See below:
Case( Count (test child::name) = 2; Substitute (RightWords ( list ; 2 ) ; ","; " and");Count (test child::name) > 2;
LeftWords (list; (Count (test child::name) - 2)) &", " & Substitute (RightWords ( list ; 2 ) ; ","; " and")
list is a calc field like Steve suggested above, substituting the carraige return with a comma.
That's what is going on. Sorry to be so vague before.
I started to look at the value list option, and maybe should examine that more, where List(valuelist) where the valuelist is filtered for each household and shows the nicknames, can count them and may treat them more like items in a list (not words or values), therefore keeping Mary with Anne. However, that approach also alphabetizes the names and I am anticipating that the client is going to want to control the order of the nicknames - I might be wrong about that, and maybe should explore that more.
Thank you again,
How about =
Let ( [ names = List ( Child::Name ) ; n = ValueCount ( names ) ] ; Case ( n ≤ 1 ; names ; Substitute ( LeftValues ( names ; n - 1 ) & ¶ ; [ "¶¶" ; " and " ] ; [ ¶ ; ", " ] ) & GetValue ( names ; n ) ) )
1 of 1 people found this helpful
Or, if you prefer =
Let ( [ names = List ( Child::Name ) ; pos = Position ( names ; ¶ ; Length ( names ) ; -1 ) ] ; Case ( pos ; Substitute ( Replace ( names ; pos ; 1 ; " and " ) ; ¶ ; ", " ) ; names ) )
Both work Great!
Thank you very much!