Retrieve Special Fields with NL(Table)
Overview
The NL("Table") function creates an Excel table which can be used as the basis for pivot tables and charts.
Where working with multiple Dynamics NAV database tables, there are some powerful techniques you can use to get exactly the data you want.
Special fields can only be retrieved from NL(Table) when using Dynamics NAV data sources.
-
Retrieving Field Values from Linked Tables
The syntax for retrieving field values from linked tables looks like the following:
=NL("Table","Table1",{"Table1Field","LinkField([Table2],[Table2Field2])"},"InclusiveLink=","Table2","Table2Field1","=Table1Field")This function would retrieve all values of Table1Field from Table1 and the first value of Table2Field2 from Table2 for each record of Table1 where the value of Table2Field1 matched the value of Table1Field and blank where no matching record on Table2 was found.
The NL(LinkField) function can make it simpler to retrieve fields from linked tables:
=NL("LinkField","Table2","Table2Field")This function could then be referenced in the Field parameter of the NL(Table) function, rather than typing in the LinkField() formula by hand.
-
Retrieving Field Sums from Linked Tables
The syntax for retrieving field sums from linked tables looks like the following:
=NL("Table","Table1",{"Table1Field","LinkSum([Table2],[Table2Field2],[Table2Field3],[A*])"},"InclusiveLink=","Table2","Table2Field1","=Table1Field")This function would retrieve all values of Table1Field from Table1 and the sum of the values of Table2Field2 for each record of Table1 where the value of Table2Field1 matched the value of Table1Field and where the value of Table2Field3 started with the letter A (or zero where no matching records on Table2 were found).
The NL(LinkSum) Function
The NL(LinkSum) function can make it simpler to retrieve sums from linked tables:
=NL("LinkSum","Table2","Table2Field2","Table2Field3","A*")This function could then be referenced in the Field parameter of the NL(Table) function, rather than typing in the LinkSum() formula by hand.
-
Retrieving Field Values and Field Sums from Nested Links
Field values and sums can be retrieved from nested linked tables.
For example:
=NL("Table","Cust. Ledger Entry",{"Entry No.","LinkField([Customer Posting Group],[Code])"},"InclusiveLink=","Customer","No.","=Customer No.","InclusiveLink=","Customer Posting Group","Code","=Customer Posting Group")This function would retrieve all values of the Entry No. field from the Cust. Ledger Entry table and the Code field from the Customer Posting Group table which is linked via the Customer table.
-
Retrieving Field Values and Field Sums from Multiple Tables
Field values and sums can also be retreived from multiple tables linked from the primary table like the following:
=NL("Table","Customer",{"No.","LinkField([Customer Posting Group],[Code])","LinkSum([Cust. Ledger Entry],[Amount])"},"InclusiveLink=","Cust. Ledger Entry","Customer No.","=No.","InclusiveLink=Customer","Customer Posting Group","Code","=Customer Posting Group")This function would retrieve all values of the No. field from the Customer table, the values of the Code field from the Customer Posting Group table, and the sum of the values of the Amount field from the Cust. Ledger Entry table. Note that both the Customer Posting Group table and the Cust. Ledger Entry tables are linked from the Customer table.
-
Retrieving Field Values and Field Sums with Link= vs. InclusiveLink=
Field values and field sums can be retrieved from linked tables using either Link= or InclusiveLink=
The difference is that Link= filters and eliminates records from the primary table when no matching record is found on the linked table whereas InclusiveLink= does not eliminate records from the primary table.
When using InclusiveLink= , if no matching records are found on the linked table, blank is returned for linked fields and 0 is returned for linked sums.
-
Retrieving a FlowField with FlowFilters
The FlowField syntax, used in the Field argument of the NL(Table) function, allows for the retrieval of a FlowField with a specific set of FlowFilters applied to only that field:
=NL("Table","Customer",{"Name","FlowField([Sales],[Date Filter],[1/1/10..12/31/10])"})The first argument is the FlowField to retrieve, followed by any number of FlowFilterField/FlowFilter pairs.
The NL(FlowField) Function
The NL(FlowField) function can make it simpler to retrieve FlowFields:
=NL("FlowField","Customer","Sales","Date Filter","1/1/10..12/31/10")This function could then be referenced in the Field parameter of the NL(Table) function, rather than typing in the FlowField() formula by hand.
Any FlowFilters that are applied to the overall query, i.e. the NL(Table) function, will also be applied to fields that are specified using the FlowField syntax. -
Including Formulas in an Excel Table
Any Excel formula can be included as a column in a table. The formula contained can also reference other columns within the table by using the format [[#This Row],[FieldName]].
For example:
=NL("Table","Customer",{"Amount","Formula([[#This Row],[Amount]]*0.15)"})This function would retrieve all values of Table1Field from Table1 and the first value of Table2Field2 from Table2 for each record of Table1 where the value of Table2Field1 matched the value of Table1Field and blank where no matching record on Table2 was found.
The NP(Formula) Function
The NP(Formula) function can be used to include formulas in the table by referencing in the Field parameter of the NL(Table) function. An NP(Formula) function can be used like the following:
=NP("Formula","[[#This Row],[Amount]]*0.15")The result of this formula would be a two column table, one column including the Amount field, and the other would include the Amount field multiplied by 0.15.