Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics 365 | Integration, Dataverse... / AOT query ranges
Microsoft Dynamics 365 | Integration, Dataverse...
Unanswered

AOT query ranges

(0) ShareShare
ReportReport
Posted on by CU20011831-0
Hello all, I need to filter records from the InventBatch table based on three fields: ItemId, expDate, and pdsVendExpirydate. The goal is to retrieve records under the following conditions:

1. If both dates are empty, the record should be selected based on ItemId.
2. If either date is greater than the current date (todate), the record should be selected.

I have implemented this code but it's not working as required:

Query           query = new Query();
QueryBuildDataSource  qbds;
SysTableLookup      sysTableLookUp;

if (TestTable.ItemId)
{
InventBatchExpDate currentDate = DateTimeUtil::date(DateTimeUtil::getSystemDateTime()); // to select current date
date nullDateCheck = mkDate(1, 1, 1900); //will use to compare null dates


qbds = query.addDataSource(tableNum(InventBatch));
qbds.addRange(fieldNum(InventBatch,ItemId)).value(TestTable.ItemId);
qbds.addRange(fieldNum(InventBatch, expDate)).value(strFmt('((%2 > %1 ) || ( %2 == %3 ))', currentDate, fieldStr(InventBatch, expDate),nullDateCheck));
qbds.addRange(fieldNum(InventBatch, PdsVendExpiryDate)).value(strFmt('((%2 > %1) || ( %2 == %3))',  currentDate, fieldStr(InventBatch, PdsVendExpiryDate), nullDateCheck));

sysTableLookup = SysTableLookup::newParameters(tableNum(InventBatch), this);
sysTableLookup.addLookupField(fieldNum(InventBatch, InventBatchId));

sysTableLookup.parmQuery(query);
sysTableLookup.performFormLookup();
}


Major challenge:
1. Null date is passing to qbds as empty (expDate == ) but it should be as (expDate == 01/01/1990 ).
2. How to deal using Query Build AddRange If one date is greater than today's date and other date is not null but less than today's date?


#D365FO | #X++ | #Dynamics365 | #D365Finance | #AXDevelopment | #ERPDevelopment | #MicrosoftDynamics | #AX2012 | #D365Customization | #F&ODevelopment | #D365 | AOTQuery
Categories:
Dynamics 365 general
  • Martin Dráb Profile Picture
    Martin Dráb 230,476 Most Valuable Professional on at
    AOT query ranges
     
    Yes, code that I posted does behaves the same as your code. I merely simplified your code, to make it easier to understand and talk about. 
     
    You focused so much on dateNull() that you've completely missed the bug I pointed out to and the fix I proposed: 
     
    An obvious problem is that your date literals aren't formatted correctly. Use date2StrXpp() to convert them to strings.
  • CU20011831-0 Profile Picture
    CU20011831-0 on at
    AOT query ranges

    Hi @Martin,

    Thanks for your response. I used datenull() and it is still setting up an empty value in the qbds for the date range. I observed the qbds query during the debugging process, and each time, whether I used MkDate(01/01/1900) or datenull(), the result was the same (expDate ==).

    For the second part:
    Let's assume our current query works fine, where expDate = 01/01/2035 and pdsVendExpiryDate = 01/01/2020. In this case, the record will not be selected 

  • Martin Dráb Profile Picture
    Martin Dráb 230,476 Most Valuable Professional on at
    AOT query ranges
    ​Let's focus on the query and remove the unrelated code. Let me also utilize dateNull() instead of your more complex solution. 
    Query query = new Query();
QueryBuildDataSource qbds = query.addDataSource(tableNum(InventBatch));
qbds.addRange(fieldNum(InventBatch, ItemId)).value("TestItemId");
qbds.addRange(fieldNum(InventBatch, ExpDate))
    .value(strFmt('((%2 > %1 ) || (%2 == %3 ))', currentDate, fieldStr(InventBatch, ExpDate), dateNull()));
qbds.addRange(fieldNum(InventBatch, PdsVendExpiryDate))
    .value(strFmt('((%2 > %1) || (%2 == %3))',  currentDate, fieldStr(InventBatch, PdsVendExpiryDate), dateNull()));
    An obvious problem is that your date literals aren't formatted correctly. Use date2StrXpp() to convert them to strings.
     
    When debugging a query, a good idea is checking what query you've generated by your X++ code. You may find that it's not what you've intended. You can see it in the debugger or use code like qbds.toString().
     
    By the way, questions about Dynamics AX and D365FO belong to Finance | Project Operations, Human Resources, AX, GP, SL forum.
    ​

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

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,784 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,476 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Get batch job history log from D365 FO to Power Automate

Product updates

Dynamics 365 release plans