Mastering Predefined Functions in Acumatica Generic Inquiries – Part 3

In Parts 1 and 2 of this series, we covered logical, text, mathematical, and date functions. In this final part, we'll explore aggregate functions, conditional aggregation, and complex formula patterns that combine multiple functions to solve real-world business problems.

⚡ Key Insight: Aggregate functions work with grouping to summarize data, while conditional aggregation allows you to perform calculations based on specific criteria—all within the Generic Inquiry framework.

Part 1: Understanding Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single summary value. In Generic Inquiries, these functions are typically used with grouping to create summarized reports.

💡 Pro Tip: Aggregate functions only work correctly when you have defined grouping on the Grouping tab. Without grouping, the system doesn't know how to segment the data for the aggregation.

1. SUM( field )

The SUM function returns the total of all values in a numeric field within each group.

Example: Total Sales by Customer

To calculate total sales for each customer:

=SUM([SOOrder.TotalAmount])

When grouped by CustomerID, this formula returns the total order amount for each customer.

2. AVG( field )

The AVG function returns the average of all non-null values in a numeric field within each group.

Example: Average Order Value by Customer

To calculate the average order value for each customer:

=AVG([SOOrder.TotalAmount])

When grouped by CustomerID, this returns the average order amount for each customer.

3. COUNT( field )

The COUNT function returns the number of values in a field within each group.

Example: Number of Invoices by Customer

To count how many invoices each customer has:

=COUNT([ARInvoice.InvoiceNbr])

When grouped by CustomerID, this returns the number of invoices for each customer.

⚠️ Note: COUNT only counts non-null values. To count all rows (including those with nulls), use COUNT(*) in some database systems, but in Acumatica formulas, use COUNT([Field]) where the field is guaranteed to have a value.

4. MIN( field )

The MIN function returns the minimum value in a field within each group. It works with numeric, date, and text fields.

Example: Earliest Order Date by Customer

To find the first order date for each customer:

=MIN([SOOrder.OrderDate])

When grouped by CustomerID, this returns the earliest order date for each customer.

5. MAX( field )

The MAX function returns the maximum value in a field within each group.

Example: Most Recent Order Date by Customer

To find the last order date for each customer:

=MAX([SOOrder.OrderDate])

When grouped by CustomerID, this returns the most recent order date for each customer.

6. STRINGAGG( field, separator )

The STRINGAGG function concatenates values from multiple rows into a single string, separated by a specified delimiter. This is useful for creating comma-separated lists within groups.

Example: List of Order Numbers by Customer

To create a comma-separated list of order numbers for each customer:

=STRINGAGG([SOOrder.OrderNbr], ', ')

When grouped by CustomerID, this returns something like "SO001, SO002, SO003" for each customer.

💡 Pro Tip: STRINGAGG is particularly useful for generating lists for export or for creating summary fields that show all related items in a single cell.

Part 2: Conditional Aggregation

Conditional aggregation allows you to perform aggregate calculations only on rows that meet specific criteria. This is typically achieved by combining IIf with aggregate functions.

7. Summing Values Based on Condition

You can sum only the values that meet a certain condition by using IIf inside SUM.

Example: Total Revenue vs. Discounted Revenue

To calculate total revenue and revenue from discounted orders separately:

=SUM([SOOrder.TotalAmount]) // Total revenue
=SUM(IIf([SOOrder.DiscountPercent] > 0, [SOOrder.TotalAmount], 0)) // Discounted revenue
=SUM(IIf([SOOrder.DiscountPercent] = 0, [SOOrder.TotalAmount], 0)) // Non-discounted revenue

This technique gives you the ability to analyze subsets of data within the same grouped report.

8. Counting with Conditions

You can count only the records that meet a certain condition using IIf inside COUNT.

Example: Count of Open vs. Completed Orders

To count open orders and completed orders separately for each customer:

=COUNT(IIf([SOOrder.Status] = 'Open', [SOOrder.OrderNbr], null))
=COUNT(IIf([SOOrder.Status] = 'Completed', [SOOrder.OrderNbr], null))

This returns the number of open and completed orders for each customer in a single summarized report.

9. Average with Conditional Logic

You can calculate averages based on conditions using IIf inside AVG.

Example: Average Order Value for Different Statuses

To calculate the average order value for open and completed orders separately:

