Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Adding a Range on a Da...
Microsoft Dynamics AX (Archived)

Adding a Range on a Date Column on a Form's Data source

(0) ShareShare
ReportReport
Posted on by Muhammad_Ali 347

Hi -

   I have been working on this line of requirement

"If TRANSACTIONORIGIN=Purchase order or Vendor invoice, and PAYMENTDATE<>blank, then select all"

I need to achieve this on my custom form that I have created. I cant figure out how to code this query with special syntax. So far what I have written is pasted below. Note this is init() method on my datasource

    Query                       query;
    QueryBuildDataSource        qbdsTransOrigin, qbdsVendTrans,qbdsVendInvoiceTrans;
    QueryBuildRange             queryBuildRange,queryBuildRange2;
    
    query = new Query();

    super();

    qbdsTransOrigin = query.addDataSource(tableNum(ProjItemTrans), tablestr(ProjItemTrans));
    qbdsVendTrans   = query.addDataSource(tableNum(VendTrans), tablestr(VendTrans));
    qbdsVendInvoiceTrans = query.addDataSource(tableNum(VendInvoiceTrans), tablestr(VendInvoiceTrans));

    qbdsVendInvoiceTrans.relations(true);
    qbdsVendTrans.relations(true);

    queryBuildRange =  qbdsTransOrigin.addRange(fieldNum(ProjItemTrans, TransactionOrigin));
    queryBuildRange2 = qbdsVendTrans.addRange(fieldNum(VendTrans, LastSettleDate));

    queryBuildRange.value(strFmt('((%1 == %2) && (%1 == %3)))',
    fieldStr(ProjItemTrans, TransactionOrigin),
    any2int(ProjOrigin::PurchaseOrder),
    any2int(ProjOrigin::VendorInvoice)));
    
    
    I need to satisfy this requirement "If TRANSACTIONORIGIN=Purchase order or Vendor invoice, and PAYMENTDATE<>blank, then select all"
    How can I do this with code ? I cant seem to understand how to code the range for Date variable (and PAYMENTDATE<>blank)?
    If someone can help me write this line in code that will be really appreciated.

Please ask me if you have any questions.

Regards-

