Advanced filters in AGR | AGR Knowledge Base

Introduction

Advanced Filters let you create precise search conditions in AGR. They work like simplified SQL WHERE clauses, with extra functions for text and dates. The Apply button only becomes active when your filter has valid syntax.

Type @ in the filter editor to see a list of all available AGR columns. Start typing to search, then hit Enter to insert.

Data types

Numbers: 123, 45.67
Text: 'chair' (always use single quotes)
Dates: 'YYYY-MM-DD' or use a date function like TODAY
True/False: True, False, Yes, No

Tip: Always match the data type of the column. Example: costPrice (number) cannot be compared to 'cheap' (text).

Columns

Uses the column Name from AGR’s technical, e.g.: itemId, itemNo, itemName, locationId, costPrice, purchasePrice, leadTimeDays, minStock, maxStock, stockUnits, stockPrice, firstSaleDate, lastSaleDate, saleQtyLast12Months, saleValueLast12Months, riskOfStockout, overstocked.

Special helper column:

columnIsOverridden (True/False): true when a value has been manually overridden.

Operators

Comparison

=, != or <>, <, >, <=, >=

Logical

AND, OR, NOT

Text functions

Always include parentheses with the search text inside:

STARTSWITH('abc')
ENDSWITH('xyz')
CONTAINS('needle')
IN ('A','B','C','1','2','3')
NOT IN ('A','B','C')

✅ itemName CONTAINS('table')
❌ itemName CONTAINS 'table'
❌ itemName CONTAINS()

Null checks

column IS NULL → finds empty fields
column IS NOT NULL → finds fields with values

Date functions

TODAY, YESTERDAY, TOMORROW
ADDDAYS(date, n) → e.g. ADDDAYS(TODAY, 2)
ADDWEEKS(date, n) → e.g. ADDWEEKS(TODAY, 1)
ADDMONTHS(date, n) → e.g. ADDMONTHS(TODAY, 6)
DIFFDAYS(date1, date2) → e.g. DIFFDAYS(firstSaleDate, TOMORROW)
DIFFWEEKS(date1, date2)
DIFFMONTHS(date1, date2)
These functions return numeric values (difference in days, weeks, or months). Typically date1 is the earlier date and date2 the later date. If you reverse them, the result is negative.
Example:
DIFFDAYS('2025-01-01', '2025-01-08') // returns 7 DIFFDAYS('2025-01-08', '2025-01-01') // returns -7

You can also compare to a fixed date:
lastSaleDate > '2024-12-25'

Arithmetic operators

+, -, *, /

Comments

Single-line: // ...
Multi-line: /* ... */

Example:

costPrice > 100 // expensive items /* vendor missing */ primaryVendor IS NULL

Examples

Basic comparisons

costPrice > 100

itemName = 'Table'

firstSaleDate < '2023-01-01' // items first sold before 2023

stockoutExpected = True

Combine conditions

costPrice > 100 AND stockUnits > 0 // only items in stock that cost more than 100

itemGroupLvl1 = 'Food' OR itemGroupLvl1 = 'Beverages' // items that match either group NOT closed

Text operations

itemName STARTSWITH('Pro')

description CONTAINS('stainless')

itemGroupLvl2 IN ('Energy drinks','Protein bars')

itemGroupLvl1 NOT IN ('A','B')

Date operations

lastSaleDate > TODAY // items sold at least once after today

nextExpectedDeliveryDate = ADDDAYS(TODAY, 7) // expected delivery exactly 7 days from now

DIFFDAYS(firstSaleDate, lastSaleDate) > 30 // at least 30 days between first and last sale

Null checks

costPrice IS NULL // items that have no cost price set

itemNote IS NOT NULL // items that have non-empty item note

Complex expressions

(costPrice * stockUnits) > 10000

itemName CONTAINS('table') AND (costPrice < 500 OR returnsHigherThanSales = True)

Using overrides

closedIsOverridden = True // items that have been closed in AGR

Parentheses for grouping

(itemGroupLvl1 = 'Electronics' OR itemGroupLvl1 = 'Accessories') AND costPrice < 100 // accessories or electronics under 100 in cost price

Syntax rules

Keywords are not case sensitive.
Column names use camelCase: costPrice, leadTimeDays, itemName.
Strings must be in single quotes.
Dates must be 'YYYY-MM-DD'.

Common mistakes

Forgetting parentheses in text functions.
❌ itemName CONTAINS 'chair'
Empty text functions.
❌ description CONTAINS()
Missing quotes around text.
❌ itemName = Laptop
Comparing wrong types.
❌ costPrice = 'cheap'
Treating booleans as numbers.
❌ closed = 0
✅ closed = No or closed = False

Tips for users coming from the old version of advanced filters

LIKE '%text%' → now use CONTAINS('text')
LIKE 'text%' → now use STARTSWITH('text')
LIKE '%text' → now use ENDSWITH('text')
IN (...) and NOT IN (...) work the same way
getdate() → use TODAY (or ADDDAYS(TODAY, -90) for date offsets)
Always wrap text values in single quotes ' ' (same rule as before)

This filter language gives you flexibility to build clear and powerful conditions while staying easy to read. The system validates syntax before applying, so if the Apply button is inactive, check your quotes, parentheses, and function arguments.