Multiple choice column options to look up options from another list and filterable in a gallery

[aantoniou]

I have a gallery in which I want to show assets based on a selected room on an organization's campus.

In my SP List named "Asset List", I have a column with the name of the asset, (let's call it "Desktop PC") and then a multiple choice column for the rooms it's available in. I want for that multiple choice column's options to be looked up in another SP List called "Room Lists". This avoids having to list every single room as a multiple choice option after having already listed it in "Room Lists" and also keeps both up to date in one go. (I know this is probably impossible though, so it's not a total issue).

The next option which is most important is for the gallery to be able to match a dropdown list of the campus' rooms (called campusRoomList) and pull up any assets who's multiple choice column has that room ticked on "Asset List".

This is what I have on the gallery Items:

Filter ('Asset List', Room.Value = campusRoomList.Selected.Title)

This works on single choice columns, but as soon as I make it multiple choice, it refuses to convert the choices to strings and compare.

For example, if I have one Desktop PC in each of the rooms C3.01 to C3.05, I want to be able to list the asset ONCE on the SP list and then on the multiple choice column tick all rooms from C3.01 to C3.05. Then in the app, when the dropdown list selects one of those rooms, say C3.02, the gallery will display any items that are located in C3.02 - in this case, Desktop PC and any others).

If this doesn't work, I will have to make hundreds of rows alone just for "Desktop PC" so that there's one for each room - and that's just one of 10-20 assets per room. This is a prone to false data entries by those populating the Asset List and will also pass the 2000 item filter limit, which can be reduced to about 20 items with the method I'm proposing.

Hopefully this is possible - thanks in advance.

[Amik · Accepted solution]

Hi @aantoniou ,

As I understand it, you have a SharePoint list called Asset List. This list contains a multi-choice column, but you'd prefer it was a multi choice Lookup column to another column in a separate SharePoint list called Rooms List.

In your App, you have a single select Dropdown control called campusRoomList. You want to set the Items property of that Dropdown control to your Multi Choice Lookup column, and then Filter a Gallery to display any items which contain the selected value in your Dropdown control.

1. Create a multi-choice Lookup column in SharePoint and ensure you have enabled multiple selections.

2. In your Dropdown control, enter the following in the Items property:

Choices([@'Asset List'].'Your multi choice lookup field')

3. Apply the below to the Items property of the Gallery:

Filter(
    'Asset List',
    Len(campusRoomList.Selected.Value) = 0 || campusRoomList.Selected.Value exactin Concat(
        'Your multi choice lookup field',
        ThisRecord.Value & ","
    )
)

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

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

[aantoniou]

Hi @Amik 

Thanks for your response, I hadn't thought of going with the LookUp option and allowing multiple choices so that works so far, however I've come across another issue when I try to make the name of the source List dynamic.

It is failing to fetch the image of the asset from the image column (which I've tried as both hyperlink and as an uploaded image).

I had to make some slight changes to determine which asset list to choose from, as I have multiple depending on each building. (I'm trying as much as possible to avoid the 2000 row limit).

As soon as I change 'Asset List' from your code to If statements like this:

Filter(
   If(buildingList.Selected.Title = "Campus 1", 'Campus 1 Asset List',
      If(buildingList.Selected.Title = "Campus 2", 'Campus 2 Asset List'
      )
   ),
   Len(campusRoomList.Selected.Value) = 0 || campusRoomList.Selected.Value exactin Concat(Room, ThisRecord.Value & ",")
) 

 It doesn't allow the image in the gallery to be set to assetImage which is the column in the Asset List.

ThisItem.assetImage

It says 'assetImage' isn't recognized.

This would all be solved if I could have the entire app filter through multiple data sources at once without setting conditions based on the results of dropdown lists. In some instances, some campuses have as many as 10 buildings and I don't think nesting 10 If statements is practical for the optimization of the app. I have multiple dropdown lists each cascading the next, going from Campus>Building>Room which leads to the gallery. Hope this makes sense.

[Amik]

@aantoniou assuming the schema is the same across all lists, particularly the logical name of the assetImage field, I cannot think of any reason the Asset Image would not be recognized.

On a separate note, consider using Switch as opposed to nested ifs. E.g.:

Filter(
    Switch(
        buildingList.Selected.Title,
        "Campus 1",
        'Campus 1 Asset List',
        "Campus 2",
        'Campus 2 Asset List',
        "Campus 3",
        'Campus 3 Asset List',
        "Campus 4",
        'Campus 4 Asset List',
        "Campus 5",
        'Campus 5 Asset List',
        "Campus 6",
        'Campus 6 Asset List',
        "Campus 7",
        'Campus 7 Asset List',
        "Campus 8",
        'Campus 8 Asset List'
    ),
    Len(campusRoomList.Selected.Value) = 0 || campusRoomList.Selected.Value exactin Concat(
        Room,
        ThisRecord.Value & ","
    )
)

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

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

[aantoniou]

Hi @Amik,

Thanks again, that works like a charm.
I was wondering if you know how this can be altered to have a combo box filter through a gallery? The client has requested that they can not only filter by room, but also filter by services to see the rooms instead. I've made a multiple choice lookup column in the Room List to write each room's functionality, e.g. Lecture Capture, Hybrid teaching, Live Streaming etc.

When I use this with a combo box, it seems to filter each combo box item individually - e.g. I want it to filter rooms that have Lecture Capture && Hybrid Teaching, not Lecture Capture || Hybrid Teaching. It's currently returning rooms for the latter. Keep in mind this is for a lot more than just 2 options. Thanks!

Filter('Room List',
 Len(requirementList.Selected.Title) = 0 || requirementList.Selected.Title exactin 
  Concat(
  Capability,
  ThisRecord.Value & ","
 )
)

[Amik]

Hi @aantoniou - I think what you're trying to say is that you want to Filter a multi-choice SharePoint Choice field with a multi-select Combo Box (a many-to-many filter). If that is correct then you can use the non-delegable example below: 

If(
    CountRows(requirementList.SelectedItems) = 0 || IsEmpty(requirementList.SelectedItems),
    'Room List',
    Ungroup(
        ForAll(
            requirementList.SelectedItems As _selecteditem,
            Filter(
                'Room List',
                _selecteditem.Value in Capability.Value
            )
        ),
        "Value"
    )
)

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

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

[aantoniou]

Hi @Amik,

That works but seems to be returning a room multiple times if it fits the criteria in more than one occasion. For example, if room 2.12 fits both option A and B, it will show room 2.12 twice in the gallery. I did have to change _selectedItem.Value to _selectedItem.Title.

Thanks,

Anastasios

[Amik]

@aantoniou my fault, try the revision below:

If(
    CountRows(requirementList.SelectedItems) = 0 || IsEmpty(crequirementList.SelectedItems),
    'Room List',
    //exclude blank rows from appearing in the Gallery for any selected choice which has not yet been entered in the data source
    Filter(
        ForAll(
        //exclude duplicates on many-to-many filters
            Distinct(
                Ungroup(
                    ForAll(
                        requirementList.SelectedItems As _selecteditem,
                        Filter(
                            'Room List',
                            _selecteditem.Value in Capability.Value
                        )
                    ),
                    "Value"
                ),
                ThisRecord
            ),
            Value
        ),
        Len(ID) > 0
    )
)

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

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

[aantoniou]

Hi @Amik,

Apologies, it seems this is doing what the original issue was which is returning all possibilities and not exact matches to all choices.

Thanks again,

Anastasios

[Amik]

Hi @aantoniou - not sure I understand. Could you share a screenshot? 

The above code will return all matches which exist in based on the selected choices.