You’re offline. This is a read only version of the page.
373
Hello again.
Is there an easy way of filtering blank value from a dropdown box items list?
Suppose I have a table or collection (Fruit) that looks like this;
Apple, Granny Smith, Green
Apple, Pink Lady, Red
Apple, , Pink
Plum, Victoria, Green
Plum, Cherry, Purple
Plum, , Black
The idea is that dropdown1 would select Apple or Plum.
Dropdown 2 items is filled with Column2 where Column1='Apple'. and dropdown3 is filled with column3 where Column1 = 'Apple'
I have got the dependencies working ok, so that dropdowns 2 and 3 only display what's appropriate for the selected fruit.
The problem is that where the options in columns 2 and 3 are not the same number of rows, the blanks appear in the dropdown. So, for example, there are only two options for the type of apple, (Granny Smith and Pink Lady) but three colours. This means that there will be a blank in dropdown2 after Pink Lady.
I have tried the Filter command with !IsBlank but to no avail.
If I tried what you did, it will complain that it does not recognize "RecordValue".
I have a similar construct to yours. I also have three dropdowns, one called "Continent", one called "Regions", and one called "Agencies".
I have a list for each.
Continent is obviously static and won't change, but in each of those other 2 lists, there is a Yes/No "Active" field, and a calculated field (either "RegionActive" or "AgencyNameActive", respective to each list) that I use to translate that, to either display the value or blank it out. If it is blank, I didn't want that blank to appear in my Items collection - it means it is no longer active and I wanted to filter it out, like you did.
So I have a "Continent", "Regions", and an "Agencies" lookup column on the list that my form is attached to, and I wanted the user to fill in the affected continent, region, and agency on my form.
Continent just gets the items directly from my list's field, for Items:
Share
Report
All responses (
Answers (
Choices([@'My Main List'].Continent)
For Region: If I tried this, to use my calculated field:
Filter(Regions, !IsBlank(RegionActive), Continent.Value = dcvContinent.Selected.Value).RegionActive
It would say "Delegation warning. The highlighted part of this formula might not work correctly on large data sets. The "Not" operation was not supported by this connector". The squiggly line was under the exclamation point. And even though my list of regions was over 100, it was not over 200, and therefore I didn't think it was that large. But it was not working as expected - my empty items were still appearing.
I fixed it using the "Active" Boolean flag I had established on the Regions list directly:
Filter(Regions, Active = true, Continent.Value = dcvContinent.Selected.Value).RegionActive
And I did the same for my Agencies dropdown, pointing it to the Active flag I had established on the Agencies list:
Filter(Agencies, Active = true, Region.Value = dcvRegion.Selected.Value).AgencyNameActive
One caveat: on the Update routines, you do need to then do a lookup back to that list to get the ID for that value the user selects:
Regions field:
{
'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: LookUp(Regions, RegionActive = dcvRegion.Selected.RegionActive).ID,
Value: dcvRegion.Selected.RegionActive
}
Agencies field:
{
'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: LookUp(Agencies, AgencyNameActive = dcvAgency.Selected.AgencyNameActive).ID,
Value: dcvAgency.Selected.AgencyNameActive
}
I finally got this working!
So this is now the code that's in the second dropdown (remember it's dependent upon the value in the first)
Filter(Collection2, !IsBlank(RecordValue ), iParentId = ddDropdown1.Selected.ParentId)
#1
WarrenBelz
146,524
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,906
Most Valuable Professional
