Create flow which gives me items from a list filtered for two different conditions

[Tom_F]

So I'm trying to make a flow which uses a filter query to filter for items in an event list that are both set to start 15 days in the future based on the column "Start Time" and also have a choice column by the name of "Task Status" set to 'Not Started'. I'm pretty sure that what I'm trying to do is possible and I'm messing up the syntax, if anyone has any advice. I'm not sure if I also need to convert the future time I'm getting into a format which will be compatible with start time?

[David_MA]

In your filter, equals is represented by eq instead of equals. Also:

• lt is less than
• gt is greater than
• le is less than or equal to
• ge is greater than or equal to
• ne is not equal to

You can review this reference for more information: OData comparison operator reference - Azure Cognitive Search | Microsoft Learn

[Tom_F]

Thanks, made these edits,

Still getting this error,

For reference here are the columns in the event list that are being referenced, "Start Time" (which is default to events lists) and "Task Status"(which is my own creation)

[David_MA]

The start time field needs to exist in the list where you are using the Get items action. Filter queries also use the internal name of the field, which you can get by going into the list settings and clicking on the field. At the end of the URL, it will show the internal name. The error message is straightforward, so I would first check to make sure the field you want to use in the filter is still in your list.

[Tom_F]

Ahh ok got it, I wasn't using the internal name of start time, it is actually assigned "EventDate" as an internal name,

I set it to filter for items which are between 0 and 15 days from "Event Date" and "Task Status" is 'Not Started', then I added some more stuff to have it send an email to people identified in a "ResponsiblePersons" Column from the items that are filtered out. It actually seems to be working perfectly, when I run a test it sends an email to the correct person(myself) for the items which fall within that filter, only weird part is that it says the flow failed.

The way I set up the apply to each is like so,

And at each step of the way it shows outputs as being null, yet I still got the email so at some point it picked out the responsible person column and sent me an email. Not even sure if this is an issue or not but I would prefer it if things worked as expected + I know that it will eventually turn off flows that continuously "fail" so not ideal.

[Tom_F]

Also, it seems that the flow often sends notifications for items which are set to Task Status of things other than 'Not Started', I am wondering if this is due to the items being recurring series' where the default value is 'Not Started'. For event items where there is a recurring series and then an individual item, is there a way for power automate to only pull the individual items?

[David_MA]

It is failing because the e-mail address for some of the items is blank. You could make it a required field or update your filter query to only get items where the e-mail address field you are using is not null.

[Tom_F]

Thanks, that works.

Only thing I am having trouble with now is that when I run the flow on my list, the test items I made which have no recurrence work as I expect, when set to "complete" they are not caught in the filter and when set to "Not Started" they are caught. However, the real items which I have set to recur again the same day next year get included in the filter query whether set to "Complete" or "Not Started". The items in the calendar are structured as a series, with each occurrence of the event as an item. The item for example which is coming up this year in the next 15 days is set to "Complete" so should not be caught in the filter, but it is getting caught in the filter and showing a status of "Not Started". The way that recurrence works on the events list is clear when I go to the datasheet style view; for events with recurrence, even when you set that year's item to "complete", there is still a version of the item set to "not started" and is due on the same day. But this ghost item does not show on the main calendar and must be the series template or whatever makes recurrence work.  I am wondering if there is a way to somehow exclude the series/ghost versions of these recurring items, as they also result in items set to not started showing up twice as the filter captures both the upcoming occurrence and the series/ghost version in the background.

[David_MA]

I am not really understanding what you are doing, but one thing I noticed in your filter query is that you need to add either and or or between your two fields in your filter query. That may be causing your issue.

[Tom_F · Accepted solution]

I think I figured out the solution,

So the way events lists make recurring items is like so, the two dates are start and end times respectively, A is the recurring series where A1 is the item due this year which is what I care about catching in my filter. So based on the way the calendar formats the series, the end date is always about a hundred years in the future or more. So, to filter these out and only get this year's occurrence I added one last condition to the filter to only pull items where the "EndDate" is less than 10 years in the future (Get future time 2).

So far this seems to work exactly as I expect and only pull data from this year's occurrences in the next however many days I specify in future date 1, then send me a reminder based on those that are still marked "Not Started".

Barring any unforeseen complications which may arise it seems to be working.