SQL statements for Lookup POS action | NaviPartner

When menu buttons are added for the LOOKUP POS action, you can set filters on the item table using an SQL statement.

pos_action_sql_filter

If you use the example from the provided screenshot - SORTING(Description) ORDER(Ascending) WHERE(Inventory Posting Group=FILTER(SHOP)) - which means that the list will have items whose Inventory Posting Group = SHOP, and it is sorted in alphabetical order according to the item names.

Some examples of statements that can be set with the LOOKUP POS action:

Item Lookup by Specific Item Category:

SORTING(Description) ORDER(Ascending) WHERE(Item Category Code=FILTER(10..103))
Item Lookup by Unit Price of a Certain Value:

SORTING(Description) ORDER(Ascending) WHERE(Unit Price=FILTER(>0&<25))
Item Lookup by Specific Inventory Posting Group
SORTING(Description) ORDER(Ascending) WHERE(Inventory Posting Group=FILTER(SHOP))
Item Lookup by Specific Description
SORTING(Description) ORDER(Ascending) WHERE(Description=FILTER(@Bag))
Item Lookup by Specific Vendor and Item Category
SORTING(Description) ORDER(Ascending) WHERE(Vendor No.=FILTER(CR000001), Item Category Code =FILTER(DESK))

Example of SQL syntax used in Business Central

Symbol	Meaning	Sample Expression	Records Displayed
=	Equal to	777	Number 777
		RED	Those with the RED code, for example, the RED warehouse code
		12	A datetime: from 12-current month-current year 0:00:00 to 12-current month-current year 22:59:59
		12 10	An exact datetime: 12-07-17 10:00:00
..	Interval	100..200	A range of numbers 100 through 200
		..500	Up to, and including 500
		..310117	Dates up to, and including 31/01/17
		P2..	Information for accounting period 2 and subsequent periods
/	Vertical bar	7/8	The vertical bar signifies either/or, so the value is either 7 or 8.
@	Ignore case (either uppercase or lowercase allowed)	@green	Text like GREEN, green or Green
**	Wildcard	Bag	Any item containing the word “Bag” in any part of the name, filtered with an asterisk (wildcard) before and after the word
>	Greater	>100	A value greater than 100
`<>`	Not equal to	`<>`100	A value not equal to 100. It can be higher or lower
`<=`	Lower than or equal to	`<=100`	A value lower than or equal to 100
`>=`	Greater than or equal to	`>=100`	A value greater than or equal to 100
@ **	Wildcard, Case Insensitive	@bag	To find any item containing the word “Bag”, “bag”, “bAg”, etc. (case-insensitive) in any part of the name, use the @ expression: @bag
@ ?**		@ba?	Text containing “ba”, “BA”, “Ba”, and “bA”, such as bag, Bags and green bag. The “ba”, “BA”, “Ba”, “bA” must be present, followed by at least one character. There can be an indefinite number of characters before and after these, and case is unimportant.
”	Blank		Value is blank. That is the field contains no value.
`<>`”	All not blank	The field contains a value.
Combination of Syntax
		4444\|5100..5490	Include any records with the number 4444 or a number from the interval 5100 through 5490
		..1000\|1200..	Include records with a number lower than or equal to 1000 or a number equal to 1200 or greater (all numbers except 10001 through 1199)
		`>30&<100`	Include records with numbers that are greater than 30 and lower than 100 (numbers 31 through 99)
		7..8	Includes all records in which the field has values from (starting with the digit 7) to (starting with digit 8), e.g. 7, 77, 717, 8, 81, 8123456 and so on.
		7?..?11	The result contains all records in which the field has values from (two-digit numbers starting with digit 7) to (three-digit numbers ending with digit 11), e.g. 70, 71, 79, 111, 211, 911, and so on.
		>B*	The result contains all records in which the field has text values that are greater than B, where B is any string that starts with “B”. e.g Bag, Bags, Buggy, Boat etc.
		>10?B*	The result contains all records in which the field has text values that are greater than 10?B, where 10?B is any string that starts with “10” followed by any random symbol, then “B”, and finally at least one random symbol.
		A&B	The text which contains both A and B
		SE\|SO\|LE..WE	Values SE, SO, LE, PE, WE
Date Formula
		20D	20 days
		6W	6 weeks
		CM+2D	The current month plus two days
		CM+1M	The current month plus one month
		CQ+1M+20D	The current quarter plus one month plus 20 days
		CW+1W	The current week plus one week
		D15	On the 15th of each month
		-CY+10M	November 1st
		CY	The current year
		CM	The current month
		CQ	The current quarter