Filter gallery where drop down value is contained within sharepointlist field

[bux123]

Hi there,

I am trying to add a filter to my gallery which filters by comparing a dropdown value (from a separate excel datasource) to a column in my sharepoint list.

The column in the sharepoint list has values delimited by ';' (can be seen below)

The dropdown value needs to be contained within the field in the sharepoint list, not an exact match. For example, if the dropdown value I select is 'Research', the gallery item for the screenshot above should be returned because 'Research' is contained within the field 'Research;Strategy;' 

Table1 is the table of dropdown values

SharepointList is the datasource for my gallery, and where I want to filter 

TagDD is the name of the dropdown 

I am mostly working off collections (this is my 'OnVisible' on my main screen). Collectiontag is where I am storing the dropdown values from Table1 and that seems to be working fine and displaying the drop down values on my app:

ClearCollect(collectfiltering, {Result:"All"});
Collect(collectfiltering, Distinct(SharepointList, Application));
ClearCollect(collectfiltersector, {Result:"All"});
Collect(collectfiltersector, Distinct(SharepointList, Sector));
ClearCollect(collecttag, {Result:"All"});
Collect(collecttag, Distinct(Table1, 'Engagement Tags'))

Dropdown: 

The following is the code for my gallery under Items.

All of the filtering and dropdowns etc. are working except the 'TagDD'. First line is to say that if I select the value 'All' from the dropdown, to just display all Gallery items. Then second line is where the issue is - I tried using a split to separate the values within the field in the sharepoint list by their delimiter ';' and then filter if the result of this equals the dropdown value but this isn't working.

I want some way to 'Filter if SharepointList column 'Engagement Tags' contains TagDD.Selected.Result' 

Filter(
    If(
    ApplicationDD.Selected.Result = "All" And SectorDD.Selected.Result = "All",SharepointList,
    ApplicationDD.Selected.Result = "All" And SectorDD.Selected.Result <> "All", 
    Filter(SharepointList, Sector = SectorDD.Selected.Result),
    SectorDD.Selected.Result = "All" And ApplicationDD.Selected.Result <> "All", 
    Filter(SharepointList, Application = ApplicationDD.Selected.Result),
    SectorDD.Selected.Result <> "All" And ApplicationDD.Selected.Result <> "All", 
    Filter(SharepointList, Application = ApplicationDD.Selected.Result And Sector = SectorDD.Selected.Result),
//Here is my problem below
    TagDD.Selected.Result = "All", SharepointList,
    TagDD.Selected.Result <> "All", Filter(SharepointList, Split('Engagement Tags',";").Result = TagDD.Selected.Result)
    ),
//This bit works fine
    SearchEM.Text in EP||SearchEM.Text in EM||SearchEM.Text in Client
   )

[bensh]

If you're looking for a "contains" or 'wildcard' filter, you may need to use the 'in' qualifier.

Filter if SharepointList column 'Engagement Tags' in TagDD.Selected.Result

[v-bofeng-msft]

Hi @bux123 :

Could you tell me:

What is TagDD.Selected.Result 's data type?Is it a string?

What is 'Engagement Tags'?Is it a text column?

I assume  TagDD.Selected.Result is a string and 'Engagement Tags' is it a text column.

Please try:

Filter(SharepointList, TagDD.Selected.Result in Split('Engagement Tags',";").Result)

Or 

Filter(SharepointList, TagDD.Selected.Result in 'Engagement Tags')

 Best Regards,

 Bof

[bux123]

Hi @v-bofeng-msft 

What is TagDD.Selected.Result 's data type?Is it a string? - yes it's a text string for example 'Strategy' is one of the drop down values

What is 'Engagement Tags'?Is it a text column? - Yes, it's text, for example 'Research;Strategy;' is one of the column values

I've tried both of the suggestion you have given but neither are working. It isn't throwing any errors in the function bar, but it just isn't filtering on anything - as you can see here from my application, I've selected to filter on 'Strategy' but it hasn't filtered the first item out. 

Any ideas?

