web
You’re offline. This is a read only version of the page.
close
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 353

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

I have the same question (0)
  • Suggested answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at

    Hi,

    Please use following expressions:

    Transaction Origin: 

    queryBuildRange.value(strFmt('((TransactionOrigin == %1) || (TransactionOrigin == %2))', 
    any2int(ProjOrigin::PurchaseOrder),
    any2int(ProjOrigin::VendorInvoice)));

    LastSettleDate:

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


  • Muhammad_Ali Profile Picture
    Muhammad_Ali 353 on at

    Hi -

     Thank you for the reply - Can you also help me with the LastSettleDate query ? how do I filter that ?

    Regards

  • Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at

    Hi,

    Yes. I updated it, I missed it last time, please check and let me know.

  • Muhammad_Ali Profile Picture
    Muhammad_Ali 353 on at

    I tried your code but unfortunately the query is still getting those records with last Settle Date field as empty. I need only the records which have TransactionOrigin as Purchase order or Vendor invoice and the Last settle date as not empty (only the records with last settle date present). ?

    Thanks Chaitanya

  • Suggested answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at

    Hi,

    Please use the following statement for Last Settle Date:

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

  • Suggested answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at

    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

  • Muhammad_Ali Profile Picture
    Muhammad_Ali 353 on at

    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

    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 353 on at

    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

  • Muhammad_Ali Profile Picture
    Muhammad_Ali 353 on at

    Do you think we can use something like this ?

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

    Can we try using queryValue(dateNull()) ?

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…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans