Related rocords in calculations

[jwells]

How do I get related records formatted in a calculation? I'm trying to build emails that show client information, including the into from related records. Is there some way to loop through, or specify to go to the next record?

Here what I have now. It works for only the first record, but then doesn't change to the next record next.

=======================

Last year you received:¶

¶

" & tblDistributorDetails::QMags & " " & tblMagazines::MagName & "¶

" & tblDistributorDetails::QMags & " " & tblMagazines::MagName & "¶

" & tblDistributorDetails::QMags & " " & tblMagazines::MagName & "¶

¶

======================

Thanks,

John Wells

[Ender]

It's possible to do this by defining a value list based on the related field, then use the valuelistitems() function to display the list of related items. However, this approach has a couple issues:

1. It requires the additional overhead of the value list.

2. If you want to show more than one related field, you'd need a calc in the related file to combine the multiple fields into one.

3. If the number of related records is large, it can be slow to refresh.

4. The sort order is alphabetical.

Another approach utilizes FM8's GetNthRecord() function. If the number of related records is small, you could hardcode the iterations, like:

GetNthRecord(related_table::field;1) & ¶ &

GetNthRecord(related_table::field;2) & ¶ &

GetNthRecord(related_table::field;3) & ¶ &

GetNthRecord(related_table::field;4) & ¶ &

...

A more general approach requires FM7 Developer or FM8 Advanced, and uses a recursive custom function to list all the iterations. This approach still has issue #3 above.

And finally, you could always use a scripted approach to loop through the related records, and update a local text field. With this, the performance would be the same to get the field updated, but once it's populated, it would be quick to go from record-to-record.

Edited June 2, 2006 by Guest

[jwells]

Wow. Thank you. This is exactly what I was looking for. I think for my needs right now, the "GetNthRecord" is best. But I'm very glad to have learned about the other methods as well.

Many thanks!

John Wells