Answered

Cannot search by Azure Active Directory name from Lookup Field in SharePoint List

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello,

I have a lookup column in a SharePoint list that allows you to select an employee. This is using the integration to Azure Active Directory.

When I try and do a search function in PowerApps to search by that employee name, I receive an error "Wrong Column Type, Expects Text Type". I see that others have done a flow that copies the data from the Lookup Field as a string to another field but that is "hacky" and doesn't solve the problem. Here is my search which works. I want to add the field in after title.

Sort(Search(EAPT_Apps,TextSearchBox1.Text,"Title","BRMSubCategory1","BRMSubCategory2"),Title)

How do I search on the name of the employee without having to change types or copy the data to another field, etc. I should be able to just insert the field into the search function with something like "EmployeeName.ToString()" or something like that.

Please advise.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (3)

Sort by

Verified answer

LRVinNC 2,297 on at

Like (1)

Report
Copy link

Link copied!
I have a search box which restricts a gallery by a selected employee name. My data is a SP list with a person field which was selected from AD.

If the user specifies they want to limit the gallery to a specific user (as indicated by togFilterbyPO), I filter and sort (rather than search) on that individual using the statement below.
If(togFilterbyPO.Value = true, SortByColumns( Filter(Projects, DepartmentName.Value = Dept && ProjectYear.Value = PlanningYear.SelectedText.Value &&
ProjectOwner.DisplayName = ddSelectedPO.SelectedText.Value ), "Title" ), SortByColumns( Filter(Projects, DepartmentName.Value = Dept && ProjectYear.Value = PlanningYear.SelectedText.Value ), "Title" ))

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!
@Anonymous

Your issue with adding that to your Search function is that Search will only search text fields. Adding a person field, number, or any other type besides a text field will generate an error.

The easier trick to solving this (as long as record counts are not excessive as delegation is an issue - but, then again, Search is not delegable anyway) is to convert your person field into a text field. Now, while the "hacks" you mention talk about this from a permanent field in your datasource and using flow or other options to set it, that is not necessary. I believe you will find that those situations are there to address delegation issues...where it IS necessary to do those things.

Anyway, in the meantime, you can consider using the following formula to Add another column to your datasource (in App) that will have the text version of the person field...that will then work fine with the Search function:

SortByColumns(
Search(
AddColumns(EAPT_Apps, "txtPeopleColumn", yourSharePointColumnName.DisplayName),
TextSearchBox1.Text,
"Title",
"BRMSubCategory1",
"BRMSubCategory2",
"txtPeopleColumn"
),
"Title")

You'll notice in red that we are shaping the data of EAPT_Apps to include a new column called "txtPeopleColumn" that we will populate with the value from your people column. In this case I used DisplayName as the property that you were interested in searching, you might have a different one in mind.

Then, in our Search function, we can add the column name (in blue) that we just added to our shaped data. This is a text column and search will have no problem with it.

I hope this is helpful for you as well as the guidance that @LRVinNC has provided.

Was this reply helpful? Yes No
Verified answer

v-xida-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @Anonymous ,

Could you please share a bit more about the LookUp column that you mentioned? Is it a Person type column in your SP list?

Do you want to add the LookUp column as Search column within your Search function?

Based on the issue that you mentioned, I think you have something misunderstanding with the Search function. Within the Search function, you could only specify Text type column as Search column. The complex type columns (e.g. Choice, LookUp, Person, etc) could not be used as Search column within the Search function.

More details about the Search function, please check the following article:

Search function

As an alternative solution, I think the Filter function could achieve your needs. Please consider modify your formula as below:

Sort(
Filter(
EAPT_Apps,
TextSearchBox1.Text in Title || TextSearchBox1.Text in BRMSubCategory1 || TextSearchBox1.Text in BRMSubCategory2 || TextSearchBox1.Text in YourPersonColumn.DisplayName
),
Title
)

Note: I assume that the LookUp column that you mentioned is a Person type column (YourPersonColumn) in your SP List.

If the LookUp column is just a normal LookUp column, please consider modify above formula as below:

low:

Sort(
Filter(
EAPT_Apps,
TextSearchBox1.Text in Title || TextSearchBox1.Text in BRMSubCategory1 || TextSearchBox1.Text in BRMSubCategory2 || TextSearchBox1.Text in YourLookUpColumn.Value
),
Title
)

In addition, I also agree with @RandyHayes 's thought almost. You could add a new Text column to store your LookUp column value:

Sort(
Search(
AddColumns(EAPT_Apps, "TextPersonColumn", YourPersonColumn.DisplayName),
TextSearchBox1.Text,
"Title",
"BRMSubCategory1",
"BRMSubCategory2",
"TextPersonColumn"
),
Title
)

please take a try with above solution, check if the issue is solved.

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Worked beautifully. The name field is actually the OfficeUsers name in the lookup field. Appending the or statements and the .displayName worked fine. Also, filtering on this list of 1000 apps is much faster. Thank you all for the great feedback.

Was this reply helpful? Yes No