Answered

Delegation workaround for Distinct()?

(0) Share

Report

Posted on by Cooper01

664

I have a canvas app where data source is Dataverse. Screen in app shows gallery connected to a list containing Products. Each product has a category field and an item description field. On this screen, I want the gallery to show a distinct list of categories and then we'll click into the category to see its items.

The actual categories displayed have some additional conditional logic based on the products themselves (such as quantity in stock), therefore I cannot point this gallery directly to the choices source for the product category.

I know Delegate is one of the functions that won't throw a warning but is not delegable, so

Distinct(Purchases, Category)

won't scale. Does anyone have any other ways to get a distinct value set? Not seeing any options within the Dataverse view and with scalability being the main goal I would like to avoid caching data onstart.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

Pstork1 69,127 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

The only option I can suggest is to use a Power Automate recurrence flow to sync a copy of the distinct values to another table in Dataverse. Then use that table for your gallery. Power Automate doesn't have the same limitations on delegation that Power Apps does. The only other way to do this is to use a delegable function to sync a copy of the full table to a local collection and then do Distinct on that. But the performance on that will be poor.

Was this reply helpful? Yes No
Cooper01 664 on at

Like (0)

Report
Copy link

Link copied!

Thank you for the response @Pstork1 . Not sure yet if this is the solution I will go with but glad to know I am not missing anything simpler.

Was this reply helpful? Yes No
jfsimpson3 2 on at

Like (0)

Report
Copy link

Link copied!

An alternative solution to using powerautomate or throwing the data into a collection during AppStart or on the page's OnVisible property is to leverage a second hidden gallery.

First create two galleries.

In the Items property of the first gallery (the one that is to be hidden aka visible=false), add your data source as well as any filters you'd like to apply.

In the second gallery (which is visible), set the items property to =Distinct('Gallery1Name'.AllItems,'DistinctColumnName').

Was this reply helpful? Yes No
thinh_noz1 13 on at

Like (0)

Report
Copy link

Link copied!

I can confirm that this will not work at all. Gallery only load 500 items each batch and only load more items when you scroll all the way down to bottom of the gallery. So putting Distinct in second gallery only gives you the Distinct records of first 500 items from data source in the first gallery. Rather just put the delegation limit to the max 2000 and using straight Distinct(Your_Data_Source) so you can get Distinct values of 2000 records (in case you expect the number of items will not go over 2000) or using Power Automate than using this 2 galleries solution

Was this reply helpful? Yes No