cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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
Highlighted
Dual Super User III
Dual Super User III

Re: Get Data from SharePoint list Filtered on data in another list

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
Highlighted
Dual Super User III
Dual Super User III

Re: Get Data from SharePoint list Filtered on data in another list

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

Highlighted
Frequent Visitor

Re: Get Data from SharePoint list Filtered on data in another list

Thanks, I'll give this method a try

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (5,449)