Answered

Initialising array dynamically

(1) Share

Report

Posted on by AH-02011058-0

150

I'm using this Flow to extract data from a Sharepoint list with more than 5000 rows. I get the lowest and highest ID. Then I initialise the threads variable, so I can query Sharepoint the right number of times. At the moment I'm hardcoding 4 elements into the array and the Flow runs the Apply to each 4 times.

This means the Flow will retrieve data from a list containing up to 20000 rows.

Instead of hardcoding 4 elements into the array and concurrency control, is it possible to work out the highest ID - lowest ID and divide the difference by 5000.

For example if my Sharepoint list has 27000 rows, I would want 6 elements in the array.

Categories:

Building flows

I have the same question (0)

All responses (15)

Answers (1)

Sort by

Suggested answer

Tomac 4,111 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hi @AH-02011058-0

To dynamically get the range, you'll follow this process. Note that I'm using two Compose actions to set my highest ID and lowest ID, you'll instead reference your values for those items.

Expressions used:

Compose Difference: formatnumber(div(sub(outputs('Compose_Highest_ID'),outputs('Compose_Lowest_ID')),5000),'#0') Compose Array: range(1,int(outputs('Compose_Difference'))) Compose All In One Step: range(1,int(formatnumber(div(sub(outputs('Compose_Highest_ID'),outputs('Compose_Lowest_ID')),5000),'#0')))

Here's the completed outputs of this flow:

In the Compose Difference action we're subtracting the lowest ID from the highest, then dividing that value by 5000, then using the formatNumber() function to round up to the nearest integer. Note that formatNumber() outputs a string, so we address that in the next step.

In Compose Array we built an array using the range() function beginning at 1, and ending at the output from Compose Difference wrapped in the int() function to turn the string into an integer.

Compose All In One simply combines these two steps into a single expression.

If this comment resolves the issue, please remember to mark it as the answer

Was this reply helpful? Yes No
AH-02011058-0 150 on at

Like (0)

Report
Copy link

Link copied!

At the moment the concurrency control is set to 4 which is how many elements are in the array. Is it necessary for them to always match? If so, how would I achieve this if the array is set as above?

Inside the Apply to Each above is where the Flow queries Sharepoint. The Compose Items per thread evens out the number of calls to Sharepoint using this formula.

add(

div(

sub(body('Send_an_HTTP_request_to_Sharepoint_Get_Highest_ID')?['value'][0]['ID'], body('Send_an_HTTP_request_to_Sharepoint_Get_Lowest_ID')?['value'][0]['ID'] ), length(variables('threads'))),1)

Was this reply helpful? Yes No
Tomac 4,111 Moderator on at

Like (0)

Report
Copy link

Link copied!

@AH-02011058-0 You can set concurrency control the the maximum value, it won't hurt anything if it has fewer runs than the maximum.

Was this reply helpful? Yes No
AH-02011058-0 150 on at

Like (0)

Report
Copy link

Link copied!

I'm getting an error message on Compose Difference. I tried

formatnumber(div(sub(outputs('Send_an_HTTP_request_to_Sharepoint_Get_Highest_ID')?['value']['ID'],outputs('Send_an_HTTP_request_to_Sharepoint_Get_Lowest_ID')),5000)?['value']['ID'],'#0')

and got a message saying ID property can't be evaluated.

Was this reply helpful? Yes No
VictorIvanidze 13,081 on at

Like (0)

Report
Copy link

Link copied!

If you need to handle all items in the list, do not use the ID at all. Use pagination as shown here: https://www.matthewdevaney.com/get-over-5000-items-from-a-sharepoint-list-in-power-automate/

Was this reply helpful? Yes No
AH-02011058-0 150 on at

Like (0)

Report
Copy link

Link copied!

Thanks @VictorIvanidze. I'll see if @Tomac says anything else. Otherwise I'll sign it for both of you.

Was this reply helpful? Yes No
Suggested answer

Tomac 4,111 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @AH-02011058-0

That error is talking about the array structure you're trying to pull the value from. In a failed flow run, expand your HTTP actions and View Full Outputs to see exactly what's being returned by those actions, and use that information to build the correct reference.

My guess is that you need to wrap the reference in the first() function, but without knowing the structure of your returned data I cannot say for sure.

Was this reply helpful? Yes No
AH-02011058-0 150 on at

Like (0)

Report
Copy link

Link copied!

This is the response with some headers removed for brevity.

{
"statusCode": 200,
"headers": {
"Cache-Control": "no-store, max-age=0, private",
"Transfer-Encoding": "chunked",
"Vary": "Origin,Accept-Encoding",
"Set-Cookie": "xx",
...
"Content-Type": "application/json; odata=nometadata; streaming=true; charset=utf-8",
"Expires": "Tue, 21 Oct 2025 14:23:12 GMT",
"Last-Modified": "Wed, 05 Nov 2025 15:23:12 GMT",
"Content-Length": "699"
},
"body": {
"odata.nextLink": "xx",
"value": [
{
"Id": 5854,
"TeamName": "Email Marketing",
"TeamType": "Validation Reps",
"RequestID": 309,
"ValidationResult": "Suppress",
"Instruction": null,
"ReferenceNumber": "12345",
"ActionDate": "2025-11-05T00:00:00Z",
"RequestDueDate": "2025-12-05T00:00:00Z",
"CompletionDate": null,
"Notes": null,
"ID": 5854
}
]
}
}

Was this reply helpful? Yes No
VictorIvanidze 13,081 on at

Like (0)

Report
Copy link

Link copied!

Try to add an andex after "value":

formatnumber(div(sub(outputs('Send_an_HTTP_request_to_Sharepoint_Get_Highest_ID')?['value'][0]?['ID'],outputs('Send_an_HTTP_request_to_Sharepoint_Get_Lowest_ID')),5000)?['value'][0]?['ID'],'#0')

Was this reply helpful? Yes No
Tomac 4,111 Moderator on at

Like (0)

Report
Copy link

Link copied!
@AH-02011058-0

Because the returned data structure is an array of items in the Value section, you'll either need to use the first() function or reference a static value as @VictorIvanidze said above.

Here's the expressions you can use without hardcoding an array location:

first(outputs('Send_an_HTTP_request_to_SharePoint_Get_Lowest_ID')['body']['value'])['ID'] first(outputs('Send_an_HTTP_request_to_SharePoint_Get_Highest_ID')['body']['value'])['ID']

Was this reply helpful? Yes No