Mastering Predefined Functions in Acumatica Generic Inquiries – Part 2

In Part 1 of this series, we covered logical functions (IIf, IsNull), text concatenation (Concat), and basic date calculations (DateDiff). In this part, we'll dive deeper into mathematical functions, string manipulation, and advanced date operations that will take your Generic Inquiries to the next level.

---

Part 1: Mathematical Functions

Mathematical functions enable you to perform arithmetic operations, rounding, and numerical transformations on numeric fields in your Generic Inquiries.

1. ROUND( value, decimals )

The ROUND function rounds a numeric value to a specified number of decimal places. This is essential for financial reports and currency formatting.

Example: Rounding Invoice Amounts

To display invoice amounts with two decimal places in a financial report:

=ROUND([ARInvoice.CuryDocBal], 2)

This formula rounds the document balance to two decimal places, ensuring consistent currency formatting across your report.

---

2. CEILING( value )

The CEILING function returns the smallest integer greater than or equal to a numeric value. This is useful for rounding up to the nearest whole number.

Example: Calculating Required Packaging Units

If you need to calculate how many boxes are required to ship a certain quantity (where each box holds 10 items):

=CEILING([SOLine.Qty] / 10)

For an order quantity of 23, this formula returns 3 (since 23/10 = 2.3, rounded up to 3 boxes).

---

3. FLOOR( value )

The FLOOR function returns the largest integer less than or equal to a numeric value. This is useful for rounding down to the nearest whole number.

Example: Calculating Full Days of Work

If you're calculating the number of full days a project has been active (where a day is considered complete after 8 hours):

=FLOOR([PMProject.TotalHours] / 8)

For a project with 25 total hours, this returns 3 (full 8-hour days).

---

Part 2: String Functions

String functions allow you to manipulate text fields—extracting substrings, changing case, trimming whitespace, and measuring length.

4. LEN( str )

The LEN function returns the number of characters in a string.

Example: Validating Field Lengths

To check if a customer name exceeds the maximum field length:

=LEN([Customer.AcctName])

This returns the character count of the customer name, which can be used with IIf to flag violations.

---

5. LEFT( str, length )

The LEFT function extracts a specified number of characters from the beginning (left side) of a string.

Example: Extracting Order Prefix

If order numbers follow a pattern like "SO-2026-001", you can extract the first 2 characters:

=LEFT([SOOrder.OrderNbr], 2)

This returns "SO" for the order number "SO-2026-001".

---

6. RIGHT( str, length )

The RIGHT function extracts a specified number of characters from the end (right side) of a string.

Example: Extracting Last 4 Characters

To extract the last 4 characters of a document number:

=RIGHT([SOOrder.OrderNbr], 4)

For order number "SO-2026-001", this returns "001".

---

7. SUBSTRING( str, start, length )

The SUBSTRING function extracts a portion of a string starting at a specified position and continuing for a specified length.

Example: Extracting Year from Order Number

To extract the year from an order number like "SO-2026-001":

=SUBSTRING([SOOrder.OrderNbr], 4, 4)

This extracts 4 characters starting at position 4, returning "2026".

---

8. UPPER( str ) and LOWER( str )

These functions convert a string to uppercase or lowercase, which is useful for case-insensitive comparisons or consistent formatting.

Example: Standardizing Customer Names

To display customer names in uppercase for reporting:

=UPPER([Customer.AcctName])

---

9. TRIM( str )

The TRIM function removes leading and trailing spaces from a string. This is essential for data cleaning.

Example: Cleaning Descriptions

To remove extra spaces from product descriptions:

=TRIM([InventoryItem.Description])

---

Part 3: Advanced Date Functions

Beyond DateDiff, Acumatica provides additional date functions for extracting date parts and performing date arithmetic.

10. DATEADD( interval, number, date )

The DATEADD function adds a specified number of intervals to a date. This is useful for calculating future or past dates.

Example: Calculating Payment Due Date

If you need to calculate a payment due date 30 days after the invoice date:

=DATEADD('d', 30, [ARInvoice.DocDate])

Example: Adding One Year

To add one year to a date:

=DATEADD('yyyy', 1, [ARInvoice.DocDate])

---

11. YEAR( date )

The YEAR function extracts the year from a date field.

Example: Grouping by Year

To create a report grouped by the year of invoice creation:

=YEAR([ARInvoice.DocDate])

---

12. MONTH( date )

The MONTH function extracts the month number (1-12) from a date field.

Example: Grouping by Month

To create a report grouped by the month of invoice creation:

=MONTH([ARInvoice.DocDate])

---

13. DAY( date )

The DAY function extracts the day of the month (1-31) from a date field.

Example: Identifying End of Month

To flag invoices issued on the last day of the month:

=IIf(DAY([ARInvoice.DocDate]) = DAY(DATEADD('d', -1, DATEADD('m', 1, [ARInvoice.DocDate]))), 
     'End of Month', 
     'Mid Month')

This formula checks if the invoice date is the last day of its month.

---

14. DATEPART( interval, date )

The DATEPART function returns a specific part of a date (year, month, day, week, etc.). It's more flexible than YEAR, MONTH, and DAY.

Example: Getting Quarter

To extract the quarter from a date:

=DATEPART('q', [ARInvoice.DocDate])

Example: Getting Week Number

To get the week number within the year:

=DATEPART('ww', [ARInvoice.DocDate])

---

Common Mathematical and String Patterns

---

Coming Up in Part 3

In the final part of this series, we'll explore:

• Aggregate Functions with Grouping: Using formulas with SUM, AVG, COUNT
• Conditional Aggregation: Summing values based on conditions
• Complex Formula Patterns: Combining multiple functions for advanced scenarios
• Performance Considerations: Optimizing formulas for large datasets