Answered

Filter a Canvas App Gallery based on a selection of a combobox for a many-to-many relationship dataverse tables.

Like (0) Share

Report

Posted on 2 May 2023 17:03:47 by Community Power Pla...

Hi,

I have two tables, Product and Product Category, in Dataverse and there is a many-to-many relationship between two tables. I used the out-of-the-box many-to-many relationship. In the Product table, I have two columns, productName (text type) and productImage (image data type). In Product Category table, I have a column called category (text type).

I created a canvas app with a gallery and a combo box. The gallery is used to list the related products of the selected category in the combo box.

I set the gallery's items property comboBox.Selected.Products. When the canvas app is initially displayed, nothing is shown in the product gallery. If I select a category from the combo box, related products are shown.

My requirements:

The gallery should show all of the products if no category has been selected in the combo box. If a category is selected, show all related products.

How do fill the products in the gallery based on the selected category in the combo box?

I am totally new canvas app.

Thanks

All responses (14)

Answers (1)

Community Power Pla... on 04 May 2023 at 13:36:24

Like (0)

Report

Re: Filter a Canvas App Gallery based on a selection of a combobox for a many-to-many relationship dataverse tables.

Thank you for all your help!
It works now.

In OnChange property of combobox:
Clear(colProduct);
ForAll(
combobox.Selected.Products,
Collect(
colProduct,
{ProductGUID: ThisRecord.Product}
)
);
I set the Items property of the gallery as follows:

If(CountRows(combobox.SelectedItems)=0, Products, Filter(Products, Product in colProduct.ProductGUID))
CNT 10,919 Super User 2025 Season 1 on 03 May 2023 at 18:48:46

Like (0)

Report

Re: Filter a Canvas App Gallery based on a selection of a combobox for a many-to-many relationship dataverse tables.

@Anonymous They are the columns (fields) that you want to display in the Gallery.
Community Power Pla... on 03 May 2023 at 14:01:51

Like (0)

Report

Re: Filter a Canvas App Gallery based on a selection of a combobox for a many-to-many relationship dataverse tables.

For column1 and column2, etc., don't you need to specify a formula for the columns?
Verified answer

CNT 10,919 Super User 2025 Season 1 on 03 May 2023 at 13:27:07

Like (0)

Report
Re: Filter a Canvas App Gallery based on a selection of a combobox for a many-to-many relationship dataverse tables.

@Anonymous Try it out first in the temporary collection to be sure that you have all the required columns (Not only the GUID). Then you can add it to the Items property of the Gallery.

ClearCollect(colTEMP, AddColumns(First(comboBox.SelectedItems).Products), "column1", "column2",........))

Please remember to give a 👍 and accept my solution as it will help others in the future.
Community Power Pla... on 03 May 2023 at 13:13:34

Like (0)

Report

Re: Filter a Canvas App Gallery based on a selection of a combobox for a many-to-many relationship dataverse tables.

Where should I put "AddColumns(First(comboBox.SelectedItems).Products), "column1", "column2",........)"?

Are column1, column2, etc., the display name, schema name, or logical name?
CNT 10,919 Super User 2025 Season 1 on 03 May 2023 at 12:50:37

Like (0)

Report

Re: Filter a Canvas App Gallery based on a selection of a combobox for a many-to-many relationship dataverse tables.

@Anonymous This confirms that the records are being returned but sometimes they may not have all the values that you need to show in the Gallery. You can wrap it around with a AddColumns().

AddColumns(First(comboBox.SelectedItems).Products), "column1", "column2",........)

Please remember to give a 👍 and accept my solution as it will help others in the future.
Community Power Pla... on 03 May 2023 at 12:46:04

Like (0)

Report

Re: Filter a Canvas App Gallery based on a selection of a combobox for a many-to-many relationship dataverse tables.

The collection contains the GUIDs of the related products. Can I create the collection of the products' GUID in the OnStart of the app or the in the OnSelect of the combobox?
CNT 10,919 Super User 2025 Season 1 on 03 May 2023 at 11:18:55

Like (0)

Report
Re: Filter a Canvas App Gallery based on a selection of a combobox for a many-to-many relationship dataverse tables.

@Anonymous Can you collect the following in a collection in the OnSelect of a Button and see if the collection has any records.

ClearCollect(colTEMP, First(comboBox.SelectedItems).Products)

Please remember to give a 👍 and accept my solution as it will help others in the future.
Community Power Pla... on 03 May 2023 at 00:24:03

Like (0)

Report

Re: Filter a Canvas App Gallery based on a selection of a combobox for a many-to-many relationship dataverse tables.

It still doesn't display the product name and image in the gallery when a category is selected.
CNT 10,919 Super User 2025 Season 1 on 02 May 2023 at 23:09:27

Like (0)

Report
Re: Filter a Canvas App Gallery based on a selection of a combobox for a many-to-many relationship dataverse tables.

@Anonymous

Please try the following,

If(CountRows(comboBox.SelectedItems)=0, Products, First(comboBox.SelectedItems).Products)

Please remember to give a 👍 and accept my solution as it will help others in the future.