Jump to content

Related rocords in calculations


jwells
 Share

This topic is 5667 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 5667 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.