AUCTRIA ACADEMY
On This Page

Schema Reference

This page and the Schema Reference section provides comprehensive documentation for database schemas and their fields, with a focus on the Filter schema and related filtering capabilities.

Filter Schema Overview

The Filter schema contains data filtering configurations that allow you to create reusable filter expressions for various tables in the system. Filters are used to select specific records based on defined conditions.

Terminology

  • Table: A collection of related data records
  • Field: A specific attribute or column within a table
  • Record: An individual row of data in a table

What Are Filters?

High Level

A mechanism through which users on the dashboard can filter records in varied contexts.

Low Level

A domain-specific language (DSL) that provides a powerful way to define filter criteria.

When and Where To Use Filters?

Versatile Feature

Filters can be leveraged anytime filtering is needed:

  • Sending targeted emails to specific groups
  • Refining report views to focus on relevant data
  • Mass editing fields for selected records
  • Custom filtering in chooser components

Common Locations

  • Report views
  • "Custom filter" dropdown options in chooser components
  • Email targeting
  • Mass operations

Advanced Mode

Be sure to toggle "Advanced Mode" to access filter functionality in many components.

Feature

How Filters Work

  • A filter is a boolean expression that is applied to each individual record
  • If the expression evaluates to true, the record is included in the processed list
  • If the expression evaluates to false, the record is excluded from the processed list
  • If something goes wrong, an error is returned

Error Types

  • Syntax errors: Problems with how the filter is written
  • Semantic errors: Problems with what the filter means

Language Primitives

  • Operands: Keywords and constants
  • Operators: Unary and binary operators

Filter Expression Syntax

Operators

Logical Operators

  • and, &&, & - Logical AND
  • or, ||, | - Logical OR
  • not, ! - Logical NOT

Comparison Operators

  • = - Equal to
  • > - Greater than
  • < - Less than
  • >= - Greater than or equal to
  • <= - Less than or equal to

More...

Coerced Booleans

Simplified boolean semantics that eliminate the need for corresponding projections:

outstandingBalance > 0

This evaluates if outstandingBalance is greater than 0, returns true if so, otherwise returns false.

Field Aliasing

More ergonomic field naming - codebase field names no longer have to match exposed field names.

Arithmetic Expressions

Support for mathematical operations:

value > 1000 and value < 5000

Also supports addition, multiplication, and division.

Date-Time Support

Filter by specific dates with support for multiple formats:

purchasedTickets[createdAt = '2024-01-15']

Aggregate Functions

Count and analyze related records:

itemsWon[count() >= 3]

Comprehensive Examples

Example 1: Payment Reminder Excluding Sponsors

Question: Send participant statements as a payment reminder but exclude sponsorship purchasers

Decomposition:

  • Participants with an amount owing
  • Exclude sponsors

Answer:

outstandingBalance > 0 and not isSponsor

Example 2: Incomplete Guest Information

Question: Ticket purchasers whose guests have not updated their guest information

Decomposition:

  • Participants who have purchased at least 1 ticket
  • At least 1 of these tickets belongs to a guest that has not updated their guest information

Answer:

purchasedTickets[not isSpecifiedGuest]

Example 3: Self-Check-In Requests

Question: Send a self-check-in request to event guests

Decomposition:

  • Don't want to send to people already checked-in
  • Ticket holders only

Answer:

AssignedTickets and not CheckedIn

Example 4: Sponsorship Ticket Holders

Question: Send a self-check-in request to ticket holders resulting from a sponsorship sale

Decomposition:

  • Don't want to send to people already checked-in
  • Ticket holders
  • Tickets bought through a sponsorship sale

Answer:

not CheckedIn and AssignedTickets[BidHistory.Item.IsSponsorship]

Advanced Tags Alternative

For complex scenarios, consider using advanced tags instead of complex data model queries. Assign tags to sponsorship ticket items and filter for the tag.

Example 5: Paddle Raise Participants

Question: Send a custom email to guests that participated in a paddle raise for our Gala

Decomposition:

  • Participated → transaction
  • Paddle raise → donation

Answer:

Transactions[Item.SystemRole = 'paddle-raise']

Example 6: Credit Card Requests

Question: Send a credit card request email to ticket holders that do not have a credit card on file

Answer:

AssignedTickets and not HasRegisteredCard

Trick

Use not HasRegisteredCard instead of HasRegisteredCard = false for better performance.

Example 7: Unseated Guests

Question: Ticket holders that are unseated

Answer:

AssignedTickets and not Table

Warning

isSeated may not be working as intended, so for now use alternative methods.

Example 8: Raffle Non-Winners

Question: Custom email to raffle ticket purchasers that did not win a raffle prize

Answer:

 Transactions[Item.TicketType = 'Raffle'] and not ItemsWon[Item.Type = 'Raffle
Prize']

Example 9: Missing Meal Choices

Question: Ticket holder that has not selected a meal choice

Answer:

AssignedTickets and not MealChoice

Example 10: Mass Editing

Question: Mass edit items within a value range

Answer:

Value > 0.00 and Value <= 25.00

Then select all returned rows and mass edit the "Starting Bid" field.

Float Comparisons

Be careful with float comparisons when working with currency values.

Fringe Features

Complex Tag Conditions

Instead of simple tag matching, you can use more complex conditions:

tags = "!XYZ" instead of not tags = "XYZ"
tags = "ABC | DEF" instead of tags = "ABC" or tags = "DEF"
tags = "ABC & DEF" instead of tags = "ABC" and tags = "DEF"

Like

Title like "Paddle%"

Filter Contexts

Filters are applied in the context of specific tables:

Items Context

  • Filter expressions work on Item table fields
  • Common fields: title, value, type, description, receiptRequired
  • Supports set operations on related records

Participants (Bidders) Context

  • Filter expressions work on Participant/Bidder table fields
  • Common fields: outstandingBalance, itemsWon, HasAssignedTicket, Tags
  • Can reference related Item records through set notation

Other Contexts

  • Donors: Filter donor records
  • Tickets: Filter ticket records
  • Raffles: Filter raffle records

Advanced Filter Features

Set Filtering

Use square bracket notation to filter within sets of related records:

itemsWon[item.number = '1']
ItemsWon[Item.Number = 'GOLD']
ItemsWon[Item.TicketType = 'Admission']
ItemsWon[Item.isEvent]

Boolean Fields

Boolean fields can be used directly without comparison:

receiptRequired
isEvent

Empty String Checks

Check for empty or null values:

description = ''
MealChoice = ''

Tag Matching

Filter by tags with various syntax options:

Tags = 'VIP'
Tags = 'GOLF' and Tags = 'SPONSOR-GUEST'
Tags = 'GOLF SPONSOR-GUEST'

Limitations

WARNING

Expression Language Limitations
The filter system does not implement a full expression language. You cannot use arithmetic operators like startingBid + bidIncrement > 1000.

Best Practices

  1. Use descriptive names for filters to make them easy to identify
  2. Test filter expressions before using them in production
  3. Keep expressions simple - complex nested conditions can be hard to maintain
  4. Use tags effectively for consistent categorization
  5. Document filter purposes in the description field
  6. Leverage advanced tags for complex categorization scenarios
  7. Use coerced booleans for cleaner expressions
Last reviewed: August 2025
SchemaReference
/advanced/schema_reference/
advanced
schema_reference
Schema Reference