strfmt range in x++

today

what's wrong with this range?

rangeTransDate = strFmt('(("%1.%2" <= "%3" && "%3" == "%5") || ("%1.%4" > "%3"))',tableStr(CustomTable),fieldStr(CustomTable,TransDate), date2str(dateTo,321,2,0,2,0,4),fieldStr(CustomTable,SettlementDate),SysQuery::valueEmptyString());

i'm getting this error:

Query extended range error: Right parenthesis expected next to position 72.

1 Answer

This page of AX 2012 documentation is still relevant (I cannot find a AX365 version). Highlighting the important bit gives:

The rules for creating query range value expressions are:

  • Enclose the whole expression in parentheses.
  • Enclose all subexpressions in parentheses.
  • Use the relational and logical operators available in X++.
  • Only use field names from the range's data source.
  • Use the dataSource.field notation for fields from other data sources in the query.

This means that X++ expects curly brackets around every comparison operator (a.k.a. "subexpression" in the documentation). You are missing some...

Also, use the date2strxpp() function to properly handle all date to string conversions. This function can handle empty date values (dateNull()) by translating these to 1900-01-01. I doubt that putting an empty string (SysQuery::valueEmptyString()) in there will work.

So try this, the commented subexpression levels show the bracket groupings:

// subexpressions lvl 2: 4 4 // subexpressions lvl 1: |1 1 2 2 3 3| // || | | | | || rangeTransDate = strFmt('(("%1.%2" <= "%3") && ("%3" == "%5") || ("%1.%4" > "%3"))', tableStr(CustomTable), fieldStr(CustomTable,TransDate), date2strxpp(dateTo), fieldStr(CustomTable,SettlementDate), date2strxpp(dateNull()));

If you still get a similar error at runtime, add even more brackets to group every subexpression in pairs:

// subexpressions lvl 3: 5 5 // subexpressions lvl 2: |4 4 3 3| // subexpressions lvl 1: ||1 1 2 2| 3 3| // ||| | | || | || rangeTransDate = strFmt('((("%1.%2" <= "%3") && ("%3" == "%5")) || ("%1.%4" > "%3"))', tableStr(CustomTable), fieldStr(CustomTable,TransDate), date2strxpp(dateTo), fieldStr(CustomTable,SettlementDate), date2strxpp(dateNull()));

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobW5pYWaGc4KOrKurnp2perOtzaCcZqGeYsU%3D