9 Replies Latest reply on Feb 23, 2011 9:59 AM by SScheier

# Summary of Text Fields

### Title

Summary of Text Fields

### Post

Is there a function that makes it possible to have a calculation field that summarizes Text fields from a number of records (similar to the GetRepetition function)? I am trying to combine FirstName field info from a number of related records, so that it can make sense in a correspondence -  not just show as a list. I'm assuming the calculation should look something like this FirstName(Record1) & ", " & FirstName(Record2) & " & " FirstName(Record3), but how can I get it to take the info from the various records?

• ###### 1. Re: Summary of Text Fields

Substitute ( List ( RelatedTable::FirstName ) ; ¶ ; ", " )

• ###### 2. Re: Summary of Text Fields

Thank you! This worked great, but is there a way to have a comma between the FirstNames of the first two records and an "&" sign between the last two records (in the case of 3 related records)?

• ###### 3. Re: Summary of Text Fields

Let ( [ TheList = List ( RelatedTable::FirstName) ;
ListRemainder = Substitute ( RightValues ( TheList ; ValueCount ( TheList ) -1 ) ; ¶ ; " & " )
] ;
GetValue ( TheList ; 1 ) & ", " & Left ( ListRemainder ; Length ( ListRemainder ) - 3 )
)

• ###### 4. Re: Summary of Text Fields

Let(
L = Substitute ( List ( RelatedTable::FirstName ) ; ¶ ; ", " ) ;
Replace ( L ; Position ( L ; "," ; 1 ; PatternCount ( L ; "," ) ) ; 1 ; " &" )
)

@Phil
your calc works only if the list contains 3 values.

• ###### 5. Re: Summary of Text Fields

@raybaudi, I always learn new tricks from your posts!

your calc works only if the list contains 3 values.

It doesn't work with 1 value. It works with 2 or more. I ran a few test before I posted it.

• ###### 6. Re: Summary of Text Fields

@ Phil
Are you sure ?

with: returns:
1 value 01,
2 values 01, 02
3 values 01, 02 & 03 // the unique correct result
4 values 01, 02 & 03 & 04

( those are the results of my test )

• ###### 7. Re: Summary of Text Fields

BTW: there is an error into my first calculation; try:

Let([
L = Substitute ( List ( RelatedTable::FirstName ) ; ¶ ; ", " ) ;
C = PatternCount ( L ; "," )
];
If ( C ; Replace ( L ; Position ( L ; "," ; 1 ; C ) ; 1 ; " &" ) ; L )
)

or:

Let([
L = Substitute ( List ( RelatedTable::FirstName ) ; ¶ ; ", " ) ;
LastCommaPos = Position ( L ; "," ; Length ( L ) ;  - 1 )
];
If ( LastCommaPos ; Replace ( L ; LastCommaPos ; 1 ; " &" ) ; L )
)

• ###### 8. Re: Summary of Text Fields

And for mine. I understood that to fit what as requested here (except for a single item). I guess I didn't read the last post carefully enough...

• ###### 9. Re: Summary of Text Fields

To raybaudi:

Thanks loads for your suggestions! The results look Great! Perhaps one day I'll understand your formula so that I can adapt it to other situations as well.