Solved: Filter Multiple SharePoint Lists in a Gallery

Chris08 · ‎12-01-2022

I've searched and tried to figure this one out but i think I am just not understanding. Can anyone help with this one please?

I have two SharePoint lists with a one-to-many relationship for a Resume Database. The main list (Resume Database) holds the user's information and the secondary list (Resume Education) hold their Education, which could be more than one. The secondary list has a column that holds the ResumeID which is the ID from the main list. I am using a gallery to display everything in the main list but want to be able to filter on their education as well.

Here is the Items on the gallery. My problem is that the Degree and Field should be pulling from the secondary list. Currently I am using a flow to copy this over to the main list but would rather not have to do that.

SortByColumns(
Filter(
AddColumns(
'Resume Database',
"Employee Name",
Name.DisplayName
),
StartsWith(
Name.DisplayName,
txtName_1.Text
),
DD_Status_1.Selected.Value in EmployeeStatus.Value,
DD_Clearance_1.Selected.Value = "All" || DD_Clearance_1.Selected.Value in 'Current Clearance'.Value,
DD_Certs_1.Selected.Value = "All" || DD_Certs_1.Selected.Value in 'Certification/Licenses'.Value,
DD_Agencies_1.Selected.Value = "All" || DD_Agencies_1.Selected.Value in 'Other Agencies'.Value,
DD_Software_1.Selected.Value = "All" || DD_Software_1.Selected.Value in Software.Value,
DD_Specialized_1.Selected.Value = "All" || DD_Specialized_1.Selected.Value in Specialized.Value,
DD_Tools_1.Selected.Value = "All" || DD_Tools_1.Selected.Value in Tools.Value,
DD_Degree_1.Selected.Value = "All" || DD_Degree_1.Selected.Value in Degree,
DD_Field_1.Selected.Value = "All" || DD_Field_1.Selected.Value in Field
),
"Employee Name",
Ascending
)

How would I get the information from the secondary list in this query?? Any help would be appreciated. Thanks

WarrenBelz · ‎12-01-2022

Hi @Chris08 ,

Another reason I do not use Choice field except for multi-values . . . please try the below

