Let’s review adding a total to a Generic Inquiry. Totals on Generic Inquiries are a fairly new feature added to Acumatica that can be used in many ways.
They allow you to use the GI as a quick on the fly reporting mechanism that totals as you adjust the filters.
You can also use them when importing documents, Open AR, Open AP during implementation to verify totals back to the legacy system.
They are also helpful, if you are using an order type, to identify outside orders to see the amount of the order type. Ie What is the total of my Web Site Orders, type WO?
Let’s begin with the Sales Order GI, SO-SalesOrder.
First, we identify the column we want to total. In this case, we will total the order total column.
We go to the Generic Inquiry screen, either via the menu or the Customization link in the top right.
Call up the GI, SO-SalesOrder, go to the RESULTS GRID tab.
Locate the field we want to total, CuryOrderTotal, and in the column for Total Aggregate Function, add SUM & save the change.
When completed, the GI form will look like this:
When you view the Inquiry, you will see the total at the bottom.
Like this:
This total is the total of all the documents that meet the filter requirements. You can start applying filters and as the filters are applied, the total will change.
By filtering for Not QT (all but quotes) and Status of Open & Back Order you can see that the total drops based on the documents that satisfy the filters.
Like this:
Now let’s apply the totaling feature to the AR Invoices & Memos GI.
We are going to total the Balance Remaining on the Open Documents.
We do the same steps as before.
Go to the Generic Inquiry, AR-Invoices, and Memos.
On the RESULTS GRID tab, find the field for Balance, CuryDocBal, and add SUM in the Total Aggregate Function column and save the changes.
When we view the Inquiry, you see that the total is there!
However, filtering for a specific customer and Open Documents, you’ll notice that the total is the sum of all the documents. The total function does not subtract the Credit Memo amount since all the numbers in Acumatica are stored as positive.
Here is how we can handle that. This does involve using a function but it is a fairly simple function.
We go back to the Generic Inquiry, back to the results grid.
We can click on the Data Field CuryDocBal. This will open the field & allow us to edit it.
Click on the pencil, and the editor will open like this: We will be working in the Blue highlighted area.
We are going to replace the CuryDocBal with a calculated field that is based on the Document type.
In the Box where the CuryDocBal is, replace it with
=iif ([ARInvoice.DocType]<>’CRM’, [ARInvoice.CuryDocBal], [ARInvoice.CuryDocBal] * -1)
This is saying if the Document Type is not a credit memo use the Document Balance and if the Document Type is a credit memo, multiple the Document Balance by -1.
We also need to add a Caption since we are creating & defining a new field.
Let’s call this field Open Total.
In the Generic Inquiry, the end result looks like this: The columns have been moved & adjusted for viewing ease.
Once the changes are saved, the final inquiry has the Document Balance total with the Credit Memos subtracted from the Open Invoices.
The final Invoices & Memos Inquiry result looks like:
These are just some of the ways that totals can be applied to and used on Generic Inquiries.
For more Information and training on Generic Inquiries, or a personalized demo please contact us!
