cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kriscachia
Regular Visitor

Filtering a Dropdown based on a Yes/No Field In SharePoint list

Hi all, 

 

Im quite new to powerapps and i have been struggling to do a really simple thing with a drop down. 

 

I have a sharepoint list called 'QA Reps' with 2 fields, QA_rep_name (text field) and Hide (Yes/No) Field

 

I have another sharepoint list called RRF and i have a lookup field called QA_Rep that is getting the QA_rep_name from the list 'QA Reps'

 

In my powerapps form linked to the sharepoint list RRF basically want to filter the list of choices so i do not show those records where Hide = "Yes"

 

What alterations do I need to do to in the Items Property: Choices([@RRF].QA_Rep) to achieve this ?

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

@kriscachia 

You're going to need to do a little more to get to the results you want.

The Choices function is going to bring back all the items from the other list.  What you need to do is filter them.

 

So for your DropDown, you will need to put a formula similar to this in your Items Property:

Filter(
    AddColumns('QA Reps', 
        "hideVal", Hide
    ),
    !hideVal
)

 

What's going on here?  First (innermost) we are adding a column to the results from the QA Reps list called hideVal and setting it to the value of the Hide yes/no column.  The reason you need to do this is because the Filter function in PowerApps still (after years of it being a problem) does not filter true or false values properly.  So, this little trick will create a new column in the results that will truly have the true or false value of the yes/no column.

Then (outermost) we filter the results based on the hideVal being not true - !hideVal

 

That should give you the filtered list you want for the control.

 

I hope this is clear and 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.
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

3 REPLIES 3
RandyHayes
Super User III
Super User III

@kriscachia 

You're going to need to do a little more to get to the results you want.

The Choices function is going to bring back all the items from the other list.  What you need to do is filter them.

 

So for your DropDown, you will need to put a formula similar to this in your Items Property:

Filter(
    AddColumns('QA Reps', 
        "hideVal", Hide
    ),
    !hideVal
)

 

What's going on here?  First (innermost) we are adding a column to the results from the QA Reps list called hideVal and setting it to the value of the Hide yes/no column.  The reason you need to do this is because the Filter function in PowerApps still (after years of it being a problem) does not filter true or false values properly.  So, this little trick will create a new column in the results that will truly have the true or false value of the yes/no column.

Then (outermost) we filter the results based on the hideVal being not true - !hideVal

 

That should give you the filtered list you want for the control.

 

I hope this is clear and 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.
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

Hi Randy,

 

Thanks for your reply, the filtering worked perfectly. I just added the fieldname after the filter command as follows because the dropdown wasn't displaying the QA_Rep_name

 

Filter(

    AddColumns('QA Reps',

        "hideVal", Hide

    ),

    !hideVal

).QA_Rep_Name

 

Many thanks once again. Very nice trick!

@kriscachia 

Yes, you can definitely add the field name, or you can choose it in the properties for the Display Field.  It just depends on what you want the records of your dropdown to be.  In your case, you'll have single column records.

Glad it all helped out.

_____________________________________________________________________________________
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.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (3,186)