8 Replies Latest reply on Aug 16, 2016 2:12 PM by philipHPG

    Portal showing only meetings in last 7 days...

    ezeitgeist

      I have a table that logs meetings with clients. I want to put a portal in a layout that shows just the meetings in the last 7 days. I'm assuming this involves a calculation and maybe CurrentDate. What's the best way to approach this?

       

      To note, yes, I have a field that has the date of each meeting in the record.

        • 1. Re: Portal showing only meetings in last 7 days...
          philipHPG

          The easiest way to do this is simply to apply a filter to the portal (select Filter portal records in the Portal Setup dialog box). Then, in the calculation you would use something like:

           

          MeetingTable::MeetingDate > ( Get ( CurrentDate ) - 7 )

           

          Of course, if you have a lot of meeting records and this is slow there are other options (filtering the relationship rather than the portal), but this is the easiest option with which to start.

          • 2. Re: Portal showing only meetings in last 7 days...
            ezeitgeist

            That worked perfectly. Now for the somewhat more complicated one ... I want to setup another portal that shows the 5 clients that need most touching base with. And by that, I mean the ones who have gone the longest since being last contacted. Given that clients are contacted a number of different times throughout the relationship (so multiple records with their specific name on it, but differing dates of the meetings/contact) how can I filter it to show the oldest 5 records of the group of only the most recent meeting dates of each client? Does that make sense?

             

            Like 1 filter says to only look at the most recent meeting of each client, and then another shows just the 5 that are the oldest.

            • 3. Re: Portal showing only meetings in last 7 days...
              philipHPG

              I understand what you are asking. It is possible.

               

              One option would be to create a MostRecentMeeting field in the client record. Either this could be a calculation: Max ( MeetingTable::MeetingDate ) or, for better performance, you could have a script trigger to update this every time a meeting is logged. Then you would have a new cross-join table occurrence (ie. AllClients) for your clients sorted by MostRecentMeeting and display the portal using that table occurrence. Does that make sense?

              • 4. Re: Portal showing only meetings in last 7 days...
                ezeitgeist

                Let me see if I follow:

                 

                (1) Create a script that is triggered by a new record of a meeting. This script makes a field on my Client table update with the most recent meeting from that specific client.

                 

                (2) Create a portal that shows the oldest of these meetings based on the new MostRecentMeeting field as the way to sort them. How do I limit a portal to only show 5 records? Just have 5 rows and no vertical scroll bar?

                • 5. Re: Portal showing only meetings in last 7 days...
                  ezeitgeist

                  Crap. I just realized, since I have multiple Users, the way I have it setup is a Table of AllClients, a Table of AllUsers, and then a Table of AllMeetings. To log a meeting, you select your User_ID and then the Client_ID and that links the two. I do not have an AllClients record that keeps track of only meetings with one individual User. Technically, the Meeting just pulls the contact information from the Clients table into a new record in a related table. And then is linked to teh User via User_ID.

                   

                  So I cannot really do step 1.

                  • 6. Re: Portal showing only meetings in last 7 days...
                    philipHPG

                    So you want the portal to show the top five clients who have remained the longest without meeting a specific user (not just any meeting at all)?

                    • 8. Re: Portal showing only meetings in last 7 days...
                      philipHPG

                      Okay, I would be inclined to use ExecuteSQL to generate a list of the top five clients by user id, then save that list into a global field upon which the portal relationship is built.

                       

                      If you need help with that, let me know the relevant table and field names and I can suggest an ExecuteSQL function call that could work.

                       

                      Also, please verify which version of FileMaker you are using (to ensure that ExecuteSQL will work).