Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet 365 Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Feedback

Retrieving Special Fields with NL(Table)


 

 

Help Topics

NL Function
Table Builder
Browser
Writing Reports

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

In order to make it simpler to retrieve fields from linked tables, the NL(LinkField) function has been introduced.  The syntax of NL(LinkField) looks like the following:

=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 A and 0 where no matching records on Table2 were found.

The NL(LinkSum) Function

In order to make it simpler to retrieve sums from linked tables, the NL(LinkSum) function has been introduced.  The syntax of NL(LinkSum) looks like the following:

=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 retreived from nested linked tables like the following:

=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

In order to make it simpler to retrieve these FlowFields, the NL(FlowField) function has been introduced.  The syntax of NL(FlowField) is as follows:

=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]] as follows:

=NL("Table","Customer",{"Amount","Formula([[#This Row],[Amount]]*0.15)"})

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.

 

Special fields can only be retrieved from NL(Table) when using Dynamics NAV data sources.

 

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

Comments