CountRows Operation is not supported by this connector

[elleeyl]

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.

[chrisog]

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]

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 · Accepted solution]

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. 

[Pstork1 · Accepted solution]

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.

[elleeyl]

@Pstork1  @chrisog  Thank you for your advice!