cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PrekshaJain
New Member

Canvas App - Filter Product records of Dynamics on basis of multiselect option set

On product entity, we have a multiselect option set field named 'Tankless Package' (It is a global option set).
In canvas app, I have 2 dropdown fields, one consists of all the options from multiselect option set and second is associated with Product entity. I want to filter the products in canvas app based on value selected in first dropdown. There is no problem if Tankless Package is single option set but as it is a multiselect option set field, we are unable to filter the products. 

For single option set, we are using this query to filter the products:
Filter(Products, Category=[@'Product Type'].Tank)
Here based on category we are filtering the products and this works well. But if we try same formula for multiselect option set field, it doesn't work. So I have used this query:

Filter(Products, CountRows(Filter('Tankless Package', Value = [@'Tankless Package'].'Noritz 25 Year Warranty Condensing'))>0)

This query doesn't give any error just delegation warning. But no output received.

 

If anyone has any suggestion on how to make it successful.

7 REPLIES 7
v-jefferni
Community Support
Community Support

Hi @PrekshaJain ,

 

Could you please share more details about your scenario?

 

Since the Tankless Package field allows multiple selections, how would you like to filter the Product table?

 

Assuming 3 selections(1, 2, 3) are included in the option-sets, selections 1 and 3 are selected in the Dropdown, then:

1\ Exact match:

Only records in the Product table that Tankless Package field exact have 1 and 3 would show up.

 

2\ Include

Records in the Product table that Tankless Package field value include 1 OR 3 would show up, which means 1, 3, 1&3

 

3\ In

Every records that have Tankless Package value in the selected values will show up, which means 1, 1&2, 1&2&3, 1&3, 2&3, 3

 

I think you would like to filter with the exact match method but need confirmation.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi @v-jefferni 

Thanks for your response.

In my case, 

Assuming 3 selections(1, 2, 3) are included in the option-sets, only one selection will be selected in dropdown. Lets say selection 1 is selected in the Dropdown. I want to search all the Records in the Product table that Tankless Package field value include 1.
Suppose I have 3 products A, B and C with A having 1 and 3 tankless package, B having 2 and 3 tankless package and C having 1 and 2 tankless package.
As selection 1 is selected in dropdown, product records should be Product A and Product C.

Hi @PrekshaJain ,

 

If you won't have more than 2000 items in the Products table, then the simple formula (with delegation issue) you could use is:

Filter(Products, TanklessComboBox.Selected.Value in 'Tankless Package'.Value)

 

But if there would be more items in the table, to bypass delegation issue please refer to below video from @RezaDorrani :

https://www.youtube.com/watch?v=44j2VRbdWjk

 

Before 10:50 is single selection Combo box and after is multiple selections Combo box. 

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi @v-jefferni  ,

Thank you for the solution you provided. This query worked:

Filter(Products, TanklessComboBox.Selected.Value in 'Tankless Package'.Value)
Though, I was getting delegation warning and I followed the video you shared which almost solved my problem. But the gallery is not coming the way it is shown in the video

PrekshaJain_0-1621248835440.png

 

The gallery comes like this as you can see (in the attachment) records are not showing from the gallery starting point.

I have used flexible height gallery, grouped the items, set the visible property still it is not the same as shown in video. 
Could you please help me what I am doing wrong here.

Hi @PrekshaJain ,

 

You may see that at 7:30 of the video there are several blank items in the Gallery. In your Gallery, the first item is blank as well, which makes it look odd. So you need:

1\ Delete the Gallery and add a new blank flexible height Gallery, data source set to the Dataverse table Products. (8:40)

 

2\ Modify the Labels in the Gallery to display column values. (8:50)

 

3\ Group all those Labels. (9:40)

 

4\ Set Visible of the group to:

 

TanklessComboBox.Selected.Value in ThisItem.'Tankless Package'.Value

 

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

PaulD1
Super User
Super User

Hi @PrekshaJain 

 

This comes up quite often, so apologies for just copying and pasting from another thread. If you want this to be delegable (and you really do want to try to make everything delegate!) you can use something like the following code. In this example, selecting the States in the multi-select combo adds them to a collection - colStatesSelected. 

 

StateName = First(colStatesSelected)'.State Name', Or

StateName = Coalesce(Last(FirstN(colStatesSelected,2)).'State Name',"X") Or

StateName = Coalesce(Last(FirstN(colStatesSelected,3)).'State Name',"X") Or

... and so on

 

This is taking your list of selected states and turning them into a series of Or expressions which are delegable - the first line of code refers to the first item in the collection, the second to the second item in the collection and so on. As there is no index type function in PowerFx we have to faff around with Last(FirstN... to get the equivalent of an index.

We don't know how many choices the user has made, so we have to coalesce with a value that will not be valid (not a State Name in this case). This could be tricky with an Option Set - I haven't used this technique with Dataverse as it is a platform I avoid whenever possible.

You'll need to add an Or expression up to the maximum number of States you think a user might select (you might want to put some code on the OnChange of the combo to give the user a warning if they have exceeded the number of choices you have code for).

You also have to build your filter so that you only reference the above code if the user has selected one or more State Names, so you may need a series of outer IFs that run different filters depending on what options the user has selected.

Also note that the Odata query can only be so long, so if you code for too many choices, you may fall foul of that limitation.

Of course, all this could be avoided if the IN operator were properly delegated or we could write queries directly against the datasource in its native querying language...

Hi @v-jefferni 

I followed all the steps from the video but still there are blanks in the gallery. Not sure what I am doing wrong here.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,272)