Dataverse filtering using a combobox with equipment in allowing multiple selected items, to show only results where room has all equipment.

(1) Share

Report

Posted on by SS-23070435-0

I have the following setup

Dataverse table with asset information on equipment in different rooms.

Combobox with the distinct equipment types.

I am wanting to filter the rooms to only show rooms which have all the equipment types selected.

At the moment when I apply filters it shows me the all the rooms which has any of the equipment selected. For example if I select equipment A, it will show me 20 rooms, if I then add equipment B to the combobox, it will show me 30 rooms. Required result is where it shows the rooms which has both equipment A and Equipment B, which result in only showing the 10 rooms which have both pieces of equipment in. Ideally this working for multiple selected pieces of equipment.

Is this possible from one combobox, without adding additional comboboxes, collections etc? In total there is 20 different equipment types, with over 300 rooms.

Any advice, guidance welcome

Categories:

General Questions

All responses (1)

Answers (1)

Verified answer

SS-23070435-0 28 on at

Like (1)

Report
Re: Dataverse filtering using a combobox with equipment in allowing multiple selected items, to show only results where room has all equipment.

@Ali_SZ_365 Many thanks for the reply. Your solution didn't work for me, and I think it's down to how I have the data modelled and that there is lookup columns involved, which meant I needed to add columns. It did however guide me to the solution.

For my situation, the solution required collections - there maybe an easier way, but this is code which worked
ClearCollect( colFiltered, AddColumns( Filter( Assets, Equipment.Name in ComboBox1.SelectedItems), "Room Number",Room.Room, "Equipment Name", Equipment.Name) ); ClearCollect( colFilteredGroup, Filter( GroupBy( colFiltered, "Room Number", "DATAGROUP"), CountRows(Distinct(DATAGROUP,'Equipment Name')) >= CountRows(ComboBox1.SelectedItems) ) );