cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Get Items filter query not returning any value

Hello everyone,

 

I have two lists two handle access requests to several applications: Access Approvals and Access Removals. 

I created a flow in which when a new item is created in the Access Removals list, it looks for the user whose access is being removed in the Access Approvals list and update some fields.

 

Since the Access Approvals has more than 1500 items, when getting the items I want to filter them in order to only get the items for that specific user, so that way the flow won't take that much time running.

 

I want to filter the Username column in the Access Approvals list to only retrieve the items in which the username is the same that in the item created in Access Removals list. 

 

Here you can see some of the fields in the Access Approvals list, and as an example we'll take the user Gobbi, Stefano in APLICACION PRUEBA app.

 

These are some of the fields of the Access Aprovals list. As an example, we'll use the user Gobbi, Stefano in the APLICACION PRUEBA app.These are some of the fields of the Access Aprovals list. As an example, we'll use the user Gobbi, Stefano in the APLICACION PRUEBA app.

 

Here are some items of the Access Removals list, showing that an item has been created for Gobbi, Stefano in the mentioned app.

These are some of the fields in the Access Removals list. As you can see, an item as been created for Gobbi, Stefano in the already mentioned app.These are some of the fields in the Access Removals list. As you can see, an item as been created for Gobbi, Stefano in the already mentioned app.

 

This is how the first connectors of the flow where configured. I want to filter the Access Approval list using Username eq 'Username DisplayName' (Username DisplayName is the username of the item created in the Access Removals list).

This is how some of the connector of the flow have been configured. When a new item is created in the Access Removal list, we get items in the Access Approvals list, and filters the query using Username eq 'Username DisplayName'This is how some of the connector of the flow have been configured. When a new item is created in the Access Removal list, we get items in the Access Approvals list, and filters the query using Username eq 'Username DisplayName'

 

However, after the flow runs, the Get Items connector is not returning any value.

However, after the flow runs, the Get Items connector is not returning any valueHowever, after the flow runs, the Get Items connector is not returning any value

 

Does anyone know why this is happening or what step am I missing?

 

Thanks in advance!

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-alzhan-msft
Community Support
Community Support

Hi @Anonymous ,

 

I have take a try with the expression in the Filter Query for Person type however with no luck to get the flow works.

 

I agree with @efialttes 's suggestion that you could take a try with Filter Array action as my screenshot below:

1.png

 

Best regards,

Alice       

 

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

So, your filter array output provides a single item right?

If so all you need to do is to use the following expression:

first(body('Filter_array'))?['ID']

One good thing to do is to add a Condition to evaluate nr of items provided in Filter Array output first

length(body('Filter_array'))

If greater than 0, update item. 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

11 REPLIES 11
efialttes
Super User
Super User

Hi!

Did you increase 'Top count' input in your 'Get items'? By default it gets first 100 items max

More info here

https://alextofan.com/2019/08/22/how-to-get-more-than-5000-item-from-sharepoint-online-in-flow/

 

Indexing the column you use for filtering can also help as some of our most experienced powerusers eplained me some time ago:

https://powerusers.microsoft.com/t5/Building-Flows/Sharepoint-lists-over-5-000-items-and-OData-filte...

 

An easy way to doublecheck any possible limit applying is by removing filter, and add a dummy Compose action block to show the nr of elements in 'Get items' output by means of length() based expression

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Anonymous
Not applicable

Yes, I have already increase the limit of elements the get items connector retrieves. 

 

If I remove the filter to Get Items, the flow works fine. But the problem is that since after these connectors there's an Apply to Each, the flow has to apply some conditions to more than 1500 elements, making the flow to run for almost 20 minutes. If I could filter the list in Get Items, I could retrieve just the items I need, optimizing A LOT the run time of the flow.

 

 

 

ScottShearer
Super User
Super User

@Anonymous 

Can you tell me if both columns that you are referring to are Person or Group columns?

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
Anonymous
Not applicable

Yes, the Username (and also the Aplicacion) column is a Person or Group column.

 

Correction: Username is a Person or Group column, and the Aplicacion column is a Lookup column.

Hi!

So, since Username is a Person or Group column, my suggestion is to check this thread

https://powerusers.microsoft.com/t5/General-Power-Automate/Unable-to-perform-oData-filter-on-sharepo...

or... did you consider to stop using ODATA Filter and use 'Filter array' action block instead? So you just need to assign as its input 'Get items' output and add the corresponding condition. Then, Apply to each shuld take as input 'Filter array' output

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



v-alzhan-msft
Community Support
Community Support

Hi @Anonymous ,

 

I have take a try with the expression in the Filter Query for Person type however with no luck to get the flow works.

 

I agree with @efialttes 's suggestion that you could take a try with Filter Array action as my screenshot below:

1.png

 

Best regards,

Alice       

 

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi!

Is this working? If so, remember you can mark as solution more than one answer, even from different community members

Thanx for making this community great!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Anonymous
Not applicable

I had tried using the Filter Array before and it works. I can actually use the advanced settings and filter two fields at the same time (Username and Application) so that way I'll get the specific item I want and don't have to use the Apply to Each connector.

 

To explain a little what it happens, by filtering the Approvals Request and finding the item, I'm confirming that the User whose Access Removal was requested is an active user in the corresponding app. Now, I need to find a way to get the ID of that item in the Approvals Request list, since now I have to update that list to change one of its fields.

 

I don't know how to do that. Basically, from the output of Filter Array, I need to get the ID of that item in the Approvals request list (the input array to Filter Array) to specify in the Update Item fields, and be able to only modify that item.

 

Capture 5.PNG

So, your filter array output provides a single item right?

If so all you need to do is to use the following expression:

first(body('Filter_array'))?['ID']

One good thing to do is to add a Condition to evaluate nr of items provided in Filter Array output first

length(body('Filter_array'))

If greater than 0, update item. 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Anonymous
Not applicable

It worked perfectly!

 

Thank you all very much.

Just one last question: In the case the Filter Array output had more than one item, what expression should I use instead of first() to get an specific item?

 

Thank you!

last(body('Filter_array'))

body('Filter_array')[0]

body('Filter_array')[1]

body('Filter_array')[...]



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022- Season 2 has kicked off!

Users online (4,129)