cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
clundy
Level: Powered On

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

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.

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

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

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

View solution in original post

Super User
Super User

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

@clundy 

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.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Community Support Team
Community Support Team

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

Hi @clundy ,

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Super User
Super User

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

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

View solution in original post

Super User
Super User

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

@clundy 

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.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Community Support Team
Community Support Team

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

Hi @clundy ,

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

clundy
Level: Powered On

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

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.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,818)