cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SteveODea
Helper I
Helper I

Get Data from SharePoint list Filtered on data in another list

I am trying to build a flow, which gets data from a (large 20,000 rows) Primary SharePoint List, however I want to be able to filter this list using the ODATA commands based on the contents of a dynamic secondary list.

 

The secondary list, is a single column with a list of reference codes, which also appear in the primary list on a one to many basis, and while I have less than 100 reference codes on my secondary list, there could be 5,000 corresponding rows in the primary list.

 

Whilst I can hard code the reference codes from the secondary list in the "Filter Query" field in the Get Items action, I don't want to be updating my flow every time the secondary list changes. Also, my secondary list is quite long and the filter would be hard to manage.

 

Is it even possible to link the dynamic (secondary list) into the ODATA filter for my Get Items action on my primary list.

 

Any ideas which might push me in the right direction would be welcome

 

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
efialttes
Super User
Super User

Hi!

Let me see I understood your challenge

Are you planning to execute a Filter Query for each reference code? So if you have 100 reference codes on your secondary list, you need to perform 100 filter queries to your primary list?

 

Did you also managed to execute a 'Get items' to the primary list without ODATA filters, and get back the more than 20,000 rows, by means of activating Pagination, etc? If so, and due to performance reasons I would suggest the following strategy:

-add a SP 'Get items 1' action block to the primary list without ODATA filters,

-add a dummy 'Compose' action block, assign as its input the a length() based WDL expression to calculate nr of items obtained by 'Get items 1' -this way you verify you obtain all items-

-add a SP 'Get items 2' action block to the secondary list

-add a dummy 'Compose' action block, assign as its input the a length() based WDL expression to calculate nr of items obtained by 'Get items 2' -this way you verify you obtain all items-

-add an 'Apply to each' action block assigning as its input 'Get items 2' to the secondary list output

 

Now inside the Apply to each:

-add a 'Filter array' action block, assign as its input 'Get items 1' to the primary list output; and define its  condition in a way to get items from the primary list whose reference code matches the one from current 'Apply to each' iteration

 

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

2 REPLIES 2
efialttes
Super User
Super User

Hi!

Let me see I understood your challenge

Are you planning to execute a Filter Query for each reference code? So if you have 100 reference codes on your secondary list, you need to perform 100 filter queries to your primary list?

 

Did you also managed to execute a 'Get items' to the primary list without ODATA filters, and get back the more than 20,000 rows, by means of activating Pagination, etc? If so, and due to performance reasons I would suggest the following strategy:

-add a SP 'Get items 1' action block to the primary list without ODATA filters,

-add a dummy 'Compose' action block, assign as its input the a length() based WDL expression to calculate nr of items obtained by 'Get items 1' -this way you verify you obtain all items-

-add a SP 'Get items 2' action block to the secondary list

-add a dummy 'Compose' action block, assign as its input the a length() based WDL expression to calculate nr of items obtained by 'Get items 2' -this way you verify you obtain all items-

-add an 'Apply to each' action block assigning as its input 'Get items 2' to the secondary list output

 

Now inside the Apply to each:

-add a 'Filter array' action block, assign as its input 'Get items 1' to the primary list output; and define its  condition in a way to get items from the primary list whose reference code matches the one from current 'Apply to each' iteration

 

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!



Thanks, I'll give this method a try

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.

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.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Users online (3,347)