Filter a Gallery based on columns in a related list

[Ryan147]

Hi All

My first post on here so if i'm missing any info let me know.

Background – I’m building a Contacts app for our organisation, the data is in 2 sharepoint lists, “Contacts Person” and “Contacts Role”, they therefore have a 1 to many relationship, with each role being linked to the person via an ID. I have a gallery containing the “Contacts Person” data which I then want to filter based upon “lookup” field data in both the “Contacts Person” list and the “Contacts Role” list via a series of dropdown boxes. I have been able to filter on the data from the “Contacts Person” list but not managed to integrate the filter from the “Contacts Role” list.

Sharepoint lists below (Data I want to filter the gallery on are in Bold)

“Contacts Person” List = ID, Title, Surname, Forename, Email, Region, Organisation,

“Contacts Role” List = ID, Person ID, Specialty, School, Job Role, Job Role 2

My current Formula works but only uses “Region” and “Organisation” from the “Contact Person” list, I therefore need to add the filters for “Specialty”, “School”, “Job Role” and  “Job Role 2” from the Roles list. Each "Contact Role" filter will need to return the person if any of their job roles contain the selected value) 

SortByColumns(

Filter(

'Contacts Person',

IsBlank(CGSearchRegionDropdown.Selected.Title) ||

IsEmpty(CGSearchRegionDropdown.Selected) ||

Region.Value=CGSearchRegionDropdown.Selected.Title,

IsBlank(CGSearchOrganisationDropdown.Selected.Title) ||

IsEmpty(CGSearchOrganisationDropdown.Selected) ||

Organisation.Value =CGSearchOrganisationDropdown.Selected.Title

        ),

"Surname",Ascending,"Forename",Ascending)

I’ve tried doing it in quite a few ways, but can’t seem to integrate the filter using both sources at once. It seems like something which people would want to do often so I’m hoping someone can help.

Thanks for reading

Ryan

[RandyHayes]

@Ryan147 

There are a couple ways to go about this depending on the amount of records you have to deal with.  Here is one method.

SortByColumns(
    Filter(
        AddColumns('Contacts Person' As _person,
            "_roleInfo", LookUp('Contacts Role', PersonID = _person.ID)
        ),
        (IsBlank(CGSearchRegionDropdown.Selected.Title) || Region.Value = CGSearchRegionDropdown.Selected.Title) &&
        (IsBlank(CGSearchOrganisationDropdown.Selected.Title) || Organisation.Value = CGSearchOrganisationDropdown.Selected.Title) &&
        (IsBlank(SpecialtyDropdown.Selected.Specialty) || _roleInfo.Specialty = SpeacialtyDropdown.Selected.Specialty) &&
        (IsBlank(School.Selected.School) || _roleInfo.School = School.Selected.School) &&
        (IsBlank(JobRole1.Selected.JobRole1) || _roleInfo.JobRole1 = JobRole1.Selected.JobRole1) &&
        (IsBlank(JobRole1.Selected.JobRole2) || _roleInfo.JobRole2 = JobRole1.Selected.JobRole2)

    ),
    "Surname", Ascending,
    "Forename", Ascending
)

I hope this is helpful for you.

[Ryan147]

Thanks for the speedy reply @RandyHayes 

I'm struggling to replicate this unfortunately:-

Can you clarify what _roleInfo should be replaced with if anything, if my understanding is correct, this is simply the title of a new column being added to the table?

Can you clarify what _Person should be replaced with if anything?

Also i'm presuming that in the bottom three filters, the text in red should be title as in the higher 2 filters? (i've obviously replaced the elements in blue with the dropdown box.

(IsBlank(School.Selected.School) || _roleInfo.School = School.Selected.School) &&

I've also been working really hard on the rest of the app to ensure all formulas are delegable, which looking at the formula this wont be, is there a delegable solution you are aware of?

Your help is much appreciated

Many Thanks Ryan

[AJ_Z]

You are correct in @RandyHayes 's elegant solution these (_roleInfo and _Person ) are simply titles of the columns added 🙂

These seem to be the names of dropdowns /combo boxes:
SpecialtyDropdown
School.Selected.School
JobRole1

