In a client-server LAN setup, I believed that portal filters are applied by the client, always acting upon the records that are sent by the server through the relationship.
Today I was confronted with a situation that seemingly bomb my belief to pieces - or at least the word “Always”.
The situation is as follows (I limit myself to writing about the databases, tables and fields directly implied in the bombing):
MacMini-based Filemaker Server 16 hosting databases, Filemaker Pro 16 Advanced on an iMac client.
Patient database, Patient table
Interested fields in Patient:
IDPatient primary key, numeric.
gAgendaDate, date, global
gIDDoctor, number, global
gSelectedPatientID, number, global
trivial, number, autoenter 1
Self relationship in Patient, TOC named SelectedPatientInAgenda:
gSelectedPatientID = IDPatient
Connected to the patient table we have the Appointments table in the Agenda database.
Interested fields in Appointments:
DateID - date
DoctorID - number
IDPatient: stored calc, GetValue(Substitute(PatientData; CHAR(9); ¶); 1), result is number
PatientText: stored calc, GetValue(Substitute(PatientData; CHAR(9); ¶); 2), result is text
Relationship Patient-Appointments, TOC named TodayAppointments:
gAgendaDate = DateID
gAgendaDoctor = DoctorID
these 2 fields are set after a doctor login, to Get(CurrentDate) and to the logged doctor’s ID. They can be changed later if desired.
On a form layout based upon the Patients table, in the Patient Database, we have various fields concerning the patient. On the right side of the layout I placed a portal showing records from TodayAppointments, 37 lines with scrolling bar. In the portal I have the following fields from the related table:
IDpatient, PatientText, timeStart, Slots.
The IDPatient, when clicked, passes its value as a parameter to a simple script that sets gSelectedPatientID to Get(scriptParameter) and GTRRs to that patient via SelectedPatientInAgenda, using the same layout we’re on.
The PatientText has a conditional formatting based upon TodayAppointments::IDPatient = Patient::IDPatient, bolding the field’s content. As result one sees the patient’s name in Bold in the portal if the Patient layout is displaying that same patient.
The patient table has 111’000 records.
The appointments table has 463’500 records.
Now, the problem.
Everything works ok and smooth, clicking in the portal immediately moves to the selected patient, changing the gAgendaDate immediately displays the corresponding patients in the portal.
However, there are entries in the agenda that have no Patient number. As it does not make sense to click to such an entry, I decided to filter the portal with
TodayAppointments::IDPatient > 0.
When returning from layout mode, a search dialog started and was on for 10-15 seconds while the portal was showing nothing, then after another 5 seconds of beachball, data was there. While watching the portal and doing nothing, the search dialog did pop up again by itself.
When I clicked on an entry in the portal, the portal first made all entries disappear, then I got the refresh symbol on it (the big round circle) and after 10 seconds the GTRR worked and the portal displayed the data again.
This repeated itself on every click and in the TopCallStats I later found lots of search entries with total duration up to 80 seconds.
As such, the filter is unusable and it’s obvious that the filtering is not acting on the 30 records that make it through the relationship, landing in the portal, but on all the appointments table.
Adding the clause trivial < IDpatient to the Relationship Patient-Appointments caused a beachball to start and after 2 minutes I force killed Filemaker then reopened it and quickly took the clause out of the relationship.
Then I changed the filter to not IsEmpty(TodayAppointments::PatientID) and everything turned back to normal, with the filter working ok and no slow down at all, so now the filter does act upon the related records, unlike before. Same holds for filtering on TodayAppointments::PatientID ≠ “”.
What gives ??