cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CraigPezet
Regular Visitor

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions

@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

 

View solution in original post

3 REPLIES 3
AhmedSalih
Super User
Super User

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

CraigPezet
Regular Visitor

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.

@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

 

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (2,862)