Using sharepoint list text records and choices to sort a gallery

[RSygroves]

Hello,

I have an issue trying to write my SortByColumns formula.

The situation is :

I have a sharepoint list with both Text and Choices columns.

In the app, I can display in a gallery every data (Tex, Choices, Dates ...) but I also use a search box so that user can find a specific line while searching with keywords   

It works well for the text, but I don't know how to make it possible to have the search text matching with the choices records (= violet and blue entries, which are related to two different choices columns in my list) :

For now, my formula in the Items part of my gallery is :

SortByColumns(Search('Prochaines actions';TextSearchBox1.Text;"Title");"Dateded_x00e9_marrage"; If(SortDescending1; Ascending; Descending))    

So obviously, it only returns the records matching with the "Title" column (which is my text column).

What can I do to include the choices ? (Violet and Blue datas in the gallery)

Thanks for your help.

Best regards.

Romain

[RSygroves]

Hello @WiZey 

Thanks to your advices, I tried several solutions.

The first one is :

SortByColumns(Filter(Search('Prochaines actions';TextSearchBox1.Text;"Title");StartsWith(Service.Value;TextSearchBox1.Text) || StartsWith(Antenne.Value;TextSearchBox1.Text));"Dateded_x00e9_marrage"; If(SortDescending1; Ascending; Descending))

I don't get any error with this formula, but when entering words in the app's TextSearchBox , I don't get any result neither.

So I tried another one :

SortByColumns(Filter('Prochaines actions';StartsWith(Title;TextSearchBox1.Text) || StartsWith(Service.Value;TextSearchBox1.Text) || StartsWith(Antenne.Value;TextSearchBox1.Text));"Dateded_x00e9_marrage"; If(SortDescending1; Ascending; Descending))

I can get results for both "Title", "Service" and "Antenne" datas but, for "Title", I would like the user to be able to search any word, not just letters or words matching with "StartsWith"...

(For the "Service" and "Antenne" datas, I don't mind as the StartsWith research is sufficient.) 

I think in my first solution, there is still something that don't allow the research to work properly, even though I don't get errrors.

But I still think it might be the best one, as it includes the "Search" function + the "StartsWith" one...

What do you think ?

[WiZey]

Hello @RSygroves ,

After some thinking, I'm afraid the first function can't actually work the way we wanted.

"Search()" will return every records whose Title match with the text, however "Filter()" will add an "And" condition on both columns Service and Antenne, which will indeed return no value unless you have one record whose Title AND Service/Antenne both match the text. 

I believe this case to be non-existent in your database, and so the first function is unfortunately useless.

However, I believe the second function can properly work in your case with the function "Find()".

https://docs.microsoft.com/en-us/power-platform/power-fx/reference/function-find

"Find()" should properly work on both Text-type columns and Choice-type columns. You'll probably see a delegation warning though, but can you still try and see how it goes?

[RSygroves]

Hi, I'm trying to write it with Find(), for the moment I don't manage to do so...

Maybe I should just stop trying to use choices columns and set them back to text... 😬

[RSygroves]

Here is what my formula looks like, no error, but still no result when entering text in the TextSearchBox...

SortByColumns(Filter('Prochaines actions';Find(TextSearchBox1;Service.Value);Find(TextSearchBox1;Antenne.Value);Find(TextSearchBox1;Title));"Dateded_x00e9_marrage"; If(SortDescending1; Ascending; Descending))

Actually, as there are few possible choices in the two choice columns, maybe using text would be easier. 

And I'll try to set the "Edit form" part of my app a bit more restrictive using dropboxes for these columns.

[WiZey]

Hello @RSygroves ,

"Find()" returns an integer which represents the position where he found the matching text inside a string, not a boolean. That's why your "Filter()" doesn't return any records.

Can you try with this formulae and see how it goes?

SortByColumns(Filter('Prochaines actions';
Find(TextSearchBox1;Service.Value) > 0;
Find(TextSearchBox1;Antenne.Value) > 0;
Find(TextSearchBox1;Title)) > 0;
"Dateded_x00e9_marrage";
 If(SortDescending1; Ascending; Descending)
)

[RSygroves]

Thanks for having written it,

The formula seems to be OK (no error). 

But when entering text in the TextSearchBox, nothing happens and gallery's records disappear as before.

Maybe this TextSearchBox has wrong properties.

Here is what I have :

OnSelect = True"

OnChange = 'Prochaines actions'

Default = ""

...

[RSygroves]

Hi @WiZey 

As I need to publish the app asap on our sharepoint internal website, I've creat a v2 of the app and modified my SP list without choices columns for the moment.

Maybe I'll be able some day to figure out how to resolve these issues.

Anyway, I would like to thank you very much for your help and the precious time spent trying to help me !

Best regards,

RS