Answered

How to overcome delegation issue that we face with Countrows and Countif

(2) Share

Report

Posted on by baldeep_1993

Hello Everyone,

I have a SharePoint list that has over 5000 records and i want to show the total count in a text label on a screen and i dont want to use Power Automate flow (send an Http request action).

As we know Countrows is non-delegable on SharePoint and I was looking for a turnaround by using

UpdateContext({records: Filter(<Order5000>, Category.Value = "Electronics")});

UpdateContext({recordcount: records.allitems})

in the text label control I only see 500 records. Is there a way without using Power automate to get the Electronic count which is 848 total out of 5000 in a Text label. Is there any other function that i can use.

Categories:

General Questions

All responses (4)

Answers (1)

Kenobi 71 on at

Like (0)

Report

How to overcome delegation issue that we face with Countrows and Countif

@FLMike Said:
Hi

1) the default delegation size is 500, you need to change the property under settings to make it between 500 and 2000

2) What you need to do is use a With

With(

{MyRecords: Filter(sourcea, x =x) },

CountRows(MyRecords)

)

This is not entirely correct, because if the number of rows after filtering exceed the data row limit you set in settings (2000), countrows will only count up to the data row limit (2000)

I created a mockup to test this, my list has around 6000 rows, and i specifically changed the Picker value to Choice 2 on 2100 lines, then i ran this and only got 2000
Verified answer

Michael E. Gernaey 40,272 Super User 2025 Season 1 on at

Like (1)

Report

How to overcome delegation issue that we face with Countrows and Countif

Hi

1) the default delegation size is 500, you need to change the property under settings to make it between 500 and 2000

2) What you need to do is use a With

With(

{MyRecords: Filter(sourcea, x =x) },

CountRows(MyRecords)

)
Kenobi 71 on at

Like (1)

Report

How to overcome delegation issue that we face with Countrows and Countif

Sorry i forgot to mention that you need to set the URL property of the component that you added to your screen in step 3 to GraphAPIURL
Suggested answer

Kenobi 71 on at

Like (1)

Report

How to overcome delegation issue that we face with Countrows and Countif

Hi,

I learnt this from my gigachad colleague, this is what you can do:

Utilize graph API to create a collection of all the items and then you can filter and countrows that collection based on your conditions.

Step 0:

Add an Office365Groups connector to your app

Step 1:
Create a component in PowerApps, I called it Looper

Add a New Custom Property called URL (or anything), set data type to Text and check "Raise OnReset when value changes" and also make sure Access app scope is On.

Step 2:

Set the OnReset Property to this:

If(

IsMatch(

Self.URL,

"https://",

MatchOptions.BeginsWith

),

With(

{

MyAPIcall: Office365Groups.HttpRequest(

Self.URL,

"GET",

""

)

},

Collect(

CollectionThatYouWillStoreTheData, //Name it as you wish

ForAll(

Table(MyAPIcall.value),

{

ID: Value(Value.id),

Title: Text(Value.fields.Title),

Name: Text(Value.fields.NAME), //repeat for all columns you want to fetch

Numbers: Value(Value.fields.ANUMBERCOLUMN) //repeat for all columns you want to fetch

}

)

);

Set(

GraphAPIURL,

Text(MyAPIcall.'@odata.nextLink')

)

)

)

Step 3:

Add this Component to the screen where you would do the operation (Insert > Custom > Looper component). You can make it invisible or 0x0 px.

Step 4 (optional button): I created a button to start the API call, but if you want to connect it to an event or an OnStart or anything you can use that

Set the Button OnClick to this:

Clear(CollectionThatYouWillStoreTheData); /just in case you press it twice accidentally you don't want duplicate records.

Set(GraphAPIURL, "https://graph.microsoft.com/v1.0/sites/[Your Site ID]/lists/[Your List ID]/items?$expand=fields($select=Title,NAME,ANUMBERCOLUMN)&$top=5000")

Replace NAME, ANUMBERCOLUMN with any columns you also want to store in the collection and make sure you include them in the OnReset as well. ID is default here, you don't need to add it.

What it does, is it fetches the first 5000 rows from your sharepoint list, and if there is a 'nextLink' parameter in the response, it will trigger a Reset in the component which then runs the query again for the next 5000 rows.

Now you have a collection with all the rows and you can do the filtering and count rows.

Hope this helps.