(applies to JDM 17.5 and higher)
Conditional lookup fields (the feature that allows you to use a field from one table on another) has gone through a substantial overhaul in this release. We have improved the performance, behavior and usability of the feature. The SQL generated by the Jet Data Manager has been improved to use only one UPDATE statement no matter how many lookup fields from different tables are included in the conditional lookup fields. This can substantially improve the performance of conditional lookups.
Since the new algorithm for creating the code is a bit smarter than the old one some of the options for
conditional lookup fields are no longer necessary. These options - Force sub select, Use temporary table,
and SQL mode - have been deprecated and we have outright removed the Manual index creation option since it has been superseded by the automatic index feature. On the other hand you will notice a new option (Multiple lookup fields) that controls what Jet Enterprise will do when there is more than one lookup field on a conditional lookup field. In any case the lookup fields are evaluated in the same order as they appear in the tree. What happens when there is a match depends on the setting which can be one of the following:
• Take the first value: The value of the conditional lookup field will be the value of the first lookup
field with a condition that evaluates to true.
• Take the first non-empty value: The value of the conditional lookup field will be the value of the
first lookup field with a condition that evaluates to true and is not empty. This setting makes it
easy to support a master data management (MDM) pattern where you have multiple candidates
for a value and want the first that is available.
We hope you will find that these changes make the options easier to understand and in turn make the
feature easier to use. If you would like to convert all your conditional lookup fields with deprecated options to the new defaults Jet Enterprise can do that for you, right click the project node, click Advanced and then click Fix Deprecated Lookup Options. This option is only available if you have conditional lookup fields in your project that use the deprecated options:
In addition to the improvements described above the changes to the algorithm also fix an unintended
behavior in the previous version where the default value defined for a conditional lookup field would
never actually be set.