4 Replies Latest reply on Aug 10, 2011 7:55 PM by PeterDowns

    Filtering portal with date range

    PeterDowns

      Title

      Filtering portal with date range

      Post

      Hi 
      I have a layout attached to a table tasks. Nested in it is a portal displaying all records of the table tasks via a 2ndary copy of the table through the relationships section called tasks 2.

      I have 2 global fields called g_startDate and g_FinishDate in table tasks and a field called Date_Start in tasks which is when the task actually starts. 

      I have set up a script "dateRangeTasks" containing - 
      Go to Portal Row [Select; First]
      Enter Find Mode []
      Set Field [Tasks 2::Date_Start ≥ Tasks::g_StartDate]
      Set Field [Tasks 2::Date_Start  ≤ Tasks::g_FinishDate] 
      Perform Find[] 

      On the main layout I have set up 2 drop down calendars displaying data from the 2 global date fields The script is triggered on "OnObjectSave" on both date fields. The portal contains the Date_Start field. Before the filtering the portal is showing all the tasks correctly.

      When I select a date (either start or finish) I get an error message - "No records match this criteria". and I know there are valid records that fit into the date range.

      Any ideas
      thanks in advance

      Cheers

      Peter

        • 1. Re: Filtering portal with date range
          philmodjunk

          You cannot perform finds on a portal like this. FInds are performed on the layout's table occurrence, not the portal's.

          What version of FileMaker are you using?

          If this is FileMaker 11, you can set up a portal filter expression in Portal Setup...

          Tasks 2::Date_Start ≥ Tasks::g_StartDate and Tasks 2::Date_Start  ≤ Tasks::g_FinishDate

          to restrict the records listed in the portal to just those related records that fall in this date range.

          To get the portal to update promptly when you select a different date range, you'll need to use this script step: Refresh Window [Flush cached join results] to force the portal to update with the new date range. An OnObjectSave trigger on each of the two global fields can be used to perform this script.

          If you are using FileMaker 10 or another fairly recent version that supports relaitonships with  more than 1 pair of fields, you can modify your relationship to include these three date fields with the inequality operators. And while this can be more work to set up, it does not require using a script to update the portal each time you change a date in the global date fields.

          • 2. Re: Filtering portal with date range
            PeterDowns

            Hi Phil,

            thanks again

            worked a treat, brilliant. (Using File Maker 11)

            Followup question. I have a calculation field sitting next to the date fields that sums up the total cost of tasks in the portal. Works well. Now that I am filtering the portal I obviously need to filter the sum. The sum field is also in 'Tasks 2' called 'TasksSum' and the calculation is performed in the table as a summary calculation (total of the field 'taskCost'). How do I go about that?

            Cheers

            Peter

            • 3. Re: Filtering portal with date range
              philmodjunk

              If this is a summary field defined in the portal table and placed inside the portal, it will update to summarize only the records that pass the filter. Since you often only want to see this field once, not in every portal row, you can put a second portal to the same table occurrence and with the same portal filter on your layout, but set up with just this summary field and with the portal set to display only a single portal row. If you make the portal boundaries invisible, this field will look just like a lone field on your layout.

              • 4. Re: Filtering portal with date range
                PeterDowns

                works a treat

                thanks