SQL statements in NL()

Recently, there has been alot of people in my country using Jet with SQL database.

Interesting thing is they can input SQL statements within a NL() by using "SQL="

Here are some examples:

:idea: You can't do a SQL= and SELECT * without specifying a field or field cache in the NL function. So your function might look like this:

=NL("Rows","SQL=Select * from [EMAS ENGERY SERVICES PTD LTD$Bank Account]",{"No_","Name","Address"})

where {"No_","Name","Address"} are referring to the fields that you want to see in that table (in this case Bank Account table)



:idea: =NL("Rows","Sql=select distinct [No_],[ Buy-from Vendor No_],[ Shortcut Dimension 1 Code] from [EMAS OFFSHORE SERVICES PTE LTD$Purchase Line]",{"No_","Buy-from Vendor No_","Shortcut Dimension 1 Code"})

Output: Unique combination of No_, Buy From Vendor No, Shortcut Dimension 1 Code will be shown


:idea: =NL("Rows","Sql=select distinct [No_] from [EMAS OFFSHORE SERVICES PTE LTD$Purchase Line] where [Shortcut Dimension 1 Code]='OS-L.SWAN'",{"No_"})

Output : Only unique No will be shown

In order to display all the fields on report, NF() must be used
For E.g =NF($E5,"No_")