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

[Garom]

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!

[Amik]

@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?

[Garom]

Hi @Amik ,

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

What can I do?

[Amik · Accepted solution]

@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]

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!

[Garom]

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?

[Amik]

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

[Amik]

@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.

[Garom]

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:

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

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!