To filter by choice fields in gallery

[Anonymous]

Hello! I have New customer list in SP. There are 3 choice fields there- City, Division and Customer group. The same fields exists in excel table, connected to gallery as data source. How to make a filter to select only relevant info from excel in gallery?

[rebeccas]

There are several ways you can do this it just depends on how you want to make it available. You can do a button or control that they select that sets a variable and then it filters off it. You could have Combo boxes they select from and your gallery filter off that selection.

What do you want to have the user click on to decide which of those show and do you want a combination of any of those three choices?

[Anonymous]

Hello, Rebecca,

User can select any combination  of three.  Depending on combination, Excel should return the  "standard discount"- field in excel. Please, suggest the simpliest way, As I am a beginner.

[rebeccas]

City, Division, Customer

Put in your gallery and on that same screen (outside Gallery) put in three Combo boxes. The Items for City would be =Distinct(DataSource.City, City)

This will help remove the duplicates and let you pick from just the ones that have been used. 

Do the same thing for Division and Customer. Also...for each of these turn on the ability to select multiples. That is a toggle towards the bottom on the right when you have the combo box selected. 

Then for the Gallery Items you would do something like this:

If(!IsBlank(cb_Customer.Selected.Result),
 If(!IsBlank(cb_Division.Selected.Result),
  If(!IsBlank(cb_City.Selected.Result),
   Filter(SPLIst, CustomerName=cb_Customer.Selected.Result && Division=cb_Division.Selected.Result && City=cb_City.Selected.Result),
   Filter(SPLIst, CustomerName=cb_Customer.Selected.Result && Division=cb_Division.Selected.Result))
  ,
  If(!IsBlank(cb_City.Selected.Result),
   Filter(SPLIst, CustomerName=cb_Customer.Selected.Result && City=cb_City.Selected.Result),
   Filter(SPLIst, CustomerName=cb_Customer.Selected.Result))
 ),
 If(!IsBlank(cb_Division.Selected.Result),
  If(!IsBlank(cb_City.Selected.Result),
   Filter(SPLIst, Division=cb_Division.Selected.Result && City=cb_City.Selected.Result),
   Filter(SPLIst, Division=cb_Division.Selected.Result))
  ,
  If(!IsBlank(cb_City.Selected.Result),
   Filter(SPLIst, City=cb_City.Selected.Result),
   SPLIst))
 )

Let me know if you need some help breaking down that if statement. 

[v-xida-msft]

Hi @Anonymous ,

Do you want to filter your Gallery Items (which connects to Excel table) based on the combination of three Choice fields from your SP List?

I have made a test on my side, please take a try with the following workaround:

1. Add three ComboBox controls in your app -- CityComboBox, DivisionComboBox and CustomerGroupComboBox. Set the Items property of the CityComboBox to following:

Choices([@'YourSPList'].City)

Set the Items property of the DivisionComboBox to following:

Choices([@'YourSPList'].Division)

Set the Items property of the CustomerGroupComboBox to following:

Choices([@'YourSPList'].'Customer Group')

set the SelectMultiple property of above three ComboBox to following:

false

Set the Items property of the Gallery to following:

Filter(
         'YourExcelTable',
          If(
             IsBlank(CityComboBox.Selected.Value),
             true,
             City = CityComboBox.Selected.Value
          ),
          If(
             IsBlank(DivisionComboBox.Selected.Value),
             true,
             Division = DivisionComboBox.Selected.Value
          ),
           If(
             IsBlank(CustomerGroupComboBox.Selected.Value),
             true,
             CustomerGroup = CustomerGroupComboBox.Selected.Value
          )
)

After that, when you select proper values within above three ComboBoxes, the related records from your Excel table would be shown up in your Gallery.

Note: The City, Division and CustomerGroup are all columns in your Excel table.

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

[Anonymous]

Hello, Kris,

I have done all as you advise, the only one point- on the Gallery I have to select all three boxed again, as it does not copy the value from the main page.

Might it be the case, when item in SP is not created yet, and formula does not see any value for City/Division/Customer group?

[v-xida-msft]

Hi @Anonymous ,

Have you taken a try to set the Items property of the ComboBox to Choices('Your SP List'.ChoiceField) formula?

Based on the issue that you mentioned, I have made a test on my side, and don't have the issue that you mentioned. Actually, regardless of whether or not there are already records existed in your SP List, the Choices('Your SP List'.ChoiceField) formula would still return available options from the Choice field in your SP List.

In addition, if you want to copy the selected Choice field value from your Main page to your Gallery screen, please consider set a Default value for the three ComboBoxes in your app.

Set the DefaultSelectedItems property of the City ComboBox to following:

{
    Value: "Selected City Option from Main page"
}

Set the DefaultSelectedItems property of the Division ComboBox to following:

{
    Value: "Selected Division Option from Main page"
}

Set the DefaultSelectedItems property of the Customer group ComboBox to following:

{
    Value: "Selected Customer group Option from Main page"
}

Please consider take a try with above solution, check if the issue is solved.

Best regards,

[Anonymous]

Kris, I doublechecked your recommendations and seems to be I did it correctly.

I see all the choice fields in gallery ( see attachment), byt my problem that user has to select it twice- first he does it  in the main page " General customent information", and then he has to do it again on the page Gallery Commercial terms. Is there any way to do it once- as user has selected it on main page, it is copied to gallery?

[rebeccas]

On the first page you can set a global variable that will carry to the second where you can refer to it. 

Use: Set(varName, ThisItem.Field) 

Just put there wherever they are clicking to select it or on the button that they click to go to screen two. You can replace the "varName" with anything you want to be the variable name (a name that isn't used elsewhere) and then the place that says "Field" you will put the value you want to save and carry. 

UpdateContext({}) is used for setting variable to use on same screen and Set() is used to set one that can be used elsewhere in your app.