Complex export from related fields

[danjacoby]

I've created a calc field containing the contents of a value list that consists of related fields from an external file. It takes a few seconds to come up, but that's another problem.

The current problem occurs when I want to export the contents of this field. Unfortunately, when I export more than one record, the field doesn't take the time to recalculate for each record; as a result, the contents of the calc field from the first exported record gets exported over and over.

Help.

[BobWeaver]

Is the calc field stored, or unstored?

[danjacoby]

It's unstored. It has to be, since related records can be added/edited.

[BobWeaver]

Hmmm, deja vu. I seem to remember this topic coming up before.

http://www.fmforums.com/cgi-bin/ultimatebb.cgi?ubb=get_topic&f=16&t=000471&p=

The formula won't always update because Filemaker doesn't detect changes to the referenced data when it's referenced by valuelistitems. But, it does update if you reference an aggregate function. So the trick is to add a dummy reference to the related file like this:

Left("",Count(RelatedFile::info))

which doesn't add anything to the calculation, but does force it to recalculate. So, your complete formula would be:

Valuelistitems("Mainfile","ValuelistName")&Left("",Count(RelatedFile::info))

Don't know if that's the problem in this case, but you could give it a try.

[danjacoby]

Haven't tried it, Bob, but thanx for the thought.

I ended up creating a text field and had the export script set the text field to equal the calc field in all records updated since the last export. Then I export the text field.