why Lookup field very slow
Can anyone tell me why using a lookup field in a join table would lead to many second delays when setting the key field?
DETAILS: I have a join table related to several child tables ( a session is related a doctor, patient, procedure, and note). I have a New Session script which fills in the key/id fields of the new session record. When the NoteID_fk field is set, Filemaker "pauses" for 5-7 seconds (measured by logging current time before and after SetField script step). I've tracked the delay down to the existence of a lookup field in the session table that copies the session date from the note table. I do this because the session date is used (in other) relationships, finds, and portal filters. With everything else the same, if I check/uncheck the field's "lookup" and re-run the script the difference in running the script is 5-7 seconds. Things I've thought to check myself: 1) NoteID field is indexed fully. 2) there are two lookup fields (the other is a to a field that is never the object of a find, relationship, etc) and the delay is the same if either or both "lookup" is checked. 3) If I uncheck both lookups, there is no delay even with field based calcs into the same note table.
Any help would be greatly appreciated, as the delays in running this script multiple times get's really annoying to users.