Solved: Filtering by Several Combo Boxes to Display an Ans...

ElizabethK · ‎10-19-2022

I have a SharePoint list that has 39 items in it. There are four different choice columns in the list to describe a particular scenario. The final column in the list is a step by step direction (multi-line text) (as shown below):

I would like to create four combo boxes to describe the scenario and have those choices filter a gallery down to just one choice - then I would like that choice to display in a form viewer and would likely make the gallery invisible altogether.

I have tried a few different approaches on a small scale but they do not seem successful:

Filter('Resending Links',(ComboBox4.Selected.Value = "App Not Yet Started" & ComboBox4_1.Selected.Value = "Compact State - Using New App" & ComboBox4_2.Selected.Value="Expired Link" & ComboBox4_3.Selected.Value = "N/A"))

The other one I did was:

Filter('Resending Links', And(Or(IsBlank(Combobox4.SelectedItems),IsEmpty(Combobox4.SelectedItems),Scenario.Value in ComboBox4.SelectedItems),Or(IsBlank(ComboBox4_1.SelectedItems),IsEmpty(ComboBox4_1.SelectedItems),'App Type'.Value in ComboBox4_1.SelectedItems),Or(IsBlank(ComboBox4_2.SelectedItems),IsEmpty(ComboBox4_2.SelectedItems),Condition.Value in ComboBox4_2.SelectedItems), Or(IsBlank(ComboBox4_3.SelectedItems), IsEmpty(ComboBox4_3.SelectedItems),Circumstance.Value in ComboBox4_3.SelectedItems)))

I also wondered if "visible" with if(combobox4.Selected.Value = "App Not Yet Started") & (Combobox4_1.Selected.Value="Compact State - Using New App")......,true - and then just doing 39 different combinations of that - but I am not sure if that is the best way to go about it.

May I have help, please?

RandyHayes · ‎10-20-2022

@ElizabethK

Okay...then let's go simple!

Change your Items property of ComboBox4 to:

GroupBy(
    AddColumns('Resending Links', "_scenario", Scenario.Value), 
    "_scenario", "Items"
)

Change the OnChange action of ComboBox4 to:

Reset(ComboBox4_1)

Change the Items property of ComboBox4_1 to:

If(CountRows(ComboBox4.Selected.Items)>0,
    GroupBy(
        AddColumns(ComboBox4.Selected.Items, "_appType", 'App Type'.Value), 
        "_apptype", "Items"
    )
)

Change the OnChange action of ComboBox4_1 to:

Reset(ComboBox4_2)

Change the Items property of ComboBox4_2 to:

If(CountRows(ComboBox4_1.Selected.Items)>0,
    GroupBy(
        AddColumns(ComboBox4_1.Selected.Items, "_condition", Condition.Value), 
        "_condition", "Items"
    )
)

Change the OnChange action of ComboBox4_2 to:

Reset(ComboBox4_3)

Change the Items property of ComboBox4_3 to:

ForAll(ComboBox4_2.Selected.Items As _item, Patch(_item, {_circumstance: Coalesce(_item.Circumstance.Value, "N/A")}))

Add an HtmlText control to your screen and set the HtmlText property to:

ComboBox4_3.Selected.Steps

Based on what I gathered from you on my questions...this should provide what you are looking to do.

See what you think!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

RandyHayes · ‎10-19-2022

@ElizabethK

Your Gallery Items filter should just be based on the selections.

Please consider changing your Formula to the following:

Filter('Resending Links',
    (Scenario = ComboBox4.Selected.Value || IsBlank(ComboBox4.Selected.Value)) &&
    ('App Type' = ComboBox4_1.Selected.Value || IsBlank(ComboBox4_1.Selected.Value)) &&
    (Condition = ComboBox4_2.Selected.Value || IsBlank(ComboBox4_2.Selected.Value)) &&
    (Circumstance = ComboBox4_3.Selected.Value || IsBlank(ComboBox4_3.Selected.Value))
)

This all assumes that the Items properties of your Dropdowns are all based on Choices.

i.e. Choices('Resending Links'.Scenario)

The next step is to set the record for your form. See if the above give you what you need first.

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Really want to show your appreciation? Buy Me A Cup Of Coffee!

ElizabethK · ‎10-19-2022

@RandyHayes as usual - you are my hero...but unfortunately it did not work.

So I am using combo boxes, should I be using drop downs instead?

