5 Replies Latest reply on Jan 25, 2012 11:01 AM by LauraZ.

# calculating list of two names in a field

Hi -

Calc on related values List(name) where names are:

Joe

Jim

Jane

Substituting to get Joe, Jim and Jane

I have a couple of ways to do it, based on number of values or number of words.

Mary Jane

John Henry

Anna Lee

??

I am sure somebody has it.

Thank you!

Laura Z.

• ###### 1. Re: calculating list of two names in a field

Hi LauraZ,

Substitute ( thelist ; "¶" ; ", " )

Substitute comma space for carriage returns

--

Steve Moore

Cumberland, Maine

• ###### 2. Re: calculating list of two names in a field

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 Uno,

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)

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,

Laura Z.

• ###### 3. Re: calculating list of two names in a field

```Let ( [
names = List ( Child::Name ) ;
n = ValueCount ( names )
] ;
Case (
n ≤ 1 ; names ;

Substitute ( LeftValues ( names ; n - 1 ) & ¶ ; [ "¶¶" ; " and " ] ; [ ¶ ; ", " ] ) & GetValue ( names ; n )
)
)
```
• ###### 4. Re: calculating list of two names in a field

Or, if you prefer =

```Let ( [
names = List ( Child::Name ) ;
pos = Position ( names ; ¶ ; Length ( names ) ; -1 )
] ;
Case ( pos ; Substitute (  Replace ( names ; pos ; 1 ; " and " ) ; ¶ ; ", " ) ; names )
)
```
1 of 1 people found this helpful
• ###### 5. Re: calculating list of two names in a field

Both work Great!

Thank you very much!

Laura Z.