2 Replies Latest reply on May 24, 2011 10:28 AM by GeraldRivard

    relationship requiring unstored calculation

    GeraldRivard

      Title

      relationship requiring unstored calculation

      Post

      I'm having a problem with a two-criteria relationship that involves an unstored (and necessary but apparently unstorable) calculation.

      Consider this subset of my data structure:
       Table Steps:
         ID
         AgentID
         Status (a calculation that relies on Sum(Sessions::Minutes) )
         ToDoFlag (a calculation that will be 1 for certain Status values)
       Table Sessions:
         ID
         StepID
         Minutes (a calculation from start & end times)
       Table Agents:
         ID
         ToDoMatch (a calculation that is always 1)

      I'm trying to build a relationship where
        Agents::ID = Steps::AgentID
      AND
        Agents::ToDoMatch = Steps::ToDoFlag

      It doesn't show any records.  If I remove the second criteria it does show records, including ones I don't want.  I think the problem is that the calculation fields that Steps::Status relies on include Sum(Sessions::Minutes), which is unstored.  But I can't change it not to be unstored, I think because it uses Sum. 

      Any ideas?  Thanks in advance.

        • 1. Re: relationship requiring unstored calculation
          philmodjunk

          Two options you can evaluate:

          1) If you are using FileMaker 11, use the relationship that just matches by ID, but then use a portal to display the related data with a portal filter expresssion: Agents::ToDOMatch = Steps::ToDOFlag

          2) Use a script that updates ToDoFlag everytime a field referenced in the calculation (Such as the start and end times in Sessions) is edited. The OnObjectSave Script trigger can be set on each such field to perform a script to do this update. This then changes ToDoFlag into a stored, indexed number flag that will work with your relationship.

          Keep in mind that 1) works well for displaying data, but 2) is sometimes necessary in order for certain calculations to work.

          • 2. Re: relationship requiring unstored calculation
            GeraldRivard

            Thanks.  I had thought of 2 after sleeping on it, and it is workable, but 1 may be a better option for what I'm trying to do (and there will likely be no reliant calculations).  But I'm using 10.0v3 (I thought I had that in my note, but it must have gotten lost in all my copy/pasted html iterations).  This feature may warrant an upgrade though.  Thanks again for your quick and knowledgeable response.