When working with Generic Inquiries in Acumatica, it's common to use multiple columns to validate different pieces of information based on specific business requirements. However, this can sometimes lead to a cluttered interface that is hard to understand and interpret. In this post, I'll show you a technique to consolidate multiple validation columns into a single 'Message' column, which will make it easier to communicate concise information about various source columns through one unified message all without developer customization.
This post will include Screenshots, Examples, and Generic Inquiries that use the SalesDemo dataset.
Our Example
Consider a Generic Inquiry where you create validation columns using formulas for one or more data elements. Let's use an example request as our starting point:
- Add a column to validate each vendor with 30D terms is within the US.
- Add an indicator to columns where a subcontractor is outside the US.
Solving: Conventional vs. Streamlined Approach
Conventional Approach:
In the conventional approach two validation columns will be used.
Here is the implementation of the conventional approach for our example above:
If the vendor terms are set to 30D and the Country is NOT set to US display 'Fix Country', otherwise do not display anything.
=IIf([VendorR.TermsID] = '30D' And [Address.CountryID] <> 'US', 'Fix Country', '')
If the Vendor Class is SUBCON (Subcontractor) and the Country is NOT set to US, display 'Outside US', otherwise do not display anything.
=IIf( [VendorR.VendorClassID]='SUBCON' And [Address.CountryID] <> 'US', 'Outside US', '')
Streamlined Approach
Using the streamlined approach introduced in this post, we can reduce to a single validation column regardless of the number of checks we need to run for validation.
This simplification not only reduces the number of columns in the Generic Inquiry but also makes it easier to identify and communicate potential issues by providing a single, unified message column that consolidates various validation checks.
To implement this technique, you can use Acumatica's formula expressions, specifically the Concat function and the IIf (If) function, to combine multiple validation checks into one string.
Here is the implementation of the streamlined approach for our example above:
If the vendor terms are set to 30D and the Country is NOT set to US concatenate 'The US Country and 30D Terms should not be used together. ' into the Message column, otherwise do not concatenate anything into the message column.
If the Vendor Class is SUBCON (Subcontractor) and the Country is NOT set to US, concatenate 'Vendor is a Contractor Outside the US. ', otherwise do not concatenate anything into the message column.
=Concat (
IIf([VendorR.TermsID] = '30D' And [Address.CountryID] <> 'US', 'The 30D Terms are only valid within the US. ', ''),
IIf([VendorR.VendorClassID]='SUBCON' And [Address.CountryID] <> 'US', 'Vendor is a Contractor Outside the US. ', '')
)
This formula concatenates a series of validation messages, each separated by a space. If a particular validation check fails, it will include the corresponding message in the output; otherwise, it will add an empty string.
The ‘Why?’
By consolidating multiple validation columns into a single message column in your Generic Inquiries, you can streamline the communication of potential issues and create a more user-friendly Generic Inquiry in Acumatica. This technique is particularly helpful when working with large datasets that require numerous validation checks. It simplifies the Generic Inquiry and makes it easier to spot and address problems in your data. Additionally, the single column allows for longer messages to be displayed which can help users understand why the message was triggered. Users can hover over the cell to see the full message.
Bonus
To add additional clarity for the user you can implement styling and confirmation messages as needed.
Styling
For each column involved in the message calculation styling can be added to add emphases the columns responsible for the error message.
In our example the Vendor Class, Country, and Terms columns are involved. Each column should use the style field to validate only that portion of the overall formula.
For Vendor Class: Use the If Statement and replace the error string with 'bad' a code in Acumatica that will highlight the cell in light red formatting with dark red text.
=IIf( [VendorR.VendorClassID]='SUBCON' And [Address.CountryID] <> 'US', 'bad', '')
For Country ID: Use the If Statement conditions in parenthesis, separated by the Or operator, and replace the error string with 'bad' a code in Acumatica that will highlight the cell in light red formatting with dark red text.
=IIf( ([VendorR.TermsID] = '30D' And [Address.CountryID] <> 'US') Or ( [VendorR.VendorClassID]='SUBCON' And [Address.CountryID] <> 'US') , 'bad', '')
For Terms: Use the If Statement and replace the error string with 'bad' a code in Acumatica that will highlight the cell in light red formatting with dark red text.
=IIf([VendorR.TermsID] = '30D' And [Address.CountryID] <> 'US', 'bad', '')
Confirmation Messages
If errors are not common, we can add a message to display, this will add a visual indicator to the user that the Messages column is working correctly.
To do this we structure our IIf Statement with a check on the concatenation value.
=NullIf(Concatenated Statement,'No message(s) found. ')
In our Example our statement would be:
=NullIf(
Concat (
IIf([VendorR.TermsID] = '30D' And [Address.CountryID] <> 'US', 'The 30D Terms are only valid within the US. ', ''),
IIf([VendorR.VendorClassID]='SUBCON' And [Address.CountryID] <> 'US', 'Vendor is a Contractor Outside the US. ', '')
), 'No message(s) found. '
)
Attached is the Example GI. Happy Validating!
