Filtering a Gallery by Combo box

[CameronTCD]

Hello, 

I've browsed many many forums looking for a solution but I'm yet to find one.

I look to filter a gallery using the results from a combo box.

I'm having two issues however. 

1. I'm searching a SharePoint list column based on 'Office'. 

   This means that I have multiple iterations of the same value, I would like to filter this by unique values.

2. I can't seem to actually find the correct code to filter the gallery using the combo box.

Help would be much appreciated.

Regards,

CameronTCD

[Pstork1]

1) Use Distinct() around the data source that you use to populate the Combo box.  That should give you just the unique values and filter out the duplicates.

2) The value that you search on will be comboboxname.fieldname. The issue is usually finding which fieldname you are actually filtering on.

[mdevaney]

@CameronTCD 

I will attempt to answer both of your questions one-by-one:

1. I'm searching a SharePoint list column based on 'Office'.   This means that I have multiple iterations of the same value, I would like to filter this by unique values.

You may use the following code if your Sharepoint list is less than 2,000 rows to show only the unique values as options.  Put this code in the Items property of the ComboBox

Distinct(your_sharepoint_list,Office);

Otherwise, you must manually define the options for selection within Office like this

[
    "Office1",
    "Office2",
    "Office3",
    "Office4",
    "Office5"
]

2. I can't seem to actually find the correct code to filter the gallery using the combo box.

Put this code in the Items property of your ComboBox.  If you used DISTINCT as I suggested for your previous question the .Result selector will work.  If not, you should try .Value or whatever autocomplete suggests.

Filter('Timesheet Lines',WorkOrder = ComboBox1.Selected.Result)

Let me know if you have any questions.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[CameronTCD]

Hi @mdevaney, @Pstork1 

Thanks for your reply.

Here's the issue with passing the distinct function...

For whatever reason, after passing 'distinct('datasource','baseoffice')', my combo list become a list of blank entries? See below:

Secondly, passing the function that you supplied @mdevaney, I recieve the error messages below: 

"=", invalid argument type

"Result", Name isn't valid. The identifier isn't recognised

Regards,

CameronTCD

[mdevaney]

@CameronTCD 

What type of Sharepoint column is 'Base Office'?  My response assumed it was a single-line text column.

If 'Base Office' is a Choices column you would need to use this code within the ComboBox Items property instead.

Distinct(ForAll('Apprentice List',{Value:'Base Office'.Value}),Value)

[Pstork1 · Accepted solution]

Result is the default name for the field used in a combobox, but as I mentioned it is often overwritten with a specific field name.  When you type the period after Combobox1.selected. you should see some entries in intellisense.  Those are the field names.  It should be one of them.  It could be Value, or Result or some specific column name from your list.

Can you provide a bit of info on the datasource you are using to get the combobox entries?

[CameronTCD]

Hi @mdevaney 

You're correct in your assumption, see below.

Regards,

CameronTCD

[mdevaney]

@CameronTCD 

Then check out the autocomplete options as @Pstork1 suggested.  Type the period before .Result and see what options are available.

[CameronTCD]

Hi @mdevaney , @Pstork1 

Please see the attached video: https://www.dropbox.com/s/lc1vq9xhsyo1p2n/cU4onHX87U.mp4?dl=0. 

Using the autocomplete, it suggested 'base office' and hoorah it worked.

Now, the issue is that it will only filter by one item at a time... when I select 'selected items' an error comes into play.

Apologies if I'm making rookie mistakes here. I'd like to see all filtered offices.

Regards,

CameronTCD

[mdevaney · Accepted solution]

@CameronTCD 

In the last few frames of your video the Equals ( = ) operator was highlighted in red because a Text value cannot be compared to a table of SelectedItems.

Try substituting the IN operator for ( = ).