Answered

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

(1) Share

Report

Posted on by OldDogNewTricks

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

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.

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.

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

poweractivate 11,078 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
OldDogNewTricks on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No