countrows number in gallery field

[francescjp]

Hello:

I have two tables : first contains animal owners, second lists animals. I have gallery with all owners . In an extra field in each row I want to display how many animals owns each person. If I use countRows I have a delegation warning. If I use Flow I can't excute directly in row field. How can I show it without delegation warnings? List will be larger than 5000 rows.

Thank you very much.

[anandm08]

hi @francescjp ,

Create a collection for owners and their animal counts:

First, create a collection that stores the count of animals for each owner. You can do this in the OnStart property of the app or a button click event.

ClearCollect(
    colOwnerAnimalCounts,
    AddColumns(
        GroupBy(
            Animals,
            "OwnerID",
            "OwnerAnimals"
        ),
        "AnimalCount",
        CountRows(OwnerAnimals)
    )
)

Here, Animals is your table with animal records, and OwnerID is the column in the Animals table that relates to the owner.

Join the owners with the animal counts:

Next, you need to join the owners' data with this newly created collection to get the count of animals for each owner.

ClearCollect(
    colOwnersWithAnimalCounts,
    AddColumns(
        Owners,
        "AnimalCount",
        LookUp(colOwnerAnimalCounts, OwnerID = Owners[@ID], AnimalCount)
    )
)

Here, Owners is your table with owner records, ID is the owner ID column in the Owners table, and AnimalCount is the count of animals each owner has.

Bind the gallery to the new collection:

Finally, bind your gallery to colOwnersWithAnimalCounts and use the AnimalCount column to display the count of animals each owner has.

colOwnersWithAnimalCounts

Display the count in the gallery:

In the gallery, you can add a label to display the animal count.

ThisItem.AnimalCount

By using this method, you avoid delegation issues as you are working with a local collection that aggregates the data. This method is efficient for large datasets as long as the initial data load and aggregation fit within the app's limits.

[mmbr1606 · Accepted solution]

hey @francescjp 

you can try this:

ClearCollect(OwnerAnimalsCollection, 
    AddColumns(Owners, "AnimalCount", 
        CountRows(Filter(Animals, OwnerID = Owners.ID))
    )
);

you might need to rename to your needs

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings

[francescjp]

Hello @mmbr1606 

It generates a warning delegation. I need to interact with more than 5000 records.

Thank you very much.

[francescjp]

Hello @anandm08 :

Your choice also generates a delegation warning. Thank you very much.

[DBO_DV]

Hey @francescjp , 

The solution of @mmbr1606 is correct. CountRows will always give a delegation warning since it is not delegable. 

That's why he filtered it first. I assume that one owner has not more than 5000 animals on its own, so you should never run into the limit. 

but the Warning will always show, there is no way to get around that.

The CountRows will work on the filtered Data so probably less than 5000 records.