Answered

Get Items doesn't retrieve anything based on the filter query

(0) Share

Report

Posted on by DGI_IT

I have a flow to send email reminder based on the entries from the Microsoft Lists. It will get today's date and compare with the date column from Microsoft Lists. It will send an email reminder if the condition is met.

I had manually checked that there are two entries that the date are today but the Get Items didn't retrieve them.

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (1)

v-bofeng-msft on at

Like (0)

Report

Hi @DGI_IT ,

Could you please tell me:

What is Nomination ? Is it a Date and Time column?

What is your SP site's time zone?

Please try:

Nomination ge '@{utcNow('yyyy-MM-dd')}T00:00:00Z' and Nomination le '@{utcNow('yyyy-MM-dd')}T23:59:59Z'

In addition, if the time zone of this SP site is not UTC0, then you will also need to adjust the time range of the filter according to the time zone of this SP site.

Best Regards,

Bof

Was this reply helpful? Yes No
DGI_IT 12 on at

Like (0)

Report

Hi Bof,

It is Nomination_Date and it is date and time format on Microsoft Lists.
My SharePoint site's default timezone is UTC+0.
I use the current time and convert time zone function then change the format to be d/M/yyyy.

Was this reply helpful? Yes No
v-bofeng-msft on at

Like (0)

Report

Hi @DGI_IT ,

Please set the filter query to:

Nomination_Date ge '@{formatDateTime(body('Conver_time_zone'),'yyyy-MM-dd')}T00:00:00Z' and Nomination_Date le '@{formatDateTime(body('Conver_time_zone'),'yyyy-MM-dd')}T23:59:59Z'

Best Regards,

Bof

Was this reply helpful? Yes No
DGI_IT 12 on at

Like (0)

Report

Hi Bof,

Thanks for your prompt reply. The Get Items is not returning empty entries now but it retrieves tomorrow's entries. The image below is the email reminder from the flow and it is retrieving tomorrow (13/9/2022) entries. However, the filter query seems correct.

Was this reply helpful? Yes No
v-bofeng-msft on at

Like (0)

Report

Hi @DGI_IT ,

Please check the SharePoint site's Time Zone.

Best Regards,

Bof

Was this reply helpful? Yes No
DGI_IT 12 on at

Like (0)

Report

Hi Bof,

I have changed the time zone to UTC+8 and update the source time zone in convert time zone function. But the Get Items still retrieve tomorrow's entries.

Was this reply helpful? Yes No
v-bofeng-msft on at

Like (0)

Report

Hi @DGI_IT ,

First, there is no need to change the time zone to UTC+8 , it's just my SP site's setting. If you can change the time zone, please set it to UTC+0.

Second, after you changed the time zone please check the SP list again , cause the date time value will be changed with the time zone you selected.

Best Regards,

Bof

Was this reply helpful? Yes No
DGI_IT 12 on at

Like (0)

Report

Hi Bof,

I try to change back the time zone to UTC+0 and the date time of Nomination_Date does change as well. Then I changed the time zone to UTC+8 again, removed all the entries in the Microsoft Lists and reimported all the data from an excel file. So now everything should be in UTC+8.

But when I run the flow again, it still retrieves data on 13/9/2022 instead of today (12/9/2022).

Was this reply helpful? Yes No
Verified answer

v-bofeng-msft on at

Like (0)

Report

Hi @DGI_IT ,

Please try:

Nomination_Date ge '@{formatDateTime(addDays(body('Conver_time_zone'),-1),'yyyy-MM-dd')}T16:00:00Z' and Nomination_Date le '@{formatDateTime(body('Conver_time_zone'),'yyyy-MM-dd')}T15:59:59Z'

Best Regards,

Bof

Was this reply helpful? Yes No
DGI_IT 12 on at

Like (0)

Report

Hi Bof,

Thanks for your assistance! It worked for me.

I found out that the date time imported from excel to Microsoft Lists will be stored as UTC+0. Hence, the date time in Microsoft Lists are actually 8 hours slower than the date time in my Excel file.

Source article: Power Automate：なぜ9時間先行する？ExcelデータをSharePointにインポートしたらタイムゾーン差異の内容と対処法 (artisan.jp.net)

Was this reply helpful? Yes No