Append data in one field of related records

[Jana]

I understand how to create an append of the data from multiple fields into one using &..., but now I'm tyring to create a field that will append the data of one single field for all related records. My attempts thus far aren't working.

Any guidance as to what function I should use?

thanks

[Fitch]

Could you give an example of the fields you're starting with and what you want the result to look like? I'm thinking GetNthRecord, but I'm not sure exactly what you're going for.

[Jana]

I've got a database that was designed by someone else and has been in use for a while that I'd like to modify. They created a Notes field to go in a job tracking layout, Project Status. Each note placed in the Project Status has been entered into a separate record through a portal on the layout as an action item.

I'd like to be able to join/append of the Notes entered into the portal to read as one continuous string of text, with some punctuation as separators, by creating an new field that appends the related Notes records into one text field.

Hope that helps to clarify. BTW, I'm actually working with FM 8 on this project.

Thanks

[Fitch]

I think you want something along these lines:

GetNthRecord ( related::note ; 1 ) & ¶ &

GetNthRecord ( related::note ; 2 ) & ¶ &

GetNthRecord ( related::note ; 3 ) & ¶ &

etc

If you do this in a script, you have the option of going to the related records and looping through them, but using GetNth your loop would look something like:

Set Variable( $how many ; Count( related::note )

Set Variable( $counter ; 0 )

Set Variable( $text ; "" ) // I like to initialize variables at the top.

Loop

Set Variable( $counter ; $counter + 1 )

Exit Loop If( $counter > $how many )

Set Variable ( $text ; $text & GetNthRecord ( related::note ; $counter ) & ¶ )

End Loop

Set Field( NewNote ; $text )

It's possible to do something like this in a calculated field, but you'd need to use a recursive custom function and I don't know if you want to get into that.