cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
elleeyl
Level: Powered On

CountRows Operation is not supported by this connector

Hi Community,

 

I have an expense report where I need an unique ID for each report submitted. The highlighted portion below where "ReportID: (CountRows(ExpenseReport_2)+1)" results in

 

1) CountRows operation is not supported by this connector.

2)  Highlighted part of this formula might not work correctly on large datasets.

 

Kindly advise on how I can overcome these 2 errors. Thank you.

 

error.PNG

2 ACCEPTED SOLUTIONS

Accepted Solutions
chrisog
Level 8

Re: CountRows Operation is not supported by this connector

Sharepoint can delegate several functions, which effectively means you can request sharepoint complete the action and then return the results. When you use a non-delegable function or data source, the data source sends everything to powerapps and powerapps performs the function. This is effected by the row limit and means that you may not get all of your data.

This is the documentation for Sharepoint including a list of the delegable functions:

https://docs.microsoft.com/en-us/connectors/sharepointonline/

 

As for how to get a unique id, I use a combination of First() and SortByColumns to make sure I always get the highest value.

 

For you, the formula would be:

ReportID: First(SortByColumns(ExpenseReport_2, "ReportID", Descending)).ReportID + 1

 

 

This asks Sharepoint to sort the data source into descending order by the column ReportID, and then return the first value. Since it is descending, the first value will always be the highest. 

View solution in original post

Dual Super User
Dual Super User

Re: CountRows Operation is not supported by this connector

SharePoint lists create their own unique ID for every list item.  Its an integer that starts at 1 and is incremented each time an item is added.  There is no particular reason to create a seperate unique ID since one already exists.

 

In terms of getting rid of the delegation warning. The approach I normally use is to create a nested datasource.  In other words have an internal filter on the data source that gets it below 500 records and then use the non-delegable functions in an outer filter to refine the count further. For example, dates are one of the fields that can't really be used so here is how to do a filter on a date.

Filter(Filter(datasourcename,ModifiedBy.Emal = User().Email),Text(Now(),ShortDate)=Text(ThisItem.Created,ShortDate))

You'll still get the warning, but you can ignore it since you know the inner delegable Filter on Email will get the record count down to a manageable number.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

5 REPLIES 5
chrisog
Level 8

Re: CountRows Operation is not supported by this connector

Hi @elleeyl,

 

What data source are you using?

 

CountRows is a non-delegable function and therefore only works for the first 500 rows by default (which can be increased to 2000). This means that it will only ever count a maximum of 2000 rows. 

 

 

elleeyl
Level: Powered On

Re: CountRows Operation is not supported by this connector

Hi @chrisog ,  thanks for the prompt response. My datasource is SharePoint list. Is there any way then on how I can create an unique ID then? Thanks again.

chrisog
Level 8

Re: CountRows Operation is not supported by this connector

Sharepoint can delegate several functions, which effectively means you can request sharepoint complete the action and then return the results. When you use a non-delegable function or data source, the data source sends everything to powerapps and powerapps performs the function. This is effected by the row limit and means that you may not get all of your data.

This is the documentation for Sharepoint including a list of the delegable functions:

https://docs.microsoft.com/en-us/connectors/sharepointonline/

 

As for how to get a unique id, I use a combination of First() and SortByColumns to make sure I always get the highest value.

 

For you, the formula would be:

ReportID: First(SortByColumns(ExpenseReport_2, "ReportID", Descending)).ReportID + 1

 

 

This asks Sharepoint to sort the data source into descending order by the column ReportID, and then return the first value. Since it is descending, the first value will always be the highest. 

View solution in original post

Dual Super User
Dual Super User

Re: CountRows Operation is not supported by this connector

SharePoint lists create their own unique ID for every list item.  Its an integer that starts at 1 and is incremented each time an item is added.  There is no particular reason to create a seperate unique ID since one already exists.

 

In terms of getting rid of the delegation warning. The approach I normally use is to create a nested datasource.  In other words have an internal filter on the data source that gets it below 500 records and then use the non-delegable functions in an outer filter to refine the count further. For example, dates are one of the fields that can't really be used so here is how to do a filter on a date.

Filter(Filter(datasourcename,ModifiedBy.Emal = User().Email),Text(Now(),ShortDate)=Text(ThisItem.Created,ShortDate))

You'll still get the warning, but you can ignore it since you know the inner delegable Filter on Email will get the record count down to a manageable number.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

elleeyl
Level: Powered On

Re: CountRows Operation is not supported by this connector

@Pstork1  @chrisog  Thank you for your advice!

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (5,305)