=AVG(IIf([SOOrder.Status] = 'Open', [SOOrder.TotalAmount], null))
=AVG(IIf([SOOrder.Status] = 'Completed', [SOOrder.TotalAmount], null))

✅ Use Case: This pattern is extremely useful for creating KPI dashboards where you need to compare metrics across different categories or statuses.

Part 3: Complex Formula Patterns

By combining multiple functions, you can create sophisticated formulas that solve complex business problems.

Pattern 1: Dynamic Status Indicators

Example: Order Status with Aging

To display an order status that includes aging information:

=IIf([SOOrder.Status] = 'Open', 
     Concat('Open (', DateDiff('d', [SOOrder.OrderDate], Today()), ' days)'),
     [SOOrder.Status])

This formula returns "Open (5 days)" for orders that are still open, showing how many days they've been open.

Pattern 2: Tiered Discount Calculations

Example: Volume-Based Discount

To calculate a discount based on order quantity:

=IIf([SOLine.Qty] > 100, [SOLine.UnitPrice] * 0.9,
 IIf([SOLine.Qty] > 50, [SOLine.UnitPrice] * 0.95,
 [SOLine.UnitPrice]))

This formula applies a 10% discount for quantities over 100, a 5% discount for quantities over 50, and the full price otherwise.

Pattern 3: Custom Formatting

Example: Formatted Currency with Symbol

To format a currency value with a dollar sign and two decimal places:

=Concat('$', ROUND([SOOrder.TotalAmount], 2))

Pattern 4: Date Range Classification

Example: Fiscal Quarter Classification

To classify a date into fiscal quarters based on a custom fiscal year:

=IIf(MONTH([ARInvoice.DocDate]) BETWEEN 1 AND 3, 'Q1',
 IIf(MONTH([ARInvoice.DocDate]) BETWEEN 4 AND 6, 'Q2',
 IIf(MONTH([ARInvoice.DocDate]) BETWEEN 7 AND 9, 'Q3',
 IIf(MONTH([ARInvoice.DocDate]) BETWEEN 10 AND 12, 'Q4', 'Unknown'))))

Performance Considerations

When working with complex formulas, especially those that use multiple functions or aggregate across large datasets, keep these performance considerations in mind:

✅ Use Filters Wisely

Apply filters to limit the dataset before performing complex calculations. This improves performance significantly.

✅ Avoid Unnecessary Nesting

Deeply nested IIf statements can impact performance. Consider if a simpler approach exists.

✅ Limit Result Sets

For large GIs, use the Disable Record Counts option to improve loading performance.

✅ Test with Sample Data

Always test your formulas with sample data before applying them to production systems.

Series Summary: Function Reference

Category	Function	Description	Part
Logical	IIf	Conditional logic	1
Logical	IsNull	Null value handling	1
Text	Concat	String concatenation	1
	LEN	String length	2
	LEFT	Extract from start	2
	RIGHT	Extract from end	2
	SUBSTRING	Extract from position	2
Date	DateDiff	Date difference	1
	DATEADD	Add to date	2
	YEAR/MONTH/DAY	Extract date part	2
	DATEPART	Extract any part	2
Mathematical & Aggregate	ROUND	Round number	2
	CEILING	Round up	2
	FLOOR	Round down	2
	SUM	Total of values	3
	AVG	Average of values	3
	COUNT	Count of values	3

Conclusion

The predefined functions in Acumatica Generic Inquiries provide a comprehensive toolkit for creating dynamic, data-driven reports. This three-part series has covered:

Part 1: Logical functions (IIf, IsNull), text concatenation (Concat), and date differences (DateDiff)
Part 2: Mathematical functions (ROUND, CEILING, FLOOR), string functions (LEFT, RIGHT, SUBSTRING, LEN, UPPER, LOWER, TRIM), and advanced date functions (DATEADD, YEAR, MONTH, DAY, DATEPART)
Part 3: Aggregate functions (SUM, AVG, COUNT, MIN, MAX, STRINGAGG), conditional aggregation, and complex formula patterns

📌 Key Takeaway: By mastering these functions, you can create sophisticated reports that would otherwise require custom code or SQL. The Formula Editor provides a user-friendly interface for building and testing your expressions, making advanced reporting accessible to all users.

🔍 Next Steps: Start by creating simple formulas with the functions covered in this series. As you become comfortable, experiment with combining multiple functions to solve complex business problems. The Acumatica Formula Editor is designed for experimentation—don't be afraid to test different approaches.