Filtering of a Gallery (with a Many to One relationship)

(0) Share

Report

Posted on by CraigPezet

Hello,

I have a Gallery that the Dataverse tables that populate it has a Many to One relationship and requires filtering.

Gallery1 (galProjects) contains a list of projects, and is filterable based on the Dataverse view so no issues there.

Gallery2 (galTasks) contains a list of items that fall under that are associated with that project by project code in the relationship and as such the data source is galProjects.Selected.'Project Items' which produces the list of initial items correctly.

However, this list requires filtering based of the values within this Project Items table, specifically a "Task Completed" Yes/No option and a Multiple Selection option for "Initial Check" (With the options Non-Issue, Team 1, Team2, Team 3). The filter would ideally apply so those entries where the Task Completed is set to "Yes" and the Initial Check is set to "Non-Issue" are excluded.

While filtering a Gallery directly by these values is not an issue, attempting to filter a gallery with a the above formula data source is just causing errors with whatever I try.

Any help would be greatly appreciated.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (1)

AhmedSalih 6,678 Moderator on at

Like (1)

Report

Hello, @CraigPezet, lets try one step at a time and see what error you get:
Filter(galProjects.Selected.'Project Items', "Task Completed"=TaskCompletedChoice.Yes, Initial Check <> Initial CheckChoice.Non-Issue)

Regards,

Ahmed

If my reply helped you, please give a 👍. And if it has solved your issue, please consider a 👍 & Accepting it as the Solution to help other members of the community find it more.
My Blog: www.powerplatformplace.com

Was this reply helpful? Yes No
CraigPezet 44 on at

Like (0)

Report

Hi @AhmedSalih,

The first part of this worked as expected but the second part proved to throw up some errors. Most likely as the Initial Check is a multi value combobox field. The current workaround I have is below:

Filter(galProjects.Selected.'Project Items',
(Toggle1.Value || 'Task Completed'='Task Completed (Project Items)'.No && Not('Initial Check' in NoIssueFilter.SelectedItems))

Where NoIssueFilter was a Input ComboBox with the Items set to Choice('Project Items'.'Initial Check') and the default value set to Last(Choices('Project Items'.'Initial Check')) with it's display mode set to view and then hidden with visibility set to false.

While this does the job, with the filter applied only when the toggle control is set to false, there is still the slight issue that there is a delegation warning as the final filter condition is making use of the in Not and In functions and as such is non-delegatable and being applied in app and ideally I would like it applied in the dataverse to prevent any future issues.

Was this reply helpful? Yes No
Verified answer

AhmedSalih 6,678 Moderator on at

Like (1)

Report

@CraigPezet, try this:

In the Toggle OnChange = ClearCollect('CollectionName',Filter(galProjects.Selected.'Project Items',
(Toggle1.Value || 'Task Completed'='Task Completed (Project Items)'.No))

Then, Where you have your Filter function, put this: Filter(CollectionName, Not('Initial Check' in NoIssueFilter.SelectedItems))

Regards,

Ahmed
If my reply helped you, please give a 👍. And if it has solved your issue, please consider a 👍 & Accepting it as the Solution to help other members of the community find it more.
My Blog: www.powerplatformplace.com

Was this reply helpful? Yes No