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
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()))
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.