With(
   {
      wEd:
      AddColumns(
         'Resume Education',
         "DegreeType",
         'Degree Type'.Value,
         "FieldType",
         'Field of Study'.Value
      )
   },      
   SortByColumns(
      Filter(
        AddColumns(
          'Resume Database',
          "Employee Name",
          Name.DisplayName,
          "Degree",
          LookUp(
            wEd,
            ResumeID = ID
          ).DegreeType,
          "Field",
          LookUp(
            wEd,
            ResumeID = ID
          ).FieldType
        ),
        StartsWith(
          'Employee Name',
          txtName_1.Text
        ) &&
        DD_Status_1.Selected.Value in EmployeeStatus.Value,
        (
          DD_Clearance_1.Selected.Value = "All" || 
          DD_Clearance_1.Selected.Value in 'Current Clearance'.Value
        ) &&
        (
          DD_Certs_1.Selected.Value = "All" || 
          DD_Certs_1.Selected.Value in 'Certification/Licenses'.Value
        ) &&
        (
          DD_Agencies_1.Selected.Value = "All" || 
          DD_Agencies_1.Selected.Value in 'Other Agencies'.Value
        ) &&
        (
          DD_Software_1.Selected.Value = "All" || 
          DD_Software_1.Selected.Value in Software.Value
        ) &&
        (
          DD_Specialized_1.Selected.Value = "All" || 
          DD_Specialized_1.Selected.Value in Specialized.Value
        ) &&
        (
          DD_Tools_1.Selected.Value = "All" || 
          DD_Tools_1.Selected.Value in Tools.Value
        ) &&
        (
          DD_Degree_1.Selected.Value = "All" || 
          DD_Degree_1.Selected.Value in Degree
        ) &&
        (
          DD_Field_1.Selected.Value = "All" || 
          DD_Field_1.Selected.Value in Field
        )
      ),
      "Employee Name",
      Ascending
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

View solution in original post

WarrenBelz · ‎12-05-2022

Hi @Chris08 ,

Probably last throw of the dice as you now have a many-to-many relationship on a field in a large and complex filter, but try this.

SortByColumns(
   Filter(
      AddColumns(
         'Resume Database',
         "Employee Name",
         Name.DisplayName,
         "DegreeType",
         Concat(
            Filter(
               'Resume Education',
               ResumeID = ID
            ),
            'Degree Type'.Value,
            " "
         )
      ),
      StartsWith(
         'Employee Name',
         txtName_1.Text
      ),
      DD_Status_1.Selected.Value in EmployeeStatus.Value,
      (DD_Clearance_1.Selected.Value = "All" || DD_Clearance_1.Selected.Value in 'Current Clearance'.Value),
      (DD_Certs_1.Selected.Value = "All" || DD_Certs_1.Selected.Value in 'Certification/Licenses'.Value),
      (DD_Agencies_1.Selected.Value = "All" || DD_Agencies_1.Selected.Value in 'Other Agencies'.Value),
      (DD_Software_1.Selected.Value = "All" || DD_Software_1.Selected.Value in Software.Value),
      (DD_Specialized_1.Selected.Value = "All" || DD_Specialized_1.Selected.Value in Specialized.Value),
      (DD_Tools_1.Selected.Value = "All" || DD_Tools_1.Selected.Value in Tools.Value),
      (DD_Degree_1.Selected.Value = "All" || DD_Degree_1.Selected.Value in DegreeType)
   ),
   "Employee Name",
   Ascending
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

View solution in original post

WarrenBelz · ‎12-01-2022

Hi @Chris08 ,

You just need to add them as below

SortByColumns(
   Filter(
      AddColumns(
         'Resume Database',
         "Employee Name",
         Name.DisplayName,
         "Degree",
         LookUp(
            'Resume Eduction',
            ResumeID = ID
         ).Degree,
         "Field",
         LookUp(
            'Resume Eduction',
            ResumeID = ID
         ).Field
      ),
      StartsWith(
         'Employee Name',
         txtName_1.Text
      ) &&
      DD_Status_1.Selected.Value in EmployeeStatus.Value,
      (
         DD_Clearance_1.Selected.Value = "All" || 
         DD_Clearance_1.Selected.Value in 'Current Clearance'.Value
      ) &&
      (
         DD_Certs_1.Selected.Value = "All" || 
         DD_Certs_1.Selected.Value in 'Certification/Licenses'.Value
      ) &&
      (
         DD_Agencies_1.Selected.Value = "All" || 
         DD_Agencies_1.Selected.Value in 'Other Agencies'.Value
      ) &&
      (
         DD_Software_1.Selected.Value = "All" || 
         DD_Software_1.Selected.Value in Software.Value
      ) &&
      (
         DD_Specialized_1.Selected.Value = "All" || 
         DD_Specialized_1.Selected.Value in Specialized.Value
      ) &&
      (
         DD_Tools_1.Selected.Value = "All" || 
         DD_Tools_1.Selected.Value in Tools.Value
      ) &&
      (
         DD_Degree_1.Selected.Value = "All" || 
         DD_Degree_1.Selected.Value in Degree
      ) &&
      (
         DD_Field_1.Selected.Value = "All" || 
         DD_Field_1.Selected.Value in Field
      )
   ),
   "Employee Name"
   Ascending
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Chris08 · ‎12-01-2022

Thank you @WarrenBelz I think this is close. Here is what it looks like now. I forgot to mention that the Degree and Field are choice columns. When I place the updated code in, I get this error. "Resume Education failed: Fetching items failed. Possible invalid string in filter query"

SortByColumns(
    Filter(
        AddColumns(
            'Resume Database',
            "Employee Name",
            Name.DisplayName,
            "DegreeType",LookUp('Resume Education',ResumeID = ID).'Degree Type',
            "FieldType",LookUp('Resume Education',ResumeID = ID).'Field of Study'
        ),
        StartsWith(
            Name.DisplayName,
            txtName_1.Text
        ),
        DD_Status_1.Selected.Value in EmployeeStatus.Value,
        DD_Clearance_1.Selected.Value = "All" || DD_Clearance_1.Selected.Value in 'Current Clearance'.Value,
        DD_Certs_1.Selected.Value = "All" || DD_Certs_1.Selected.Value in 'Certification/Licenses'.Value,
        DD_Agencies_1.Selected.Value = "All" || DD_Agencies_1.Selected.Value in 'Other Agencies'.Value,
        DD_Software_1.Selected.Value = "All" || DD_Software_1.Selected.Value in Software.Value,
        DD_Specialized_1.Selected.Value = "All" || DD_Specialized_1.Selected.Value in Specialized.Value,
        DD_Tools_1.Selected.Value = "All" || DD_Tools_1.Selected.Value in Tools.Value,
        DD_Degree_1.Selected.Value = "All" || DD_Degree_1.Selected.Value in DegreeType.Value,
        DD_Field_1.Selected.Value = "All" || DD_Field_1.Selected.Value in FieldType.Value
           ),
    "Employee Name",
    Ascending
)

WarrenBelz · ‎12-01-2022

Hi @Chris08 ,

Another reason I do not use Choice field except for multi-values . . . please try the below

With(
   {
      wEd:
      AddColumns(
         'Resume Education',
         "DegreeType",
         'Degree Type'.Value,
         "FieldType",
         'Field of Study'.Value
      )
   },      
   SortByColumns(
      Filter(
        AddColumns(
          'Resume Database',
          "Employee Name",
          Name.DisplayName,
          "Degree",
          LookUp(
            wEd,
            ResumeID = ID
          ).DegreeType,
          "Field",
          LookUp(
            wEd,
            ResumeID = ID
          ).FieldType
        ),
        StartsWith(
          'Employee Name',
          txtName_1.Text
        ) &&
        DD_Status_1.Selected.Value in EmployeeStatus.Value,
        (
          DD_Clearance_1.Selected.Value = "All" || 
          DD_Clearance_1.Selected.Value in 'Current Clearance'.Value
        ) &&
        (
          DD_Certs_1.Selected.Value = "All" || 
          DD_Certs_1.Selected.Value in 'Certification/Licenses'.Value
        ) &&
        (
          DD_Agencies_1.Selected.Value = "All" || 
          DD_Agencies_1.Selected.Value in 'Other Agencies'.Value
        ) &&
        (
          DD_Software_1.Selected.Value = "All" || 
          DD_Software_1.Selected.Value in Software.Value
        ) &&
        (
          DD_Specialized_1.Selected.Value = "All" || 
          DD_Specialized_1.Selected.Value in Specialized.Value
        ) &&
        (
          DD_Tools_1.Selected.Value = "All" || 
          DD_Tools_1.Selected.Value in Tools.Value
        ) &&
        (
          DD_Degree_1.Selected.Value = "All" || 
          DD_Degree_1.Selected.Value in Degree
        ) &&
        (
          DD_Field_1.Selected.Value = "All" || 
          DD_Field_1.Selected.Value in Field
        )
      ),
      "Employee Name",
      Ascending
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Chris08 · ‎12-01-2022

@WarrenBelz You are the best. This works perfectly. Thank you so much.

Chris08 · ‎12-02-2022

Hi @WarrenBelz , I think i spoke too soon. I noticed today while testing that it the filter was only pulling the first item it found from the secondary list. Example: if I entered a Degree Type of AS first then a Degree Type of MBA second the filter below is only finding AS and not the MBA.

With(
    {
        wED: AddColumns(
            'Resume Education',
            "DegreeType",
            'Degree Type'.Value
        )
    },
    SortByColumns(
        Filter(
            AddColumns(
                'Resume Database',
                "Employee Name",
                Name.DisplayName,
                "DegreeType",
                LookUp(
                    'Resume Education',
                    'Resume Database'[@ID] = ResumeID
                ).'Degree Type'.Value
            ),
            StartsWith(
                'Employee Name',
                txtName_1.Text
            ),
            DD_Status_1.Selected.Value in EmployeeStatus.Value,
            (DD_Clearance_1.Selected.Value = "All" || DD_Clearance_1.Selected.Value in 'Current Clearance'.Value),
            (DD_Certs_1.Selected.Value = "All" || DD_Certs_1.Selected.Value in 'Certification/Licenses'.Value),
            (DD_Agencies_1.Selected.Value = "All" || DD_Agencies_1.Selected.Value in 'Other Agencies'.Value),
            (DD_Software_1.Selected.Value = "All" || DD_Software_1.Selected.Value in Software.Value),
            (DD_Specialized_1.Selected.Value = "All" || DD_Specialized_1.Selected.Value in Specialized.Value),
            (DD_Tools_1.Selected.Value = "All" || DD_Tools_1.Selected.Value in Tools.Value),
            (DD_Degree_1.Selected.Value = "All" || DD_Degree_1.Selected.Value in DegreeType)
        ),
        "Employee Name",
        Ascending
    )
)

WarrenBelz · ‎12-02-2022

@Chris08 ,

You did not say anything about it being multi-choice - I assume the field is multi-choice as well ?

Chris08 · ‎12-05-2022

Sorry @WarrenBelz. I might not have explained this fully. There are two SharePoint lists like below. The main list if Resume Database. The ID for the Resume Database is copied over to Resume Education in the ResumeID column. This is where the one-to-many relationship is. Right now the code is only pulling back the first Degree and not all of them for the one person. The Degree field is a choice field (dropdown field not multiple select checkbox.)

Resume Database

ID	Name
20	John Doe
22	Jane Frost

Resume Education

ID	ResumeID	Name	Degree
1	20	John Doe	AS
2	20	John Doe	PhD
3	22	Jane Frost	MBA
4	22	Jane Frost	BS

I think i need a ForAll in this code but learning this as i go. Here is what i have so far but it is still only pulling the first degree per person and not all degree's per person.

With(
    {
        wED: AddColumns(
            'Resume Education',
            "DegreeType",
            'Degree Type'.Value
        )
    },
    SortByColumns(
        Filter(
            AddColumns(
                'Resume Database',
                "Employee Name",
                Name.DisplayName,
                "DegreeType",
                ForAll(wED,LookUp(
                    'Resume Education',
                    ResumeID = 'Resume Database'[@ID]
                ).'Degree Type'.Value)
            ),
            StartsWith(
                'Employee Name',
                txtName_1.Text
            ),
            DD_Status_1.Selected.Value in EmployeeStatus.Value,
            (DD_Clearance_1.Selected.Value = "All" || DD_Clearance_1.Selected.Value in 'Current Clearance'.Value),
            (DD_Certs_1.Selected.Value = "All" || DD_Certs_1.Selected.Value in 'Certification/Licenses'.Value),
            (DD_Agencies_1.Selected.Value = "All" || DD_Agencies_1.Selected.Value in 'Other Agencies'.Value),
            (DD_Software_1.Selected.Value = "All" || DD_Software_1.Selected.Value in Software.Value),
            (DD_Specialized_1.Selected.Value = "All" || DD_Specialized_1.Selected.Value in Specialized.Value),
            (DD_Tools_1.Selected.Value = "All" || DD_Tools_1.Selected.Value in Tools.Value),
            (DD_Degree_1.Selected.Value = "All" || DD_Degree_1.Selected.Value in DegreeType)
        ),
        "Employee Name",
        Ascending
    )
)

Thank you for all your help with this.

WarrenBelz · ‎12-05-2022

Hi @Chris08 ,

Probably last throw of the dice as you now have a many-to-many relationship on a field in a large and complex filter, but try this.

SortByColumns(
   Filter(
      AddColumns(
         'Resume Database',
         "Employee Name",
         Name.DisplayName,
         "DegreeType",
         Concat(
            Filter(
               'Resume Education',
               ResumeID = ID
            ),
            'Degree Type'.Value,
            " "
         )
      ),
      StartsWith(
         'Employee Name',
         txtName_1.Text
      ),
      DD_Status_1.Selected.Value in EmployeeStatus.Value,
      (DD_Clearance_1.Selected.Value = "All" || DD_Clearance_1.Selected.Value in 'Current Clearance'.Value),
      (DD_Certs_1.Selected.Value = "All" || DD_Certs_1.Selected.Value in 'Certification/Licenses'.Value),
      (DD_Agencies_1.Selected.Value = "All" || DD_Agencies_1.Selected.Value in 'Other Agencies'.Value),
      (DD_Software_1.Selected.Value = "All" || DD_Software_1.Selected.Value in Software.Value),
      (DD_Specialized_1.Selected.Value = "All" || DD_Specialized_1.Selected.Value in Specialized.Value),
      (DD_Tools_1.Selected.Value = "All" || DD_Tools_1.Selected.Value in Tools.Value),
      (DD_Degree_1.Selected.Value = "All" || DD_Degree_1.Selected.Value in DegreeType)
   ),
   "Employee Name",
   Ascending
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Chris08 · ‎12-05-2022

@WarrenBelz Thank you so much for all the help. Happy to report that this worked perfectly.

Filter Multiple SharePoint Lists in a Gallery

Helpful resources

Power Apps News & Announcements

Introducing the Community Calls Conversations

Power Apps Community Blog