cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ando23
Level: Power Up

Filter Dropdown value based on another fields true or false status.

Hey,

 

How would I filter a SP choices dropdown field based on a status value, true or false. Basically I am trying to have the drop downlist present only contractor names that have a true value in the Contractor Name:Approved field.

 

In Sharepoint I have the following fields;

Sharepoint List

and I was trying the following syntax:

Filter(Choices('Remote Access Request'.Contractor_x0020_Name), Contractor_x0020_Name_x003a_Appr.Value = "true")

3 REPLIES 3
Super User
Super User

Re: Filter Dropdown value based on another fields true or false status.

Hi @ando23 

 

Is your 'Contractor name approved' column a text column?

Is your form in Edit mode or New mode?  (Edit populates from Items: property, New populates from Default: property).

 

If it's a Yes/No column then you can try this

Filter(Choices('Remote Access Request'.Contractor_x0020_Name), Contractor_x0020_Name_x003a_Appr.Value)

Kind regards,


RT 

ando23
Level: Power Up

Re: Filter Dropdown value based on another fields true or false status.

Hey RT,

 

So my form is in edit mode and the 'Contractor name approved' is just a single line of text. However it is a lookup from another sharepoint list.

 

I tried your suggestion below and it looks like it no longer returns any values when you make this change.

 

 

Super User
Super User

Re: Filter Dropdown value based on another fields true or false status.

Hi @ando23 ,

 

Yip, it wouldn't - Choices is for choices, and there are none in that column if it's just plain text.
I assume then that it is a plain text field and not a boolean field?

 

Either way, that's not really the problem.  I believe the reason it's not working is that you're trying to tell PowerApps that the Contractor_x0020_Name column in your 'Remote Access Request' SPO list is a Choices column - and it can't be because it appears to be a Lookup column?

 

It's a bit unclear, so - some assumptions: 

1: If your form is in EDIT mode, that means you already have a record and now you want to change it. 

2: You want to change the contractor name for the 'Remote Access Request' record

3: You want to filter the available Contractor names in the dropdown to only those contractor names who have a "true" value for the Contractor_x0020_Name_x003a_Appr column in the same source.

4: The source of your Contractor Data is another SPO List, (we'll call it ContractorTable for now) and you've joined this to your 'Remote Access Request' table by way of a Lookup column called Contractor_x0020_Name - we'll just assume the target list source is ContractorTable.ContractorName

4: You're also then bringing in the Contractor_x0020_Name_x003a_Appr column from this ContractorTable.

5: The "Contractor_x0020_Name_x003a_Appr" column is the Choice column in this equation (hence the .Value property) and it has more than two options (being "true", "false" and some other values - otherwise I'm not sure why you'd be using a Choices column for this info instead of a Yes/No column or just plain text).

 

So my confusion originated with the Choices function being applied to 'Remote Access Request'.Contractor_x0020_Name because this appears to be a Lookup column in SPO, not a Choice column....

 

Back to what you're actually trying to achieve - a dropdown of names from the ContractorTable, where the contractor's name is approved - i.e. Contractor_x0020_Name_x003a_Appr = "true".  If you also wanted to filter these results by names that already exist in your 'Remote Access Request' list, then that's a different formula.

If this isn't what you want you may need to clarify some more 🙂

 

 

But - if I'm right on the first count, then to achieve this, you should be connecting to (and filtering) your ContractorTable for the dropdown - not the 'Remote Access Request' table.

Your DropDown items filter should then look like this;

Filter(ContractorTable, Contractor_x0020_Name_x003a_Appr.Value = "true")

Don't be surprised if you get a delegation warning though - Choice columns in SPO are complex columns and generally not friendly when it comes to filter delegation.  You may want to manage your inputs into that column from PowerApps using a simple toggle button or actual boolean true/false but flatten the column down to plain text.  

 

Lastly, assuming you for some reason wanted to filter the list further by only looking at names already in your 'Remote Access Request', it might look something like this;

Filter(ContractorTable, ContractorName in 'Remote Access Request'.Contractor_x0020_Nam && Contractor_x0020_Name_x003a_Appr.Value = "true")

Again - expect delegation issues with this approach. 

In general, my approach when working with SPO as your source, is to let PowerApps do the complicated joins, filters and choices and keep your data on the backend flat, indexed and therefore as delegable as possible.  The more complicated the source data constructs, the more complicated (and constrained) PowerApps tends to be.

 

Hope this helps,


RT

 

 

 

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,793)