CountRows Operation is not supported by this connector

(0) Share

Report

Posted on by elleeyl

121

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.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (2)

chrisog 213 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
elleeyl 121 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

chrisog 213 on at

Like (2)

Report

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.

Was this reply helpful? Yes No
Verified answer

Pstork1 68,697 Most Valuable Professional on at

Like (2)

Report
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.

Was this reply helpful? Yes No
elleeyl 121 on at

Like (0)

Report

@Pstork1 @chrisog Thank you for your advice!

Was this reply helpful? Yes No