I have created a Table function to read the G/L Entry table from NAV. There are NO record filters set. Yet when I refresh, the function pulls in only about 8000 records of the 10000 in the table.
This is my function (created using the wizard)
=NL("Table";"G/L Entry";$D$4:$F$4;"Headers=";$D$3:$F$3;"TableName=";"G/L Entry")
Date
Votes
2 comments
-
Jet Reports Historic Posts If you remove the current filters ($D$4:$F$4) and headers and just use:
=NL("Table";"G/L Entry";;"TableName=";"G/L entry")
Do you get the results you expect?
The issue may be that the fields you are retrieving (specified in D4 to F4), may not be sufficient to differentiate all records from each other.
Let's assume that we have field names "A", "B", and "C" and 10 records with the following info:
Rec A - B - C
——————–
001 X - 5 - 7
002 X - 5 - 7
003 X - 5 - 7
004 Z - 1 - 2
005 Z - 2 - 1
006 Z - 0 - 0
If I use the NL(Table) function to only return fields A, B, and C, I will get the following:
X 5 7
Z 1 2
Z 2 1
Z 0 0
The reason for this is that, like all Jet functions, the NL(Table) returns unique values. Since there is no way (using *just* A, B, and C) to differentiate records 2 and 3 from record 1, only one of those is returned.
In situations where we want all the records to be returned, we need to include enough fields to uniquely identify each record. In the example above, if "Rec" is a field, I can include it in the information I am returning like and get the following:
001 X 5 7
002 X 5 7
003 X 5 7
004 Z 1 2
005 Z 2 1
006 Z 0 0
I hope that helps. -
Jet Reports Historic Posts Thanks, after some experimentation, I discovered that it only retrieves the first instance of the combination of fields. So the primary key of the table must be part of the field set selected.
Please sign in to leave a comment.