Power Platform Community Forum Thread Details

Hello all,

This is my first post here and It's been 2 months I am using Power Automate.My question is in my flow, I need to retrieve employees from the Employee list with filter query( substringof('30', MMHubEmployeeGroup) or substringof('31', MMHubEmployeeGroup) or substringof('32', MMHubEmployeeGroup) or substringof('33', MMHubEmployeeGroup) or substringof('34', MMHubEmployeeGroup) or substringof('35', MMHubEmployeeGroup)) and sites to follow from the list IntranetSites list where the the group number is between 30 to 35.Currently, I'm using 2 loops to process this information, but the flow is running into a timeout error due to the large number of records.What would be the best approach to handle this more efficiently.Thank you!

Categories:

Building flows

Hi @CU09010512-1,

In the first place, I would suggest not to loop through 4000 employees one by one. Instead, filter at source, batch updates via SharePoint API, or assign sites by group mapping. This will eliminate timeout errors and let you “force follow” sites efficiently. Essentially, when we deal with large datasets (like 4000 employees) in Power Automate, looping through each record to “follow sites” will almost always hit timeouts.

Before we jump in the flow, let's do some clean up to filter your criteria: I considered your current query is:

substringof('30', MMHubEmployeeGroup) 
or substringof('31', MMHubEmployeeGroup) 
or substringof('32', MMHubEmployeeGroup) 
or substringof('33', MMHubEmployeeGroup) 
or substringof('34', MMHubEmployeeGroup) 
or substringof('35', MMHubEmployeeGroup)

Honestly, this will work, but it’s verbose and error‑prone.

Consideration-1:

If MMHubEmployeeGroup is a text column that contains the group number, you can use a range filter instead of multiple substringof calls:

MMHubEmployeeGroup ge '30' and MMHubEmployeeGroup le '35'

NOTE: ge = greater than or equal and le = less than or equal. This returns all employees where the group number is between 30 and 35.

Consideration-2:

In case, if the column (MMHubEmployeeGroup) contains mixed text (alphanumeric) like "Group 30", "Group 31", etc., then you do need substringof. Here, you can still simplify:

substringof('3', MMHubEmployeeGroup) and 
(MMHubEmployeeGroup ge '30' and MMHubEmployeeGroup le '35')

Consideration-3:

If you want to keep it explicit but cleaner (if the column contains number):

(MMHubEmployeeGroup eq '30' or 
 MMHubEmployeeGroup eq '31' or 
 MMHubEmployeeGroup eq '32' or 
 MMHubEmployeeGroup eq '33' or 
 MMHubEmployeeGroup eq '34' or 
 MMHubEmployeeGroup eq '35')

So, at this point, we have cleaned up the filter query and we can retirieve emplpoyee of that range without loop!

The same way now lets do the same thing for Sites to Follow (Intranet Sites list), we can apply the same filter to your Intranet Sites list:

GroupNumber ge 30 and GroupNumber le 35

This way, we can retrieve only the sites where the group number is between 30 and 35.

Time to put filters for both employees and site together in power Automate:

A. Get items from Employee list → Filter query:

MMHubEmployeeGroup ge '30' and MMHubEmployeeGroup le '35'

B. Get items from Intranet Sites list → Filter query:

GroupNumber ge 30 and GroupNumber le 35

Combine results (A and B) → Let's use Compose or Union action to merge both arrays if you want a single output.

Time to avoid tmeout - flow design (this is very high level)

1. Trigger - Manual, scheduled, or based on an event (e.g., new employee added).

2. Get Items (Employee List) - We can use OData filter or let's use Compose /Union action results from above.

Just to recall, we can use this filter in Get Items action if needed

MMHubEmployeeGroup ge 30 and MMHubEmployeeGroup le 35

3. Prepare Batch Payload- We can use a Select action to transform employee IDs into batch request objects. Example mapping (json):

{
  "method": "PATCH",
  "url": "/sites/contoso/_api/web/lists/getbytitle('Employee')/items(@{item()?['ID']})",
  "headers": {
    "IF-MATCH": "*",
    "X-HTTP-Method": "MERGE"
  },
  "body": {
    "SitesToFollow": "IntranetSiteA"
  }
}

NOTE: This creates one request per employee for site "IntranetSiteA"

4. Compose Batch Request - Wrap the array of requests in a single JSON object(in a compose action):

{
  "requests": @{outputs('Select')} // Output of Select
}

5. Send HTTP Request to SharePoint - Use the Send an HTTP request to SharePoint action.

Method: POST
URL: _api/$batch
Headers: Content-Type: application/json
Body: the batch JSON from the Compose step (from point 4).

6. Handle Response - Parse the response to confirm which updates succeeded or failed. Optionally log errors to a separate list.

Here’s what the final payload looks like when updating multiple employees (you see it when flow runs successfully):

{
  "requests": [
    {
      "method": "PATCH",
      "url": "/sites/contoso/_api/web/lists/getbytitle('Employee')/items(1)",
      "headers": {
        "IF-MATCH": "*",
        "X-HTTP-Method": "MERGE"
      },
      "body": {
        "SitesToFollow": "IntranetSiteA"
      }
    },
    {
      "method": "PATCH",
      "url": "/sites/contoso/_api/web/lists/getbytitle('Employee')/items(2)",
      "headers": {
        "IF-MATCH": "*",
        "X-HTTP-Method": "MERGE"
      },
      "body": {
        "SitesToFollow": "IntranetSiteA"
      }
    }
  ]
}

Benefits

No looping through 4000 employees → avoids timeout.
Single HTTP call → SharePoint processes all updates in one batch.
Scalable → You can update hundreds of records at once.
Flexible → You can vary the SitesToFollow value per group by building different batch requests.

I am sure some clues I tried to give. If these clues help to resolve the issue brought you by here, please don't forget to check the box Does this answer your question? At the same time, I am pretty sure you have liked the response!