Jet Reports Homepage |  Community Forum |  Downloads |  Submit A Ticket |  Jet Express Support
Feedback

Selecting the Specific Key by Function ( Key= )


Selecting the Specific Key by Function

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

Jet Essentials 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.

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=”. 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.”})

When using “Link=”, the position of the “Key=” in the formula will determine the table with which the key is associated. The following 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 Reports 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 Essentials does not recognize to be an existing key in the table, an error will occur.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments