Chuck Posted September 20, 2004 Share Posted September 20, 2004 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 Link to comment Share on other sites More sharing options...
Vaughan Posted September 20, 2004 Share Posted September 20, 2004 Ray has written a paper on this. Go to http://www.nightwing.com.au/filemaker/ (the URL might not be correct). Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted September 21, 2004 Share Posted September 21, 2004 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 Link to comment Share on other sites More sharing options...
Chuck Posted September 21, 2004 Author Share Posted September 21, 2004 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 Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted September 21, 2004 Share Posted September 21, 2004 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. Link to comment Share on other sites More sharing options...
Newbies Noah Miller Posted December 31, 2004 Newbies Share Posted December 31, 2004 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? Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7190 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 accountSign in
Already have an account? Sign in here.
Sign In Now