Extracting Data With Generic Inquiries
Using Generic Inquiries in Acumatica
Generic Inquiries (GI) in Acumatica are a popular way to create “reports” in Acumatica. However, they are much more than that. Generic Inquiries are similar to the results of a view in SQL. They are a tabular representation of data from one or more database tables. They can be used to display data, build screens, and they can even drive other system actions like doing mass updates to database fields or triggering business events (wow).
Generic inquiries are one option for extracting data from Acumatica. They are incredibly flexible but they do require a bit of a learning curve. You have multiple options for extracting data:
- For financial reporting, use “ARM” or “Advanced Report Manager” reports.
- For formatted reports like invoices, printed checks, or quotes, use report designer when greater formatting capability is required. Report designer involves downloading a separate application included with Acumatica.
- For flexible options for viewing and acting on Acumatica data, use Generic Inquiries.
Here are a few tips for using Generic Inquiries as of the Acumatica 2018 R2 release.
Understanding Data Access Classes (DAC)
These classes are a middle layer between the database and the Acumatica screens. When building generic inquiries, you use DACs to retrieve data from the database. The classes typically match the database table names (except for calculated fields). If needed, you can create custom DACs that pull different information based on what you need. Creating a custom DAC may require developer skills.
Choosing The Fields for your Generic Inquiry
At the time of this blog’s publication, there is currently not a database schema published for Acumatica. While this is under development, for now it takes some trial and error to understand the tables and fields that you need to call in your inquiry. Using a little tool called “Inspect Element” makes this possible.
To use “inspect element”, the customizer user role is required. On the screen from which you’d like to pull data, you may choose “inspect element” or use the Ctrl+Alt+Click as a keyboard shortcut. The Inspect element pop-up then shows the DAC where the data is stored. The pop-up also gives info on the field you need to use. For the document segment (below the header) click on the table name, not the field value, in order to get the field & table names displayed correctly when you inspect the element. Here’s an example of inspecting the element when looking at a customer:
Minimum Required Items:
Once you’ve identified the table and field names for the objects you want to use, you can pop back over to generic inquiry area to begin building. At minimum, you must identify a table and identify necessary fields in the results grid.
Optionally, you may add conditions, a site map location (for adding your generic inquiry to the Acumatica site), relations (if you’re adding multiple tables), parameters, grouping, sorting, entry points, and navigation (to do things like add hyperlinks or configure a side panel).
Building the inquiry
On the Generic Inquiries > Conditions tab there is a “From Schema” checkbox. This gives you the option to rely on existing formatting or selection list values as you configure the fields. Generally, for formatted fields such as dates or selection lists, check this to reduce errors related to formatting. For text fields, choosing ‘from schema’ is illogical because there is no pre-existing data or formatting from which to select. Here’s an example:
These generic inquiries can be accessible from multiple places within your Acumatica site. As an example, you could create a GI called “Cases by Customer”. After creating the GI, add it to the site map. Then, from the Orders generic inquiry screen, you can select “Cases by Customer” as a navigation screen, and link to that GI based on a match on Customer ID. This is part of displaying a true customer 360 view. The good people in development built this feature in response to customer requests.
To add the navigation, go to the navigation tab and select the screen that you’d like to open. In addition to adding generic inquiries to screens, you may also use them to open or launch a report. If you’re opening a report, first check the report itself to understand which parameters you need to pass through to the inquiry to configure your navigation. Once you set up the navigation, it is available to be selected in the “Navigate to” column in the ‘Results grid’ tab.
Generic inquiries can also drive business events in the system. A business event is a condition that can trigger an action. For example, if you wanted to send a customer a notice 5 days before their invoice due date, you would create a generic inquiry to show customers whose invoices will be due in the next 5 days. The records that are the RESULT of the GI can now have a set of actions performed against them. Once that’s created, associate these records with a “trigger condition” that would typically monitor a field for a change. Trigger conditions can also be based on a schedule rather than being based on a field change.
Then, once this is configured, establish the subscribers to the event. In our example, the subscribers would be the customer email addresses that should be notified of the upcoming invoice due date. Additionally, subscribers could also include launching an import scenario or another system action.
We are frankly blown away by the wide-reaching functionality of business events that are driven by generic inquiries. We have not seen competing software with this level of integrated alerts functionality.
Gotchas To Be Aware Of:
As with all good technology improvements, there are some bugs that will need to be addressed in the future: For example, in release 2019R1, if your GI includes groupings, the business events will not work.
Be aware of the required joins between tables that may not be obvious. For example, the Sales Order table key is a composite key: Order Number and Order Type. There could be an invoice and an order with the same document number, but it’s the “type” field that distinguishes the documents. So, document type, if it’s ever included in a table, will typically be a key field. With the field service features of Acumatica, the field “service order type” is also a key field. This is undocumented.
Understand the role of the business account ID. Each company in Acumatica has a business account ID. The company could be both a customer and a vendor- and it would have a single business account ID. Employee records also fall under a business account ID. So, be sure you’re creating a table join to the customer, vendor or employee table, be cognizant that the join may be from the document record (eg an Invoice record’s Business Account ID) to the Vendor table (Vendor Code) or some other unfavorable scenario.
There are security consideration related to business accounts. You may create restriction groups to limit access to vendor, customer and employee records. The parent table for all three of these is the business account. The business account table, however, does not respond to security groups. To avoid displaying all records, and only the records that a user should see, do not join tables to the BAccount (Business Account) table. Instead, pull records from the Vendor (or customer or employee) table so that the results inherit the security associated with the vendor restriction group.
For example, to link a Purchase Order to a Vendor record:
PurchaseOrder.VendorID = VendorTable.BAccountID
Use the expression editor. When adding fields to your generic inquiry, you can add logic and formulas to the field using the expression editor. Within the editor, there are predefined formulas, and a validation option to check your formula. However, you must prefix your formula with “ = “ . If you don’t do this, it will pass validation, but the formula will not work.
A formatting hack: to format fields showing an amount value, you may actually reference another field in an unrelated table as long as it has the destination formatting you prefer. This will not add the unrelated table to the underlying SQL and will not affect performance. Instead, the field’s formatting is referenced and applied to the field you have selected. To use this, add the formatted field in the ‘Schema Field’ column in the results grid tab. Cool!
To view the underlying SQL that generates your GI, view the trace. To see this, go to “Tools>Trace” on the screen that has the GI results. Do not open it in a new tab – it will do that on its own.
To get fancy, you could even create an SQL view as a customized DAC. First, create the view. Then, import the view as a customization. Then, that view can be pulled into a GI. This is one way to create subqueries within a generic inquiry.
There is SO much that you can do with generic inquiries. We love that they are just embedded into the Acumatica product- no special add-ons or additional licensing is needed. They are flexible, powerful, and available for you to use right within the product. Let us know if you would like any guidance on creating some to get started!