How to subtract days using the Filter Query in Get Items

[Holly_CMS]

Hi...

I need to go back to 14 days from the start date and I have tried several expressions. All errored out...

[wskinnermctc]

I'm thinking the 'null' is the problem. You can't compare against null since it doesn't exist.

You will need to check the CMSEmail some other way like empty() or length()

empty(CMSEmail) eq true

length(CMSEmail) eq 0

Also, if the Effective Date is not in the exact same format, then it would be a problem too. Like if the EffectiveDate is a datetime column, it probably has some hours on the end, so it would never match 'MM/dd/yyyy'. 

I don't know exactly what is your issue but I'm sure null is one of them. Hope this helps,

[Holly_CMS]

How did you determine that NULL is the issue? It works fine. However, I need the Start Date to go back 14 days from its start date...

[wskinnermctc]

I only guessed Null is the problem because it usually is a problem in filters. Also, your expression formatdatetime(adddays(utcnow(),-14),'MM/dd/yyyy') is correct.

So that means that the filter issue is with the Null, OR it is with the format of the "Effective Date" that it is being compared to. Or there is something else completely wrong with what you are doing that I would be unable to see with the information provided.

I don't understand how your filter is based on "Effective Date", but then you want to go back 14 days from the "Start Date"? Is there a difference between "Effective Date" and "Start Date"

And you are trying to filter a "Get Items" step with dates from and Excel table??

Can you show your whole flow? It is difficult to tell what you are trying to do. 

[Holly_CMS]

Gotcha - I am creating a table inside the body of the email. So with that said, the effective date is the actual name of the column in SP list whereas I renamed it to Start Date in the flow. The Start date is when an employee starts with the company.

The reason why I want to go back 14 days is because the process at work shows all new onboarding tickets must be worked on within a day or so from the ticket submission date. However, we don't have the submission date in the SP list. The email address missing in the email field is an indication that the tickets have not been completed. Therefore, put the 14 days before start date expression in to see which older onboarding items are not done yet.  Hope this clarifies for you?

[wskinnermctc]

Ok thank you, I made some columns in a sharepoint list and added some items. Using the -14 days from today 5/10/2023 would be 4/26/2023. So I have 2 items in my sharepoint list with the date of 04/26/2023. The first item does not have an email address in an Email text column, and the second item does contain an email address.

I was able to get the single item that has a date of 04/26/2023 and does not have an email with the filter query example below. 

Note: The view you have with the Filter Query that has drop down options in considered "Experimental Features". If your settings has turned On the Experimental Features, the view will look like yours with the drop down options. 

If you turn Off the Experimental Features then the dropdowns are not available and the Filter Query has to be written in all the way.

The two arrows beside Filter Query will toggle the view between the regular view and the Experimental Feature view.

Below is an example of the same step that has the view toggled.

Filter Query Views

Whenever you use the Experimental Features type view it will add single quotes ' ' around what is put in the filter value even though you are not able to see the single quotes. (If you went into the Peek Code option of the action you can see the quotes were added.)

Single quotes usually work and are required for most values used in a filter query, but the quotes will not work when using null. You will have to toggle the view to be the regular text format and then remove the single quotes ' ' that are around 'null'. So the expression shows null instead of a text 'null' . 

Below is the same step that had the view toggled and the filter value 'null' changed to null without single quotes.

Edit Filter Query

I was able to filter my items with this example.

The name of my SharePoint columns were "SP_Text_Email" and "SP_Text_EffectiveDate". I also had a separate column "SP_DateTime_EffectiveDate" that was a date time type column, but the results were the same when the date column was text as well as date time.

My Filter Query is below.

Incorrect:

(SP_Text_Email eq 'null') and (SP_Text_EffectiveDate eq 'formatDateTime(addDays(utcNow(),-14),'MM/dd/yyyy')')

Correct:

(SP_Text_Email eq null) and (SP_Text_EffectiveDate eq 'formatDateTime(addDays(utcNow(),-14),'MM/dd/yyyy')')

[Holly_CMS]

Got it! Question - does the expression only bring back the ONLY date of 4/26/2023? It works but it made me realized that there was nothing for that day.

If so, now I see what I did wrong...How do I ask in expression for the "previous within the 14 days" from the start date, not the precise of the previous 14th day. Like 4/24/2023  to 5/5/2023.

We don't want the "current week" of start dates like 5/8/2023 of this week to show up. This flow will be set up a weekly flow before the start of the current week (*Mondays).

[wskinnermctc]

I don't know of a way to filter by the value itself. You can't say, "I want the Effective Dates that are within -14 days of the effective date." That is a self reference and doesn't make a logical filter.

You have to establish the date that is being compared as a filter for the values.

Like you can say "I want the Effective Dates that are greater than or equal to utcNow()" Which will get all of the Effective Dates that are today or anytime after today.

If you schedule the flow to run on Monday, but you don't want any Effective Dates from this week, you can say "I want the Effective Dates that are greater than or equal to addDays(utcNow(),7)" which would give you all of the effective dates that are the next Monday or a later date.

Are you saying that you only want Effective Dates that are within the next 14 days? As in you only want to get the people that are supposed to start work within the next two weeks/14 days?

Or are you saying you want all of the Effective Dates that are within the past 14 days? As in any date that is between or equal to 4/26/2023-5/10/2023?

[Holly_CMS]

Let's try this...I have 2 items that started on 5/8/2023 yet they do not have the email address as of today 5/10/2023. So I need to go back in at least 2 weeks to capture the items that already started but are missing the email addresses. Not the upcoming start dates in which would be 5/15/2023....

I need to pull all onboards that is missing an email address after their start date but not going back so far like months or years before. 

[wskinnermctc · Accepted solution]

Is that SharePoint column a DateTime type column or is it a Text type column?

I'm assuming it is a DateTime type column, if it is a Text type column this example will not work.

The Filter Query is basically the same except you add another value to filter by which will create a range.

So this is saying "I want items that do not have an email AND the Effective Date is greater than 14 days before today AND the Effective Date is less than today."

Get Items Filter Date Range

In my example my SharePoint columns were named "SP_Text_Email" which is a Text type column and "SP_DateTime_EffectiveDate" which is a DateTime type column.

The expression that is in the Filter Query is below:

(SP_Text_Email eq null) and (SP_DateTime_EffectiveDate ge 'addDays(utcNow(),-14,'MM/dd/yyyy')') and (SP_DateTime_EffectiveDate le 'utcNow('MM/dd/yyyy')')

Don't forget to remove the single quotes ' ' from around the null. 

The use of formatDateTime() is not necessary since most time expressions like utcNow() or addDays() allows for a formatting option within the expression itself. 

formatDateTime(utcNow(),'MM/dd/yyyy') is the same output as utcNow('MM/dd/yyyy')

[Holly_CMS]

It works!!!!!!!

Thank you Thank you Thank you!!!!