Filtering combobox

[ruth]

I'm hoping I can explain this issue properly. 

I built a contacts list on SP.  A contact can belong to 1 to many categories. My categories reside on a metadata list. I have a very basic function that I'm attempting.  In Combo box 1, I list the categories. Items = SortByColumns(LU_Category,"Title") --> Works. Then in Combobox 2, I want to list all the names of individuals that fall within a given category.  The individual can belong to one to many categories. But I'm only selecting 1 category at a time. I had this working when the column was populated with a basic lookup table.  But once I switched the category column to be populated by metadata, it no longer works. The Category field in the contacts fields list is not even available to filter.  Only when I put the data on a form, can I see that the column actually exists, but I'm still unable to filter it. Here is how I have ComboBox2 coded for Items=SortByColumns(Filter(Contacts,!InActive && ComboBox1.Selected.Title in Category.Value),"FullName")

Any idea what i'm doing wrong?  Is this a limitation to using metadata?

[WarrenBelz]

Hi @ruth ,

If you are only selecting one at a time, you should not need the in filter

SortByColumns(
   Filter(
      Contacts,
      !InActive && 
      Category.Value = ComboBox1.Selected.Title
   ),
   "FullName"
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[ruth]

When I try that the equals sign is rejected as an invalid argument type. 

I think my problem has something to do with the Category field being a "table".  When a sampling of the data is displayed, that column is showing up as a table?

[WarrenBelz]

Thanks @ruth ,

I was assuming from your code that Category was a Choice field - what type is it?

[ruth]

It's metadata?

[WarrenBelz]

@ruth ,

Managed Metadata? What does it say on your SharePoint list settings?

[ruth]

Managed Metadata.  That's my problem. I originally had the column defined as a Lookup column and it worked fine. I then changed it to managed metadata and in the data sampling, it is showing up as a table, but I can't filter on it.

[WarrenBelz]

@ruth ,

Yes you have created a problem for yourself with that data structure. Your syntax should technically work except I suspect that Value is not the name of the sub-field in your Metadata field that you are trying to match Title to. So using xxxx for that field name to replace, does this work?

SortByColumns(
   Filter(
      Contacts,
      !InActive && 
      ComboBox1.Selected.Title in Category.xxxx
   ),
   "FullName"
)

if not, try this

SortByColumns(
   Filter(
      AddColumns(
         Contacts,
         "Multi",
		 Concat(
            Category,
            xxxx & ""
         )
      ),
      !InActive && 
      ComboBox1.Selected.Title in Multi
   ),
   "FullName"
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[ruth]

Thanks, I've already tried the first suggestion and the error I get is on Title.  It says I can't convert a text to a table. I just tried the second suggestion (Contacts is my table and Category is my field but since it's managed metadata, it too is a table). So, it doesn't like "Category" which you have as XXXX. The error I get is "Invalid argument type. Expecting Text, Number, Boolean.....".

[Jeff_Thorpe · Accepted solution]

Try this:

SortByColumns(Filter(Contacts,!InActive && ComboBox1.Selected.Title in Category.Label),"FullName")

You will probably get a delegation warning and that is because "in" isn't delegable operator. If your list is under the delegation query limit then you should be fine.