Also, you can group inquiry data by specifying grouping settings and by adding rows that return aggregated values for a group.
In this chapter, you will learn how to modify an existing generic inquiry in the following ways:
You may find the information in this chapter useful when you are responsible for the customization of Acumatica ERP in your company. To speed up inquiry creation, you have copied an existing generic inquiry that provides results similar to those you need. Now you want to group and aggregate the inquiry output and add a sort order to suit your needs.
You can use the settings on the Sort Order tab of the Generic Inquiry (SM208000) form to specify how the inquiry data is sorted—that is, the default order in which the results should be displayed on the generic inquiry form. For example, the inquiry results can be sorted by date and by customer name. To do this, on the Sort Order tab, you add a row for each data field of each particular column that you want to use for sorting the inquiry results. In these rows, you specify whether the results are sorted in ascending or descending order of the values in the column; the default Ascending sort order is selected when you add a row.
Any user-defined sorting that a user of an inquiry specifies (by clicking the column header and specifying a condition in the dialog box) overrides any default sorting you specify on the Sort Order tab.
If an inquiry has no sorting settings specified on the Sort Order tab, the system displays a warning in the tab title, as shown in the following screenshot. Until you define sorting settings, the default ones are used for the inquiry.
On the Grouping tab (shown in the following screenshot) of the Generic Inquiry (SM208000) form, you can specify the data field or fields by which you would like to group data. On the Results Grid tab, you can also add rows that will hold the aggregated values of these groups. For example, you may want to group sales orders by date and status to get the count of sales orders, as well as their total and average amounts for each day and status.
If no function is selected in the Aggregate Function column for a data field used for grouping, the following aggregate functions are applied by default:
The aggregate function must correspond with the type of the field selected in the Data Field column. Selecting the SUM function for a character data type (such as a customer’s name, an address, or an email address) causes a runtime error. For the calculated columns, you have to select the appropriate aggregation function manually, because no single function applies to them by default.