cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SteveODea
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
efialttes
Super User III
Super User III

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

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

Thanks, I'll give this method a try

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Users online (28,356)