Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

How to filter a list based on value from look-up table

(1) ShareShare
ReportReport
Posted on by

I'm using SharePoint lists.  I've got a list of tickets, cleverly named TicketList that includes columns that represent their:

  • Status (Named TaskStatus (lookup column))
  • Task owner (Named AssignedTO (person field))

OldDogNewTricks_0-1692733582509.png

 

The TaskStatus column is the look-up that references another List named StatusList.  StatusList includes the following columns:

  • Title (Text and includes Assigned, Not Started, Waiting for response, Closed, Cancelled) 
  • Active Yes/No (Everything is Yes now)
  • Open Yes/No.  All are set to Yes except for the Closed and Cancelled values.

OldDogNewTricks_1-1692733648542.png

 

My screen has a dropdown with the options of Open or Closed.

 

I want to filter items from TicketList based on who is logged in against the AssignedTO value AND the Open (Y/N) value from TaskStatus.

 

The following filter works against the AssignedTo column (If(OpenCloseDropdown.SelectedText.Value="Open",Filter(TicketList,User().FullName=AssignedTO.Value))),

but I can't figure out how to link back to the TaskStatus Open column for the remainder of the filter.

 

 

The dropdown is named OpenCloseDropdown.

 

The following filter also works but does not accomplish everything I want:

If(OpenCloseDropdown.SelectedText.Value="Open",Filter(TicketList,User().FullName=AssignedTO.Value And TaskStatus.Value = "Assigned"))

 

Thank you in advance for any suggestions.

  • Re: How to filter a list based on value from look-up table

    Fantastic and Thank You!  I'm brand new to PowerApps, and this is the type of functionality I knew was there but couldn't figure out on my own.

  • Verified answer
    poweractivate Profile Picture
    poweractivate 11,078 on at
    Re: How to filter a list based on value from look-up table

    You can accomplish this by using a combination of Filter and LookUp

    Here's a formula that should work for your scenario:

    If(
     OpenCloseDropdown.SelectedText.Value = "Open",
     Filter(
     TicketList,
     User().FullName = AssignedTO.Value,
     LookUp(StatusList, Title = TaskStatus.Value).Open = "Yes"
     )
    )
    

    This formula performs the following:


    Checks if the dropdown selection is "Open."
    Filters the TicketList based on the logged-in user's AssignedTO value.
    Uses LookUp to find the corresponding TaskStatus from the StatusList, and checks if the Open column value is "Yes."


    Make sure to adjust the formula if the Open column in the StatusList is a boolean rather than text. If it's a boolean, you should use

    LookUp(StatusList, Title = TaskStatus.Value).Open = true

     instead.

     

    See if it helps @OldDogNewTricks 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,666

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,996

Leaderboard