Modify query string on a form to add filter based on other fields

Question

I have an enquiry form which works from a view with a custom query. The form has filters, which I use in the executeQuery method of the view on the form to add ranges on various fields.

A new requirement is to filter based on two fields in the query. Example: The PurchLine table is one of the tables in the query.

A new range is needed :

if PurchLine.ItemId != “” then 
    filter by PurchLine.PurchStatus == None 

but, if the Item has a SPECIFIC value,

then filter by PurchStatus == Received.  

(Ok, this is just an example!).

I am unable to modify my query to add a range on the PurchStatus based on the Item field.

I know exactly how the string value of the query must look, but how can I modify the query string?

The current query string looks like this (if I breakpoint on super in executeQuery):

SELECT FIRSTFAST * FROM OpenPOLinesView(OpenPOLinesView) WHERE ((CreatedDateTime<='2016-11-30T23:59:59')) AND ((VendAccount = N'S000001048'))

I want to add this at the end:

AND (((ItemId = N'') AND (PurchStatus = 0)) OR ((ItemId = N'XXX123') AND (PurchStatus = 2)))

How can I modify the query string in code?

Answer 1

You can use query expression for this, e.g.

queryBuildRange.value(strFmt('((%1 == %2) || ((%1 == %3) && (%4 == "%5")))',
                      fieldStr(InventTable, ItemType),
                      any2int(ItemType::Service),
                      any2int(ItemType::Item),
                      fieldStr(InventTable, ProjCategoryId),
                      queryValue("Spares")));

Please refer to this link Using Expressions in Query Ranges [AX 2012] for details.