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 / Finance forum / How to fetch records b...
Finance forum

How to fetch records between two date fields in X++ query on refereceFieldLookup?

(0) ShareShare
ReportReport
Posted on by badduke 259

Hi All,

I'm facing difficulty while fetching records between two utcdatetime fields by using X++ query on refereceFieldLookup.

I've querying a View which has the ValidFrom and ValidTo fields.

the data source for the view is HcmPositionWorkerAssignment table and the View has ValidtimeStateProperty set to NO.

I'm aware of the below solution, to fetch from ValidtimeStateTable , but in my scenario I've wanted it ValidtimeStateProperty as NO.

 query.validTimeStateDateTimeRange(rangeStart, rangeEnd);

Below expression I've tried  but its not filtering based on the query value .I can see the ranges has been added to the query using query.Tostring() method, but it not shows any records in the refereceFieldLookup , not between the given date range

 utcDateTime             rangeStart, rangeEnd;
 date                    startDate, endDate;
 TimeOfDay               startTime, endTime;
 QueryBuildDataSource    qbdsHcmPositionWorkerAssignment;
 QueryBuildRange         qbrWorker,qbrPosition;
   
 // set range variables
  startDate   = 07\01\2018;       // day\month\year
  endDate     =30\06\2019;       // day\month\year

  startTime   = str2time('12:00:00 am');     //GMT, NOT user time zone - this is how stored in the table
  endTime     = str2time('12:00:00 am');     //GMT, NOT user time zone - this is how stored in the table

  rangeStart = DateTimeUtil::newDateTime(startDate,startTime);
  rangeEnd   = DateTimeUtil::newDateTime(endDate,endTime);

  qbdsHcmPositionWorkerAssignment          = query.addDataSource(tableNum(HcmPositionWorkerAssignmentView));
  qbrPosition = qbdsHcmPositionWorkerAssignment.addRange(fieldNum(HcmPositionWorkerAssignmentView, DataAreaId));

  qbrPosition.value(strFmt('(( %1.%2 >= "%3") && (%1.%4 <= "%5"))',
            qbdsHcmPositionWorkerAssignment.name(),
            fieldStr(HcmPositionWorkerAssignmentView, ValidFrom),datetime2Str(rangeStart),
            fieldStr(HcmPositionWorkerAssignmentView, ValidTo),datetime2Str(rangeEnd)));

//below is the sql querystring generated

SELECT * FROM HcmPositionWorkerAssignmentView(HcmPositionWorkerAssignmentView_1)
WHERE (((( HcmPositionWorkerAssignmentView_1.ValidFrom>= "7/1/2018 12:00:00 am") && (HcmPositionWorkerAssignmentView_1.ValidTo <= "6/30/2019 12:00:00 am")))) 
AND ((Worker = 68719484600)) AND ((Position = 68719480329))
Categories:
Development / Customization / SDK
  • badduke Profile Picture
    badduke 259 on at
    RE: How to fetch records between two date fields in X++ query on refereceFieldLookup?

    Hi

    This statement does work on directly on SQL , I've had to use AND instead of &&

     SELECT * FROM HcmPositionWorkerAssignmentView as HcmPositionWorkerAssignmentView_1
     WHERE (((( HcmPositionWorkerAssignmentView_1.ValidFrom>= '7/1/2018 12:00:00 am') AND (HcmPositionWorkerAssignmentView_1.ValidTo <= '6/30/2019 12:00:00 am'))))
    AND ((Worker = 68719484600)) AND ((Position = 68719480329))

  • Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: How to fetch records between two date fields in X++ query on refereceFieldLookup?

    I would recommend you to use SQL trace or profilel to see SQL query generated HcmPositionWorkerAssignment table has ValidtimeState enabled.

  • Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: How to fetch records between two date fields in X++ query on refereceFieldLookup?

    Are you saying that if you grab SQL statement and execute it in management studio it gives you expected result ?

  • badduke Profile Picture
    badduke 259 on at
    RE: How to fetch records between two date fields in X++ query on refereceFieldLookup?

    Hi Ievgen,

    I've tried the same , but still lookup shows up empty. The Sql query generates correct though.

    Is there any specific rules for initializing range for this kind of scenario (qbrPosition) ?

    I've tried using the DataAreaId, RecId, ValidFrom and ValidTo fields to Initialize the qbrPosition range, but result is still same.

  • Suggested answer
    Community Member Profile Picture
    Community Member on at
    RE: How to fetch records between two date fields in X++ query on refereceFieldLookup?

    Hi Dileep,

    Have a took at this blog (it shows how to use datetime in extended query range): blogs.msdn.microsoft.com/.../passing-utcdatetime-values-in-extended-query-syntax

  • Martin Dráb Profile Picture
    Martin Dráb 231,801 Most Valuable Professional on at
    RE: How to fetch records between two date fields in X++ query on refereceFieldLookup?

    Sorry Dileep, you're right, I didn't realize we're talking about two different fields here.

  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: How to fetch records between two date fields in X++ query on refereceFieldLookup?

    Hi Dileep,

    Try 

    qbrPosition.value(strFmt('(( %1.%2 >= "%3") && (%1.%4 <= "%5"))',
            qbdsHcmPositionWorkerAssignment.name(),
            fieldStr(HcmPositionWorkerAssignmentView, ValidFrom), 
            DateTimeUtil::toStr(rangeStart),
            fieldStr(HcmPositionWorkerAssignmentView, ValidTo), 
            DateTimeUtil::toStr(rangeEnd)));


    instead

  • badduke Profile Picture
    badduke 259 on at
    RE: How to fetch records between two date fields in X++ query on refereceFieldLookup?

    Hi Martin,

    I've tried the mentioned approach , But what i faced I've been only able to apply the queryrange on either on of the fields. 

    qbdsHcmPositionWorkerAssignment.addRange(fieldNum(HcmPositionWorkerAssignmentView, ValidFrom)).value(queryRange(rangeStart, rangeEnd));

    But what my requirement is I need to filter between two date fields ( fromDate and ToDate) . I'm not clear how to implement queryrange on these two fields together.

    like the below X++ select statement 

     while select HcmPositionWorkerAssignmentView
        where HcmPositionWorkerAssignmentView.Worker == 68719484600
          && HcmPositionWorkerAssignmentView.Position == 68719480329
           && HcmPositionWorkerAssignmentView.ValidFrom >= rangeStart
           && HcmPositionWorkerAssignmentView.ValidTo <= rangeEnd
        {
            info(strFmt('%1 - %2', HcmPositionWorkerAssignmentView.ValidFrom,HcmPositionWorkerAssignmentView.ValidTo));
        }
  • Martin Dráb Profile Picture
    Martin Dráb 231,801 Most Valuable Professional on at
    RE: How to fetch records between two date fields in X++ query on refereceFieldLookup?

    What if you simply try

    queryRange(rangeStart, rangeEnd)

    instead of

    strFmt('(( %1.%2 >= "%3") && (%1.%4 <= "%5"))',
        qbdsHcmPositionWorkerAssignment.name(),
        fieldStr(CMCHcmPositionWorkerAssignmentView, ValidFrom),datetime2Str(rangeStart),
        fieldStr(CMCHcmPositionWorkerAssignmentView, ValidTo),datetime2Str(rangeEnd))

    ?

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

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 231,801 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans