You might try re-indexing the field. Open up the field's definition, turn off indexing, exit Manage Database, re-open manage database and turn it back on.
Thanks Phil for your reply, but try as I might, your suggestion and anything else I've tried does not affect this delay.
Two file soln, UI file and data file. Data file is on server connected by FMNET. Script in UI file creates new records in tables in the data file and sets the key field. Re-indexing didnt help. If I uncheck lookup, even with other calc field that reference into the joined record there is no delay. Only when I add the lookup field do I get this delay.
Alternately, if anyone can suggest an alternative way to structure this. Many sessions can refer to a note. Sessions are at times sorted and found by date, which is kept in the note record. I use the lookup to copy the note::date into the session record for relationships, sorting, and finding.
Any help is greatly appreciated.
"Script in UI file creates new records in tables in the data file and sets the key field."
Sets the foreign key or the primary key? If the primary key, that seems a dangerous way to set things up. If the foreign key, how does your script do this? This can often be automated as a side affect of creating a new related record, by entering data in a blank portal row, for example.
Our system uses looked up value fields to copy current pricing into invoice line items--a join table between Invoices and Materials--thousands of times a day over our LAN with no noticeable delays, so I'm looking for anything that might be different here.
Script in UI file **pseudocode**
Goto Layout Note
Set Variable $NoteID = Note::NoteID_prime
Goto Layout Session
Set Field Session::NoteID_fk = $NoteID <---- this is the script step that takes many seconds
Session and Note are defined and related by Session::NoteID_fk = Note::NoteID_prime in the data file. Session table has fields that reference Note, some are calc's based on the length of the note's text fields. One session field is a lookup into the Note::Date based on Session::NoteID_fk. Does this help at all?
Sounds like the delays may be due to what happens immediately after that step, "some are calc's based on the length of the note's text fields. One session field is a lookup into the Note::Date based on Session::NoteID_fk."
You might try selectively disabling each of those features to see what impact, if any each has on this delay.
I had the same thought, so I
commented out all the calc fields -- no change in delay
unchecked the lookup -- delay gone
put calc fields back -- delay still gone
re-check lookup -- delay is back
reindex the field as you suggested -- no change in delay.
What happens internally in Filemaker with that lookup in place? What kind of things might make the for the delay? Does Filemaker transfer any records back/forth from server/client that could account for it?
I don't get a delay under the same circumstances you describe unless something more than a look up is involved. How much data is being looked up? Does it need to be looked up?
A lookup physically copies data from one table to another, so if there is a lot of data being looked up (and you might have more than one field where data is being looked up when Session::NoteID_fk recieves a value in your set Field step.)