cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Annannas
New Member

Troubles with the "Get items" node and a formatDateTime Conversion after changing the regional settings of the SharePoint list

Dear community, 

I have some troubles with my flow. I tried to use the Get items node with the filter query : Column X eq formatDateTime(addDays(utcNow(),5), 'yyyy-MM-dd'). 

I found out that the node always gave an empty output because my SharePoint list had a German region and thus, it didn't recognise the dates in Column X. I changed the region to the USA. However, I face the problem now, that the entries in column x are still not correctly recognised by the flow. The "Get Items" node still throws an error. The node works as soon as I manually change the date for the rows. I suspect that the dates in column X are still formatted in the wrong date format for the flow. Do you know how to change the dates in column X so that they are recognized by the flow and I don't have to manually change all the dates? 
The list has about 1,000 entries. 

3 REPLIES 3
ekarim2020
Super User
Super User

Hi @Annannas ,

 

(1) The Filter query will require the date expression to be enclosed in single quotes

Snag_aee4cbf.png

(2) The date format for the filter query will be yyyy-MM-dd

Snag_aa9838d.png

 

[CORRECTION] Another example: Created le '2021-11-23'

means a date less than or equal  to 2021-11-23 where Hour=00 Minutes=00  Seconds=00

The filter query will not include records created after 2021-11-23 Hour=00 Minutes=00  Seconds=00.  Therefore an item created at 10:15 AM on 2021-11023 will not be included in the query even though it was created on 2021-11-23.

 

(3) To read more than a 100 items in a Get items action you can adjust the pagination settings:

 

Snag_ab36324.png

 

Snag_ab3799a.png

To read more than 5000 records, see Get more than 5000 items from SharePoint with Power Automate (tachytelic.net)

 

Is Column X a SharePoint field of type Date? 

Snag_af0b3eb.png

Can you post some sample data please?

 

Hope this helps.


Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

Annanas
New Member

Hi @ekarim2020

 

thank you so much for your answer. Column X is a date column. The problem I have is that it appears that the date displayed has been changed correctly in the view (from dd-mm-yyyy to mm-dd-yyyy), but not when the flow matches the data. The flow only matches the data correctly if I have changed the date once and set it back to the original value. Therefore, it leads me to believe that the date was only changed correctly in the interface.



Created ge '2021-11-23' means a date greater or equal  to 2021-11-23 where Hour=00 Minutes=00  Seconds=00

The filter query will not include records created after 2021-11-23 Hour=00 Minutes=00  Seconds=00.  Therefore an item created at 10:15 AM on 2021-11023 will not be included in the query even though it was created on 2021-11-23.


Do you know a workaround for this problem? I didn't know that so far.

[CORRECTION] Apologies, the paragraph should read:

 

Another example: Created le '2021-11-23' will return records with a date less than or equal to 2021-11-23 where Hour=00 (midnight) Minutes=00  Seconds=00.  The filter query will not include records created after 2021-11-23 Hour=00 (midnight) Minutes=00  Seconds=00.  Therefore an item created at 10:15 AM on 2021-11-23 will not be included in the query even though it was created on 2021-11-23.

 

Therefore if you want include all records created on or before '2021-11-23' just add one day and use the less than operator: Created lt '2021-11-24'  (lt = less than).

 

Hope this clarifies the paragraph.

 

Ellis

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (4,233)