During one of my presentations at Acumatica Summit 2017, entitled Business Intelligence for Business Leaders, I mentioned, “Building Generic Inquiries Isn’t Scary”.
Click here for the slides and other information from that presentation.
But, what did I mean by “Building Generic Inquiries Isn’t Scary”?
Well, I guess we should start with defining what a Generic Inquiry is. A Generic Inquiry in Acumatica is a query that can pull from anywhere in the Acumatica database. If you have a SQL background, then think of it as a SELECT statement. The query result is displayed in the Generic Inquiry window and a user can interact with it (filtering, sorting, changing the column order, adding/removing columns, etc.).
But building a Generic Inquiry can be intimidating, especially for those who aren’t very technical and they don’t know SQL.
I hope to convince you in this post that building generic inquiries isn’t scary.
They key to this is understanding that Inspect Element is your best friend. For more on that, go read Inspect Element is your Best Friend and then come back and continue reading this post.
You can use Inspect Element to find the database tables and fields that you need. Then all you need to do is drop them onto a Generic Inquiry.
Let’s use a simple example. Let’s say that you want a simple Generic Inquiry with only two columns, the two columns pictured in the screenshot below.
First, we need to open the Generic Inquiry (SM208000) screen in Acumatica. Then, give it a name like SampleGenericInquiry (hey, that’s pretty creative). And there it is in the screenshot below. See? Not too scary so far right?
Next, we need to list the tables that we collected by using our best friend, Inspect Element. In our example, both of the fields that we want are in one table: ARInvoice.
So we need to list ARInvoice on the Tables tab of the Generic Inquiry (SM208000) screen. You can just click the + icon, then type “ARInvoice” which will search all of the tables for you so you don’t have to scroll.
We want the one that is in the AR module, so we need to choose PX.Objects.AR.ARInvoice.
Next, we need to list the fields that we want on the Results Grid tab. Using our best friend, Inspect Element, we can see that the two fields we want are RefNbr and CuryDocBal.
You first put the table in the Object column (we only have one table in our example), then you put the field in the Data Field column.
And that’s it! Not too scary huh?
To run the Generic Inquiry, just click the VIEW INQUIRY button on the top.
And, voilà, here it is
Now, we might not want to see invoices that don’t have a balance. So, let’s add a filter to the Conditions tab of the Generic Inquiry (SM208000) screen.
This filter will hide invoices whose balance is not zero.
Now, when we click the VIEW INQUIRY button, we won’t see any invoices with balances.
And that’s it! If this is your first Generic Inquiry, then congratulations. Job well done.
Hopefully now you agree with me that building Generic Inquiries isn’t scary.
There is a scary part though that I probably should have told you about sooner.
The scary part is the Relations tab. You need this tab if you wind up listing more than one table on the Tables tab. But the Relations tab can get kind of scary. You have to understand a bit about SQL Joins (Outer, Inner, Cross, etc.) and relational databases.
My recommendation on the Relations tab: just call your Acumatica partner. You can still build everything else and do 80% of the work, but give your partner a call on the Relations tab. Trust me, you won’t regret it.
