web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / AddRange with and and ...
Microsoft Dynamics AX (Archived)

AddRange with and and or conditions

(1) ShareShare
ReportReport
Posted on by Mauro Vecchiato 175

I have a report that use this range 

this.query().dataSourceTable(tablenum(purchLine)).addRange(fieldnum(purchLine, deliveryDate)).value(sysQuery::range(FromDate.value(), toDate.value()));

I want to add another condition on the confirmeddlv field but it has to be an or condition.

How can i do this ?

Thank you

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    nunomaia Profile Picture
    nunomaia 25 Moderator on at
    RE: AddRange with and and or conditions

    qbr.value(strFmt('((%1 == "Value1")

    (%2 == "Value2"))',

    fieldStr(purchLine, field1),

    fieldStr(purchLine, field2)));

  • Mauro Vecchiato Profile Picture
    Mauro Vecchiato 175 on at
    RE: AddRange with and and or conditions

    Thank you, but can you explain me how assign it to the current query

  • Suggested answer
    Heinz Schweda Profile Picture
    Heinz Schweda 1,367 on at
    RE: AddRange with and and or conditions

    Hello, another example with date-values:

       Query query = new Query();    

       QueryBuildDataSource qbds_purchLine = Query.addDataSource(tableNum(PurchLine));

       sysQuery::findOrCreateRange(qbds_purchLine, fieldNum(PurchLine, RecId)).value(

                                                                       strfmt('((%1 = %2) || (%3 = %4))',

                                                                                       fieldstr(PurchLine, ConfirmedDlv),

                                                                                       Date2StrXpp(systemDateGet()),

                                                                                       fieldstr(PurchLine, ConfirmedDlv),

                                                                                       Date2StrXpp(dateNull())));

  • Mauro Vecchiato Profile Picture
    Mauro Vecchiato 175 on at
    RE: AddRange with and and or conditions

    Thank you. Now it's running. Bye.

  • Mauro Vecchiato Profile Picture
    Mauro Vecchiato 175 on at
    RE: AddRange with and and or conditions

    it seems to ignore the filter.

    I have copied the text in the fetch() method and after sysQuery::findOrCreateRange.....

    i have inserted this :

    ret = super();

    return ret;

    The report returns me all the purch lines without filter on ConfirmedDlv.

  • Martin Dráb Profile Picture
    Martin Dráb 236,972 Most Valuable Professional on at
    RE: AddRange with and and or conditions

    It doesn't require the extended query syntax. Keep it clear and simple:

    Query q = new Query();
QueryBuildDataSource ds = q.addDataSource(tableNum(PurchLine));
    
ds.addRange(fieldNum(PurchLine, DeliveryDate)).value(queryRange(1\1\2014, 5\1\2014));
ds.addRange(fieldNum(PurchLine, DeliveryDate)).value(queryRange(31\1\2014, 5\2\2014));
    
info(ds.toString());

    Result:

    SELECT * FROM PurchLine(PurchLine_1)
WHERE ((DeliveryDate>={ts '2014-01-01 00:00:00.000'}
    AND DeliveryDate<={ts '2014-01-05 00:00:00.000'})
OR (DeliveryDate>={ts '2014-01-31 00:00:00.000'}
    AND DeliveryDate<={ts '2014-02-05 00:00:00.000'}))
  • Mauro Vecchiato Profile Picture
    Mauro Vecchiato 175 on at
    RE: AddRange with and and or conditions

    I see the the correct ds in the info message but the report query does not see the range.

    It' s behave only if i use this syntax : this.query().dataSourceTable(tablenum(purchLine)).addRange(fieldnum(purchLine, deliveryDate)).value(sysQuery::range(FromDate.value(), toDate.value()));

    but in this case i don't know as add an or condition to the filter

  • Martin Dráb Profile Picture
    Martin Dráb 236,972 Most Valuable Professional on at
    RE: AddRange with and and or conditions

    Sorry for confusing you, I thought it would be clear that my code was a stand-alone example and you would be able to use the same approach in your code.

    This is closer to your actual implementation:

    QueryBuildDataSource ds = this.query().dataSourceTable(tablenum(purchLine));

