Add complex range in AOT queries

Before explaining this, a disclaimer: Microsoft clearly states to not have complex queries in the AOT form because of performance issue.

In the following example, I wanted to write the range (InventTrans.StatusReceipt==Purchased ) || InventTrans.StatusIssue == Deducted) and to achieve that, I got the enum values for these fields and wrote it as((InventTrans.StatusReceipt == 1) || (InventTrans.StatusIssue == 2)) in the value column of the range field. Note that the range can now be applied to any field but make sure the data source is names as it is, i.e. InventTrans instead of InventTrans_1.

Bonus:  to do it in X++:

itemQBR.value(strFmt('(%1.%2 LIKE "%3") || (%4.%5 LIKE "%6") || (%7.%8 LIKE "%9")',

                                                                tableStr(GWCustomerItems),

                                                                fieldStr(GWCustomerItems, ItemId),

                                                                SysQuery::valueLike(FilterItems.text()),

                                                                tableStr(GWCustomerItems),

                                                                fieldStr(GWCustomerItems, ShortCode),

                                                                SysQuery::valueLike(FilterItems.text()),

                                                                tableStr(GWCustomerItems),

                                                                fieldStr(GWCustomerItems, ProductName),

                                                                SysQuery::valueLike(FilterItems.text())));

Notice the use of tableStr and fieldStr methods – these ensure that if the table or field names change then you will see the error at compile time rather than noticing errors after it has been deployed into a live environment J

It’s also useful to know that if you have a field like ItemId in the InventTable that you need to filter on you can pass a string that is longer that the ItemId length – this is something that you cannot do when writing a select statement.