cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brank
Level 8

CountIf is not delegable function, any alternative? Custom API?

Hello, I am building an app and it is imprescindible for me to have the CountIf for count rows if they satisfy a condition, for example, I have a list in a gallery that has 2 columns: name of product and quantity. I want to know how many products I have by name, for instance:

 

Computers - 2

Laptops - 5

Tablets - 10

 

This is clearly the next SQL Sentence:

SELECT Name, COUNT(Name) FROM Products GROUP BY Name

 

In my application I get all names and then use the next CountIf:

CountIf(Products; Name = ThisItem.Name)

 

Of course this doesn't work if I have more than 500 rows in my table Products that is located in SQL Server because it is just looking for the first 500 rows. My solution for try to do this was to replace the CountIf for a Filter that is delegable:

 

CountRows(Filter(Products; Name = ThisItem.Name))

 

The problem of this is that in my gallery each row do that sentence, this provokes that the application works really slow for a simple action that is counting..

 

Do you know a better way to do this? I was thinking as well to use as connection a custom api and pay azure subscription but I am not sure if that would fix my problem, I think with a custom API I can get all the information, but how do I send that information for the custom API to the Application in powerapps?

4 REPLIES 4
Community Support Team
Community Support Team

Re: CountIf is not delegable function, any alternative? Custom API?

Hi Brank,

 

From this documentation, we know that CountRows doesn’t support delegation:
https://powerapps.microsoft.com/en-us/tutorials/delegation-overview/

 

I am not sure if Custom API can get all the information, you could have a try with it, please refer to this documentation about how to register Custom APIs in PowerApps:
https://powerapps.microsoft.com/en-us/tutorials/register-custom-api/

 

Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Meneghino
Level 10

Re: CountIf is not delegable function, any alternative? Custom API?

hi @Brank

you can use some caching in the process to improve performance

Highlighted
Brank
Level 8

Re: CountIf is not delegable function, any alternative? Custom API?

We still in the same way, because I don't know how many records I have in my data source, it could be 500 or 1000 or 4000, I understand that you want me to get some cache as creating Collections and store the information in groups of 500 rows.

 

I have to say that I created a web service and I am getting the data using a custom API, I have to say that I can get all the rows I am expecting, this means that it doesn't have the 500 limit. I also can store that data in a collection for use it, this means that I can store 1000 rows in a collection if I get the data from the custom API.

 

With this method all the functions are delegable because I am getting the data with the custom API. (CountRows, CountIf, Collection, etc).

 

The only thing is that I am working right now with Azure App Service and I would like to know if I can start a web server using a physical server such as my local machine.

 

Regards,

Meneghino
Level 10

Re: CountIf is not delegable function, any alternative? Custom API?

Hi @Brank

Using a web service is a much better solution, but I have not had time to try it myself.  I will come back to you for guidance when I will try.  But hopefully Sum, CountRows etc. will be delegated before I need to.  Alternatively if we were able to see views and not just tables, we could create summary views.

Anyway, thanks for letting me know.

Helpful resources

Announcements
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 173 members 4,551 guests
Please welcome our newest community members: