Search function - how can I use a (non-text) lookup field

[Madumi]

I have a gallery linked to a CDS Entity containing three custom fields: Name (text), State (lookup), Organization (lookup).

How can I use the search function in my Canvas powerapp to work with multiple (three) columns when two (lookup) fields are not text?

I am gathering I may have to create a collection based on those lookup fields so that they can work as text. Is there a better way to do this? (nb. the Entity is some 20,000 records long).

Thanks!

[PowerAddict · Accepted solution]

Hi, 

You can use the AddColumns function for the lookup fields and then search by the new columns. For example, consider a gallery of Contacts. If I want to search by Created By or Modified By, and I use the following expression, it throws an error that the columns are not text (I added address1 city to make my test as similar to your use case as possible since you have 2 lookups and 1 text field):

Search(Contacts,  TextSearchBox13.Text, "address1_city", "createdby", "modifiedby")

Now I can make this possible by using the below expression:

Search(AddColumns(Contacts,"CreatedBy",'Created By'.'Full Name', "ModifiedBy", 'Modified By'.'Full Name'),  TextSearchBox13.Text, "address1_city", "CreatedBy", "ModifiedBy")

The AddColumns function creates 2 new text columns that store the full name of Created By and Modified By and now I can use these new columns as search parameters. 

Let me know if this helps. 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

Thanks!
Hardit Bhatia
https://www.thepoweraddict.com

[Madumi]

Thanks so much @PowerAddict 

One followup question:

When I use the formula for a single lookup field, it works eg:

Search(AddColumns('Member Agencies',"StateName",State.Name),AgencySearch.Text,"StateName")

Am I right thinking that it's not actually adding the column to the database. It's only adding the column to the function where the calculation is being made?

Oh, and it looks like the function is only returning the first 500 records... Is there a way to have it still search all 20,000 records?

Thanks so much!

[PowerAddict]

Awesome, that's great!

To answer your questions:
1. You are correct. The column isn't added in the database.
2. The output of AddColumns is subject to the delegation limit, hence the 500 records. One option is to get all of your member agencies in a collection and then use that collection as your data source. This may not be the best option, so I will let you know when I come up with something better.

Let me know if that helps.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://thepoweraddict.com

[v-yutliu-msft]

Hi @Madumi ，

Just to supplement what he said @PowerAddict .

The AddColumns function is used to shape table, not update table. So you do not need to worry it will change the data in the original table.

The reason why you need to use AddColumns function is that Search function only works for text type, so you need to add a text column with the value of the lookup field.

The reason why you can only get 500 records limit is because delegation limit.

Delegation limit is powerapps itself limit. It could only deal with data in local at most 2000.

You could change the non-delegation limit at most to 2000.

 Then you could get result from top 2000 record.

As AddColumns function is non-delegateable function, so using this function could only at most get result from top 2000 record.

Or you could try to save data in collection.Then search result in the collection. In collection, there's no delegation limit.

Here's a doc about delegation for your reference:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

Best regards,

[PowerAddict]

Glad I could help! Good luck with the rest of the app! Feel free to reach out in case of any questions!

Thanks!
Hardit Bhatia
https://thepoweraddict.com