Hi,

I have a Person table related to an Index table.

In the Index table is a Locator_Number field and a Symbol field. The Index table will always have at least 1 related record and maybe as many a 12 or so. There will always be data in the Locator_Number field but not always in the Symbol field. I want to get a field populated in the Person table that will combine all Locator_Number data and Symbol data formatted like this:

10, 25+, 56, 90, 101*, 150

I have written a script which will do this, it Loops through the found Persons (3000 - 5000 records) and at each record it Loops through the related data and compiles the field. This works but I wanted explore the possibility of a CF to do this instead. My knowledge of recursive CF's is meager. The script is listed below. Thanks for any help.

Graham

Set Variable [ $loopTotal; Value:Person::z_Count]

Set Variable [ $loopCount; Value:1 ]

Freeze Window

Loop

Exit Loop If [ $loopCount = $loopTotal + 1 ]

If [ $loopCount = 1 ]

Set Field [ Person::Loc_Export; GetNthRecord ( Index::LocNum ; $loopCount ) &

Case (IsEmpty ( GetNthRecord ( Index::LocSymbol ; $loopCount ) ) ;

"" ;

GetNthRecord ( Index::LocSymbol ; $loopCount )) ]

Else

Set Field [ Person::Loc_Export; Person::Loc_Export & ", " &

GetNthRecord ( Index::LocNum ; $loopCount ) &

Case (IsEmpty ( GetNthRecord ( Index::LocSymbol ; $loopCount ) ) ;

"" ;

GetNthRecord ( Index::LocSymbol ; $loopCount )) ]

End If

Set Variable [ $loopCount; Value:$loopCount + 1 ]

End Loop

Commit Records/Requests

Exit Script [ ]

Edit

I was thinking about this and I realized I could create a Calculation field in the Index table which would combine the Locator_Number, Symbol, comma and space. I would make it easier, but I would still like some help on the possibility of a recursive CF is this case.

Thanks again.

Edited November 18, 200817 yr by Guest

This isn't necessarily a task for a recursive CF, a scripted replace containing this:

""" & Substitute(List ( Related::Builder );["¶";"";""];["ççç";""]) & """

...where the builder field is defined in the related table to deal with NULL values:

Case ( IsEmpty ( Value );"ççç";Value )

...where the ççç is chosen, because it hardly ever occur in my language at least!

--sd

The simple thing would be to concatenate the Number and Symbol values in a calculation field in the Index table, then get them all at once using the List() function (while substituting the return with a comma).

Or you could use a custom function to do both - see:

http://fmforums.com/forum/showpost.php?post/290607/

Thanks both Soren and Comment,

It has given me ideas for a new direction.

G.

Edit

Thanks again it worked out well with your suggestions.

Edited November 18, 200817 yr by Guest