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

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Joris dG Profile Picture

Joris dG 5

#2
Alexey Lekanov Profile Picture

Alexey Lekanov 2

#2
Henrik Nordlöf Profile Picture

Henrik Nordlöf 2 User Group Leader

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans