Search a SharePoint "Choice" column in Power Apps?

[Australia]

Hi fellow Power Apps veterans,

I am new to Power Apps and currently am trying to search a choice value.

I have the following function:

Sort(
Search('My SharePoint List',
Search.Text,        --this is an Input Text
"StaffName",      -- A column in my SP list, single line of text
"StaffNumber",  -- A column in my SP list, single line of text

"Choice"             -- A column in my SP list, choice

),
'Date of Submission',  -- A column in my SP list, date
Descending
)

Where "Choice" is a choice in my SharePoint list. 

I tried both "Choice".selected.value and "Choice".value but no luck.

Hope someone would be able to shed some light on how I could get the syntax to work.

Thank you.

[PowerAddict · Accepted solution]

Try this: 

Sort(
    Search(
        AddColumns('My SharePoint List', "ChoiceValue", Choice.Value),
        Search.Text,
        "StaffName",
        "StaffNumber",
        "ChoiceValue"
    ),
    'Date of Submission',
    Descending
)

Let me know if this works. 

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

[Australia]

You are a genius, Hardit! 

It works like a charm! Thanks for much for such a quick response and fix.

I am sure this thread will benefit a lot of people in the future. 😄

Much appreciated.

p.s. Do I also use AddColumns for the "Choice" in a datacard of a form?

DataSourceInfo([@'My SharePoint List'], DataSourceInfo.MaxLength, "Choice")

[PowerAddict]

I don't think you need to change anything for the form. Are you not seeing the value in a form? 

And 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

Microsoft Business Applications MVP

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

[Australia]

Hi Hardit,

No unfortunately I'm not seeing any value in this Choice datacard. 😞

[PowerAddict]

Unless I am missing something, here is a form in which the Compliance Status field is a Choice field in my SP list. Here are the data card properties: 

Here are the combobox properties: 

I didn't have to change anything. So may be I am missing something? Let me know and we can fix 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

[Australia]

Thanks for coming back, Hardit.

No unfortunately values don't seem to pass through any datacards now. I notice that there is a red error in the Form.

Here's my layout:

I have a Gallery

where

Items f(x) = the syntax you wrote in the Solution

Form_StaffDetails

where

Items f(x) = Gallery.Selected   -- this is where the error is. 'Selected' is underlined in red.

Thanks mate.

[PowerAddict]

I think I know what's happening (not sure what error message you are getting, so if doesn't solve your problem, let me know the exact error message).

We used AddColumns function for the gallery. That creates a temporary modified table which doesn't exactly match the original data source. In other words, your gallery's data source is no longer 'My SharePoint List'. It is a modified table which has a new column called ChoiceValue. 

But your form's data source is still the original table 'My SharePoint List'. So what you will have to do is look up the original record using the ID of the selected record from the gallery. Try something like this for the Item property of your form:

LookUp('My SharePoint List', ID = Gallery.Selected.ID)

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
Microsoft Business Applications MVP
https://thepoweraddict.com

[Australia]

It makes sense Hardit. And yes, the data has now flown through following your script 'LookUp('My SharePoint List', ID = Gallery.Selected.ID)'.  You are my life saver.

Though I did receive a Form error: "This Form isn't connected to any data". 

Is this expected? Hope this is the last error we need to fix. 🙂

[PowerAddict]

Happy to help!

Now, are you getting this error (or warning) only when you are in the editor or even when you play the app? Can you also confirm that the Data Source of the form is set to your SharePoint list? Last, when you say that the last formula worked, does that mean you are able to see the details of a record in the form? 

Let me know.

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

Thanks!
Hardit Bhatia
Microsoft Business Applications MVP
https://thepoweraddict.com