web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Trying to Filter galle...
Power Apps
Answered

Trying to Filter gallery using a Person field in another list

(0) ShareShare
ReportReport
Posted on by Paolo666 51

I have a Power App which is using 2 lists, 'Projects' and 'Shocs' (This is a checklist with tasks to complete). 'Projects' contains the Oracle project number, Project manager, due date etc.  A project will have a checklist (Shoc) linked to it and the checklist looks up the project info (project name, project number etc.) from the related Project record but, due to SharePoint limitations (Solved: Lookup Person columns from another SharePoint List - Power Platform Community (microsoft.com)), it can't have a column that looks up the Project manager as that is a people picker. We also cannot use a text field which we write the Project Manager's name to on creation as the Project Manager can change and needs to dynamically update throughout the app. 

 

I have a gallery which shows all the current checklists (shocs) and I currently filter by 'status' using a dropdown and also by project number using a TextInput box.  This works fine however, the end user now wants to filter by Project Manager instead of Project Number but I cannot seem to get this to work.

In the gallery, the Project manager is shown in a text label using the code below and it displays the Project Manager correctly.  I've tried adding the code below in to the filter in place of the Oracle project number but I keep getting errors.

 

LookUp(Projects,'Oracle Project Number'=ThisItem.'Oracle Project Number'.Value,'Project Manager'.DisplayName)

 

 

Here is the original code that filters by status and Oracle Project Number:

 

SortByColumns(
 AddColumns(
 Filter(
 Shocs,
 DropdownSHOCStatus1.Selected.Value = "All" && TextSearchBox1_3.Text in 'Oracle Project Number'.Value || 'SHOC Status'.Value = DropdownSHOCStatus1.Selected.Value && TextSearchBox1_3.Text in 'Oracle Project Number'.Value
 ),
 "OracleProjectNumber",
 ThisRecord.'Oracle Project Number'.Value
 ),
 "ID",
 SortOrder.Ascending
)

 

Here is an image of the code I've tried to show the problem areas.  I've tried variations of this without success.  Can anyone suggest a way I can filter this gallery on the Project Manager value instead of the Oracle Project Number?

PM Lookup code not working.png

I hope this makes sense but let me know if more information is needed.

Thanks.

