SQL Views

To understand how to use a Field Cache, it is useful to first understand how Detail Lists work with standard SQL tables.

As explained in the article Using a Detail List or a Summary List, a Detail List will produce a list of records based on the filters chosen.  The replicating function returns a Record Key rather than a field from the database.  Users create a record key by dragging a table into Excel from the Jet Browser or by building an NL(Rows) or NL(Columns) function using the Jet Function Wizard.  In either case, leaving the Field blank indicates that Jet Reports should create a Record Key. 

For example, in GP, use the Jet Browser to drag and drop first the GL00100 (Account Master) table and then the ACTINDX (Account Index) field from that same table into Excel.

Notice that the NL function created simply indicates a list of rows from the table, i.e., =NL(“Rows”,”GL00100”):

When building detail list keys against standard SQL tables, the Jet add-in automatically detects and references the table’s primary key.

In this example, the Record Key automatically references the ACTINDX field, which is the primary key to the GL00100 table:

When building reports against a view, on the other hand, Jet Reports has no primary key to reference. If you drag the Jet GL Account table into Excel, the function which Jet creates, =NL(“Rows”,”Jet GL Account”), will return the error “Table ‘Jet GL Account’ does not have a primary key.  You must specify one or more fields”:

Instead, use the Browser to select all fields from Jet GL Account and drag them into Excel. You will notice that the Field argument populates with an array of Field names, since the table does not contain a primary key.

When an NL Function has an array in the Field parameter, it returns a value called a Field Cache into the cell. This is a text value that identifies all the field values and their data types.  If you use the NF function with the cell address containing a Field Cache as the first argument and one of the field names contained in it as the second argument, the NF function will return the value of the field.  You must include a field in your Field Cache if you wish to display that field using the NF function.

Selecting all the fields in a view using the Jet Browser is a simple way to be sure that you have created a unique Field Cache that can accurately function as a primary key to the data in your table:

If you do this, simply delete any columns the Jet Browser creates that you do not wish to include in your report.  Alternatively, if you know the field or fields that can function as a primary key on a view, simply ensure that those fields are included in your Field Cache, regardless of whether you wish to display those fields.

In some circumstances you may wish to use a field cache that deliberately does not include all information in the underlying table.  In this case, you are using the Field Cache to create a summary table. GP users, for example, sometimes want to list the unique combination of only two segments from their chart of accounts.  For example, say you are a GP user whose chart of accounts is as follows:

Segment 1 - Entity

Segment 2 - Natural Account

Segment 3 - Department

You wish to list every unique combination of Natural Account and Department on a separate row. By creating a Record Key whose Field Cache contains only Segments 2 and 3, you can create a list of unique natural accounts and departments:

Notice that each combination of Segments 2 and 3 appears only once.

If you add Segment 1 to you Field Cache, the record key will create a row for every unique combination of Segment 1 (Entity) with Segments 2 (Natural Account) and 3 (Department). The function is =NL(“Rows”,”Jet GL Account”,{“Segment 1”,”Segment 2”,”Segment 3”}). Now when you display Segments 2 and 3 using the NF function, the unique combination of these two values repeats:

The repetition is created by the presence of Segment 1 in the Field Cache, regardless of whether or not Segment 1 is displayed using an NF function.