What is this?
Admin note: This guide explains how to use “Smart Tools” (functions) to transform and analyze your data on the fly. It covers things like calculating VAT, extracting years from dates, or counting how many items are in an order.
Who is this for? Administrators should read this to understand how they can do math and date-based analysis directly in their searches. It’s particularly useful for creating reports. Developers will use these functions extensively in automated workflows.
Key benefits for the e-shop:
- Automated Math: Filter products based on calculated values (like “price with VAT”) without needing a separate field in your database.
- Time-Based Insights: Easily find orders from a specific year, month, or even hour of the day.
- Summary Reports: Count, sum, or average your data instantly (e.g., “show me customers with a total order value over $500”).
- Text Cleaning: Clean up your search results by converting names to lowercase or trimming extra spaces.
Functions and Aggregation
Built-in functions allow you to transform and analyze your data directly within your filter conditions.
[navigate:where-basics.md|Basic Filters|filter] [navigate:where-advanced.md|Advanced Filters|database]
Date and Time Functions
Extract specific components from date/time fields or calculate time period boundaries.
Extraction Functions
year(dateOfIssue) = 2023 and month(requestedDeliveryDate) = 12
[badge:Functions|secondary|right]
Quarter and Week
quarter(dateOfIssue) = 4 and dayofweek(dateOfIssue) = 1
[badge:Functions|secondary|right]
Date Range Functions
These return precise start and end boundaries for time periods.
dateOfIssue >= startOfYear(now()) and dateOfIssue <= endOfMonth(now())
[badge:Functions|secondary|right]
Aggregation Functions
Perform calculations across MANY relations (one-to-many, many-to-many) for powerful analytics.
Basic Aggregation
count(variants) >= 3 and sum(variants.priceWithVat) > 1000
[badge:Aggregation|secondary|right]
Filtered Aggregation
Count or calculate values only for related records that match a specific condition.
count(variants[priceWithVat > 100]) > 2
[badge:Aggregation|secondary|right]
Average, Min, and Max
avg(variants.priceWithVat) > 500 and min(variants.weight) > 1
[badge:Aggregation|secondary|right]
Arithmetic Operators
Perform mathematical operations on numeric fields and values using standard precedence rules (*, /, % before +, -).
Price and Quantity Calculations
priceWithVat * 1.21 > 100 and standardPrice - 5 > 10
[badge:Arithmetic|secondary|right]
Modulo and Grouping
actionPrice % 2 = 0 and (priceWithoutVat - purchasePriceWithoutVat) * 1.21 > 500
[badge:Arithmetic|secondary|right]
String and Math Functions
Text Transformations
lower(name) = "iphone" and length(description) > 50
[badge:Functions|secondary|right]
String Search and Extraction
indexof(email, "@") > 0 and substring(username, 0, 3) = "PRD"
[badge:Functions|secondary|right]
Math Rounding
round(priceWithVat) = 100 and floor(weight) = 4
[badge:Math|secondary|right]
Reference Table
| Function | Category | Description | Example |
|---|---|---|---|
now() |
Special | Current timestamp | createdAt > now() |
me() |
Special | Current user ID | createdBy_id = me() |
count() |
Aggregation | Count related records | count(variants) > 3 |
sum() |
Aggregation | Sum numeric field | sum(variants.priceWithVat) > 100 |
avg() |
Aggregation | Average numeric field | avg(variants.weight) > 4 |
min() |
Aggregation | Minimum value | min(variants.priceWithVat) > 10 |
max() |
Aggregation | Maximum value | max(variants.priceWithVat) < 100 |
year() |
Date/Time | Extract year | year(createdAt) = 2023 |
month() |
Date/Time | Extract month (1-12) | month(createdAt) = 12 |
day() |
Date/Time | Extract day (1-31) | day(createdAt) = 25 |
hour() |
Time | Extract hour (0-23) | hour(createdAt) = 14 |
round() |
Math | Round to nearest int | round(priceWithVat) = 100 |
concat() |
String | Combine strings | concat(firstname, surname) = "JohnDoe" |
indexof() |
String | Find substring pos | indexof(email, "@") > 0 |
trim() |
String | Remove whitespace | trim(name) = "Product" |
isnull() |
Special | Check if null | isnull(ean) = true |
Date Range Reference
| Period | Start Function | End Function |
|---|---|---|
| Year | startOfYear(date) |
endOfYear(date) |
| Month | startOfMonth(date) |
endOfMonth(date) |
| Day | startOfDay(date) |
endOfDay(date) |
| Hour | startOfHour(date) |
endOfHour(date) |