they are probably whats causing your error as you needed to add the filters for “Specialty”, “School”, “Job Role” and “Job Role 2” . so you will need to add them to your app and ideally call them the same name. these reference 'Contacts Role'

for the second part of your question that depends on your data but based on what you have above it might be. It would just need to match up with the column names in the datasource 'Contacts Role'

@RandyHayes 's solution looks like it will meet your needs it is in fact more elegant than the suggestion i was going to make so i will probably look to do it this way for the future myself. I also tested it my end with dummy data and it worked 🙂

p.s. i edited this reply as you asked something completely different to what i first answered

[Ryan147]

So this is what i ended up with in the end, it resulted in no errors or delegation warnings

SortByColumns(
    Filter(
        AddColumns('Contacts Person'As _Person,
            "_roleinfo", LookUp('Contacts Role','ID Person' = _Person.ID)
        ),
        (IsBlank(CGSearchRegionDropdown.Selected.Title) || Region.Value = CGSearchRegionDropdown.Selected.Title) &&
        (IsBlank(CGSearchOrganisationDropdown.Selected.Title) || Organisation.Value = CGSearchOrganisationDropdown.Selected.Title) &&
        (IsBlank(CGSearchSpecialtyDropdown.Selected.Title) || _roleinfo.Title = CGSearchSpecialtyDropdown.Selected.Title) &&
        (IsBlank(CGSearchSchoolDropdown.Selected.Title) || _roleinfo.Title = CGSearchSchoolDropdown.Selected.Title) &&
        (IsBlank(CGSearchJobRole1.Selected.Title) || _roleinfo.Title = CGSearchJobRole1.Selected.Title) &&
        (IsBlank(CGSearchJobRole2Dropdown.Selected.Title) || _roleinfo.Title = CGSearchJobRole2Dropdown.Selected.Title)

    ),
    "Surname", Ascending,
    "Forename", Ascending

However it unfortunately did not work, the Region and Organisation elements worked ok, but the Specialty, School, JobRole1 and JobRole2 elements did not.

It also caused the rest of my app to stop working, presumably because they no longer recognise the selected person within the gallery.

[AJ_Z]

I think i see the problem:

First question in these dropdowns what is the Items? 

(IsBlank(CGSearchSpecialtyDropdown.Selected.Title) || _roleinfo.Title = CGSearchSpecialtyDropdown.Selected.Title) &&
        (IsBlank(CGSearchSchoolDropdown.Selected.Title) || _roleinfo.Title = CGSearchSchoolDropdown.Selected.Title) &&
        (IsBlank(CGSearchJobRole1.Selected.Title) || _roleinfo.Title = CGSearchJobRole1.Selected.Title) &&
        (IsBlank(CGSearchJobRole2Dropdown.Selected.Title) || _roleinfo.Title = CGSearchJobRole2Dropdown.Selected.Title)

is it Contacts Role?

if so i think it should not be .Title unless you have done something different to the item in contacts role? let me know what is in there and i can take a look

[Ryan147]

Thanks @AJ_Z for helping with @RandyHayes solution

The items in all the dropdowns are derived from separate SharePoint lists (rather than the main 'Contacts Person' list, so all my lists are.

Main Lists:-

• Contacts Person (where the people are stored)
• Contacts Role (where all the roles for the people are stored)

Lookup (dropdown) lists:-

• Contacts Region
• Contacts Organisation
• Contacts Specialty
• Contacts School
• Contacts Jobrole1
• Contacts Jobrole2

[AJ_Z]

In that case try this:
let me know how it goes

SortByColumns(
    Filter(
        AddColumns('Contacts Person'As _Person,
            "_roleinfo", LookUp('Contacts Role','ID Person' = _Person.ID)
        ),
        (IsBlank(CGSearchRegionDropdown.Selected.Title) || Region.Value = CGSearchRegionDropdown.Selected.Title) &&
        (IsBlank(CGSearchOrganisationDropdown.Selected.Title) || Organisation.Value = CGSearchOrganisationDropdown.Selected.Title) &&
        (IsBlank(CGSearchSpecialtyDropdown.Selected.Title) || _roleinfo.Specialty = CGSearchSpecialtyDropdown.Selected.Title) &&
        (IsBlank(CGSearchSchoolDropdown.Selected.Title) || _roleinfo.School = CGSearchSchoolDropdown.Selected.Title) &&
        (IsBlank(CGSearchJobRole1.Selected.Title) || _roleinfo.'Job Role' = CGSearchJobRole1.Selected.Title) &&
        (IsBlank(CGSearchJobRole2Dropdown.Selected.Title) || _roleinfo.'Job Role 2' = CGSearchJobRole2Dropdown.Selected.Title)

    ),
    "Surname", Ascending,
    "Forename", Ascending

[RandyHayes]

@Ryan147 

As you and @AJ_Z have been discussing (and I'm replying to your first response here)...the _roleInfo is the name of the column that is created.  You would not replace that. Also _Person is the name of the table result of the datasource.  Since both lists have ID columns, we need to be specific on the match between the two.  The As operator gives us the chance to name the results and thus be specific on which is which.  Nothing should change in that part.

The bottom three filters were all suggestions - since I didn't know how you were populating the Items of your dropdowns, it was just an assumption.

Your formula is not delegable only because of the AddColumns function (which is not delegable).  Unfortunately, the formula editor will not make you aware that it is not delegable.  But why would it be?  Everything in PowerApps is restricted by the record limit.  AddColumns is not doing any filtering, so there is nothing to delegate.  Thus it is just restricted to record limit (record limit and delegation are two different things!)

The solution to making that fully delegable is to provide something to delegate.  In that case it would be a filter on the datasource initially.  That would be yet to be determined in your scenario.  You certainly would have to get the other list record to perform your lookup, but you can change the formula to the following and use the initial list to filter a delegable criteria and then perform the other operations after that.

The formula would look like this:

SortByColumns(
    Filter(
        AddColumns(
            Filter('Contacts Person'As _Person,
                (IsBlank(CGSearchRegionDropdown.Selected.Title) || Region.Value = CGSearchRegionDropdown.Selected.Title) &&
                (IsBlank(CGSearchOrganisationDropdown.Selected.Title) || Organisation.Value = CGSearchOrganisationDropdown.Selected.Title)
            ) As _Person,
            "_roleinfo", LookUp('Contacts Role','ID Person' = _Person.ID)
        ),
        (IsBlank(CGSearchSpecialtyDropdown.Selected.Title) || _roleinfo.Specialty = CGSearchSpecialtyDropdown.Selected.Title) &&
        (IsBlank(CGSearchSchoolDropdown.Selected.Title) || _roleinfo.School = CGSearchSchoolDropdown.Selected.Title) &&
        (IsBlank(CGSearchJobRole1.Selected.Title) || _roleinfo.'Job Role' = CGSearchJobRole1.Selected.Title) &&
        (IsBlank(CGSearchJobRole2Dropdown.Selected.Title) || _roleinfo.'Job Role 2' = CGSearchJobRole2Dropdown.Selected.Title)
    ),
    "Surname", Ascending,
    "Forename", Ascending
)    

The above would be delegable.  However, like every formula in PowerApps, it is still restricted to the record limit.

What is happening in the above is that we first Filter the Contacts Person list with delegable criteria.  That will return a table to which we then AddColumns.  At that point delegation is not an issue because the first Filter has already returned delegated results.  From this point forward in the formula, delegation is no concern.

By the way, I took the most recent suggested formula from @AJ_Z for the above and modified it. So, not sure if the other points you were working on in it are accurate.

[Ryan147]

Excellent, it's working, i just had to add to change them to 

(IsBlank(CGSearchJobRole2Dropdown.Selected.Title) || _roleinfo.'Job Role 2'.Value = CGSearchJobRole2Dropdown.Selected.Title)

It's pretty slow though and i only have limited amount of test data in at the moment. So depending how it goes when rolled out, i may be back for a less processer intensive solution.

It has however caused all my forms to break. I have a button on each row of the gallery, when pressed this sets a variable Set(VarParentItem,CGGallery.Selected that is then read by the 'item' in the form, and the button takes them too the form. I cant understand why this is now broken? Why is it now unable to store and read what item is selected?

The error being received is "Invalid formula. Expected a value compatible with 'DataSource'."

This is even the case for a form that looks up from a different gallery (albeit contains similar data)