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;
and I was trying the following syntax:
Filter(Choices('Remote Access Request'.Contractor_x0020_Name), Contractor_x0020_Name_x003a_Appr.Value = "true")
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)
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.
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,
Check it out!
Fill out a quick form to claim your user group badge now!
Find out where you can attend!
Features releasing from October 2019 through March 2020
The largest Power BI, Power Platform, and Data conference in New Zealand