Skip to main content

Notifications

Community site session details

Community site session details

Session Id : 8ZX5fRgO/nStO4S3nDthAL
Power Apps - Building Power Apps
Answered

Search function in a gallery of a search type column from Sharepoint List + search Person or group type columns

Like (0) ShareShare
ReportReport
Posted on 20 Nov 2023 22:12:19 by 15

Hello,

 

I am currently having some trouble creating a gallery in Power Apps, that shows searched items in a column from sharepoint list.

This column is a search type column, so to fill any text in it, it is necessary to have the same text in another list.

 

Being more exact: I am creating an APP to manage customer claims, that are registered in a Sharepoint List called NC Management. This list has a search type column which is linked to another Sharepoint list, with all our customers. What I want to do is to have a gallery that can show all the claims related to a customer.

 

The code of the Gallery looks as it follows:

Search('NC Management';Filter_Company.Text;"Company")

but it doesn't recognises any item. When I write this code, it gets totally in blank (no information appears).

 

Beside this, I have another trouble: I am not able to search in a "Person or group" type column?

 

I would appreciate if anyone could help to solve this problems.

 

Thank you all!

  • Garom Profile Picture
    15 on 27 Nov 2023 at 18:28:26
    Re: Search function in a gallery of a search type column from Sharepoint List + search Person or group type columns

    Hi @Amik,

    Thank you for your answer! I have tried to apply what you said, but I don't know why it doesn't works to me.

    Here is my Function:

    Garom_0-1701109260212.png

    Notice I have:

    - 3 columns in my Sharepoint list with users

    - 1 Lookup column in my Sharepoint list

    - Ans as I show you in the following screenshot, 2 of my input text boxes are Dropdown boxes

    Garom_1-1701109521550.png

    The Gallery doesn't show anything because it says: "Search" function has some invalid arguments.

    Can you tell me what I am doing wrong?

    Thank you very much!

  • Ami K Profile Picture
    15,665 Super User 2024 Season 1 on 22 Nov 2023 at 21:55:50
    Re: Search function in a gallery of a search type column from Sharepoint List + search Person or group type columns

    @Garom - I am not clear on what you mean by the fields being related to an option column.

     

    Regardless, I assume you're populating your Form control with a record. Typically, we populate our Form with a record based on a Gallery selection. For example we can populate the Item property of a Form control with something like:

     

    Gallery1.Selected

     

     

    However, with a Form control, the DataSource property and the Item property must not only read from the same data source, but the data source it is reading must also have the same schema.

     

    Whenever use a "transform" function such as the AddColumns or GroupBy function, the schema of the Items property of the Gallery has been changed. Because of this, the DataSource property and the Item property of the Form no longer match.

     

    To address this, we can simply use a LookUp function to return the correct record:

     

    LookUp(
     'NC Management',
     ID = 'Your Gallery'.Selected.ID
    )
    

     

     

    Or if using a variable:

     

    LookUp(
     'NC Management',
     ID = 'YourVariable'.ID
    )
    

     

     

    As noted at the beginning, I do not understand how you're populating the Item property of your Form with a record, but hopefully the above explanation will provide you with direction.

  • Ami K Profile Picture
    15,665 Super User 2024 Season 1 on 22 Nov 2023 at 21:46:56
    Re: Search function in a gallery of a search type column from Sharepoint List + search Person or group type columns

    @Garom - you can simply chain two Search functions together:

     

    Search(
     Search(
     AddColumns(
     'NC Management',
     "MyLookUpField",
     LookupStage.Value,
     "MyPeopleField",
     'Your People Field'.DisplayName
     ),
     'Your Text Input Box'.Text,
     "MyLookUpField"
     ),
     'Your Other Text Input Box'.Text,
     "MyPeopleField"
    )
  • Garom Profile Picture
    15 on 22 Nov 2023 at 20:26:06
    Re: Search function in a gallery of a search type column from Sharepoint List + search Person or group type columns

    Hi again @Amik !

    As you said the function AddColumns had impacts in a Form linked to the Sharepoint List: All the fields related to an option column, have the message "lookup items", and when I preview the App, there is no form showed.

    Does it mean that I cannot use Addcolumns and a Form linked to the same Sharepoint list?

  • Garom Profile Picture
    15 on 22 Nov 2023 at 19:50:02
    Re: Search function in a gallery of a search type column from Sharepoint List + search Person or group type columns

    Thanks a lot @Amik !!!

     

    It worked perfectly!

     

    Just another one doubt: Which function shoud I need if I wanted to have these two items in different Text input boxes as filters in my App, and make Gallery show the results of all of them combined?

     

    Hope you can help me again!

  • Verified answer
    Ami K Profile Picture
    15,665 Super User 2024 Season 1 on 22 Nov 2023 at 01:07:52
    Re: Search function in a gallery of a search type column from Sharepoint List + search Person or group type columns

    @Garom assuming your Lookup and People fields are set to single select, try the below:

     

    Search(
     AddColumns(
     'NC Management',
     "MyLookUpField",
     'Your Lookup Field'.Value,
     "MyPeopleField",
     'Your People Field'.DisplayName
     ),
     'Your Text Input Box'.Text,
     "MyLookUpField",
     "MyPeopleField"
    )
    

     

    It is not possible to use the Search function on complex field types (such as Choice, Lookup, People).

     

    Instead we use the AddColumns function to bring a "Virtual" column into our table. An equivalent of this is using a Vlookup in Excel - you're bringing in a new column with data which does not actually exist at source.

     

    In this example, we have created a column two columns called  "MyLookUpField" and "MyPeopleField" which return the Lookup value and the Display Name attribute of the People field in text format.

     

    We then run a Search against these columns to return the results we need.

     

    Note that "MyLookUpField" and "MyPeopleField" are entirely made up names, and it can be anything you want.

     

    Also note that using the AddColumns function this will modify your Gallery schema, and could impact any variables you might be running against the Gallery. In addition, both the Search and AddColumns functions are not delegable.

     

    Further reading:

     

    https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-table-shaping 

  • Garom Profile Picture
    15 on 21 Nov 2023 at 08:13:51
    Re: Search function in a gallery of a search type column from Sharepoint List + search Person or group type columns

    Hi @Amik ,

     

    Yes you are right. It is exactly how you explained it.

    What can I do?

  • Ami K Profile Picture
    15,665 Super User 2024 Season 1 on 20 Nov 2023 at 22:26:24
    Re: Search function in a gallery of a search type column from Sharepoint List + search Person or group type columns

    @Garom - there is no such thing as a "search type" column.

     

    As I understand it, you have two SharePoint Lists:

    • SharePoint List 1 is called NC Management.
    • SharePoint List 2 is called Customers.

     

    In the NC Management SharePoint list, you have a SharePoint Lookup field which is a Lookup to the Customers list.

    Based on your description, the name of SharePoint Lookup field is called "Customer".

     

    The data source for your Gallery is NC Management, and you want to use the Search function on the Lookup column AND on a People picker column.

     

    Is that correct?

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

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,743 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,079 Most Valuable Professional

Leaderboard
Loading started