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

(0) Share

Report

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

I have the same question (0)

All responses (9)

Answers (0)

Martin Dráb 237,990 Most Valuable Professional on at

Like (0)

Report
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))

?

Was this reply helpful? Yes No
badduke 259 on at

Like (0)

Report
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)); }

Was this reply helpful? Yes No
Suggested answer

Mea_ 60,284 on at

Like (0)

Report
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

Was this reply helpful? Yes No
Martin Dráb 237,990 Most Valuable Professional on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
badduke 259 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

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

Was this reply helpful? Yes No
badduke 259 on at

Like (0)

Report

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))

Was this reply helpful? Yes No