10 Acumatica Generic Inquiry Tips and Tricks for SQL Gurus
On the recent AUGForum.com podcast reporting party there was a discussion at 17:36 of the pros and cons of SQL views vs Acumatica Data Access Classes.
For those with a SQL background, working within the Acumatica framework sometimes requires a change of mindset and a little creativity to get the job done. Instead of composing a SQL query directly, you use either the Report Designer or the Generic Inquiry Screen (SM208000) to define a query schema comprised of one or more related Data Access Classes (DAC). Behind the scenes, Acumatica then translates that query schema into a SQL query for you.
If requirements dictate a query that is not possible within the Acumatica tools, one option is to create a SQL view and publish that view as a DAC within Acumatica. Doug Johnson wrote a great article that walks through this process: Technical Tuesday: Building Reports from an SQL View in Acumatica Cloud ERP Software .
That being said, there are some real benefits to staying within the Acumatica toolset:
- Row level security rules are respected automatically
- Data relationships are defined automatically
- Take full advantage of the declarative capabilities of Acumatica data access classes
- Increased likelihood that future changes to the Acumatica physical data layer will not impact reports and queries
As I challenge myself to stay within the Acumatica toolset, I'd like to share a few helpful tips and tricks that I've discovered along the way.
- Take advantage of Acumatica DAC functionality
Using an Acumatica DAC as the basis for a Generic Inquiry gives you relationships and navigation functionality for free.
For example, let's consider the CROpportunity DAC, which as the name suggests contains opportunities. This DAC includes the field WorkgroupID, which if we look in SQL is an integer value. By simply adding the WorkgroupID field to the Results Grid, Acumatica will display the Workgroup name and hyperlinked the field automatically.
We can also use a condition on the WorkgroupID to filter on only "Solution Sales", and Acumatica automatically does the mapping and returns the appropriate records.
2. Use the SQL Trace to understand what Acumatica is doing
After viewing the results of a Generic Inquiry, you can click Tools-->Trace to see the details of the SQL query that was just executed. In the example above, this allows us to see that Acumatica has automatically looked up the Workgroup description and added the appropriate condition to the WHERE clause: [CROpportunity_CROpportunityRevision].[WorkgroupID] = 104
The lookup works without any joins or additional code because the workgroupID field of the CROpportunity DAC includes the [PXCompanyTreeSelector] attribute.
3. Use fields in a formula on the Value column of the conditions tab
Another hidden feature that I've discovered is the ability to enter a formula in the Value 1 column. Although the formula editor that opens with the pencil icon doesn't show Fields as an option, the parser will accept them. This is one way to compare two data fields.
4. Create a hidden parameter, and use it to compare with the results of any formula
To create more complex formulas, a useful technique is to create a hidden parameter. We can set a default value for the parameter and then compare this value to the results of a formula on the Data Field column.
This is the parameters tab where we have created hidden parameter with the name 'True', and set the default value equal to 1.
Then we can create a formula like this one, and compare it with the parameter:
=IIf([CROpportunity.CreatedDateTime]>[CROpportunity.CloseDate], 1, 0)
5. Look for missing data with a left join
Another common scenario involves the search for missing data. I wrote more about that here. In our current example, we might want to find the business accounts that have NOT had a recent opportunity. In SQL we can accomplish this using a left join, and similarly in Acumatica we can use a left join and look for an empty field in the child table to find the accounts with missing data.
We can add other criteria on the join, such as a cutoff date or opportunity type. It's important to specify the criteria the matching records here rather than on the conditions tab. Just as in SQL, if we put this condition in our WHERE clause then we will lose the ability to look for the blank rows which are the ones with the missing data!
Also make note the parent and child fields on the data links will default to the corresponding parent and child DAC tables, however this is not a requirement. You use can any arbitrary formula and reference other tables within the data links.
Finally add the condition that the CROpportunity.OpportunityID field is empty, which is equivalent to CROpportunity.OpportunityID IS NULL in SQL.
6. Use a formula and relative dates to specify a default value for parameters
In the example above, the [LastOpp] parameter used on the relation is our cutoff date. The query will look for opportunities that were created after the specified date. We can set a default for this parameter by using relative dates in a formula. In this example we are looking for accounts that have not had an opportunity in the last 365 days.
7. Join the same DAC table multiple times by using an alias
Now let's add the CROpportunity DAC a second time so that we can display the latest opportunity date.
The alias is how we will refer to this table in the other GI tabs, such as on the table relations:
8. Unhide the Aggregate Function column using the column selector
To see the latest create date, we need to specify the aggregate function MAX. The Aggregate Function column is often hidden by default, so you may need to unhide it with the column selector.
9. Group on fields that are not being aggregated
Be sure to group on any fields that are not being aggregated, in this case the BAccountID field.
Now we have a working generic inquiry that allows the user to enter a date and see all business accounts that have not had any opportunities created since the specified date.
10. Optimize and troubleshoot by using the SQL Trace to understand what Acumatica is doing!
We could extend this example further to look at only the opportunities assigned to a particular workgroup. The DAC often provides predefined relationships to other tables such as the workgroupID_description.
By using the SQL Trace feature we can see that Acumatica has written the subquery to map the workgroupID_Description automatically to the workgroupID.
'Solution Sales' = (SELECT TOP (1) [EPCompanyTreeExt].[Description]FROM [EPCompanyTree] [EPCompanyTreeExt]WHERE ( [EPCompanyTreeExt].[CompanyID] = 2) AND [EPCompanyTreeExt].[workGroupID] = [CROpportunity_CROpportunityRevision].[WorkgroupID])
If the query is running slowly, and we wanted to make this code more efficient, we could modify this to join on the WorkgroupID field directly. Note that in joins, Acumatica does NOT automatically apply the DAC schema settings, hence the WorkgroupID value here should be the numeric id. We can find the value that we need in our earlier SQL trace and specify it as shown below.
Now the trace will look like this:
104 = [CROpportunity_CROpportunityRevision].[WorkgroupID]
I hope a few of these tips and tricks will help you get up to speed quickly with the Acumatica reporting tools. Happy report writing! Find more Tips and Tricks at Parallel Solutions.
Technical Consultant
10moGood Morning, Can you help me about the column Amount The condition is when the Reference number starts with ARC the amount will be negative.
Asistant Manager ER Manager(ERP) | Database Administrator| Crystal Report Developer | AcumaticaERP | QuickBook Crystal Report developer
1yDear Laura Jaffe i need some help i want condition that when i open GI it will not show me the last clock in wo which is less than 1 mint i dont understand how i call it
Principal Consultant at DataQuest Solutions | Acumatica Cloud ERP Business Partner |
2yHubba!