Get certain items in a SharePoint List and send an email

[tested1]

Hello!

My scenario is as follows:

1. I have a sharepoint list and one column is 'Status' 
2. I want to set up a 'check' at say the end of the day so to start with set a delay of say 12 hours or pick a time to start this check
3. Flow would then check this sharepoint list, and any item which has the 'Status (column name)' 'Pending (DEFAULT column value)' would be sent in one email to someone.

Is this possible to do? Atm I have made it so it will send individual items but not the whole list

My flow atm is for one item and is very brief (see pic). I need to figure out the step of getting all items but only get the items whos status' are set to the default value of Pending. If it helps my status column will have 3 statuses but Pending is the default value

And then send all these items in a table or whatever format is best to one person

Thank you!!!

[rebeccas]

If it were me I would just add in a Condition below that which looks for only items in your list with a 'Created' Date of today. You can probably also do it in the 'Filter Query' but because it will be a limited amount already by this point I would just do a Condition below.

[tested1]

Thanks @rebeccas 

That could work..What would I put in the condition to only get items created today?

Created is equal to Today ? Can I put in a time if I wanted so only items created before say 12am as it is a time based trigger?  (See pic below)

[rebeccas]

For today would be utcNow(). You can do it with just date or with time too...you will just have to make sure they are formatted the same. 

I have one that does just date (put in the expression box) that is:

formatDateTime(items('Apply_to_each')?['Created'],'yyyy-MM-dd') 

is equal to

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

So the first is looking at my SPList and pulling the Created date and the second is looking for todays date. If you format to include time then that could be added in too. To figure out what you want for the part that looks at your created date I normally just click on it from the Dynamic content first and then hover over it to see what that says and then put that where I have the "items('Apply_to_each'?['Created']" above. 

I didn't test it but this looks right and should be the one to include time:

'formatdatetime(triggerBody()?['EndTime'],'yyyy-MM-ddThh:mm:ss')'

[tested1]

Thanks @rebeccas ]

Where would the conditon go

Recurrence -> Condition -> Get items -> Compose -> Send email?

[rebeccas]

If you already have a Condition in there you could just add in another row on it and put it there.

What is your Condition looking for already?

[tested1]

Hello @rebeccas 

ATM The trigger is a 'recurrence' so the flow can be explained as follows:

1) Once per day, there is a recurrence checker (at the end of the night 11:54 am) 

2) This will check my SharePoint list and all items with the status 'Pending' that were created on that day means something has gone wrong

3) So the the get items will be used and the condition if they were created today will be satisfied

4) use compose to get number of items with status pending and created today / before 11:54 am and send an email to someone

For now if the condition is if the item was created today then it should be ok as I can scheudle the recurrence at 11:54am?

Does this make more sense? 

[rebeccas]

It will allow you to do any time you need. 

I think you should be good...Im not spotting any issues right off. Give it a test and see.

[tested1]

hi @rebeccas 

Any idea whats wrong here? Got the erro tempalte validation failred 

[rebeccas]

Your condition has to be below Get items or it won't know what it is referring to.

[tested1]

I seem to get the same error '

The template validation failed: 'The repetition action(s) 'Apply_to_each' referenced by 'inputs' in action 'Condition' are not defined in the template.'.



My conditions are:

formatDateTime(items('Apply_to_each')?['Created'],'yyyy-MM-dd') = 

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

[rebeccas]

I think that I misspoke about being able to add in a Condition, it is not an option because your count is being generated off the number of "Get items" so you will have to get all your filtering in there in the Fitler Query part. 

You can do multiples there by just putting an and in between.

This is an example of doing it for the status Open and Created today. If you are wanting more specific times for your date I am not 100% sure on that you might just look at some of the Expression options and see if you spot one that includes that...I know there is one that mentions Hour but I havent' tried it yet.

Mine that here is : startOfDay(utcNow(),'yyy-MMM-ddTHH:mm:ssZ')

[tested1]

Hello @rebeccas 

Any idea why I am getting this error? 

My filter query formula is 

startOfDay(utcNow(),'yyy-MMM-ddTHH:mm:ssZ')

Anf compose is

length(body('Get_items')?['value'])

When I click test/run on the recurrence I get the bad request error. I will remake it and see. The flow works without the startofday formula so just get items but shows all get items from every day. So issue is with the created today formula it seems

[rebeccas]

If you just added in the part about the time you will have to redo any items that refer to it because of your changes...I don't know why exactly but you do. 

Try building it from scratch exactly the same or at least redo the code for everything below your 'Get Items' and see if that does the trick. It is annoying but I have had that happen to me several times.

Rebecca