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.

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.

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.

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.

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.