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

⚠️ Please note that this chapter describes how the Acumatica ERP Generic Inquiry engine works in 2022 R1 version. The behavior may be different in other versions of the product.

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

First let’s see what time zones we may need to deal with. There are three time zones that you may need to work with:

  1. Database Time Zone – the time zone in which SQL Server that you have Acumatica System connected to returns select Getdate();
  2. User Time Zone – the time zone that is set in the user profile in Acumatica
  3. UTC Time Zone – Coordinated Universal Time Zone

The easiest way to see the current time in these three time zones is to use the following GI functions:

  • NowUTC() returns current datetime in UTC time zone.
  • Now() returns current datetime in database time zone.

💡 Pro tip: if you are going to work with datetime conversions it is very convenient to set the same datetime format everywhere. I personally recommend ISO 8601: yyyy-MM-dd hh:mm:ss

Usually the datetime fields in Acumatica ERP are stored in UTC time in the database and displayed in the user time zone in the UI. So, whenever you retrieve a datetime field in the GI you can expect that it is already converted to the current user time zone. However, if you use a datetime field value in a formula the value will be used as is (without conversion).

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

UTCNow()-Now() gives you current time difference between UTC and your database time zone.

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

==DateAdd([Event.StartDate], 'h', DateDiff( 'h', NowUTC(), Now()))

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?

    EG
    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?

    Thanks
    Matt

    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?

      Thanks!

Leave a Reply

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