High performance large value lists?
Is a related table the best source for a value list containing thousands of choices? If so, how can I get much better performance than I’m getting with the solution I’ve described here?
I’m developing a layout to enter the list of medical Problems related to an Encounter with a patient. An Encounter can have many Problems. Each Problem has one Diagnosis.
The Diagnosis table contains over 14,000 records. An administrator updates that table periodically but the user is simply using it as a look up. So, each Diagnosis record can be related to many Problem records.
Encounter -----< Problem >----- Diagnosis
These are the keys:
Problem::fkEncounter = Encounter::pkEncounter
Problem::Code = Diagnosis::Code
On the Encounter layout I have a Problem portal. In that portal, the Problem::Code field is a dropdown with the value list populated by Diagnosis::Code and Diagnosis::CodeDescription as the first field and second field.
This works on a MacBook Pro, but it’s not as responsive as a drop down with fewer choices, and the performance will get worse since I need to work with other value lists that are even larger.
Furthermore, this approach isn’t acceptable when the database is shared with a FileMaker Go client on an iPad. There the performance is extremely slow and erratic.
This problem seems similar to a parts list for an assembly, where choices are made from thousands of parts.