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)

Finding data that has overrides

In AGR settings are applied on a global level, affecting all items. The settings, and imported data, can be changed/ overridden through Bulk Update and Item details.

To surface these overrides, you can use the Advanced Filter, by typing in: "IsOverridden = True" after selecting any column that can be overridden

Example = Which Items have been manually closed in AGR

1 - Choose the column: "Closed"

2 - key in "IsOverridden" directly after the column name

3 - add command: "= True"

Note! "IsOverridden" is both space and case sensitive.

The following data / settings can be overridden:

Item info:

Closed

Replenishment information:

Lead time days
Order Multiple
Min Order Qty
Pallet Quantity
Demand on Saturdays
Demand on Sundays
Exclude from Forecst
Forecast End Date
Forecast Model Category
Forecast Profile
Forecast start data
Forecast type
Outlier detection
Promotional Smoothing
Stockout bridging

Safety stock settings

Safety stock policy
Safety stock units
Safety stock days
Confidence factor

Order Logic Settings

Additional Lead time
Exclude Lost sale
Max Stock
Min Display Stock
Order Coverage Days
Order Frequency Days
Order Multiple Threshold (%)
Reorder Point
Sales Orders
Special Order item

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.

Work in Items and Reports

Order Schedules

August

December

How does AGR calculate service level?