Overview
=NL (What,"SQL=...",Field,FilterField1,Filter1,FilterField2,Filter2,FilterField10,Filter10)
Purpose: A special type of NL function that uses the power of SQL to allow complete flexibility in retrieving data from a database.
Argument | Meaning |
What | "Rows", "Columns", "Sheets", N, -N, Blank, "SQL" (SQL shows the SQL statement created) |
Table |
To add Jet interpreted filters to the WHERE or HAVING clauses, add %Filter9% to your existing WHERE term. These parameters will be replaced by appropriate WHERE terms enclosed in ().
If your filter is *, the corresponding %FilterX% will be converted to field LIKE '%'. If the preceding word in the SQL query is WHERE, and there are no following words like AND or OR, then WHERE will be removed. If the preceding word is AND, OR, etc., then that word will be removed. If the preceding word is WHERE and the following word is AND or OR, then the following word will get stripped. For example:
To add user-determined sorting to your SQL statement, add %Sort% to the Order By clause. Since Joins use a common sort (and Sub queries do not use sorting), you do not need to put a number on %Sort%. Despite this, your NL filters for sorting must still be prefixed by a filter number (see the section on FilterField below) |
Field |
This is a field cache and returns a Record Key just as when an array of field names is passed in to this argument with a normal NL function. ( for example: {"Field1","Field2"} ) The field names actually returned depend on the SQL SELECT statement, itself |
FilterField |
This parameter contains the WHERE clause replacements in the format "1S=FieldName". The first character (e.g., 1) corresponds to the %FilterX% that you want to replace. The second characters determines the Field type and can be one of the following (please note that every %FilterX% in the SQL statement MUST have at least 1 corresponding Jet filter): D = Date N = Number S = String G = GUID B = Boolean The entire FilterField (including the 1S, 2D, etc.) can be prefixed with a "+" or "-" to sort. So, for example, you can use "+1S=FieldName" or "-1S=FieldName". Please note that despite the fact that a sort may not correspond to any particular %FilterX% replacement, you must still prefix your soft field with a filter number, data type, and =. Simply using +FieldName will not work.
|
Filter | Filter using standard Jet filters |
Examples
The following function returns the contact names from the customers table where the contact name starts with A and sorted by the City in descending order.
=NL("Rows","SQL=SELECT ContactName FROM Customers WHERE %filter1% ORDER BY %sort%","ContactName","1S=ContactName","A*","-1s=City","*")
The following function returns all the contact names from the customers table sorted by the city.
=NL("Rows","SQL=SELECT ContactName FROM Customers WHERE %filter1% ORDER BY %sort%","ContactName","-1s=City","*")
SQL= is not available for the Dynamics NAV connector.
Comments