ds.addRange(fieldNum(PurchLine, DeliveryDate)).value(queryRange(fromDate.value(), toDate.value()));
ds.addRange(fieldNum(PurchLine, DeliveryDate)).value(queryRange(anotherDate, yetAnotherDate))
  • Mauro Vecchiato Profile Picture
    Mauro Vecchiato 175 on at
    RE: AddRange with and and or conditions

    Ok I have this situation :

    my deliveryDate is "2014-08-08" and my ConfirmedDlv is "2014-08-10".

    My report has to extract all the purchlines with ConfirmedDlv ( or  deliveryDate if the ConfirmedDlv is null ) between fromdate and todate.

    if i use

    this.query().dataSourceTable(tablenum(purchLine)).addRange(fieldnum(purchLine, deliveryDate)).value(queryRange(FromDate.value(), toDate.value()));

       this.query().dataSourceTable(tablenum(purchLine)).addRange(fieldnum(purchLine, ConfirmedDlv)).value(queryRange(FromDate.value(), toDate.value()));

    and fromdate = "2014-08-09" and todate = "2014-08-11" the report is empty.

    In the original report the query was only

    this.query().dataSourceTable(tablenum(purchLine)).addRange(fieldnum(purchLine, deliveryDate)).value(queryRange(FromDate.value(), toDate.value()));

    I have to add a test on ConfirmedDlv, so my sql query has to be :

    select * from purchline where (ConfirmedDlv >= Fromdate and ConfirmedDlv <= Todate) or ( deliveryDate >= Fromdate and deliveryDate <= Todate and  ConfirmedDlv = '1900-01-01').

    If i use the syntax this.query()...... it seems to keep the filter. If i use

    QueryBuildDataSource     qbds;

       queryBuildRange          qbr;

       query                    q1;

      q1 = new Query();

      qbds = q1.addDataSource(tableNum(PurchLine));

    bds.addRange(fieldNum(PurchLine, DeliveryDate)).value(queryRange(9\8\2014, 10\8\2014));

    qbds.addRange(fieldNum(PurchLine,CONFIRMEDDLV)).value(queryRange(9\8\2014, 10\8\2014));

    info(qbds.toString());

    the info give my the right filter but the report extracts all the purchlines in the table so that it seems the filter is ignored. The datasource in the report is PurchTable in join with Purchline.

    Thank you for your answer.

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 236,972 Most Valuable Professional on at
    RE: AddRange with and and or conditions

    Aha, thank you for providing these details., I finally see what you're trying to achieve. Try to tell us more from the beginning next time.

    If you need OR condition between different fields, you can't use the strategy I showed. It works only for ranges of the same field.

    If you actually run your code:

    QueryBuildDataSource     qbds;
queryBuildRange          qbr;
query                    q1;

q1 = new Query();
qbds = q1.addDataSource(tableNum(PurchLine));
qbds.addRange(fieldNum(PurchLine, DeliveryDate)).value(queryRange(9\8\2014, 10\8\2014));
qbds.addRange(fieldNum(PurchLine,CONFIRMEDDLV)).value(queryRange(9\8\2014, 10\8\2014));
    
info(qbds.toString());

     you'll see that you don't get anything corresponding to your requirement:

    select * from purchline
where (ConfirmedDlv >= Fromdate and ConfirmedDlv <= Todate)
or ( deliveryDate >= Fromdate and deliveryDate <= Todate and  ConfirmedDlv = '1900-01-01').

    It in fact returns this:

    SELECT * FROM PurchLine(PurchLine_1)
    WHERE ((DeliveryDate>={ts '2014-08-09 00:00:00.000'}
    AND DeliveryDate<={ts '2014-08-10 00:00:00.000'}))
    AND ((ConfirmedDlv>={ts '2014-08-09 00:00:00.000'}
    AND ConfirmedDlv<={ts '2014-08-10 00:00:00.000'}))

     Although you said that "the info gives you the right filter", it's clearly not the same. There is no OR operator or condition for empty ConfirmedDlv.

    You'll really have to resort to the extended query syntax to build this composite condition. Like this:

    date fromdate = 09\08\2014;
date todate = 11\08\2014;
str rangeStr;

Query q = new Query();
QueryBuildDataSource qbds = q.addDataSource(tableNum(PurchLine));

rangeStr = strFmt('(((%1 >= %3) && (%1 <= %4)) || ((%2 >= %3) && (%2 <= %4) && (%1 = %5)))',
    fieldStr(PurchLine, ConfirmedDlv),
    fieldStr(PurchLine, DeliveryDate),
    date2strXpp(fromdate),
    date2strXpp(toDate),
    date2strXpp(dateNull()));

qbds.addRange(fieldNum(PurchLine, ConfirmedDlv)).value(rangeStr);
    
info(qbds.toString());

    This is the output:

    SELECT * FROM PurchLine(PurchLine_1)
    WHERE (((((ConfirmedDlv >= 09\08\2000) && (ConfirmedDlv <= 11\08\2014))
    || ((DeliveryDate >= 09\08\2000) && (DeliveryDate <= 11\08\2014) && (ConfirmedDlv = 01\01\1900)))))

    By the way, please use the button called "Insert code using Prettify" to insert code (you have to switch to rich formatting), it's then much easier to ready and copy.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Pallavi Phade – Community Spotlight

We are honored to recognize Pallavi Phade as our Community Spotlight honoree for…

Congratulations to the October Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Tocauer Profile Picture

Martin Tocauer 4

#2
Alexey Lekanov Profile Picture

Alexey Lekanov 3

#3
Willem van Duren Profile Picture

Willem van Duren 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans