get items by date (-21 days) problem

[jpb56]

I have a SharePoint list with entries of when an item is checked. I need to find the latest date for a given primary field. In this case the field is "Building". I have no problem doing a -21 day search but I am struggling trying to figure out how to only search (for a date -21 days) for the latest date for a given Building entry.

[KvB1]

So basically, you want to create a get items action which filters on a specific primary field, and then returns to you the newest item only?

[jpb56]

Yes that is correct, but I also want to take the resulting list and only keep the items that are older than 21 days (adddays -21).

[KvB1]

So you want to know which item is the newest of the ones that are older than 21 days?

[jpb56]

Yes, that is correct.

[KvB1]

Heres what you could do in the get items action:

Create odata filter query to only retrieve items older than 21 days and fulfil your building condition, like:

Created gt '@{addDays(utcNow(),-21)}' and Building eq 'blabla'

Sort by: Created desc

Max amount: 1

This will retrieve only the most recently created item, within the condition of it being older than 21 days, and where the value in the Building column matches blabla

[jpb56]

I see where you are heading, but there are 63 different Building entries (Actually Building is a combination of Building and room)

[KvB1]

Ok, so previously said that it was 'a given' Building entry. Do you want to get them for all of them then?

[jpb56]

Yes, I am sorry for not being clear. I have a list with multiple entries for any of 63 Building entries. I need to get a list of all building entries, but only by the latest date for each one and then only the entries that are also older than 21 days. I hope this is a clearer description

[KvB1]

Okay, that makes it a bit more complicated, because there is no real elegant way to sort an array in powerautomate

What you could do:

- Run a dry 'get items' action

- From this, select only the building field in a select action

- Create compose action, where you union(ouputs('select'),outputs('select)) so you end up with an array with all the unique Building entries present in your list

- Creat apply for each loop, using this array as input.

- Within the loop, use get items action

                   - Filter with Created gt '@{addDays(utcNow(),-21)}'           AND

                   - Filter with Building eq '@{items('Apply_for_each')}'          AND
                   - Sort by: Created desc                                                        AND

                   - Max amount: 1

[jpb56]

I am working through this and am at the compose step. I am a bit unclear as to how to accomplish the last section starting with create a loop. I assume that means use "apply to each" If I then create a get items action, it wants me to select a Sharepoint list. I am missing something.

[KvB1]

Yeah, you you use the same list that holds all the entries that you want to get the last record from, but older than 21 days, per Building

Only now, because you can look for each distinct Building value, you are able to use the 'Created desc' in the order by field and still get the data you want

[jpb56]

That tip helped, thanks for that. I am now getting an error

The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@outputs('Compose')' is of type 'String'. The result must be a valid array.

This is what I am using for the compose step. Union(outputs('@{body('Select')}'),outputs('@{body('Select')}'))

The apply to each barks about it being a string and not an array. I very much appreciate your help and I am sorry for taking up so much of your time.

[KvB1]

No worries, I can see you copy pasted the dynamic input into the expression. By enclosing the phrase in inverted comma's you are telling the flow that its just text. That means it wont actually grab the output of the action as input, but just uses '@{body('Select')} as input. 

Try with this:

union(outputs(body('Select'),outputs(body('Select'))

[jpb56]

Same result: The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@outputs('Compose')' is of type 'String'. The result must be a valid array. I wonder if the problem is in my select step. Here is what it looks  like. I cant figure out why this is not working. My compose statement is now union(ouputs('select'),outputs('select)).

[KvB1]

There are some typos, try:

union(outputs('Select'),outputs('Select'))

If it still doesnt work, could you please share the output of the select action?

[jpb56]

I was not able to make this work, but your solution makes sense and because of your effort I gave you a thumbs up. I ended up going another route (Remove the item that was selected when submit was used on the edit form. Once again thanks for you awesome assistance.