3 Replies Latest reply on Jan 23, 2015 4:17 PM by philmodjunk

    Evaluating Calculations Fields

    melcam

      Title

      Evaluating Calculations Fields

      Post

      I am creating a Daily Task tracking system. Tasks are viewed on the individual profile of each employee via portal. Each task has a "Complete" Field that needs to be set from Complete to "Not Done" each day (or sometimes every other day, depending on the frequency). I have written a calculation into Complete that uses Get(CurrentDate) and compares it to the Last Day Completed field for each task. When This evaluates to false, the field is set to not done.

      The problem I'm experiencing is that the Complete field only evaluates when corresponding data from one of the other fields is changed. I need the fields to update automatically to not done when the solution is opened, which does not happen.

      I can't set the fields to "unstored" or they will no longer appear in the portal in which they are regularly viewed.

      What is the best way to get the fields to recalculate?

      Thanks!

        • 1. Re: Evaluating Calculations Fields
          philmodjunk

          I can't set the fields to "unstored" or they will no longer appear in the portal in which they are regularly viewed.

          Why is that? are you using the field as a match field in the relationship on which the portal is based? If this is FileMaker 11 or newer, you might choose not to use this field as a relationship match field and filter the portal with a portal filter instead. Then this field can be unstored.

          Otherwise, you'd need to use the OnFirstWindowOpen script trigger that you can set in File Options... to perform a script that finds and updates all of these records.

          (Many versions ago, we had a function called "Today" that could be used in a stored, indexed calculation field to refer to Today's date and would correctly update the result with each new day. Sounds like a great idea right? Well, it had a problem. In order to work, a special routine kicked in every time the file was opened that looped through every record in the table and re-evaluated any calculation field at used this function--sometimes bringing the entire system to its knees for an extended period of time if the table had a lot of records in it.)

          • 2. Re: Evaluating Calculations Fields
            melcam

            I was using the Complete field as a match field in order to alternate between viewing the completed and uncompleted tasks in the portal. This was done by setting the sortFilter field equal to "Completed" or "Not Done". Would it be possible to use a portal filter in the same way, to alternate between views?

            • 3. Re: Evaluating Calculations Fields
              philmodjunk

              Do it all the time.

              LayoutTable::SortField = PortalTable::SortFilter

              Change the value of LayoutTable::SortField back and forth between the values of "completed" and "not done". Commit records between each change and do not make this field a global field. (If you want to do that, we have to do a few extra tricks in order to get the portal to update.)