7 Replies Latest reply on Sep 30, 2014 9:38 AM by philmodjunk

    Filter portal dynamically by date range.

    TimCranwill

      Title

      Filter portal dynamically by date range.

      Post

      I have looked at some of the solutions for this question but none all encompassing. I need a checklist of must haves for this to work. Or maybe just a link to something updated and a clear step through.

      Thanks

        • 1. Re: Filter portal dynamically by date range.
          philmodjunk

          Then I suggest you start by describing what you want in more detail. As a guess, you want something like this:

          Relationship:

          Parent-----<Child

          Parent::__pkParentID = Child::_fkParentID

          There's a field of type Date in Child and you want to filter a portal to Child by specifying two dates, with all related records from child that have a date that is not in that date interval, omitted from the portal.

          Does that sound like what you had in mind?

          PS and I can think of about 4 different ways (all similar variations of the same concept) to get that result...

          • 2. Re: Filter portal dynamically by date range.
            TimCranwill

            That is exactly what i have in mind, I have parent child relationship as you described.

            I have a table of employees(parent) and another table expenses portal (child). So far i have placed 2 global date fields in the parent table, stat date and end date, and a script that sets a start date variable and an end date variable then performs a find within the portal date field with the "..." between the 2 dates but it does not omit anything?

            please let me know if you need more detail than this? Thanks

            • 3. Re: Filter portal dynamically by date range.
              philmodjunk

              That find won't produce the results you want. It won't filter the portal at all, it will only modify the layout's found set by finding only those employee records that have at least one expenses record in the specified date range. Then, once the layout returns to Browse mode, you see all related expenses records for the employees thus found.

              Do this instead:

              Go to Manage | Database | Relationships.

              Select Expenses and make a duplicate Tutorial: What are Table Occurrences? by clicking the duplicate button (two green plus signs). Double click the new occurrence box to open a dialog where you can rename it to be Expenses|DateRange.

              Now drag from Employees to Expenses|DateRange to link it exactly the same as your current relationship between employees and expenses.

              Double click this relationship line to open the Edit Relationship dialog. Modify the relationship by selecting your two global date fields and creating a set of Match field pairs similar to this: (Use your field names in place of mine)

              Employees::__pkEmployeeID = Expenses|DateRange::_fkEmployeeID AND
              Employees::Start Date < Expenses|DateRange::Date AND
              Employees::End Date > Expenses|DateRange::Date

              (Employees::__pkEmployeeID = Expenses|DateRange::_fkEmployeeID is my notation for the original pair of match fields that you used in your original relationship linking Employees to Expenses.)

              Now return to your layout and add a new portal to Expenses|DateRange in place of your current portal. By selecting different dates in Start Date and End Date, you'll see different sets of related Expenses records for the current Employee record appearing in your portal.

              Caulkins Consulting, Home of Adventures In FileMaking

              • 4. Re: Filter portal dynamically by date range.
                TimCranwill

                That worked like a charm, now I'm attempting to have multiple portals from different tables also filter based on that global start and end date. Should just be a matter of making more table occurrences with that less than or equal to relationship? 

                • 5. Re: Filter portal dynamically by date range.
                  philmodjunk

                  The global fields can often be defined in any table, but when used as match fields, they must be defined in the parent table of the relationship where they are used.

                  • 6. Re: Filter portal dynamically by date range.
                    TimCranwill

                    As you can see on the right hand side all the expenses and the date range table occurrences. those all work fine. Now im trying to have the pink table on the left "expenses rounds" filter the same way. The reason its relationship with rounds is because we are having expenses generated while doing rounds. How can i have that also filter the same way as the date range occurrenses?

                    • 7. Re: Filter portal dynamically by date range.
                      philmodjunk

                      From a context of a layout based on what table occurrence? Rounds?

                      Your match fields, all 5 pairs of them, do not seem workable for a relationship. I would expect to see a match only by RoundFK to Round_Id pluse the pair of date fields so that you can specify a date.

                      But that still seems a bit odd. Isn't a single record in Rounds limited to a single "day"? I don't know for sure what a "round" is in your context (Doctor's rounds?), but it doesn't sound like something that would be even an entire day in length...