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?
From this documentation, we know that CountRows doesn’t support delegation:
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:
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.
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.