Combo Box Cross Filtering

[TimHawkins]

I have an app that I'm building that I'm trying to create dropdown boxes/combo boxes for the user to filter the data. Currently I'm connected to a SharePoint list with dozen of columns, but the 3 of note are:

Facility (Sharepoint Choice field)

Engineer (Sharepoint person or group field)

Status (Sharepoint Choice field)

I have a dropdown box grabbing all of the Facility choices available via:

Choices('ListName'.'Facility')

This is working great. I also have a combobox for collecting the engineer names with similar syntax:

Choices('ListName'.'Engineer')

What I'm struggling to figure out is a way to have the Engineer combobox update to only populate with values where the list entry's Facility value in SharePoint matches what the user has selected in the Facility dropdown. To add another wrinkle, I'd also like to be able to only bring over items where the Status field is equal to "Open".

For example, in the data below, if the user selects "Facility B" from the Facility dropdown, I would like the Engineer combobox to only show Engineer 4, Engineer 5, and Engineer 7.

 I've tried every combination of filtering on the Engineer combobox Items field that I can think with no luck. Is there an obvious solution I'm missing?

Thanks-

[Amik · Accepted solution]

@TimHawkins - assuming all Choice fields are set to single choice, I think the below is what you're trying to do?

Apply the below to the Items property of the Combo Box for Engineer:

Filter(
    AddColumns(
        'Your SharePoint List',
        "_Engineer",
        Engineer.DisplayName
    ),
    Facility.Value = 'Your Facility ComboBox'.Selected.Value,
    Status.Value = 'Your Status ComboBox'.Selected.Value
)

After adding the above expression, configure the Combo Box properties to return the "virtual" column we created:

Note despite the lack of a warning message, the AddColumns function is not delegable.

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan

[FLMike]

Hello,

Apologies but it's a bit confusing to me what you are trying to accomplish and think maybe some disconnects between the questions and your ultimate goal are. So please 🙂 see if this works for you or if you have more questions etc.

1. You require hierarchical filtering because you want to filter the whole list by Status. But where are you putting these results, a gallery or a data table or other in the app?

2. Is your intent that they can edit these things in the app or create new? There can be a difference in how the data above drives edit/view versus new.

Filtering the results by status. 

Filter('Your SharePoint List', ChoiceColumn.Value = "Open") 

Filtering the Items added to the Engineer dropdown based on the selection in the Facilities drop down compared to the list of total possible engineers in the list that have that same facility and the item is status = open

There are a couple of ways to do this. 

1. in the App.OnStart or other place load all the values into collections but I have no idea how many there are honestly

2. Use a virtual column search but in this case, I don't think that helps because I assume you will be driving this off of some New/Edit/View overall gallery/database/card

So, I chose this way.

I have an engineer and facilities choice fields in my sample list. You can also see that I have a status. All of these are choices. I already gave you the way to filter ALL the records based on the status

For the next in the facilities box I have this :

Choices('Solartech List Demo'.Facility)

Note: I am not filtering on if the facility has at least ONE open item. You didn't ask for that, so you get them all atm.

Next, in the OnChange event of the facility I have this code.

Clear(EngineersOnly);

ForAll(Filter('Solartech List Demo', Status.Value = "Open" && Facility.Value = facilities.Selected.Value),
    Collect(EngineersOnly,  {Engineer: ThisRecord.Engineer.Value} );
);

This will clear any existing entries and only add back a single value, the Engineer column into a simple collection. This stops it from collecting a lot of other stuff

Now in the engineer drop down I have this in Items

EngineersOnly

and in the Value field selection in the right, I have Engineer (which is the only column)

Now that does everything y0u asked for but I still feel there is a disconnect between the answers and what you want all up.

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey