Blend Multiiple Condition in Combo Box

[Phineas]

Location Filter:

I'm using the following to filter a Dropdown by Location (and based on the Member Location) that returns only those Streets that are associated with the Member's Location in Ascending order.

          Sort(Distinct(Filter(List, Location = Dropdown.SelectedText.Value),Street), Result,Ascending)

I'm using the following to filter a Dropdown by for all Locations and return all Cities in Ascending order.

          Sort(Filter(LocationCollection, Title=varCity), Title, Ascending)

I would like to build a condition that does both.

     1. Show all Cities (option 2 above)

     2. However if User hierarchy equals 'Region' option 1 kicks in. The Member List (where all of this is coming from) has a

        'ProgramHiearchy' column.

    3. Each member is assigned a hierarchy. Hiearchies are 'National', 'Region', 'Park', 'Employee'.

    4. Return all Cities unless User hierarchy equal 'Region' where only show Cities associated with their Location.

[iAm_ManCat]

Hi,

Its not exactly clear what you want out of your points 1 to 4, but I think I can help with the alternating options:

If(
   ProgramHierarchyDropdown.Selected="Region",
   // If region selected then filter by dropdown
   Sort( Distinct( Filter(List,
                          Location = Dropdown.SelectedText.Value
                   ),
                   Street
          ),
          Result,
          Ascending
   ),
   // Otherwise don't filter and show all cities
   Sort(Filter(LocationCollection, Title=varCity), Title, Ascending)
)

Let me know if you need any further help with this,

Cheers,

Sancho

[Phineas]

Thank you for your reply.

Let me try again.

Terms:
'Member List' - all user names, ProgramHierarchy, user Region, user City 

'Location List' - all Location Region names, City names 

'ProgramHierarchy' - the access level assigned to each user on the Member List. The hierarchies are 'National', 'Region' (meaning regional employee; and having no relationship to the 'city or location region'), 'Location', 'City'. 

'Region' - a geographical area where cities are located.

'Region' (ProgramHierarchy) - the user's assigned access hierarchy (also includes 'National', 'Location', and 'City'.

Dropdown 1 - content equals name of location region (Region 1, Region 2, Region 2, etc., where the user is located

Combo Box 1 (dependent upon Dropdown 1) - content equals list of Cities based on content of Dropdown 1, and defaults to user's City

Existing formula in Combo Box 1 returns ALL cities AND defaults to the user's city first.

Access to the Combo Box is controlled by the 'ProgramHierarchy' assigned to each member through the Combo Box DisplayMode property.

If member hierarchy is 'Location' or 'City' the Combo Box is 'Disabled'. For all other hierarchy Combo Box is 'Enabled' and ALL Cities are returned. Regardless of user or hierarchy the list always default to the logged in User's city.

What I need added: If the user's 'ProgramHierarchy' is 'Region' return only those 'Cities' within the region the user/user City is located. All other hierarchy show all Cities.

Example:

     If the user hierarchy is 'Region' and the user is located in 'Location 1' return to Combo Box ONLY Cities in 'Location 1'; if user is hierarchy 'Region' and is located in 'Location 3' return only Cities in 'Location 3'. All other hierarchy return all cities from all regions.

[iAm_ManCat]

If(
   // How are you determining region?? Enter that here
   ProgramHierarchyTHING="Region",

   // If region then filter by dropdown and combobox
   // replace Region and Location with the field names you used in the list
   Sort( Distinct( Filter(LocationList,
                          Region = RegionDropdown.Selected.Value
                   ),
                   Title
          ),
          Result,
          Ascending
   ),

   // Otherwise don't filter and show all cities
   Sort(Filter(LocationList, Title=varCity), Title, Ascending)
)

[Phineas]

I've got the filtering to filter by Region based on the user being a 'Region' hierarchy (I think) and only showning parks from that user's region.

             If(varIsHierarchyRegion, Sort(Distinct(Filter(MembeList(, Region=Dropdown1.Selected.Region),Park), Result, Ascending)

I need to combine the above witht the below, so that if the user is 'Region' hierarchy they only see parks from their region. This currently works with the first formula. I also need the second formual (that use to work fine - and shows all Parks) to kick in for all other hierarchy.

However, I haven't been able to append the above with the original (below) - which returns all Parks, without getting an error.

          Sort(Filter(ParkListCollection(Title=ParkAlpha), Title, Ascending)

[iAm_ManCat · Accepted solution]

Something like this?

 If(
    varIsHierarchyRegion,
    Sort(
         Distinct(
                  Filter(MembeList,Region=Dropdown1.Selected.Region),
                  Park
         ),
         Result,
         Ascending
    ),
    Sort(
         Filter(ParkListCollection,Title=ParkAlpha),
         Title, 
         Ascending
    )
)

[Phineas]

Actually took some of your and some of mine and come up with the following.

It works, but the Gallery no longer auto-populates data based on the content of the Combo Box on initial start any longer; It did with the old formula. Kinda need that function back. The correct home park DOES default into the Combo Box OnVisible, however the presence of the content in the Combo Box no longer triggers the auto-population of the data in the Gallery. 

In the Combo Box DSI is [varParkAlpha], which returns the home park of the logged in user, which IS what I want.

Once you change the default content in the Combo Box the Gallery works fine. Something about how the default content is loaded/viewed is not registering with the Gallery.