Generic Inquiry Tips & Tricks

Hi everyone! As you may know, a lot of things (such as OData, Push Notifications, Dashboards) in Acumatica are based on Generic Inquiries. In this post I’d like to share some tips and trick related to building GIs I found useful. I hope you’ll find them useful too.

Join conditions containing multiple tables.

Probably, the most unintuitive trick that you can do is adding join conditions based on fields that are not in the list of Parent Fields or Child Fields. Actually, you can use every field from every table to build join conditions in GI just like you do in SQL. You are not restricted by just Parent fields or just Child fields. To do that you define full name of the field instead of short one. E.g. in the following example, I have CABatch fields in the Parent field box for CABatchDetail.

Generic Inquiry with Join Conditions Based on Fields from Multiple Tables
Generic Inquiry with Join Conditions Based on Fields from Multiple Tables

Although Acumatica shows a warning indicating that it cannot find the field, the condition will still work fine. You can even add Is Null conditions for child fields. To do that you put child field into the Parent field box.

Multi Locale Fields

Another thing that you can do with Generic Inquiries is to get raw values of Multi Locale fields. Let’s say you want to get Inventory Item description in all languages at once when you read them via OData.

Multi Locale Description of Stock Items
Multi Locale Description of Stock Items

However, regular Generic Inquiry will give you only description of the Stock item in current Locale. To get the raw value of the field that includes data for all locales, you put =[Table.Field] formula into the Data Field box on the Results Grid instead of just a field name.

Formula that Gets Raw Value of the Field
Formula that Gets Raw Value of the Field

The GI will show something like that in the results grid for the modified field:

<v type="DescrEN">Lego 500 piece set</v><v type="DescrFR">Lego 500 pièces</v>

As you see, it contains values for both locales in xml format.

Date Time Conversion

Sometimes you may need to convert dates and times from one time zone to another in a Generic Inquiry.

If you need to show some DateTime field that is stored in UTC in the database converted to current Timezone in a GI, you can use DateTimeField+ NowUTC() – Now() function to convert dates.

NowUTC() returns current datetime in UTC timezone.

Now() returns current datetime in current user’s timezone.

Thus, UTCNow()-Now() gives you current time difference between UTC and your local timezone.

Please note that you should actually use DateTime methods to properly work with datetime fields, e.g.

=DateAdd([SOOrder.CreatedDateTime],'d',-Hour( NowUTC())+Hour( Now()))
Conversion of Database Time to Current Time Zone
Conversion of Database Time to Current Time Zone

Relations Explorer Form

Acumatica has an out of the box Parent Child relations explorer, but it looks like not everyone knows about that.

Merged DB Structure Form (SM402000)
Merged DB Structure Form (SM402000)

To access the form you use ScreenId=SM402000 parameter in the URL as the screen is not in the sitemap by default. The screen shows all incoming and outgoing Parent-Child relations for any DAC in the system. It also shows which fields are foreign keys for that tables and what the behavior of the system is when you try to delete a record. It may be either Cascade, which means that deletion of record will lead to cascade deletion of child records or Restrict, which means that it is not allowed to delete a Parent record until is has some children.

The form shows only those links that are declared with PXParent or PXForeignReference attributes. Thus, you will not find links declared in selector attributes there.

5 Replies to “Generic Inquiry Tips & Tricks”

  1. Hi,

    Is there a way to create a join with the MAX function?

    Basically I am trying to convert currencies and need to pull the most recent CurrencyRate where the CurrencyRate.CuryEffDate <= CurrencyInfo.CuryEffDate, however I only want to get the MAX of this result?

    CurrencyInfo.CuryEffDate = 08/04/2021
    CurrencyRate.CuryEffDate results that are <= 08/04/2021 may be 07/04/2021, 06/04/2021, 05/04/2021 etc.

    How can I get the join to display just the CurrencyRate results for 07/04/2021?


    1. Hello Matthew,
      There is no way to do it purely in GI.
      You can create a custom DAC with that logic and use it in the GI.
      See GLHistoryByPeriodCurrent or Similar DACs for an example

  2. Thanks this is very helpful. I am trying to modify the Stock Items GI to show the Global Alternate ID and have the conditions set to something like:

    ((AlternateType Equals Global) Or (AlternateType IsNull)) but it is then not returning those that have an AlternateType = Vendor as an example as it is not Global and is not null. There is only one Global Alternate ID or no Global Alternate ID.

    In SQL I can do a LEFT JOIN from the Inventory table to the XRef table and include the filter condition on the join to retrieve all records where the Alternate ID is Global and still get the nulls.

    Something like this:

    FROM Inventory LEFT JOIN XRef ON Inventory.InventoryID = XRef.InventoryID AND XRef.AlternateType = ‘GLBL’

    Is the above possible to accomplish in a GI?

    1. Kevin,

      I am running into the same issue that you are describing. Did you find a solution that may be shared?


Leave a Reply

Your email address will not be published. Required fields are marked *