acuamitca.com logo ACUAMITCA
~/blog/article

Mastering Predefined Functions in Acumatica Generic Inquiries – Part 1

Acumatica 0 mins read August 25, 2026
 

Generic Inquiries are one of Acumatica's most powerful reporting tools, and their true potential lies in the ability to use predefined functions within formulas. These functions allow you to perform complex calculations, transform data, apply conditional logic, and create dynamic reports without writing SQL code. In this multi-part series, we'll explore every predefined function available in Acumatica Generic Inquiries with practical examples.

⚡ Key Insight: Formulas in Generic Inquiries give you the ability to use advanced calculations and data transformation functions when certain values need to be computed or are dependent on other data fields.

Understanding Formulas in Generic Inquiries

Before diving into specific functions, it's essential to understand how formulas work in Generic Inquiries. You can access the Formula Editor dialog box by double-clicking a cell in the Data Field column on the Results Grid tab of the Generic Inquiry (SM208000) form.

The Formula Editor is organized into three panes:

  • Component Type (upper left): Lists categories like Functions, Fields, Styles
  • Component Selection (upper right): Shows available items in the selected category
  • Formula Text (bottom): Where you build and edit your formula

💡 Pro Tip: When referencing data fields in formulas, the DAC name precedes the data field name, and this complex name is enclosed in brackets, like [ARInvoice.CuryDocBal].

Part 1: Logical Functions

Logical functions are essential for creating conditional logic in your Generic Inquiries. They allow you to test conditions and return different values based on the result.

1. IIf( expr, truePart, falsePart )

The IIf function is one of the most commonly used functions in Generic Inquiries. It evaluates an expression and returns one value if the expression is true and another value if it's false.

Parameter Description
expr The condition to evaluate
truePart Value returned if condition is true
falsePart Value returned if condition is false

Example: Highlighting High-Value Invoices

A common use of IIf is to apply conditional formatting to rows. For instance, you can highlight rows where the document balance exceeds $1,000:

=IIf([ARInvoice.CuryDocBal] > 1000, 'yellow', 'default')

This formula checks if the CuryDocBal (document balance) is greater than 1000. If true, it returns 'yellow' (highlighting the row). If false, it returns 'default' (no highlighting).

✅ Use Case: You can use the same approach to highlight rows based on any condition—overdue invoices, low stock items, or high-value orders.

Example: Nested IIf for Aging Categories

You can nest IIf functions to create multi-condition logic. For example, to categorize records into aging buckets:

=IIf(DateDiff('d', [ARInvoice.DueDate], [AgingDate]) <= 30, '0-30 Days',
 IIf(DateDiff('d', [ARInvoice.DueDate], [AgingDate]) <= 60, '31-60 Days',
 'Above 60 Days'))

2. IsNull( value )

The IsNull function checks if a value is null and returns a boolean result. It's often used in combination with IIf to handle null values gracefully.

Example: Handling Null Due Dates

In aging calculations, you may need to handle cases where the due date is null. The IsNull function helps you fall back to a default value:

=IIf(DateDiff('d', ISNULL([ARInvoice.DueDate], [ARInvoice.DocDate]), [AgingDate]) > 0, 
      IIf(DateDiff('d', ISNULL([ARInvoice.DueDate], [ARInvoice.DocDate]), [AgingDate]) < 31, 
      [ARInvoice.DocBal], 0), 0)

In this example, if DueDate is null, the formula uses DocDate as a fallback before calculating the date difference.

Part 2: Text Functions

Text functions allow you to manipulate and transform string values in your Generic Inquiries.

3. Concat( str1, str2, ... )

The Concat function combines multiple string values into a single string. This is useful for creating descriptive columns or custom labels.

Example: Combining Order Number and Description

Suppose you want to display each sales order's number followed by its description in a single column:

=Concat([SOOrder.OrderNbr], ': ', [SOOrder.Description])

This formula creates a result like "SO12345: Customer order for winter jackets".

💡 Pro Tip: The Concat function can accept multiple string parameters. Use it to combine fields, static text, and separators.

Part 3: Date Functions

Date functions are critical for working with date fields, calculating differences, and formatting dates.

4. DateDiff( interval, date1, date2 )

The DateDiff function calculates the difference between two dates in the specified interval (day, month, year).

Interval Description
'd' Days
'm' Months
'yyyy' Years

Example: Calculating Days Overdue

You can calculate how many days an invoice is overdue by comparing the due date to today's date:

=DateDiff('d', [ARInvoice.DueDate], [AgingDate])

This formula returns the number of days between the due date and the aging date. A positive value indicates the invoice is overdue.

Example: Aging Buckets with DateDiff

Combine DateDiff with IIf to create aging categories:

=IIf(DateDiff('d', [ARInvoice.DueDate], [AgingDate]) <= 30, '0-30 Days',
 IIf(DateDiff('d', [ARInvoice.DueDate], [AgingDate]) <= 60, '31-60 Days',
 'Above 60 Days'))

Coming Up in Part 2

In Part 2 of this series, we'll cover:

  • Mathematical Functions: SUM, AVG, MIN, MAX, ROUND, CEILING, FLOOR
  • String Functions: LEFT, RIGHT, LEN, TRIM, UPPER, LOWER, SUBSTRING
  • Date Functions: DATEADD, DATEPART, YEAR, MONTH, DAY
  • Aggregate Functions: Using formulas with grouping and aggregation

📌 Key Takeaway: Logical and text functions are the building blocks of dynamic Generic Inquiries. By mastering IIf, IsNull, Concat, and DateDiff, you can create powerful, conditional reports that adapt to your business needs.

🔍 Further Reading: Experiment with creating your own formulas using the examples in this article. The Formula Editor in Acumatica provides a user-friendly interface for building and testing your expressions.