Jet Reports – Using a Field Cache with the NL function can give unexpected results.

The Field Cache in Jet reports is a list of fields that are to be retrieved from a table with the NL function. If the Field Cache is not specified then all fields are returned.

An example without a Field Cache;

=NL("Rows","Item  Ledger Entry")

An example with a Field Cache;

=NL("Rows","Item  Ledger Entry",{"Item No.", “Quantity","Posting  Date"})

In the second example above the Field Cache is the list of fields contained within the {} brackets;

{"Item No.","Quantity",”Posting  Date”}

A Field Cache is mandatory for a table that has no primary key, and is also recommended for a table with a primary key since it speeds up the report and reduces the amount of data.

So what does this all mean in practise and why can it lead to unexpected results?

Consider the following item ledger entries for items in the Dynamics NAV Cronus database;

Figure 1

Entry No. Item No. Quantity Posting Date

1

1000

10

01/04/2015

2

1100

12

01/04/2015

3

1000

8

02/04/2015

4

1120

3

02/04/2015

5

1100

9

02/04/2015

6

1155

25

02/04/2015

7

1000

8

02/04/2015

8

1000

15

03/04/2015

9

1100

6

03/04/2015

If you need a Jet report to list records from the Item Ledger Entry table and have columns for ‘No.’, ‘Quantity’ and ‘Posting Date’ , you could use the following NL function ( together with NF functions for ‘No.’, ‘Quantity’ and ‘Posting Date’) to report the data;

 =NL("Rows","Item  Ledger Entry")

You will get the following results;

Figure 2

Item Quantity Posting Date

1000

10

01/04/2015

1100

12

01/04/2015

1000

8

02/04/2015

1120

3

02/04/2015

1100

9

02/04/2015

1155

25

02/04/2015

1000

8

02/04/2015

1000

15

03/04/2015

1100

6

03/04/2015

This is ok, however if you have a large number of records to retrieve you may choose to use a Field Cache to reduce the amount of data retrieved from the database by only choosing the fields that are required for the report.

Instinct would be to change the NL function to be as follows;

=NL("Rows","Item  Ledger Entry",{"Item No.","Quantity","Posting  Date"})

Using the function above will give the following results;

Figure 3

Item Quantity Posting Date

1000

10

01/04/2015

1100

12

01/04/2015

1000

8

02/04/2015

1120

3

02/04/2015

1100

9

02/04/2015

1155

25

02/04/2015

1000

15

03/04/2015

1100

6

03/04/2015

Note that a record for Item 1000, Quantity 8 and Posting Date 02/04/2015 is missing.

This is because the Field Cache is used and the NL function will only report the unique combination of the fields in the cache, in our case this is "Item No.","Quantity","Posting Date".

From Figure 2 above the following records are ‘repeated’ and the NL function will only report one of them;

Figure 4

Item Quantity Posting Date

1000

10

01/04/2015

1100

12

01/04/2015

1000

8

02/04/2015

1120

3

02/04/2015

1100

9

02/04/2015

1155

25

02/04/2015

1000

8

02/04/2015

1000

15

03/04/2015

1100

6

03/04/2015

To avoid this you can add a field (or fields) to the Field Cache that form the primary key for the Dynamics NAV table, in our case this field is ‘Entry No.’ Adding this field to the Field Cache will report all records, this is because the Entry No. is unique for each record.

Our Jet NL function will then be;

=NL("Rows","Item Ledger  Entry",{"Item No.","Quantity","Posting  Date","Entry No."})

So how do I know which field(s) forms the primary key for a Dynamics NAV table?

This information can be found from the table information in Dynamics NAV.

In RTC Client use the ‘About This Page’ option to list the table fields information;

Image-001

 

Image-002

The field(s) at the top of the list with the suffix (PK) are those that form the Primary Key, in the example above this is ‘Entry No. (1) (PK)’

In Classic Client use the Sort option to list the table keys;

Image-003

The field(s) shown on the first line are those that form the primary key. In the example above this is ‘Entry No.’.

Leave a Reply