*This post is locked for comments

  • Muhammad_Ali Profile Picture
    Muhammad_Ali 347 on at
    RE: Adding a Range on a Date Column on a Form's Data source

    Thanks Chaitanya this is working fine now !

    Much Appreciated !! Thanks

  • Verified answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at
    RE: Adding a Range on a Date Column on a Form's Data source

    Hi,

    If its on form, no need to add any datasource again to the query instead we need to access the form query and provide required ranges either in the init method or in the executeQuery method of datasource.

    1. In init method of vendTrans, after super add below code:
    QueryBuildDataSource qbdsVendTrans;
    QueryBuildDataRange queryBuildRange2 ;

    super();

    qbdsVendTrans = this.query().dataSourceTable(tableNum(VendTrans));
    queryBuildRange2 = qbdsVendTrans.addRange(fieldNum(VendTrans, LastSettleDate));
    queryBuildRange2.value(strFmt('(LastSettleDate > %1)', Date2StrXpp(01\01\1990)));

    2. In init method of datasource ProjItemTrans, after super add below code:
    QueryBuildDataSource qbdsTransOrigin;

    super();

    qbdsTransOrigin = this.query().dataSourceTable(tableNumProjItemTrans));
    queryBuildRange = qbdsTransOrigin.addRange(fieldNum(ProjItemTrans, TransactionOrigin));
    queryBuildRange = qbdsTransOrigin.addRange(fieldNum(ProjItemTrans, TransactionOrigin));
    queryBuildRange.value(strFmt('((TransactionOrigin == %1) || (TransactionOrigin == %2))',
    any2int(ProjOrigin::PurchaseOrder),
    any2int(ProjOrigin::VendorInvoice)));

  • Muhammad_Ali Profile Picture
    Muhammad_Ali 347 on at
    RE: Adding a Range on a Date Column on a Form's Data source

    Thanks Chaitanya much appreciated

  • Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at
    RE: Adding a Range on a Date Column on a Form's Data source

    Hi,

    Let me check, give me few mins.

  • Muhammad_Ali Profile Picture
    Muhammad_Ali 347 on at
    RE: Adding a Range on a Date Column on a Form's Data source

    Do you think we can use something like this ?

     ds.addRange(fieldNum(CustTrans, Closed)).value(queryValue(dateNull()); ?

    Can we try using queryValue(dateNull()) ?

  • Muhammad_Ali Profile Picture
    Muhammad_Ali 347 on at
    RE: Adding a Range on a Date Column on a Form's Data source

    Hi Chaitanya -

     No result yet - Hmm I'm not sure what we are doing wrong here. Just for your info plz look at the datasources for my form below.

    5736.eee.JPG

    Maybe this will help you get the better idea. And this init method that we are working on is in ProjItemTrans.

    Thanks

  • Suggested answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at
    RE: Adding a Range on a Date Column on a Form's Data source

    Hi,

    Yes, you are correct. I missed it. Here is the update code.

    Query                       query;
    QueryBuildDataSource        qbdsTransOrigin, qbdsVendTrans,qbdsVendInvoiceTrans, qbdsVendInvoiceJournal;
    QueryBuildRange             queryBuildRange,queryBuildRange2;
    
    query = new Query();
    
    super();
    
    qbdsTransOrigin = query.addDataSource(tableNum(ProjItemTrans));
    qbdsVendInvoiceTrans = qbdsTransOrigin.addDataSource(tableNum(VendInvoiceTrans));
    qbdsVendInvoiceTrans.joinMode(JoinMode::InnerJoin);
    qbdsVendInvoiceTrans.relations(true);
    
    qbdsVendInvoiceJournal = qbdsVendInvoiceTrans.adddataSource(tableNum(VendInvoiceJour));
    qbdsVendInvoiceJournal.joinMode(JoinMode::InnerJoin);
    qbdsVendInvoiceJournal.relations(true);
    
    qbdsVendTrans   = qbdsVendInvoiceJournal.addDataSource(tableNum(VendTrans));
    qbdsVendTrans.joinMode(JoinMode::InnerJoin);
    qbdsVendTrans.relations(true);

    queryBuildRange =  qbdsTransOrigin.addRange(fieldNum(ProjItemTrans, TransactionOrigin));

    queryBuildRange.value(strFmt('((TransactionOrigin == %1) || (TransactionOrigin == %2))', 
    any2int(ProjOrigin::PurchaseOrder),
    any2int(ProjOrigin::VendorInvoice)));
    
    queryBuildRange2 = qbdsVendTrans.addRange(fieldNum(VendTrans, LastSettleDate));
    queryBuildRange2.value(strFmt('(LastSettleDate > %1)', Date2StrXpp(01\01\1990)));


  • Muhammad_Ali Profile Picture
    Muhammad_Ali 347 on at
    RE: Adding a Range on a Date Column on a Form's Data source

    Hi Chaitanya -

      Still no result - I see you have connected the tables together with inner joins - I think we are missing one more table in between "VendInvoiceJour".  VendTrans has a relation with VendInvoiceJour and then VendInvoiceJour  has a relation with VendInvoiceTrans.  VendTrans  doesnt have any direct relation with  VendInvoiceTrans, I have seen that in AOT.  Maybe we need to add couple of lines of code to include VendInvoiceJour in a relation as well in our method?

    What do you suggest ?

    Thank you for your help

  • Suggested answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at
    RE: Adding a Range on a Date Column on a Form's Data source

    Hi,

    Also I changed your code, please try with below code and post me if anything required.

        Query                       query;
    QueryBuildDataSource        qbdsTransOrigin, qbdsVendTrans,qbdsVendInvoiceTrans;
    QueryBuildRange             queryBuildRange,queryBuildRange2;
    
    query = new Query();
    
    super();

    qbdsTransOrigin = query.addDataSource(tableNum(ProjItemTrans));
    qbdsVendInvoiceTrans = qbdsTransOrigin.addDataSource(tableNum(VendInvoiceTrans));
    qbdsVendInvoiceTrans.joinMode(JoinMode::InnerJoin);
    qbdsVendInvoiceTrans.relations(true);
    
    qbdsVendTrans   = qbdsVendInvoiceTrans.addDataSource(tableNum(VendTrans));
    qbdsVendTrans.joinMode(JoinMode::InnerJoin);
    qbdsVendTrans.relations(true);

    queryBuildRange =  qbdsTransOrigin.addRange(fieldNum(ProjItemTrans, TransactionOrigin));

    queryBuildRange.value(strFmt('((TransactionOrigin == %1) || (TransactionOrigin == %2))', 
    any2int(ProjOrigin::PurchaseOrder),
    any2int(ProjOrigin::VendorInvoice)));
    
    queryBuildRange2 = qbdsVendTrans.addRange(fieldNum(VendTrans, LastSettleDate));
    queryBuildRange2.value(strFmt('(LastSettleDate > %1)', Date2StrXpp(01\01\1990)));

    Output resultant query:

    NAME: qbdsTransOrigin
    VALUE:
    SELECT * FROM ProjItemTrans(ProjItemTrans_1) WHERE ((((TransactionOrigin == 13) || (TransactionOrigin == 65)))) JOIN * FROM VendInvoiceTrans(VendInvoiceTrans_1) ON ProjItemTrans.VendInvoiceTransRecId = VendInvoiceTrans.RecId JOIN * FROM VendTrans(VendTrans_1) ON (((LastSettleDate > 01\01\1990)))
    TYPE: QueryBuildDataSource

  • Suggested answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at
    RE: Adding a Range on a Date Column on a Form's Data source

    Hi,

    Please use the following statement for Last Settle Date:

    queryBuildRange2.value(strFmt('(LastSettleDate > %1)', Date2StrXpp(01\01\1990)));

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,409 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans