Unindexable Calculations

[Chuck]

One of my biggest troubles with FileMaker is working around unindexable calculations. I often need to base a relationship on this information, and I'm continually trying to find the best workaround.

My latest idea was to take advantage of the new auto-enter calculation feature where you can specify that the calculation should replace the existing value of the field, but this doesn't seem to work when the calculation is based on related data.

So my next workaraound attempt was to create two fields. One is the unindexed calc based on related data, and the other was a regular field with auto-enter calc that is simply the first field and the "Do not replace existing value of field (if any)" option unchecked. My thinking was that perhaps the trigger field for the auto-enter calc had to be in the same table, but this didn't work (updating the field in the related table that the calc is based on updates the calc, but not the regular field). So now I'm thinking that the fields that trigger the auto-enter calc must be indexable, which is the same problem I had originally.

I do have a plugin (Troi Activator) that I can use to run a script when the fields change, but sometimes they will change during scripts, which means that the system may not be on the right layout when the field changes, which complicates the script I have to write for when the field gets updated.

Any ideas out there?

In case you're wondering, here's a more detailed description of the actual problem.

I have three tables: ITGBL, Traffic and TrafficSections. TrafficSections has a field text field called ManifestID and a calculation field with number result called AssignedToManifest = not IsEmpty( ManifestID ).

TrafficSections is related to Traffic based on a TrafficID field in TrafficSections. Traffic has a calculation field with number result called AssignedToManifestCalc = Sum( TrafficSectionsForTraffic::AssignedToManifest ) > 0. Finally, Traffic has another field, a number field called AssignedToManifestAutoEnter with auto-enter options set to enter a calculation (simply AssignedToManifestCalc) and the "Do not repalce" option turned off.

ITGBL has a complex relationship (something like six or seven keys using different types of match operators). ITGBL has should only show records in Traffic that are not assigned to a manifest. Currently, the match criteria for that portion of the relationship is set to gConstant <> AssignedToManifestAutoEnter, but the portal into the relationships shows all of the Traffic records because none of them are updating from the AssignedToManifestCalc using the auto-enter options.

Any help would be greatly appreciated.

Thanks,

Chuck

[Vaughan]

Ray has written a paper on this. Go to http://www.nightwing.com.au/filemaker/ (the URL might not be correct).

[Ugo DI LUCA]

Hi,

As Vaghan said, Ray has an article about auto-indexing, which is based on a lookup on Modification Date.

I'm not sure though that it would fit your needs as the modification would take place in the Traffic Section Table, and you'd like in your case to have the field in traffic upating when a record in its related Table is modified.

Though, a workaround would be to use calculated fields for the portal display, so that you can "hide" those with no Affiliation. The portal would need to be sorted backward involving your boolean calc, so that the matching records would display at first.

Another method, scripted, would be to use a Copy All Records step in a layout where only the Traffic ID is showing, based on your calculation Choose(AssignedToManifestCalc;"";ID) and grab this list for a matching relationship.

The second method would be quicker, wouldn't need extra fields, and more reliable.

I suddenly have an idea I might test. I'd report if it works.

HTH

[Chuck]

The paper mentioned is found at http://www.nightwing.com.au/FileMaker/resources/AutoIndexing.pdf in case anyone else is interested. I'll take the time read it today or tomorrow and report back on it's utility.

Thanks,

Chuck

[Ugo DI LUCA]

Hi,

The latest idea I had was working with 6 but no with 7 because of the Commit Record/Request thing.

It involved a lookup from the distant table if you entered the TrafficAssignements through a portal in Traffic.

I'll stay tune to see if really there's something else than a plug in or a scripted method.

[Noah Miller]

I'm running up against this same problem. Has anyone found a solution -- a way to trigger an auto-enter calculation that refers to fields in a related table?