[v-bofeng-msft]

Hi @bux123 :

Oh ,It's so wired.It should work. I've made a test:

Could you check the value of TagDD.Selected.Result?For example:

Add a label control and set it's Text property to:

TagDD.Selected.Result="Strategy"

• If the result you get is "False", then there is a problem with the value of TagDD.Selected.Result.
• If the result you get is "True",then set the text property to

CountRows(Filter(SharepointList, "Strategy" in 'Engagement Tags'))

• If you get 0, then there is a problem with the 'Engagement Tags 'column
• If the result you get is greater than 0 ,explain that there is a problem with the logic of your original formula.

Best Regards,

Bof

[bux123]

Hi @v-bofeng-msft ,

Thank you for your suggestions!

I am getting a false with the first one. So it must be my drop down value. I am using a separate list for the dropdown which is an excel table that I have connected through onedrive. It pulls through fine to the collection and displays the values in the dropdown.

I've got this in the OnVisible on my main screen

ClearCollect(collecttag, {Result:"All"});
Collect(collecttag, Distinct(Table1_1, Tags))

This is the source excel table: 

Here they are pulling through correctly to the drop down:

It's even showing the correct result when I hover over it in the function:

So I'm not sure why it's not working when I try to use the selected value to filter the original sharepoint list?

[bux123 · Accepted solution]

Update - I realised I am using IF clauses for multiple search drop downs. 

I updated these to integrate the 'In' search across all of the IF scenarios and it is now working. Thank you @v-bofeng-msft for you help with the function! 

If anyone is interested, the code I have used to get three separate drop downs working using If and including an 'in' search is: 

Filter(
    If(
    //All so no filtering
    ApplicationDD.Selected.Result = "All" And SectorDD.Selected.Result = "All" And TagDD.Selected.Result = "All",'Sharepoint List',
    //All Applications, All Tag, Selected Sector
    ApplicationDD.Selected.Result = "All" And TagDD.Selected.Result = "All" And SectorDD.Selected.Result <> "All", 
    Filter('Sharepoint List', Sector = SectorDD.Selected.Result),
    //Selected Application, All Tag, All Sector
    SectorDD.Selected.Result = "All" And TagDD.Selected.Result = "All"  And ApplicationDD.Selected.Result <> "All", 
    Filter('Sharepoint List', Application = ApplicationDD.Selected.Result),
    //All Applications, Selected Tag, All Sector
    SectorDD.Selected.Result = "All" And ApplicationDD.Selected.Result = "All"  And TagDD.Selected.Result <> "All", 
    Filter('Sharepoint List', TagDD.Selected.Result in 'Engagement Tags'),
  //Selected Applications, All Tag, Selected Sector
    ApplicationDD.Selected.Result <> "All" And TagDD.Selected.Result = "All" And SectorDD.Selected.Result <> "All", 
    Filter('Sharepoint List', Application = ApplicationDD.Selected.Result And Sector = SectorDD.Selected.Result),
    //Selected Application, Selected Tag, All Sector
    ApplicationDD.Selected.Result <> "All" And TagDD.Selected.Result <> "All" And SectorDD.Selected.Result = "All", 
    Filter('Sharepoint List', Application = ApplicationDD.Selected.Result And TagDD.Selected.Result in 'Engagement Tags'),
    //All Applications, Selected Tag, Selected Sector
     ApplicationDD.Selected.Result <> "All" And TagDD.Selected.Result <> "All" And SectorDD.Selected.Result = "All", 
    Filter('Sharepoint List', Sector = SectorDD.Selected.Result And TagDD.Selected.Result in 'Engagement Tags'),
  //Selected Applications, Selected Tag, Selected Sector
     ApplicationDD.Selected.Result <> "All" And TagDD.Selected.Result <> "All" And SectorDD.Selected.Result <> "All", 
    Filter('Sharepoint List', Sector = SectorDD.Selected.Result And TagDD.Selected.Result in 'Engagement Tags' And Application = ApplicationDD.Selected.Result)
    ))