Categories:
Building Power Apps
  • Ami K Profile Picture
    Ami K 15,689 Super User 2024 Season 1 on at

    @Paolo750 ,

     

    Try:

     

    Sort(
 Filter(
 AddColumns(
 Shocs,
 "_ProjectManager",
 LookUp(
 Projects,
 'Oracle Project Number' = Value('Oracle Project Number'.Value),
 'Your People Field'.DisplayName
 )
 ),
 DropdownSHOCStatus1.Selected.Value = "All" && TextSearchBox1_3.Text in 'Oracle Project Number'.Value || 'SHOC Status'.Value = DropdownSHOCStatus1.Selected.Value && TextSearchBox1_3.Text in 'Oracle Project Number'.Value,
 CountRows(YourPeopleComboBox.SelectedItems) = 0 || IsBlank(YourPeopleComboBox.SelectedItems) || _ProjectManager = YourPeopleComboBox.Selected.DisplayName
 ),
 "ID",
 SortOrder.Ascending
)

     

     

    Note despite the absence of a warning, the AddColumns function is not delegable.

     

    Also note the below line, which is not delegable either. I would recommend you drop the Lookup field in the Shoc List to the SharePoint ID in the Projects list, and instead replace with a Single Line Text or Number (I have yet to see any need to use SharePoint Lookup columns in a Canvas App).

     

    'Oracle Project Number' = Value('Oracle Project Number'.Value)

     

    ------------------------------------------------------------------------------------------------------------------------------

     

    If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

    If you like my response, please give it a Thumbs Up.

    Imran-Ami Khan

  • Paolo666 Profile Picture
    Paolo666 51 on at

    Hi @Amik 

    Thanks for your suggestion.  I've been looking at this today without success.  I've tried to break it down in to smaller steps to get rid of the errors starting with trying to get the 'AddColumns' working.

    I've trimmed the code back to the following 

    Sort(
 AddColumns(
 Shocs,
 "_ProjectManager",
 LookUp(
 Projects,
 'Oracle Project Number' = Value('Oracle Project Number'.Value),
 'Project Manager'.DisplayName
 )
 
 ),
 "ID",
 SortOrder.Ascending
)

    but I get an error below

    Screenshot invalid use message.png

     

     

     

     

    If I delete .Value from the end, the error stops but _ProjectManager is blank per below

    Screenshot PM colum blank.png

     

    I tried changing the 'lookup' to

    LookUp(
 Projects,
 'Oracle Project Number' = ThisRecord.'Oracle Project Number',
 'Project Manager'.DisplayName
 )

    Which doesn't give any errors or warnings and the data in the gallery is correct but, the _ProjectManager column has errors per the screenshot below:

     

    Screenshot PM column Error.png

     

    If I can get the add column working, I think I can get the rest of your code to work but I just haven't been able to solve this piece of the puzzle.  Can you see what I'm missing here?

    Thanks

    Paolo

  • Ami K Profile Picture
    Ami K 15,689 Super User 2024 Season 1 on at

    @Paolo750 - is the 'Oracle Project Number' field in your "Shocs" list not a SharePoint Lookup column to the Projects list?

     

    I had assumed as much, given you're using "Oracle Project Number'.Value" in your expressions.

  • Paolo666 Profile Picture
    Paolo666 51 on at

    @Amik  - Yes, it is a lookup column to the Projects List.

    I've now got it to return a record in the _ProjectManager column we've added and the gallery displays the results I expect.

    Screenshot PM column working.png

     

    Now, if I add in the original filter by Project number, it works correctly with no errors and still displaying the expected gallery items.  Filtering by project number also works although this is not needed now, it needs to be replaced by a filter searching by Project Manager.

    Screenshot Working with existing filter.png

    If I try to change the filter to use _ProjectManager.Displayname It throws an error.Screenshot NOT Working with new filter.png 

     

     

    and I lose all the gallery items.

    If I go into play mode and start typing in the search box, I get the following error.

     

    Screenshot error with new filter.png

     

    I tried adding a text label to the gallery to show the Project Manager display name from the added column using the following but I get the little red squiggle at the end and the error above appears again.

    Screenshot Label error.png

    It seems that every time I try to reference the _ProjectManager column we added, it gives an error of some sort.

  • Verified answer
    Ami K Profile Picture
    Ami K 15,689 Super User 2024 Season 1 on at

    @Paolo750 , I think the below is what you're trying to do:

     

    With(
 {
 _data: Sort(
 AddColumns(
 Shocs,
 "_ProjectManager",
 LookUp(
 Projects,
 'Oracle Project Number' = Value('Your Lookup Field'.Value),
 'Project Manager'.DisplayName
 )
 ),
 ID,
 SortOrder.Descending
 )
 },
 If(
 DropdownSHOCStatus1.Selected.Value = "All",
 _data,
 Filter(
 _data,
 CountRows(DropdownSHOCStatus1.SelectedItems) = 0 || IsBlank(DropdownSHOCStatus1.SelectedItems) || 'SHOC Status'.Value = DropdownSHOCStatus1.Selected.Value && TextSearchBox1_3.Text in Value('Your Lookup Field'.Value),
 CountRows(YourPeopleComboBox.SelectedItems) = 0 || IsBlank(YourPeopleComboBox.SelectedItems) || _ProjectManager = YourPeopleComboBox.Selected.DisplayName
 )
 )
)
//assumes DropdownSHOCStatus1 is a Combo Box

     

    ------------------------------------------------------------------------------------------------------------------------------

     

    If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

    If you like my response, please give it a Thumbs Up.

    Imran-Ami Khan

  • Paolo666 Profile Picture
    Paolo666 51 on at

    Thank you @Amik 

    I had to change what I was doing slightly as I was using a text input rather than a people picker.  Once I spotted that and added a people picker I was able to get it to work!

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

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
11manish Profile Picture

11manish 405 Super User 2026 Season 2

#2
Mohsin Ali Profile Picture

Mohsin Ali 368

#3
WarrenBelz Profile Picture

WarrenBelz 244 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans