Select the Specific Key by Function

This topic is for advanced users who have a strong understanding of Dynamics NAV and the impact that keys can have on performance.

Selecting the Specific Key by Function

Jet Reports selects the key to apply by analyzing the filters to be set on the table against the available list of keys. While the default algorithm is designed to select the key that performs best in the majority of cases, there are situations in which a different key may perform better.  Depending on the data in the table, it may be beneficial for the user to specify a key that is known to perform well based on the given set of filters.

Examples

In some cases the user may know the specific key that will perform best for a given query.  In this situation, the user can specify the key fields in the NL function using the special filter field “Key=”

1. The key itself is specified as an array of fields.  For instance:

   =NL("Rows", "Customer", "No.", "Key=", {"Name", "Address", "City"})

   Note that this is to exhibit syntax only and is not a practical example of when “Key=” should be used.

   The primary key of a table (i.e. “No.” in the Customer table) is implicitly appended to every key, and Jet Reports will accept the key as valid whether the primary key is included or not.   The following example is equivalent to the example above and is also valid:

   =NL("Rows", "Customer", "No.", "Key=", {"Name" ,"Address", "City", "No."})

2. When using Link= , the position of the Key= in the formula will determine the table with which the key is associated.

   This example specifies the key to be applied to the Cust. Ledger Entry table:

   =NL("Rows", "Customer", "No.", "Link=", "Cust. Ledger Entry", "Customer No.", "=No.", "Key=", {"Entry No.”})

The key fields can also be specified in a range of cells, just as any other array argument in Jet Reports.  In addition, the Jet Function Wizard (Jfx) supports key lookups in the NL function, provided that the Table argument is specified and the FilterField is "Key="

If a key is specified that Jet does not recognize to be an existing key in the table, an error will occur.