how to work with seach function with lookup column

[rajesh6]

Hi Team,

I am working on the search box with gallery, when user search the employee and department that display the gallery but i am getting error message like "wrong column type, expects text type" department is a lookup column(its not a dropdown). data is coming from the crm system.

any alternative way to achieve the lookup column with search functionality in power apps

here is the example code

If(

    IsBlank(SearchBox.Text),

   TestColl1,

    Search(TestColl1,

        SearchBox.Text,

         "employee","department"

    )

)

[RandyHayes · Accepted solution]

@rajesh6 

Consider changing your Formula to the following:

Search(
    AddColumns(TestColl1, "lDepartment", department.Title)
    SearchBox.Text,
    "employee", "lDepartment"
 )

Replace .Title above with whatever value you have in the department record that you want to search on.

I hope this is helpful for you.

[Anonymous]

@rajesh6 

Sounds like you are using CDS as your data source? If so, I'm not that familiar with it but the following troubleshooting generally works when using SharePoint as a data source

try changing ... "department" ... to 

- ... "department".Value ... or

- ... "department".Name ...

Use the dot operator with PowerApps Intellisense to guide you if 'Value' or 'Name' do not work. 

Beyond this, you may need to find someone who works with CDS because it does have some little quirks to it when using it in PowerApps. I'll tag someone I think maybe able to assist

@PowerAddict ?

[PowerAddict]

Thanks @Anonymous!

@rajesh6, as far as I understand, both Department and Employee are lookup fields, is that correct? If so, do you want to search by employee's first or full name? If so, what is the relevant field on the Employee entity? Similar questions for Department as well if that's a lookup field. Do you want to search by department name? If so, what is the name field on the Department entity called? 

Let me know the answers to these questions and we should be able to solve this. 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

Thanks!

Hardit Bhatia

Microsoft Business Applications MVP

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email