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.
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.
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?
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?
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.
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)?
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).