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

[AJ_Z]

thanks @RandyHayes  for replying am i right in my understanding that this means the filter cannot show more than 2000 rows?

also @Ryan147 try Onselect of gallery Set(VarGallerySelected,ThisItem) and reference VarGallerySelected elsewhere in the app to see if that makes a difference. regarding the performance hit i feel that is to be expected sadly based on the way we have the data. A more future proof solution would be to try and combine both sharepoint lists into one is that something that works for you or do you need them seperate.

[RandyHayes]

@AJ_Z 

Any function in PowerApps will only be able to return the record limit setting (2000 max).  You can delegate criteria to millions of records, but if your results from that are more than 2000, then you will only get 2000...although they will be results you want.  This is why record limit and delegation are two totally different things.

@Ryan147 

The Forms will all break because you are referencing the Gallery record - which now has a new schema.

Setting a variable to that record will not make a difference.

The column needs to be dropped.  So your formula would be:

DropColumns(
  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'.Value = CGSearchJobRole2Dropdown.Selected.Title)
    ),
    "Surname", Ascending,
    "Forename", Ascending
  ),
  "_roleinfo"
)

As for performance - yes, this is not going to be the most speedy process as it has a lot of looking up to do, plus the LookUp is happening for each record.  

If you've not already changed your formula to my last suggestion, the above formula should be implemented as it will reduce the slowness.  Before the formula was doing a lookup on each record - regardless, and then filtering.  The above does a pre-filter and then only does the lookup on the resultant records.

If that still seems so slow, then the next question would be - how many records are in the Contacts Role list and how large is it expected to grow?

[Ryan147]

Thanks @AJ_Z 

And Thanks @RandyHayes For the detailed explanation.

I will just create a popup error if the return is 1999 or more, stating that its the maximum and people need to increase there filtering. Although i don't envisage post filtered results to be that high.

The overall amount of Roles in the app i expect to be below 2000, but it wont be too far off and likely to exceed this number in the future.

I updated the formula to your newly recommended one, and got a very strange error.

(IsBlank(CGSearchRegionDropdown.Selected.Title) || Region.Value = CGSearchRegionDropdown.Selected.Title) &&
(IsBlank(CGSearchOrganisationDropdown.Selected.Title) || Organisation.Value = CGSearchOrganisationDropdown.Selected.Title)

Its strange that it doesn't like these, when they were ok in the previous iterations, i wonder if this is linked to the same issue as the variable?

If i cant call that variable anymore, can i change the variable or use something else, its used to make update the item in my forms as toggle some buttons visibility.

Thanks for your continued support with this issue guys. I had finished the rest of the app already, so just need to get this filter working without breaking what I've done, can then have a celebratory beer!

[RandyHayes]

@Ryan147 

Sorry, in my haste to alter the formula, I missed a part.  Should be:

DropColumns(
  SortByColumns(
    Filter(
        AddColumns(
            Filter('Contacts 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'.Value = CGSearchJobRole2Dropdown.Selected.Title)
    ),
    "Surname", Ascending,
    "Forename", Ascending
  ),
  "_roleinfo"
)

Yeah, for performance...if the Roles list was below 2000, then there are some tricks to increase performance of the above formula.  But if not, then performance will be an issue with such large lists.

You mentioned a variable ("If i cant call that variable anymore...what") is that about?

[Ryan147]

@RandyHayes 

That's ok, i'm not in a rush to fix this today.

Formula works great thanks, still pretty slow, but i may be able to optimise this by reducing what's displayed in the gallery. I'll wait and see how it handles the full data set and how close it is to 2000 before looking into further optimisations.

I think I've fixed the issue with Variable that displays the selected record in a form on another screen now, and its a none issue. 

Just got to test out a few things and can then hopefully tick this off as resolved (for now):)

Thanks for all your help guys, first time I've used the community and its been brilliant!

[RandyHayes]

@Ryan147 

It is one of the trade-offs in PowerApps (any app for that matter).  When you deal with a lot of data and looking up information for all of it, it is slow.  It has to be done, but you can trade-off where it is done.

One optimization you can consider is the following formula.  

