Answered

Concat Collection two columns into dropdown on a gallery filter

(0) Share

Report

Posted on by kbashaw

233

I have a dropdown that is used to filter a gallery. If creates a collection and adds "Search Category - ALL" to the list.

OnVisible of the screen
ClearCollect(colcategory, {Value:"Search Category - ALL"});Collect(colcategory, Distinct(Cat,Title))

Inside the Cat SharePoint List is a column called Descr.
I need to combine the two fields together in the dropdown.

I have another dropdown in the program using:
SortByColumns(AddColumns(Cat,"FullDescription",Concatenate(Title, " - ",Descr)),"Title")

(This displays what I need to display inside the other one with the additional Search Category - ALL option.

I can’t find the right combination to add this into the collection and still have the gallery filter correctly.

Categories:

Building Power Apps

All responses (6)

Answers (1)

Sort by

LaurensM 12,516 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @kbashaw,

To adjust your collection to contain the concatenated values, you could use the following code in the OnVisible:

ClearCollect( colcategory, //Ungroup nested tables Ungroup( Table( //Add additional option {Items: [{Value: "Search Category - ALL"}]}, { Items: Distinct( Cat, //Concatenate Title and Desc Title & " - " & Desc ) } ), "Items" ) );

Filtering will depend on how your gallery is currently displaying the data. Does the concatenated column also exist in your gallery via e.g. an AddColumns function? Should this be the case, you could use:

Filter( <Your Items Code>, //Filter based on concatenated field if selection is not equal to Search Category - ALL Dropdown.Selected.Value = "Search Category - ALL" || FullDescription = Dropdown.Selected.Value )

Should the column not exist, you will have to split the selected value and filter Title & Desc separately:

With( { wTitleFilter: Index(Split(Dropdown.Selected.Value, " - "), 1).Value, wDescFilter: Index(Split(Dropdown.Selected.Value, " - "), 2).Value }, Filter( Datasource, //Filter based on Title & Desc if selection is not equal to Search Category - ALL Dropdown.Selected.Value = "Search Category - ALL" || (Title = wTitleFilter && Desc = wDescFilter) ) )

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!
kbashaw 233 on at

Like
a
(0)

Report
Copy link

Link copied!

That code is helping a lot!
The two columns do exist in the SharePoint List. Only one column is displayed alone in the Gallery - "Title".

I tested your code, I can see the Title and Desc together in the dropdown6. They are concatenated and it does search the gallery correctly.
When I used before: SortByColumns(AddColumns(Cat,"FullDescription",Concatenate(Title, " - ",Descr)),"Title")
once, I clicked on the dropdown selection, it would only keep the Title in the drop down and not the Descr. I want them to see both when they click on the arrow, but I was only the Title displayed after the click off. Is there any way to do that?
kbashaw 233 on at

Like
a
(0)

Report
Copy link

Link copied!

Also, I get this error when I run it The 'Index' function cannot be called with an empty table.
Verified answer

LaurensM 12,516 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @kbashaw,

You will have to use colcategory as the Items property of your dropdown. Make sure that the dropdown.selected.value within the Split() function refers to this particular dropdown.

Via a Combobox control you can allow multiple fields to be displayed in the combobox selection list (when pressing the chevron). This can be done by selecting the combobox, going to the property panel on the right and selecting 'Edit fields'.

However, only the primary value will be displayed within the combobox itself after selection. A workaround would be using only 1 field instead - the concatenated column. Alternatively, you may want to use 2 separate dropdowns for both columns respectively.

I hope this helps!
MudassarSZ365 591 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @kbashaw ,
Put this code on the Onvisible Property of the screen

ClearCollect( colcategory, {Value: "Search Category - ALL"} ); Collect( colcategory, AddColumns( Distinct(Cat, Concatenate(Title, " - ", Descr)), "FullDescription", Concatenate(Title, " - ", Descr) ) );

for your dropdown's Items property, use colcategory directly:

Dropdown1.Items = colcategory.Value

Put this code on the items property of the gallery

If( Dropdown1.Selected.Value = "Search Category - ALL", YourGalleryDataSource, Filter( YourGalleryDataSource, Title & " - " & Descr = Dropdown1.Selected.Value ) )

If this solution helps, please consider accepting it. 😊
kbashaw 233 on at

Like
a
(0)

Report
Copy link

Link copied!

I did end up having to switch to a combo box and use the double option. I added this and got rid of Search Category - ALL and it started working.

(TicketCategory=ComboBox1.Selected.Title ||IsEmpty(ComboBox1.SelectedItems)||IsBlank(ComboBox1.SelectedItems))