Building a flow that filters all completed items that were finalized last month.

[JWPowder]

Hello, I am building a flow that gets items from a sharepoint list that have been marked as completed, then filters those items out to only the ones completed last month. 

Then, I want the flow to randomly select 3 items from that list and compose them into an email and send it. 

I am using this guide but I am having trouble with the filter query. https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Get-a-random-item-from-a-SharePoint-list... 

The necessary columns are DateFinalized and Status. 

[ScottShearer]

@JWPowder 

Please post a screen shot of your Get Items actions and be sure to include the filter query.

Also, post a screen shot of any errors that you see.

Also, here is a link to a great reference on creating the filter queries

[JWPowder]

@ScottShearer 

Current State:

Filter Query expression : 

formatDateTime(getPastTime(1, 'Month'), 'yyyy-MM-dd')

Error Message:

[ScottShearer]

@JWPowder 

Try this:

DateFinalized le 'you expression here'

You need to add the le operator after DateFinalized.

That will give you items where the date is less than or equal to to date in your expression.  I suspect that you'll want to do more than that.  Probably something like this:

DateFinalized ge 'expression that gives you beginning of month' and DateFinalized le 'expression that gives you end of the month'

[JWPowder]

@ScottShearer That is correct, I modified my query from above to be DateFinalized ge '

startOfMonth(getPastTime(1,'MONTH'))' and DateFinalized le '

startOfMonth(utcNow())'

Now I am getting an error on the compose section.

This is my expression I am using in the inputs section of compose. 

body('Get_items')?['value'][rand(1,length(body('Get_items')?['value']))]

[ScottShearer]

@JWPowder 

Take a look at your last run - were any items returned by Get Items?

[JWPowder]

@ScottShearer There are items in the outputs for the GetItems that I have to "download" to view. 

[ScottShearer]

@JWPowder 

I am a a bit of a loss.  I verified that your expressions are valid.  The only time that I see an error is when there are no rows returned by Get Items.

I suggest adding a condition - if the result of the following expression is 0, then don't go further:

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

[JWPowder]

@ScottShearer some of the fields for the DateFinalized column are null because they have not been finalized yet. Could this be causing my error? I have confirmed that there are indeed rows that SHOULD be included in the query and the outputs contains those records but I'm not sure if the null rows might be throwing it off.

[JWPowder]

@ScottShearer this is the current expression in my Compose section:

body('Get_items')?['value'][rand(1,length(body('Get_items')?['value']))] 

How would I modify it to contain the condition you listed above? 

[ScottShearer]

@JWPowder 

Please try this..

Add a compose action immediately after Get Items.

Add the following expression: length(body('Get_items')?['value'])

Run your Flow and let me know the value shown.

[JWPowder]

@ScottShearer 

[JWPowder]

@ScottShearer It is odd to me that it is returning 1 as the input and output for the compose step. In the guide I am using it shows JSON in the inputs and outputs once they run the flow for the first time. 

This then allows them to parseJSON in the next step of the guide. Something which I will not be able to do with the current output.