Portal filtering using global variable - Looking for better solution
I have Clients and Instructors which are indirectly related via Events. Now I want to store and display the real-life relationship status ("friendship") between Clients and Instructors and between Instructors themselves. The Instructor who is logged into the database should see only friendship status' that includes himself.
I have designed a working solution, however, there are some glitches I dislike. My solution works the following way:
- I added two tables holding only fID_Client, fID_Instructor, FriendshipStatus and fID_Instructor1, fID_Instructor2, FriendshipStatus respectively. FriendshipStatus is stored in a simple boolean field (number field using 0 or 1, shown as checkbox). fID_Client is related to pID_Client in the Clients table. fID_Instructor is related to pID_Instructor in the Instructors table. As the logic is the same for both tables I am describing the solution for the Client-Instructor relationship only.
- Each Instructor dataset includes a field holding his AccountName. When an Instructor logs into the database I am searching for the matching Instructor dataset and store its ID as a global variable (which is specific for one session only).
- On a layout based on a Clients table occurrence the Friendship checkbox is display within a portal. This portal is filtered using $$Logged_In_InstructorID = fID_Instructor so that only the one checkbox is displayed which is related to the Instructor currently logged in.
- Each time a new Client is added a new dataset in the Friendship table is created as well and fID_Client is assigned and fID_Instructor is set from the global variable. The same holds true whenever an Instructor is viewing an existing Client dataset for the first time i.e. when no record exists in the Friendship table including his ID and fID_Client.
What I dislike about this solution:
- I need to add a portal for display of just one field in one row. This portal row highlights in grey color whenever a user clicks on it. As this behavior is implemented deep within Filemaker (CSS) it cannot easily be changed.
- I need to add a script trigger executing "Refresh window (flush cached join results)" in order for the portal to update appropriately. I learned in this forum that this method has negative impact on database performance when viewing data over the network on iOS platform.
- Portal filtering is inferior to filtering by relationship the more records have to be filtered.
So far I have not come up with a better solution avoiding these glitches.
I'd be happy if someone can add some ideas. Thanks.