cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chris_young
Helper III
Helper III

Filters - Choice

All, 

 

I have a screen which has many filters on it. Below is the code I'm using and a screenshot of the error and screen. 

I cannot get the SharePoint Choices (module) to work. I've read some blogs about using Distinct and Choice and some other things but I can't get it. All the other filters work, but they aren't Choices. Any ideas?

 

Filter(
'Customer Reference Data',
CompanyDropDown.SelectedText.Value=Blank() || Title =CompanyDropDown.SelectedText.Value,
IndustryDropDown.SelectedText.Value=Blank() || Industry.Value =IndustryDropDown.SelectedText.Value,
IndustrySubgroupDropDown.SelectedText.Value=Blank() || 'Industry Subgroup' =IndustrySubgroupDropDown.SelectedText.Value,
ModulesDropDown.Selected.Value=Blank() || Module = ModulesDropDown.Selected.Value,
//Referenceable.SelectedText.Value=Blank() || Title =CompanyDropDown.SelectedText.Value,
//SolutionDropDown.SelectedText.Value=Blank() || Solutions.Value = SolutionDropDown.SelectedText.Value,
EngagementTypeDropDown.SelectedText.Value=Blank() || Engagement.Value =EngagementTypeDropDown.SelectedText.Value)

 

chris_young_0-1629760386524.png

chris_young_1-1629760502694.png

 

 

7 REPLIES 7
v-qiaqi-msft
Community Support
Community Support

Hi @chris_young

Based on the issue that you mentioned, do you want to filter based on the Module(Choice) column?

Could you please tell me that how you set the Items property of the ModulesDropDown, using Choices() or anything else?(IMPORTANT)

Generally, we use choices() to set a Choice Dropdown Items property as below:

Choices(Product.Module)

So, if you set your ModulesDropDown as above, please modify your formula as below:

Filter(
'Customer Reference Data',
CompanyDropDown.SelectedText.Value=Blank() || Title =CompanyDropDown.SelectedText.Value,
IndustryDropDown.SelectedText.Value=Blank() || Industry.Value =IndustryDropDown.SelectedText.Value,
IndustrySubgroupDropDown.SelectedText.Value=Blank() || 'Industry Subgroup' =IndustrySubgroupDropDown.SelectedText.Value,
ModulesDropDown.Selected.Value=Blank() || Module.Value = ModulesDropDown.Selected.Value,
//Referenceable.SelectedText.Value=Blank() || Title =CompanyDropDown.SelectedText.Value,
//SolutionDropDown.SelectedText.Value=Blank() || Solutions.Value = SolutionDropDown.SelectedText.Value,
EngagementTypeDropDown.SelectedText.Value=Blank() || Engagement.Value =EngagementTypeDropDown.SelectedText.Value) 

Note that you should refer to Module column using "Module.Value"

Best Regards,
Qi
WarrenBelz
Super User
Super User

Hi @chris_young ,

Is Module a Choice field? If you you need

Module.Value = ModulesDropDown.Selected.Value

Also SelectedText is deprecated - use Selected instead.

 

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.

 

The items on the Module dropdown is set as:

SortByColumns(Choices('Customer Reference Data'.Module),"Value",Ascending)

 

In the gallery, if I add the .Value on the end I am still getting the same error message in the gallery

Thank you Warren. Yes Module is a choice field in SharePoint. 

 

I tried to add the .Value on the end but I'm still getting the error. I will change the selectedtext to just selected. Thank you for that piece of information. 

chris_young_0-1629813511393.png

 

@WarrenBelz @v-qiaqi-msft Sorry huge point I just realized I left out. The Choices is a MultiSelect in Sharepoint. They will only filter on one of them, but the backend allows for multiple selections in that field. 

Hi @chris_young

If the Choice field in your SP list allows multi select, either you could change the select setting from multi-select to single-select, or you could simply change the formula based on the multiple selections. Of course all changes have to be based on your actual needs, if you really only need single selection, then changing the data type is the best option.

1). If you just need a single selection, you could go to column setting and turn off the allow multi select option, and please modify as below:

Filter(
'Customer Reference Data',
CompanyDropDown.SelectedText.Value=Blank() || Title =CompanyDropDown.SelectedText.Value,
IndustryDropDown.SelectedText.Value=Blank() || Industry.Value =IndustryDropDown.SelectedText.Value,
IndustrySubgroupDropDown.SelectedText.Value=Blank() || 'Industry Subgroup' =IndustrySubgroupDropDown.SelectedText.Value,
ModulesDropDown.Selected.Value=Blank() || Module.Value = ModulesDropDown.Selected.Value,
//Referenceable.SelectedText.Value=Blank() || Title =CompanyDropDown.SelectedText.Value,
//SolutionDropDown.SelectedText.Value=Blank() || Solutions.Value = SolutionDropDown.SelectedText.Value,
EngagementTypeDropDown.SelectedText.Value=Blank() || Engagement.Value =EngagementTypeDropDown.SelectedText.Value)

2). If you need a multi selectionplease modify as below:

Filter(
'Customer Reference Data',
CompanyDropDown.SelectedText.Value=Blank() || Title =CompanyDropDown.SelectedText.Value,
IndustryDropDown.SelectedText.Value=Blank() || Industry.Value =IndustryDropDown.SelectedText.Value,
IndustrySubgroupDropDown.SelectedText.Value=Blank() || 'Industry Subgroup' =IndustrySubgroupDropDown.SelectedText.Value,
If(
IsEmpty(ModulesDropDown.SelectedItems.Value),
IsEmpty(Module.Value)
) || Concat(
ModulesDropDown.SelectedItems.Value,
Value,
","
) in Module.Value,

//Referenceable.SelectedText.Value=Blank() || Title =CompanyDropDown.SelectedText.Value,
//SolutionDropDown.SelectedText.Value=Blank() || Solutions.Value = SolutionDropDown.SelectedText.Value,
EngagementTypeDropDown.SelectedText.Value=Blank() || Engagement.Value =EngagementTypeDropDown.SelectedText.Value) 

Best Regards,
Qi

@v-qiaqi-msft Thank you, that got me halfway there. I'm getting an error on the Concat that has invalid arguments but I will work through that. The SharePoint list needs to be a multi-select, but the filter will only filter on a single item within that multi-select. I think that is where I'm getting hung up. If there are A, B, and C, assigned to that record, the end-user will filter on an individual dropdown basis. They would select A and anything with an A should then filter in the Gallery. Even if the order is B,C,A or A,B,C

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.

Power Apps Ideas

Check out the New Ideas Site

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

Top Solution Authors
Top Kudoed Authors
Users online (3,316)