Getting list of related values in FMP 7

[Steve Jensen]

I've just moved solution I generated in FMP 6 over to FMP 7 Server, and am now going back to see where I can leverage the new features in 7. Here is one that has me stumped: What is the best way to generate a list of related values? Currently, I have a table of applicable documents. I have another table that contains paragraphs from a Statement of Work. There is a relationship between them based on document ID (actually, its a bit more complicated than that... it's a many to many relationship, so I have a jump table to link the two tables). What I want is a comma-delimited list of paragraph IDs (so I can have a field in the applicable doc record that states "Referenced by paragraph(s): 5.11, 5.12, 5.13")

My current solution is to set up a value list with the related records, and use it as so:

"Referenced by paragraphs(s) " &

Substitute ( ValueListItems(Get(FileName); "Value List of Related Records") ; "

[Fenton]

I think you're on the right path with the ValueListItems(), as far as it goes, which is for display. But, as you said, it must be unstored. Any unstored field is not so hot for searching; and when it's a related calculation via a many-to-many join table relationship, it's not a good idea. I don't quite understand how Find deals with many-to-many joins (or don't want to think about it :-), but I've also noticed that it doesn't work for finding empties. You can however Sort by it, showing you the empties.

Any solution to make it stored is going to result in "redundant" data. If the data never changes for those particular records, and easy complex Finds are mandatory, then a local field could be populated; either with a script, or by creating a similar ValueListItems calculation field in the related file (using a self-relationship), then looking that up (as you found, looking up the regular field just gets the 1st value).

The other, preferred solution would be to script the Find so that it goes to the related file, performs the find, captures the IDs of the found set, via Copy All Records on a layout with only the ID, then pastes them into a global field (in either file); with a relationship from the multi-line global to the ID.

If you want to find out how many "child" records don't have a "parent" (ie., unreferenced documents in this case), probably the best way is a scripted Loop, going through the "many" documents, seeing if each has any match.

[Steve Jensen]

Thanks for the suggestion... actually, this is how I solved the problem. I wrote a script that copies the unstored, calculated field into a static field, and then performs a find on the static field. It works...

I'm glad to hear I guessed the right solution. It helps to know I'm not way off base.