cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chris08
Frequent Visitor

Filter Multiple SharePoint Lists in a Gallery

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 

2 ACCEPTED SOLUTIONS

Accepted Solutions

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

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

9 REPLIES 9
WarrenBelz
Super User
Super User

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

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
)

 

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

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

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
    )
)

 

@Chris08 ,

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

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

IDName
20John Doe
22Jane Frost

 

Resume Education

IDResumeIDNameDegree
120John DoeAS
220John DoePhD
322Jane FrostMBA
422Jane FrostBS

 

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.

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

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

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,977)