6 Replies Latest reply on May 19, 2011 9:24 AM by philmodjunk

    Portal Filtering by Date Range

    AndrewDunn1749

      Title

      Portal Filtering by Date Range

      Post

      Hi, I know this has already been asked a couple of times but i am not completely sure yet about how this works and have had no luck in finding the answer so far.

      Background: I am currently using FM11 and have two tables set up. One for clients and another for transactions. Each client has a ref no. and each transaction has a field showing the client ref. I have created a portal on my client layout to show all associated transactions and have set up the relationship no problem.  Each transaction has a date field in which it was made.

      Problem: I would like to setup on my client layout a start date field and an end date field and have my portal filter the transactions within the date range. From what I understand I know the best way to do this is use global storage fields but I am not sure as to the proccess. If someone could please help explain how to this I would be very grateful!

      Andy

        • 1. Re: Portal Filtering by Date Range
          philmodjunk

          Actually, this can work about the same whether you use global storage or not. The filter expression is the same, but the differences between global and normal storage are something for you to evaluate in terms of your layout design and whether you are designing a database to be shared over a network or not.

          This requires that all three date fields be defined of type date. If you are using a text field to store the transactiondate, this will not work.

          Define two date fields, Date1, Date2.

          Your filter expression would look something like this: (Substitute your table and field names for mine here.)

          Transactions::TransDate > Clients::Date1 And Transactions::TransDate < Clients::Date2

          You also need to use a script to force the portal to update after you edit either the Date1 or Date2 fields. This script can be performed via an OnObjectSave trigger set on both date fields:

          CommitRecord
          Refresh window [Flush cached join results]

          Differences between global and normal storage:

          If you set up a list view layout where you can see several client records at a time, using global storage, all the portals will filter to the same date range. If you use normal storage for these two date fields, you can filter each portal by a different date range.

          If you share the file over a network, use global fields if at all possible. This prevents two users from looking at the same client record and interfering with each other's desired date range when they both try to edit the same pair of date fields. (With global storage, each user gets their own "copy" of the global fields and they don't see the values entered/edited by the other in them.)

          • 2. Re: Portal Filtering by Date Range
            AndrewDunn1749

            Hi Phil, thanks for your quick response, really appreciate the help. I will try it and get back to you if I have any problems or further queries

            • 3. Re: Portal Filtering by Date Range
              aammondd

              If you go the Global route Id suggest placeing the Global Fields in  their own table.

              Id also add a Global to store the Client ID as well so you can build your relationship against the Global table.

              You can use a number of methods to populate the Global Client ID.

              The only reason I suggest a seperate Global table is so you can keep track of your filtered relationships easier.

              If you add the global date fields to your client table you wont have to worry about the global client key.

              The global table however has some advantages though.

               

              • 4. Re: Portal Filtering by Date Range
                AndrewDunn1749
                Thanks. Actually your answer has prompted another question. Currently I have a portal showing all transactions per client. Is there a way to select multiple clients and view transactions for a custom date range?
                • 5. Re: Portal Filtering by Date Range
                  aammondd

                  thats a bit more convoluted but yes. There are a number of ways to do this as well none particularly great.  ( I edited my earlier post so if you didnt see review it)

                   Just reread phils answer it would appear that he is suggesting yes as well with not as much difficulty as I was envisioning. Of course I was trying to do it without the list view :)

                  • 6. Re: Portal Filtering by Date Range
                    philmodjunk

                    One way to view multiple selected clients is with this relationship:

                    Clients::gClientList = Transactions::ClientID

                    gClientList is a field that has each selected clientID in a list separated by returns. You'd then filter your portal by date range as we'd already discussed.

                    The challenge is in building the list in the global field in the first place. You'd need to set up a system where you can select clients to add them to the list (Drop down with list of clients is one option), and also a way to remove them from the list (button in portal row, or second drop down of selected clients.) All of this requires scripting and triggers to manage the values in the list.