Using a combo box in a gallery filter with yes/no or true/false

[CindyZ]

I am using a Sharepoint list.  

I have lots of filters working perfectly, but I'm tripped up on this one.  It is a toggle in my app that shows as below in my sharepoint  -- a checkmark for true.  



If I use this distinct formula to try to pull true/false or yes/no, I get a blank dropdown. Sort(Distinct(colMembers,'Leadership Role?'),Result)

If I put a table in of ["Yes", "No"] or ["true", "false"], then the formula shows me something similar to the following (tried lots of variations): 

 Basically, it does not give me an option to choose after "Select" or "Selected Items".    BTW, if I export the list to excel it comes through as TRUE or FALSE.  I tried that also -- all caps.  Same issue; no choice of value or text after Selected.  

Anyone know how to untangle this for me? 

Thanks so much!  

Cindy 

[RandyHayes · Accepted solution]

@CindyZ 

You are trying to compare text to Boolean in your formula.  This will not work.

I'd seriously reconsider using a combobox for a yes/no choice and instead favor a dropdown.  A combobox only brings multiple selection and searching to the dropdown experience - I cannot imaging the your scenario for this would need those.  But, a combobox brings extra complexity that you don't need.

That aside, Set your Items property on the combobox (or dropdown if you change) to:

   Table({Choice:"Yes", Value:true}, {Choice:"No", Value:false})

Now the formula you have for the Gallery should work fine.

I hope this is helpful for you.

EDIT: Oh, I should add...There is an issue with Boolean column filtering in PowerApps for Yes/No columns.  So, your results may not be what you expect.

[CindyZ]

Thanks so much, Randy.  I did try, prior,  to work with the drop down box but eventually just gave up.  I'm sure it is simple but somehow it always reverts back to the default in the control and not what I'd submitted to my list.  My submission shows perfectly in my list.  I'm submitting the drop down choice to my sharepoint list via update on the card.  The control shows choices of Yes and No in that order.  I submit "No" and my sharepoint list says "No".  But, when my screen refreshes, it shows Yes again.  

What I'm trying to do is state -- If this dropdown shows Yes, then these other fields must become visible and their content is required.  If I change it back to No, then I want it to clear those required and visible fields are no longer seen and they have no content. 

I did change my formula in the combo box I had but the formula gave me the same results as I had prior.  

Sorry...I always expect this stuff to be easy.  

[RandyHayes · Accepted solution]

@CindyZ 

Actually it is easy! Yes, what you mention is easy to make happen in the form....

When you add your text based field to your form, all you need to do is set the control type of the field to "Edit Allowed Values".  PowerApps will automatically replace the input control with a Dropdown and it will connect all of the pieces you need to make sure that it updates properly and shows its underlying value properly.

Once you change to Edit Allowed Values (and by the way, you cannot change the control type once you unlock the card), your datacard will now have an AllowedValue property.  In that property you can put ["Yes", "No"] and you are done!  All the rest is done for you.

BUT - I got the impression from your first post that the column was a yes/no column, so the approach is different for that.

A combobox is overly complex in this situation because comboboxes are table/record based.  Whereas Dropdowns are Text based.  So, for most trying to grasp PowerApps, the Dropdown is far easier to set its value than the Combobox.

Now, as for the other cards/fields on your form, their visible property should be based off of this dropdown control you have in your datacard.  So, let's say you have a dropdown called DataCardValue1 and you have other datacards that should be visible when the dropdown value is "Yes", then simply set the Visible properties of those datacards to : DataCardValue1.Selected.Value = "Yes"

Then they will be visible when Yes is selected and No when it is not.

If you want the controls in those datacards to reset when No is selected. Then set the Reset property of those controls to: DataCardValue1.Selected.Value = "No"

So, the reality is - yes it is easy - with the above, you have:

1) Changed the control type

2) Added a table to the AllowedValues property

3) Added a formula in the Visible of the datacards

4) Added a formula in the Reset of the controls

[CindyZ]

Thank you so much, Randy! 

[RandyHayes]

@CindyZ 

Everything working as expected now?

[CindyZ]

Randy, I started with a clean slate -- and I'm still having challenges.  Sorry.  No matter what I do, my text input is not resetting.  

Let me tell you what I have. 

1. A Text box in my Sharepoint list which I set up as you suggested in paragraphs 1 & 2 that has given me a Yes/No drop down in my form entitled DDGuestSpeakerYN.  This works perfectly fine.  If I choose yes, I get yes in my list and yes is still on my form when I return to it. Same with no.  All seems to be good there. 

It has-

DataCard
AllowedValues(DC): ["Yes","No"]
DataField: Guest Speaker
Default: ThisItem.GuestSpeaker

Update: ddGuestSpeakerYN.Selected.Value

DataCardValue:
Default: Parent.Default
Items : Parent.AllowedValues

Then I have my Text Input dcEventSpeakerName
Datacard

Datafield: "EventSpeakerName"
Default: ThisItem.EventSpeakerName
Update: dcvEventSpeakerName.Text
Visible: ddGuestSpeakerYN.SelectedText.Value = "Yes"
Required: ddGuestSpeakerYN.SelectedText.Value = "Yes"

DataCardValue
Default: Parent.Default
Reset: ddGuestSpeakerYN.Selected.Value = "No"

No matter what I do, once I add a value into the dcvEventSpeakerName, the field will not return to blank OR clear my Sharepoint column - the name just stays.  I even tried a formula for that....no go. 

Thanks again. 

Anymore ideas or is it obvious what I'm missing.  

2. 

[RandyHayes]

@CindyZ 

First, not that it is related to your issue, but change your Visible and Required properties for the SpeakerName datacard to:

   Visible :  ddGuestSpeakerYN.Selected.Value = "Yes"

   Required: Self.Visible

The first one, Visible, you were using SelectedText.  That is a deprecated property and should not be used.

The second, it is best to have only one "real" formula to determine a "state", in this case visible already determines the state, so we can just use it in the Required as reference rather than repeating the formula.

So now, I am trying to break down what you have said:

No matter what I do, once I add a value into the dcvEventSpeakerName, the field will not return to blank OR clear my Sharepoint column - the name just stays.  I even tried a formula for that....no go. 

First - If you want to be able to "blank" out a column in your list, you need to make sure that the Error-level formula management feature is turned on in your app settings.

Next, can you describe this part of your statement - "the field will not return to blank" ?

[CindyZ]

Deleted this irrelevant content. 

[CindyZ]

Unfortunately, I had two apps with the same issue but pulled both of them into the same conversation -- so -- new readers, this may be confusing.  

But, as always Randy's instructions were perfect and I started over and all is working perfectly now.  So, Randy's reply 9 corrected the first question. 

Then, for the second problem where I wanted to select yes  in one a drop down and have it make another field visible as well as require input -- then if I select no in the dropdown,  I want it to wipe out the name that was input earlier and make the field invisible, Randy answered that in 7.  I started fresh, followed his guidelines and all is now working perfectly.  

My apology for the confusion -- and it is easy.  Some simple things just trip me up!