AUGForums.com

An Acumatica User Group

  • Forums
  • Podcast
  • Blog
  • Rolodex
  • Login
  • Start Here
  • Courses
  • Register

Building Generic Inquiries Isn’t Scary

January 30, 2017 by Tim Rodman

Building Generic Inquiries Isn’t Scary. Well, actually it can be scary, but more on that at the bottom of this post.

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.

Building Generic Inquiries Isn't Scary

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?

Building Generic Inquiries Isn't Scary

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.

Building Generic Inquiries Isn't Scary

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.

Building Generic Inquiries Isn't Scary

And that’s it! Not too scary huh?

To run the Generic Inquiry, just click the VIEW INQUIRY button on the top.

Building Generic Inquiries Isn't Scary

And, voilà, here it is

Building Generic Inquiries Isn't Scary

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.

Building Generic Inquiries Isn't Scary

Now, when we click the VIEW INQUIRY button, we won’t see any invoices with balances.

Building Generic Inquiries Isn't Scary

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.

Building Generic Inquiries Isn't Scary

Filed Under: Acumatica Reporting Tagged With: Acumatica, Acumatica Blog, Acumatica Generic Inquiry, Acumatica Learning, Acumatica Summit 2017, Acumatica Training

By using this website, you agree to our Terms of Use (click here)

Online Members

 No online members at the moment

Recent Blog Posts

  • EP 37: Jody Lorincz, IT Manager at MHR Brands, sharing his Acumatica Customer Story (Podcast) January 13, 2021
  • EP 36: What Motivates ERP Veteran Nicole Ronchetti To Participate in Acu-Connect (Podcast) January 8, 2021
  • Acumatica and Scanco WMS – A Cautionary Tale for ISVs January 6, 2021
  • EP 35: Ideas for AUGForums.com Live with Ryan Brown (Podcast) December 12, 2020
  • EP 34: Talking Acumatica eCommerce and the Next Normal for Distribution, Wholesale, and Manufacturing with Ted Stenstrom (Podcast) December 11, 2020
Acumatica Learning Resources

Recent Forum Posts

  • RE: Using « start with » unstead of « contrains »

    It's a contains search, not a "starts with" search unfo...

    By Tim Rodman, 35 mins ago

  • RE: Problem when i have more then one Alternate ID

    This article I wrote explains how to work with barcodes...

    By Carl Brooks, 7 hours ago

  • RE: Problem when i have more then one Alternate ID

    The xref is actually its own report table. You'll need ...

    By lesliealt0502, 8 hours ago

  • RE: Construction Module/Purchase Orders - Commitments

    Do you see them in the Commitment tab of the project? I...

    By johnymt, 2 days ago

  • RE: Compressing Images

    That would be cool. Not that I know of personally. Are ...

    By Tim Rodman, 2 days ago

  • RE: Support for custom fonts

    @ckwiat That's what I've experienced with MICR which is...

    By Tim Rodman, 3 days ago

  • RE: Support for custom fonts

    I actually came across this that I will try:

    By Cory, 3 days ago

  • RE: Support for custom fonts

    @timrodman do you know if anything was definitely figur...

    By Cory, 3 days ago

  • RE: Adding bill description on check stub

    @cfritsch Chip great modifications. Just a quick questi...

    By Jswartz, 3 days ago

Recent Tweets

Terms of Use & Disclaimers :: Privacy Policy

Copyright © 2021 · AUG Forums, LLC. All rights reserved. This website is not owned, affiliated with, or endorsed by Acumatica, Inc.