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

Jet Express - NL Function Reference


=NL (What,Table,Field,FilterField1,Filter1, ..., FilterField10,Filter10)

Purpose:  Returns fields from a table, based on filters.

Help Topics

Filtering

 

Parameter Argument Description
What    
  "Link"

Returns a string value that can be used as a filter in another NL function.

  "LinkField"

Returns a string used to retrieve a field from a link table in an NL(Table) function.

  "LinkSum" Returns a string used to retrieve a sum of a field from a link table in an NL(Table) function.
  "FlowField"

Returns a string used to retrieve a FlowField from the primary table in an NL(Table) function.

FlowFilters in this function are only applied to the specified FlowField and not the parent query.

  "Table" Creates an Excel table object based on the field values returned. Leaving the Field argument blank returns all fields. Use a Field Cache to return multiple fields.
     
 Table The name, number, or caption of the table.

 

 Field

The name, number, or caption of the field to return. For Dynamics NAV users, this can also be an Advanced Dimension.

To return a Field Cache, specify an array of fields.  e.g., {"No.","Name","Balance","Address","City","County","Country/Region Code"}

A Field Cache can be used with NL(Table) when you only want to see some of the fields from the table rather than all of them. An NL function with a Field Cache will return only the unique combinations of the values of the fields in the Field Cache.

 FilterField1

The name of the first field by which to filter.  For Dynamics NAV users, this can also be an Advanced Dimension.  

The following special values are allowed as FilterField arguments:  

  "Company=" or 0 Overrides the default company with the one specified by the Filter argument.
  "InclusiveLink=" Links the primary table to the one specified by the Filter argument for the purpose of retrieving data.
  "Limit=" Limits the number of records or values returned to the number specified by the Filter argument.
  "Filters=" Specifies a set of filters for the query with an array of filters specified by the Filter argument.
  "Headers=" Overrides field headers with the array of headers specified by the Filter argument. For use with an NL(Table) or NL(Lookup) function.
  "HideTotals=" When the value of the Filter argument is TRUE, hides the totals row created by NL(Table).
  "TableName=" Specifies the name to use for the Excel table object created by NL(Table) with the name in the Filter parameter. Use this to refer to the table by name from a Pivot table.
  "UseLocalFormulas=" For use with an NL(Table) function. Specifies that any resulting Excel formulas will be written in the language currently set in windows, otherwise English formulas will be returned.
  "IncludeDuplicates=" When the value of the Filter argument is TRUE, specifies that all matching records from the source data will be included in the NL(Table) results.
Filter1

The value of the filter to apply to FilterField1.

FilterFieldN Same as FilterField1. Up to 10 field and filter pairs can be specified. If you specify multiple filters, they combine in a logical AND. 
FilterN Same as Filter1, but applies to FilterFieldN. 

 

 

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

Comments