Create Gallery Search based on SharePoint List that contains multi-select

[jmaur769]

I have created a Gallery with several search options: A search bar and 3 drop down filters.  One of the drop down filters pulls from a SharePoint list item that contains multiple items (eg. "shoes, bags, shirts") which is a multi-select in the SharePoint list itself.  I have tried the following code to set the filter, and any iteration I try does not work. Any help is greatly appreciated. The code below is in the Items of the Gallery itself, which references the values of the filters.

The issue is bolded below.

Extra information:

TextSearch is the text input used for searching

DDTest is a single option drop down that works correctly

DDAvailable is a single option drop down that works correctly

DDProject is the issue. This is the Sharepoint multi-select, and is a single-select drop down in Power Apps.

Current code:

Filter(
     DataSource,
     Or(
          TextSearch.Value in Title,
          TextSearch.Value in 'Manufacturer Part'
          )
               ,IsBlank(DDTest.SelectedItems.Value) || IsEmpty(DDTest.SelectedItems) || DDTest.Selected in 'Associated Tests'
              ,IsBlank(DDAvailable.SelectedItems.Value) || IsEmpty(DDAvailable.SelectedItems) || DDAvailable.Selected.Value = IsAvailable
              ,IsBlank(DDProject.SelectedItems.Value) || IsEmpty(DDProject.SelectedItems) || DDProject.Selected.Value in 'Assoc. Project'
)

[WarrenBelz]

HI @jmaur769 ,

You have a many-to-many relationship and this requires a different approach - I have made a couple of other suggestions as well

With(
   {
      _Data:
      Filter(
      DataSource,
      Or(
         TextSearch.Value in Title,
         TextSearch.Value in 'Manufacturer Part'
      ),
      (
         Len(DDTest.Selected.Value) = 0 || 
         DDTest.Selected.Value in 'Associated Tests'
      ),
      (
         Len(DDAvailable.Selected.Value) = 0 || 
         IsAvailable = DDAvailable.Selected.Value
      )
   },
   If(
      Len(DDProject.Selected.Value) = 0,
      _Data,
      Ungroup(
         ForAll(
            DDProject.SelectedItems As _Sel,
            Filter(
               Data,
               _Sel.Value in 'Assoc. Project'
            )
         ),
         "Value"
      )
   )
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[jmaur769]

@WarrenBelz Thank you! I actually have now used this for a different item, but I was a bit unclear with my original problem I think. The Drop down option for DDProject is a single choice drop down in Power Apps, but is a multi-select in the SharePoint list where we get the data from.  For some reason this still doesn't work, even for items that only have 1 entry in the SharePoint column for Project.

[WarrenBelz]

@jmaur769 ,

Can you please list all relevant control and field types so we can get this correct 

[jmaur769]

Controls are as follows:

Drop down single selects: DDTest, DDAvailable, and DDProject (DDProject is the issue, the other two now work perfectly)

Field type (source) for the DDProject is from a multiple-choice SharePoint List Column.

Thank you!

[WarrenBelz · Accepted solution]

@jmaur769 ,

If DDProject is a single selection Combo Box, then your original code should work - although I have used some consistent syntax below that may assist.

Filter(
   DataSource,
   (
      TextSearch.Value in Title ||
      TextSearch.Value in 'Manufacturer Part'
   ) &&
   (
      Len(DDTest.Selected.Value) = 0 || 
      DDTest.Selected.Value in 'Associated Tests'
   ) &&
   (
      Len(DDAvailable.Selected.Value) = 0 || 
      IsAvailable = DDAvailable.Selected.Value
   ) &&
   (
      Len(DDProject.Selected.Value) = 0,
      DDProject.Selected.Value in 'Assoc. Project'
   )
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[jmaur769]

That works, thank you! I am actually having an issue it appears in linking the SharePoint Data Source to a collection. I am getting what I believe to be an error in the collection itself that I am referencing.  The symbol attached is showing up in the location of the collection where the multiple choices chosen should be appearing.  Eg. SharePoint list has shoe,belt listed as the two items chosen, but when I put that into a collection, it gives the symbol attached instead of the data.

 

[WarrenBelz]

@jmaur769 ,

Please nark the response as accepted if it works. Regarding you last question, if that is the multiple value field, you need to use Concat(ThisItem.FiekdName, Value, ",") to show the values separated by commas