Get Items Filter Query

[Lindy007]

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

[MattWeston365]

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/

[Lindy007]

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'

[MattWeston365 · Accepted solution]

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:

[lindi007]

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

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.

[lindi007]

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

[MattWeston365]

That's great to hear @lindi007 🙂

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

[lindi007]

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

[MattWeston365]

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.

[lindi007]

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

Thanks!

[Anonymous]

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

[MattWeston365]

Hi Sanket (@Anonymous)

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 🙂

[Anonymous]

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 :

Hope you understand.

Thanks in advance,

Sanket

[Anonymous]

Hi I am facing the same issue. Could you please kindly tell why we need to isolate a date range starting from 00:00 to 23:59:59 on the day? Is it because of time zone , or is there any other reason?

[yasmean]

This solution worked for me , thanks