cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JamesMac
Helper I
Helper I

Filter SharePoint Multiple Select Lookup Column

I am needing to filter a SharePoint multiple select lookup column (RequiredSpecialOptions), against a multiple select value in a collection (mySpecialOptions).  If one or more values from mySpecialOptions are present in RequiredSpecialOptions, then the record should be included in the collection.  I am able to successfully filter for specific values, but am having a difficult time getting the right syntax to filter against multiple values.


Initial collection where mySpecialOptions is saved

 

 

ClearCollect(
  collMyInfo,
  Filter(
    'StaffRoster',
    'StaffMember'.Email = User().Email )
);

//mySpecialOptions is a multiple select SharePoint lookup column
Set(mySpecialOptions, collMyInfo.'SpecialOptions');

 

 


Filter for individual value works

 

 

ClearCollect(
  collMyRequirements,
  Filter(
    'Catalog',
    "None" in 'Required Special Considerations'.Value
  )
);

 

 

Filter for individual value works

 

 

ClearCollect(
  collMyRequirements,
  Filter(
    'Catalog',
    "All" in 'Required Special Considerations'.Value
  )
);

 

 


Filter for multiple values does not work

 

 

ClearCollect(
  collMyRequirements,
  Filter(
    'Catalog',
    ForAll(
      mySpecialOptions,
      //'RequiredSpecialOptions' is a multiple selection SharePoint lookup column
      If(
        mySpecialOptions.'SpecialOptions' in 'RequiredSpecialOptions'.Value,
        true,
        false
      )
    )
  )
);

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
JamesMac
Helper I
Helper I

I FINALLY got it to work, with a little bit of this and a little bit of that from the suggestions, and then a whole lot of research and trial and error.  It took a few nested If statements, some ForAll statements, and some Collect statements, all of course having to be in just the right sequence. While it does work, I'm not too keen on the idea of using hidden galleries to manipulate the data so that it could be used in data filters.  Regardless, here is the working code, which I have in the App OnStart.

 

//GET USER POSITION DATA
ClearCollect(
    collMyPositon,
    Filter(
        'StaffOrganization',
        'StaffMember'.Email = User().Email
    )
);
Set(
    myBranch,
    Last(collMyPositon.Branch).Branch
);
Set(
    myGrade,
    Last(collMyPositon.Grade).Grade
);

//DATASOURCE FOR HIDDEN GALLERY: glryHiddenMySpecialOptionsObjectString
//RECORD ITEM IS ';' DELIMITED STRING OF VALUES
ClearCollect(
    collMySpcOptions,
    collMyPositon.'SpecialOptions'
);

//DATASOURCE FOR HIDDEN GALLERY: glryHiddenMySpecialOptionsObjectItems
ClearCollect(
    collMySpecialOptions,
    Split(
        glryHiddenMySpecialOptionsObjectString.Selected.Title5.Text,
        ";"
    )
);

//GET USER TRAINING REQUIREMENTS
//collMyRequirements is the Datasource the tblMyTrngRequirements Data Table
Clear(collMyRequirements);
ForAll(
    'MandatoryTrainingCatalog',
    If(
        And(
            Status.Value = "Required",
            Switch(
                myBranch.Value,
                "OF",
                Or(
                    Int(Right(myGrade.Value, 2)) in 'Required OF Grades'.Value,
                    "All" in 'Required OF Grades'.Value
                ),
                "EN",
                Or(
                    Int(Right(myGrade.Value, 2)) in 'Required EN Grades'.Value,
                    "All" in 'Required EN Grades'.Value
                ),
                "WO",
                Or(
                    Int(Right(myGrade.Value, 2)) in 'Required WO Grades'.Value,
                    "All" in 'Required WO Grades'.Value
                ),
                "VI",
                Or(
                    Int(Right(myGrade.Value, 2)) in 'Required VI Grades'.Value,
                    "All" in 'Required VI Grades'.Value
                ),
                "CC",
                "All" in 'Required CC'.Value
            )
        ),
        If(
            "None" in Concat(
                'Required Special Options'.Value,
                Value & ";"
            ),
            Collect(
                collMyRequirements,
                {
                    'Training Title': 'Training Title',
                    Frequency: Frequency,
                    Mode: Mode,
                    'Required Special Options': 'Required Special Options'
                }
            ),
            ForAll(
                glryHiddenMySpecialOptionsObjectItems.AllItems,
                If(
                    Result in Concat(
                        'Required Special Options'.Value,
                        Value & ";"
                    ),
                    Collect(
                        collMyRequirements,
                        {
                            'Training Title': 'Training Title',
                            Frequency: Frequency,
                            Mode: Mode,
                            'Required Special Options': 'Required Special Options'
                        }
                    )
                )
            )
        )
    )
);

 

View solution in original post

7 REPLIES 7
BlessedCobba
Responsive Resident
Responsive Resident

I don't think you will need the for all or the if statement within the filter, filter is essentially and if statement itself 

 

have you tried something simpler like

 

ClearCollect(
  collMyRequirements,
  Filter(
    'Catalog',
        mySpecialOptions in 'RequiredSpecialOptions'.Value,
  )
);

I tried that but get the following error message. on the .Value:

 

Can't convert this data type. Power Apps can't convert this Table to a Text.

BlessedCobba
Responsive Resident
Responsive Resident

Hi @JamesMac 

okay need to convert that variable which has multiple values (a table) into a string then use it in the filter

 

Concat( mySpecial Operations, ColumnWithTheData, & "; ")


This will create a string like "OperationA; OperationB; " 

 

Then you can use it in a filter 

ClearCollect(
  collMyRequirements,
  Filter(
    'Catalog',
        Concat( mySpecial Operations, ColumnWithTheData, & "; ") in 'RequiredSpecialOptions'.Value,
  )
);
JamesMac
Helper I
Helper I

When I try that approach, I get the following error on the value highlighted in bold red text:

Invalid argument type (Table). Expecting a Text value instead.

Concat(mySpecialOperations, 'RequiredSpecialOptions', "; ") in 'RequiredSpecialOptions'.Value

Same error on this.

Concat(mySpecialOperations, 'RequiredSpecialOptions'.Value, "; ") in 'RequiredSpecialOptions'.Value

 

Also, I'm not sure that this approach would work as it is a many-to-many relationship between the two columns data sets.  I believe that with Concat it would evaluate the whole concatenated string against the values of the second column.

JamesMac
Helper I
Helper I

StaffRoster.SpecialOptions (e.g. mySpecialOperations) >> Lookup >> _SpecialOptions.Title

 

Catalog.RequiredSpecialOptions >> Lookup >> _SpecialOptions.Title

You could try a hidden gallery with your variable as the items and then 

ClearCollect(collMyRequirements, ForAll(gallery.allitems,Filter('Catalog','RequiredSpecialOptions'.Value = GalleryLabel)))

 

 

JamesMac
Helper I
Helper I

I FINALLY got it to work, with a little bit of this and a little bit of that from the suggestions, and then a whole lot of research and trial and error.  It took a few nested If statements, some ForAll statements, and some Collect statements, all of course having to be in just the right sequence. While it does work, I'm not too keen on the idea of using hidden galleries to manipulate the data so that it could be used in data filters.  Regardless, here is the working code, which I have in the App OnStart.

 

//GET USER POSITION DATA
ClearCollect(
    collMyPositon,
    Filter(
        'StaffOrganization',
        'StaffMember'.Email = User().Email
    )
);
Set(
    myBranch,
    Last(collMyPositon.Branch).Branch
);
Set(
    myGrade,
    Last(collMyPositon.Grade).Grade
);

//DATASOURCE FOR HIDDEN GALLERY: glryHiddenMySpecialOptionsObjectString
//RECORD ITEM IS ';' DELIMITED STRING OF VALUES
ClearCollect(
    collMySpcOptions,
    collMyPositon.'SpecialOptions'
);

//DATASOURCE FOR HIDDEN GALLERY: glryHiddenMySpecialOptionsObjectItems
ClearCollect(
    collMySpecialOptions,
    Split(
        glryHiddenMySpecialOptionsObjectString.Selected.Title5.Text,
        ";"
    )
);

//GET USER TRAINING REQUIREMENTS
//collMyRequirements is the Datasource the tblMyTrngRequirements Data Table
Clear(collMyRequirements);
ForAll(
    'MandatoryTrainingCatalog',
    If(
        And(
            Status.Value = "Required",
            Switch(
                myBranch.Value,
                "OF",
                Or(
                    Int(Right(myGrade.Value, 2)) in 'Required OF Grades'.Value,
                    "All" in 'Required OF Grades'.Value
                ),
                "EN",
                Or(
                    Int(Right(myGrade.Value, 2)) in 'Required EN Grades'.Value,
                    "All" in 'Required EN Grades'.Value
                ),
                "WO",
                Or(
                    Int(Right(myGrade.Value, 2)) in 'Required WO Grades'.Value,
                    "All" in 'Required WO Grades'.Value
                ),
                "VI",
                Or(
                    Int(Right(myGrade.Value, 2)) in 'Required VI Grades'.Value,
                    "All" in 'Required VI Grades'.Value
                ),
                "CC",
                "All" in 'Required CC'.Value
            )
        ),
        If(
            "None" in Concat(
                'Required Special Options'.Value,
                Value & ";"
            ),
            Collect(
                collMyRequirements,
                {
                    'Training Title': 'Training Title',
                    Frequency: Frequency,
                    Mode: Mode,
                    'Required Special Options': 'Required Special Options'
                }
            ),
            ForAll(
                glryHiddenMySpecialOptionsObjectItems.AllItems,
                If(
                    Result in Concat(
                        'Required Special Options'.Value,
                        Value & ";"
                    ),
                    Collect(
                        collMyRequirements,
                        {
                            'Training Title': 'Training Title',
                            Frequency: Frequency,
                            Mode: Mode,
                            'Required Special Options': 'Required Special Options'
                        }
                    )
                )
            )
        )
    )
);

 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (1,839)