cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GTMSWadmin
Advocate II
Advocate II

Fastest/most optimal method to check whether a value exists in a Sharepoint list

Hi experts, I am looping through a XML file to get a list of names.

For each of these names, I want to check whether they exist in a Sharepoint list (Sharepoint Online) and then carry on to do something.

Name is just one of the many columns in that SP list.

 

The first method I tried:

  • First, setup an array with all the names from the SP list
    • Use 'Get items' to get all items from the SP list (this is fast, 1 second for 640+ records)
    • Use 'Apply to each' to add all items from the SP list to an array (this took 6min+ for 640 records)
  • When looping through the XML file, use a Condition to check each name against the array e.g. if(contains(arrayFromSPList, nameString),true,false) [this is fast]

 

The second method I have not tried but I doubt it will be faster:

  • When looping through the XML file, for each name to check against the SP list, use 'Get items' with FilterQuery by Name and TopCount = 1 to search for name in the SP list
    • i.e. if there are 1000 names in the XML file, call 'Get items' 1000 times
  • Use condition: if length(body('Get_items')) > 0 means it exists in the list, else it does not

 

Is there a faster and more optimal way to do this?

 

GTMSWadmin_0-1597053270327.png

 

6 minutes may seem to be fast, but this is only 600+ records and this is the timing for only one step. I have many other actions to take which will take time e.g. checking whether any other column values have changed, updating SP lists, etc.

 

Thanks for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
stcubill
Community Support
Community Support

 

Hello,

 

The reason there are only 600 records reported is due to the SharePoint throttling limitations, which limits up to 600 API calls in 1 minute. 

 

Throttling Limits

 

Now, what we need to do with this flow is to find which is the trigger action. and the values you want to filter to lower the number of records. 

 

Using Filter Expressions in OData URIs 

 

This is an example of what you can do using filters

Edit.JPG

 
 

with this action, I lowered the next apply to each record to just 1. 

 

Capture.JPG

 

 

I hope you find this information helpful and if you need further assistance do not hesitate in contacting us. 

Community Support Team_Stayner Cubillo
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

3 REPLIES 3
stcubill
Community Support
Community Support

 

Hello,

 

The reason there are only 600 records reported is due to the SharePoint throttling limitations, which limits up to 600 API calls in 1 minute. 

 

Throttling Limits

 

Now, what we need to do with this flow is to find which is the trigger action. and the values you want to filter to lower the number of records. 

 

Using Filter Expressions in OData URIs 

 

This is an example of what you can do using filters

Edit.JPG

 
 

with this action, I lowered the next apply to each record to just 1. 

 

Capture.JPG

 

 

I hope you find this information helpful and if you need further assistance do not hesitate in contacting us. 

Community Support Team_Stayner Cubillo
If this Post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Hi Stayner,

 

No, there are only 600+ records because there are only 600+ items in the SP list I am testing with (there are more but I'm just testing). I do know about how to increase the pagination limits.

 

What you described is actually the second method in my question. Which means for every name I want to check whether it exists in the SP list, I will call 'Get items' with a filterQuery. Which means if there are 1000 names to check against the SP list, I call 'Get items' 1000 times.

Is this really more optimal than the first method?

Hello, For Sure the Filter query is better than the method with no filters. this will decrease the number of times the apply to each needs to occur. 

 

Community Support Team_Stayner Cubillo
If this Post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Register for a Free Workshop.png

Register for a Free Workshop

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

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (4,577)