cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Get Items Filter Query

I'm new to Flow and am having difficulty with the ''Get Items'' Filter Query.  I have a SharePoint project list and would like an email reminder sent to the project owner 14 days after a project was last modified where the project status does not equal 'Complete'.  I can get the workflow to run successfully but no records are returned and therefore no email is sent (I've checked and there are list items that meet the criteria of being modified 14 days prior to the current date).  I left the project status out of the query when I tested as I'm not sure how check if the project status does not equal 'Complete'?  I can only see the option 'eq' for equal to?

I want to filter for items modified 14 days prior to the current date, with the Project Status not equal 'Complete':

ProjectStatus eq 'Complete' and Modified eq 'addDays(utcNow('yyyy-MM-ddTHH:mm:ssZ'),-14)'

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Get Items Filter Query

Hi @Lindy007 , ok so to handle the day we need to isolate a date range starting from 00:00 to 23:59:59 on the day.

 

To do that, I would approach it in this way:

  1. Initiliase a variable to hold just the date using the following expression:
    formatDateTime(addDays(utcNow(),-14),'yyyy-MM-dd')
  2. Use that date within the filter query and append the times:
    ProjectStatus ne 'Complete' and (Modified ge '<DATE VARIABLE>T00:00:00' and Modified le '<DATE VARIABLE>T23:59:59')

It will therefore look similar to this:

FlowCom.PNG

 





Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
If you liked my response, please consider giving it a thumbs up. THANKS!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

View solution in original post

12 REPLIES 12
Highlighted
Super User
Super User

Re: Get Items Filter Query

Hi @Lindy007 , odata queries are a bit of a dark art at times. Not equal to is shortened to "ne" so try this:

 

ProjectStatus ne 'Complete' and Modified eq 'addDays(utcNow('yyyy-MM-ddTHH:mm:ssZ'),-14)'

I always refer to this URL for guidance which may help you going forward:

https://www.odata.org/documentation/odata-version-2-0/uri-conventions/

 

 





Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
If you liked my response, please consider giving it a thumbs up. THANKS!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

Highlighted
Regular Visitor

Re: Get Items Filter Query

Thanks for your help!  That solves the project status bit.

I'm still stuck on the modified date though. When I run the workflow the query still returns nothing?  I have one list item which should meet the criteria of being modified (16 days ago for this test).  The list item was last modified 26/06/2019.  After running the workflow I checked the Flow run activity and the filter query shows as below with modified 26/06/2019 so should return one list item.  Is the time bit relevant?  Obviously I don't care what time the item was modified on that date - just modified any time on that date.

ProjectStatus ne 'Complete' and Modified eq '2019-06-26T01:38:04.0000000Z'

Highlighted
Super User
Super User

Re: Get Items Filter Query

Hi @Lindy007 , ok so to handle the day we need to isolate a date range starting from 00:00 to 23:59:59 on the day.

 

To do that, I would approach it in this way:

  1. Initiliase a variable to hold just the date using the following expression:
    formatDateTime(addDays(utcNow(),-14),'yyyy-MM-dd')
  2. Use that date within the filter query and append the times:
    ProjectStatus ne 'Complete' and (Modified ge '<DATE VARIABLE>T00:00:00' and Modified le '<DATE VARIABLE>T23:59:59')

It will therefore look similar to this:

FlowCom.PNG

 





Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
If you liked my response, please consider giving it a thumbs up. THANKS!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

View solution in original post

Highlighted
Frequent Visitor

Re: Get Items Filter Query

Hi @MattWeston365 , thanks so much for your help with this Smiley Happy

Your suggestion seemed to work but there seems to be an issue with the date calculation as it is one day out?

As I had several items in my list that were modified 3 days ago on 12th July, I added the following expression to initialize a variable as you suggested:

formatDateTime(addDays(utcNow(),-3),'yyyy-MM-dd')

When I ran the workflow, no items were returned?  I checked the Flow run activity and noticed that the date variable value was acually showing as 2019-07-11 (2 days ago). 

I then changed my initialize variable expression to minus 2 days and it then returned items modified 3 days ago (12th July).  Any idea why this might be?

Thanks.

 

Highlighted
Frequent Visitor

Re: Get Items Filter Query

Thanks so much for all your help @MattWeston365 .  You solved this for me Smiley Happy

Highlighted
Super User
Super User

Re: Get Items Filter Query

That's great to hear @lindi007 🙂

 

Can you please mark the relevant post as the solution so others can benefit?





Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
If you liked my response, please consider giving it a thumbs up. THANKS!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

Highlighted
Frequent Visitor

Re: Get Items Filter Query

How?  First time I've used these forums!  Maybe I need to find a forum on how to use forums? 🙂

Highlighted
Super User
Super User

Re: Get Items Filter Query

Believe it or not that is actually being worked on 🙂

 

Normally you will see "Accept as solution" as a button next to the posts within a thread which you have started, but I've noticed that your username has changed since the original post. If you can let me know which post was most useful I'll be able to make it for you.





Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
If you liked my response, please consider giving it a thumbs up. THANKS!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

Highlighted
Frequent Visitor

Re: Get Items Filter Query

Your post on Friday with the screenshots of how to 'Initialize Variable' and 'Get Items' Smiley Happy

Thanks!

 

 

Highlighted
Helper II
Helper II

Re: Get Items Filter Query

Hello @MattWeston365 ,

 

I have kind of simliar problem also i am new to MS Flows.

 

I have 2 lists:

- List 1 (Offers: name, address, etc.)

- List 2 (Offers accepeted or not)

 

Each order has a paticular ID number(not item ID of sharepoint) .

 

What i want is when a new item is created in List 2 and ID number in list 2 (only the newly created ID not all) is equal to ID number in list 1 (offer ID) then call data from List 1 (eg. name, adress, etc.) with same ID and send email.

 

Thanks in advance,

Sanket

Highlighted
Super User
Super User

Re: Get Items Filter Query

Hi Sanket (@sk2912)

 

I created a video on how to use the filter query and check if something exists:

https://youtu.be/x6QcntAs2_0 

 

Basically use the filter query where the field in your lookup list equals (eq) the data in the first list. If the length of the data which is return is 0, then it doesn't exist.

 

Have a watch and then let me know if you need further help 🙂





Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
If you liked my response, please consider giving it a thumbs up. THANKS!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

Highlighted
Helper II
Helper II

Re: Get Items Filter Query

Hello @MattWeston365 ,

 

Thanks for your reply. 

 

But i am really a basic user and my background is not IT. So if possible could make a flow for better explanation.

This is my flow :

image.png

 

Hope you understand.

 

Thanks in advance,

Sanket

Helpful resources

Announcements
firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Users online (7,408)