With({_roles: 'Contacts Role'},
  DropColumns(
    SortByColumns(
      Filter(
        AddColumns(
            Filter('Contacts Person',
                (IsBlank(CGSearchRegionDropdown.Selected.Title) || Region.Value = CGSearchRegionDropdown.Selected.Title) &&
                (IsBlank(CGSearchOrganisationDropdown.Selected.Title) || Organisation.Value = CGSearchOrganisationDropdown.Selected.Title)
            ) As _Person,
            "_roleinfo", LookUp(_roles, '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'.Value = CGSearchJobRole2Dropdown.Selected.Title)
    ),
    "Surname", Ascending,
    "Forename", Ascending
   ),
   "_roleinfo"
  )
)

In the above, the entire 'Contacts Role' datasource is loaded into the _roles scoped variable.  That is then used on the LookUp of the add columns.  

The difference is that, doing individual lookups to the datasource will be slow as each has to occur on each record from the inner filter.  Loading all the role records into local memory (the variable) ahead of time will take time, but will be faster than lookup after lookup.  Now, referencing the LookUp to that variable instead of the datasource, will be faster.

Results may vary though depending on sizes of data.  AND, this would only work in the scenario where the 'Contacts Role' datasource is less than 2000 records.

[Ryan147]

@RandyHayes Thanks for the above alternative.

Whilst doing some testing unfortunately i have found a fundamental issue with the previous solution and therefore presumably this one also.

The issue is that each person has more than role, hence why its set up as two related lists and i mentioned in my original post that they were in a one to many relationship. So the issue is that when filtering, its just looking at the top role, not across all the roles that person has. 

I want it to retrieve all the people who have the selected values in Region, Organisation, Specialty, School, etc, in any of their roles.

And i thought we'd cracked it sorry? Any ideas?

[RandyHayes]

@Ryan147 

Ah yes, and if you mentioned that earlier, my apology for not noticing.

Your formula would change then to the following:

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

Definitely not normally delegated without the formula provided, but the above is delegable.

[Ryan147]

@RandyHayes 

Apologies for the delay in responding, i've been taking a break over Christmas. Thanks for taking another look at it, i should have made it clearer at the time.

For some reason its not liking the following elements in red?

(IsBlank(CGSearchSpecialtyDropdown.Selected.Title) || CGSearchSpecialtyDropdown.Selected.Title in _roleinfo.Specialty) &&
(IsBlank(CGSearchSchoolDropdown.Selected.Title) || CGSearchSchoolDropdown.Selected.Title in _roleinfo.School) &&
(IsBlank(CGSearchHEEJobRoleDropdown.Selected.Title) || CGSearchHEEJobRoleDropdown.Selected.Title in _roleinfo.'HEE Job Role') &&
(IsBlank(CGSearchHEESubJobRoleDropdown.Selected.Title) || CGSearchHEESubJobRoleDropdown.Selected.Title in Concat(_roleinfo.'HEE Sub Job Role'.Value))

Regarding the Title i've tried switching it out for Value, also adding .Value to the end of _roleinfo.Specialty but to no avail. The error given is  any ideas why this is "Powerapps cant convert this type of data to a record", any ideas what other type it should be?

Also for the Concat(_roleinfo.'HEE Sub Job Role'.Value)) i switched out your comma as assumed it was a typo, bit its still producing an error as there are not enough arguments.

I've also tried making those 4 arguments the same, ie ie with and without the concat but to no avail.

Would you mind having another look at this bit for me? Struggling to het my head round it TBH

Many Thanks Ryan

[Ryan147]

@RandyHayes 

Ignore my previous message, after a bit of fiddling i think i may have got it to work, i'm going to run a few more tests tomorrow to be sure, here's what it looks like now. If you notice any major issues let me know.

Again thanks for all your help, i definitely couldn't have done this on my own, i cant believe they don't have a simpler method of doing something seemingly so fundamental on an app.

Loving this community  

With({_roles: 'Contacts Role'},
  DropColumns(
    SortByColumns(
      Filter(
        AddColumns(
            Filter('Contacts Person',
                (IsBlank(CGSearchRegionDropdown.Selected.Title) || Region.Value = CGSearchRegionDropdown.Selected.Title) &&
                (IsBlank(CGSearchOrganisationDropdown.Selected.Title) || Trust.Value = CGSearchOrganisationDropdown.Selected.Title)
            ) As _Person,
            "_roleinfo", Filter(_roles, 'ID Person' = _Person.ID)
        ),
        (IsBlank(CGSearchSpecialtyDropdown.Selected.Title) || CGSearchSpecialtyDropdown.Selected.Title in Concat(_roleinfo.Specialty,Specialty.Value)) &&
        (IsBlank(CGSearchSchoolDropdown.Selected.Title) || CGSearchSchoolDropdown.Selected.Title in Concat(_roleinfo.School,School.Value)) &&
        (IsBlank(CGSearchJobRole1Dropdown.Selected.Title) || CGSearchJobRole1Dropdown.Selected.Title in Concat(_roleinfo.'JobRole1','JobRole1'.Value)) &&
        (IsBlank(CGSearchJobRole2Dropdown.Selected.Title) || CGSearchJobRole2Dropdown.Selected.Title in Concat(_roleinfo.'JobRole2','JobRole2'.Value))
    ),
    "Surname", Ascending,
    "Forename", Ascending
   ),
   "_roleinfo"
  )
)