Filters - Nioron07/Easy-Acumatica GitHub Wiki

This guide explains how to use the F object from easy-acumatica to build flexible, type-safe OData-v3 queries in a Pythonic way. This new method uses operator overloading to replace manual string manipulation.

1. Importing the Helper

To get started, import the F factory object.

from easy_acumatica.models.filter_builder import F

2. The F Object: Your Gateway to Filtering

The F object is a factory for creating filter expressions. You can refer to any field in your Acumatica entity by simply accessing it as an attribute on F.

• F.FieldName creates an expression for a standard field.
• F.MainContact.Email creates an expression for a field on a linked entity.

These F objects can then be combined using standard Python operators to build your filter.

3. Building Filters with Operators

You can use familiar Python operators to create your filter logic. The library automatically translates them to the correct OData syntax.

3.1. Comparison Operators

Python Operator	OData Equivalent	Example
==	eq	F.Status == 'Active'
!=	ne	F.Type != 'Credit Memo'
>	gt	F.Amount > 100
>=	ge	F.Amount >= 100
<	lt	F.Amount < 500
<=	le	F.Amount <= 500

3.2. Logical Operators

Because Python's and, or, and not keywords cannot be overloaded, we use the bitwise operators &, |, and ~ for logical operations.

Python Operator	OData Equivalent	Example
&	and	(F.Status == 'Open') & (F.Amount > 1000)
|	or	(F.Name == 'Matt') | (F.Age >= 30)
~	not	~(F.Country == 'USA')

Important: Always wrap individual clauses in parentheses () when combining them with logical operators to ensure correct precedence.

3.3. Arithmetic Operators

Standard arithmetic operators +, -, *, /, and % are also supported.

# OData: (Price mul 1.1) gt 50  
filter_expr = (F.Price * 1.1) > 50

# OData: (QtyOnHand sub QtyOnOrders) gt 0  
inventory_check = (F.QtyOnHand - F.QtyOnOrders) > 0

4. Using OData Functions

For OData functions like tolower, substringof, or round, you can chain methods directly onto your F object expressions.

4.1. String Functions

• contains(value): F.CustomerName.contains('Acme') -> substringof('Acme', CustomerName)
• startswith(value): F.SKU.startswith('PROD-') -> startswith(SKU, 'PROD-')
• endswith(value): F.FileName.endswith('.pdf') -> endswith(FileName, '.pdf')
• tolower(): F.Email.tolower() == '[email protected]'
• toupper(): F.Country.toupper() == 'USA'
• length(): F.Description.length() > 10
• trim(): F.Comment.trim() != ''
• indexof(substring): F.Description.indexof('Error') > -1
• replace(find, replace_with): F.Description.replace(' ', '\_')
• concat(string): F.FirstName.concat(' ').concat(F.LastName)
• substring(pos, length?): F.AccountNumber.substring(0, 4\) == 'ACCT'

4.2. Date and Math Functions

• .day(), .month(), .year(), .hour(), etc.: F.DueDate.month() == 12
• .round(), .floor(), .ceiling(): F.Freight.round() > 10

5. Putting It All Together: Complex Examples

The true power of this new syntax is how cleanly these pieces can be combined.

Example 1: Nested Logic

Find all open invoices over $500, or any invoice for a specific customer.

filter1 = (F.Type == 'Invoice') & (F.Status == 'Open') & (F.Amount > 500)  
filter2 = F.CustomerID == 'C00123'

final_filter = filter1 | filter2  
# This creates a filter like:  
# ((((Type eq 'Invoice') and (Status eq 'Open')) and (Amount gt 500)) or (CustomerID eq 'C00123'))

Example 2: Chained Functions and Logic

Find all active customers whose email address is in the 'example.com' domain.

active_customers = F.Status == 'Active'  
email_check = F.MainContact.Email.tolower().endswith('@example.com')

final_filter = active_customers & email_check
# ((Status eq 'Active') and endswith(tolower(MainContact/Email),'@example.com'))

Example 3: Filtering on Custom Fields

You can easily filter on custom fields using the special F.cf() helper method.

# Find records where the custom string field UsrRepairType is 'Battery'  
cf_filter = F.cf("String", "ItemSettings", "UsrRepairType") == 'Battery'

# Use it in a larger query  
full_query = (F.IsActive == True) & cf_filter
# ((IsActive eq true) and (cf.String(f='ItemSettings.UsrRepairType') eq 'Battery'))

This updated approach provides a much more intuitive and less error-prone way to construct powerful OData queries.