4 Replies Latest reply on Jul 22, 2012 7:04 PM by RyanVicente

    Show data in portal based on multiple filter



      Show data in portal based on multiple filter



      I'm pretty new with FileMaker and I'm working on a database where I have data from different dates that are listed by Week, Month, Quarter. I need help in showing a filtered data in a layout via a portal. The user will have to set filters by "Person" and then select one of the following: a. Week b. Month c. Quarter. 

      Playing around with it and thanks to a lot of forum answers and online resource, I found a way to get the data to show by Week; but can't seem to make the Month and Quarter work. 

      Any help is appreciated. Thanks!

        • 1. Re: Show data in portal based on multiple filter

          I have data from different dates that are listed by Week, Month, Quarter.

          Exactly what do you mean by that? Is there a single date field in your portal's table and you want the user to see all related records where the date falls in a user selected week, month or quarter?

          How do you plan to set up the layout for them to specify this? (There are several different ways and I can adapt the filter expression to work from the methods you choose to set up.)

          • 2. Re: Show data in portal based on multiple filter

            Hi PhilModJunk, 

            Thanks for the reply. 

            Basically, team members log multiple activities in the database on a daily basis. For example:

            Staff - Date - Task - Hours

            Rick - 1 Jan 2012 - Laundry - 2 hours

            Rick - 12 Feb 2012 - Lunch - 1 hour

            Jo - 2 Jan 2012 - Laundry - 3 hours

            Jo - 27 Feb 2012 - Lunch - 1 hour

            The portion where they "log" the activies doesn't have a portion for week, month or quarter. The plan is to do that in the background probably by playing around with Date functions available in FileMaker like WeekOfYear, etc. 

            Now, there's a different layout where the staff can then select fields that have their name as the first filter and one of the ff as a second filter (each have their own separate field, the user just picks one and leaves the other 2 blank):

            A. Week number, B. Month, C. Quarter

            The plan is for the portal to show records that fall on the filters selected and show:

            Date - Task - Hours

            I hope this clarifies things a bit. :D Thanks in advance for any help that you could offer. 

            • 3. Re: Show data in portal based on multiple filter

              I've included a screen shot of a set of fields I use in some of my databases for pulling up reports of dated records just to show what might be used for this. While my scripts use the data in these global fields to create search criteria for performing a find, a portal filter could also use the same interface design.

              Working just from what you describe, I don't see a field for specifying the year. I would guess that either your current concept assumes that these filters will work off of the current year or that you left out a field for specifying the year.

              I'm going to assume that you also have a "year" field, but you can replace each reference to that field with Year ( get ( CurrentDate ) ) if you want the filter to always filter for this year's data.

              For week number, I am also assuming you want to match to the value returned by WeekOfYear ( date ).

              This portal filter should do the trick, but to avoid filtered portal refresh issues note my comments at the end of this post that discuss a method for a relationship that will smoothly update each time the user modifies one of these "filter" fields.

              Let ( [ Y = Year ( PortalTO::Date ) ;
                        M = Month ( PortalTO::Date ) ] ;
                        Case ( Not IsEmpty ( YourTable::WeekNumber ) ; WeekOfYear ( PortalTO::Date ) = YourTable::WeekNumber and YourTable::year = Y ;
                                   Not IsEmpty ( YourTable::Month ) ; YourTable::Month  = M and YourTable::year = Y ;
                                   YourTable::Quarter = Ceiling ( M / 3 ) and YourTable::Year = Y
                                 ) // case
                    ) // Let

              This filter refers to four fields that can be edited by the user: WeekNumber, Year, Month and Quarter. Unless you include them in the portal's relationship, you'll need to use a script (can be performed by script triggers on these four fields) with this script step: Refresh Window [Flush cached join results]. This works, but can result in very long delays while the screen refreshes in a number of circumstances so it is better to avoid using this script step when possible. You can avoid needing that script if you set up your relationship for the portal like this:

              YourTable::WeekNumber x PortalTO::anyField AND
              YourTable::Month x PortalTO::anyField AND
              YourTable::Year x PortalTO::anyField AND
              YourTable::Quarter x PortalTO::anyField AND
              add the current fields already used in your relationship here

              You can double click the relationship line to open a dialog box where you can add the additional fields and use the cartesian join operator (x) instead of =. It will not matter what field in the Portal's table occurrence that you specify for these added pairs of match fields.

              • 4. Re: Show data in portal based on multiple filter

                Thanks for the help! This should help a lot :)