Search function include text and choices

[RDJHUD]

I created a new Canvas App. I am using a CDS table as my data source. I created all of the necessary columns in my table to store my data.  I added 2 dummy records to my data. I am now configuring a screen that will show the records in a gallery.  I am able to get the gallery to populate ok (with the help from this group...thank you!), but I'm now trying to add a search box to the screen so that my users can filter the data and show only interesting records.  The problem is that some of the fields in my CDS data source are "Choices" fields, not simple text fields.  This makes the search function throw the error that it expects a text field (of course).  I've tried using the AddColumns function but I don't think I quite understand how to structure or use this, below is one of the variations of what I've tried so far.  My understanding is I point it to my data source (CDS_DATASOURCE), then give the "virtual" column a name (txtFunction) and then tell it what column to feed into that virtual column (Recommendation Number).  Then I reference the search box and point it to the virtual column and it should populate my gallery based on the user's input.  Not working...so I'm obviously doing this wrong.  Any assistance would be appreciated!  

Search(AddColumns(CSD_DATASOURCE, "txtFunction", 'Recommendation Number', SearchInputBox.Text,"txtFunction"))

Thanks,

Randy

[Drrickryp]

Hi @RDJHUD 

I would leave the textinput box and add a dropdown for the choices.  You could then filter for both controls in the items property of the gallery. The real value of a choice type field is to be able to show them in a toggle (2 choices), radio control (several),  dropdown (many choices), or combo box (so many you have to search them to narrow the choices).

[Prakash4691]

Hi @RDJHUD ,

AddColumns returns table by adding, dropping, renaming, and selecting its columns.

To achieve your functionality, use "filter" formula. Search function finds records in a table that contain a string in one of their columns.

ex: Below formula it to filter accounts based on account name and status reason. you can add any number of logical test.

Filter(Accounts, 'Account Name' = TextSearchBox4.Text, 'Status Reason' = 'Status Reason (Accounts)'.Active)

If you have any dropdown in an app for filter. you can use below formula. I have added sample dropdown FYR.

Filter(Accounts, 'Account Number' = Dropdown3.Selected.'Account Number' || 'Account Name' = TextSearchBox4.Text, 'Status Reason' = 'Status Reason (Accounts)'.Active)

Including reference link from docs:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

Mark it as resolved, if it answers your problem.

Regards,
Prakash

[v-yangar-msft · Accepted solution]

Hi @RDJHUD ,

I think there is a little problem with the function you are using. Firstly, you should put the text value of Choices Column in "virtual" column, but you used 'Recommendation Number' directly in your function.

I advise you to put the text value of Choices Column in "virtual" column which named (txtFunction), Because you use ‘Choice’ column, you should use Concat() function to convert multi value to text.

Please try to use the function as below:

Search(AddColumns(Projects,"txtFunction",Concat(choiceTT,Value &",")),TextInput2.Text,"txtFunction")

Here is the result in my side:

In the previous screenshot, “ChoiceTT” is the Choices column in my CDS table. Porject5 relates to cate2 and cate7 as below:

When I type cate2 or cate7, both of results are project5:

Help it helps!

Many Thanks,

Arrow