Each combo box's formula is the choices, but I also alphabetized them, could that be causing the error maybe?

Combobox4, Items: SortByColumns(Choices('Resending Links'.Scenario),"Value")

Combobox4_1, Items: SortByColumns(Choices('Resending Links'.'App Type'),"Value")

Combobox4_2, Items: SortByColumns(Choices('Resending Links'.'Condition'),"Value")

Combobox4_3, Items: SortByColumns(Choices('Resending Links.'Circumstance), "Value")

I did the formula you suggested and it said they were incompatible types for comparison for Galler24.Items

RandyHayes · ‎10-19-2022

@ElizabethK

This would mean that you have set your columns (i.e. Scenario, App Type, etc.) to be multiple-select.

Can you verify if that is the case by looking at your column settings in the list settings?

If they are set to be multiple-select choices, do you need that?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Really want to show your appreciation? Buy Me A Cup Of Coffee!

ElizabethK · ‎10-20-2022

Hi @RandyHayes - you are so clever, I would have expected that to be the answer too, but when I looked at each column, none of them allow multiple selection. (I am including a screenshot of the column settings just so you know I looked and it is true 🙂 )

But when I demo the app, I can choose multiple...how do we disable that within the combo box settings?

ElizabethK · ‎10-20-2022

I changed to dropdowns to see if that would make a difference - but it did not. I get the same error.

RandyHayes · ‎10-20-2022

@ElizabethK

Boom...smack to my head!! Totally forgot they were choice columns in the formula!! 🙄

Formula should be:

Filter('Resending Links',
    (Scenario.Value = ComboBox4.Selected.Value || IsBlank(ComboBox4.Selected.Value)) &&
    ('App Type'.Value = ComboBox4_1.Selected.Value || IsBlank(ComboBox4_1.Selected.Value)) &&
    (Condition.Value = ComboBox4_2.Selected.Value || IsBlank(ComboBox4_2.Selected.Value)) &&
    (Circumstance.Value = ComboBox4_3.Selected.Value || IsBlank(ComboBox4_3.Selected.Value))
)

Must have been too long a day yesterday!!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Really want to show your appreciation? Buy Me A Cup Of Coffee!

ElizabethK · ‎10-20-2022

@RandyHayes Why are you so amazing? Yep - that worked. Now...for setting the record for the form, and making the gallery invisible? Is there a way that once the user chooses all of those drop downs....the directions just show up in a form - no clicking on the item in the gallery necessary? - the dream mock up:

Also....

The combo box still does allow me to pick more than one option if I wanted to - any way to disable that?
Also, also, There is are a few list items where the circumstance does not matter....

For these ones...is there a way for me to make it where if the first 3 options are selected, the fourth option automatically defaults to N/A (likely then making that question invisible with an "if" statement and then it shows the steps? Mock Up:

Randy I owe you coffee yet again. ❤️🍵

RandyHayes · ‎10-20-2022

@ElizabethK

Thanks 😁

So, here would be my big question...will it always resolve to ONLY one record in the gallery? Is there a potential to have more than one record matching? And if so, then what then?

On these:

The combo box still does allow me to pick more than one option if I wanted to - any way to disable that?
Turn off the Allow Multiple Selections property.
Also, also, There is are a few list items where the circumstance does not matter....
This actually kind of plays into my above question (I think) in regard to matching to the exact record. But, yes, we can make the dropdown be N/A or just not visible.

In regard to sort of automatically showing the form and making the gallery disappear...I am wondering what role the gallery plays then. If the user is not selecting a record, then what is the gallery providing?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Really want to show your appreciation? Buy Me A Cup Of Coffee!

ElizabethK · ‎10-20-2022

Hey @RandyHayes

Correct - the combination of 1-4 answers will always resolve to only one record.

The use of this particular screen in the app is instructional in nature, I'm a learning and development specialist so I am using PowerApps a little differently that the norm.

Instead of providing staff with a document that has a large list of all the different scenarios and what procedure to follow, I decided I would make it so they can think about the scenario they find themselves in...pick the choices for each column in the sharepoint list data set, and have that then pull up the step by step directions.

I guess I thought the gallery was the only way to narrow it down and then the form viewer would be the way to manipulate showing those instructions.

So if columns 1, 2, 3 and 4 match when the user puts that info in, the form viewer should display my last column, which shows the steps...does that make any sense?