Help with a filter please

[RobH1]

I have a SP List that looks something like the below. I need to have a gallery that Filters by the Office (ComboxOffice.selected.value) and then shows only the most recent (current) status for each door number.  Each office would have 50-60 doors.

So if I selected Berlin, my gallery would look like

I have tried so many combinations of Filter, Sort, group by that I'm going mad.  Any help would be greatly appreciated.  Thanks

[mdevaney · Accepted solution]

@RobH1 
In my view this problem requires two SharePoint lists

1.  'DoorEvents' - this is what you have already: a log of all the events
2.  'LocationDoors' - this is a list of each all the unique locations and doors

LocationDoors would look like this.  The additional columns shows only current status of each door.  Everytime a door is opened or closed you will need to write the event to 2 places: DoorEvents & Locations Doors.

ID	Office	Door number	Status	Modified
1	London	Door 1	Closed	21/04/2020
2	London	Door 2	Open	22/04/2020
3	Berlin	Door 1	Open	22/04/2020
4	Berlin	Door 2	Open	22/04/2020

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[WarrenBelz · Accepted solution]

*** UPDATE - Just noticed I posted after @mdevaney - see which option works best for you  ***

Hi @RobH1 ,

There are two things you need to do.

Firstly, the Items of your gallery need to be grouped

GroupBy(
   YourListName,
   "Modified",
   "Doors"
)

Firstly your Modified will already be available as ThisItem.Modified.
The rest of the fields will need this syntax  - 'Door Number' example - the Text of the label

First(
   Sort(
      ThisItem.Doors,
      Modified,
      Descending
   )
).'Door Number'

then the same for the rest. I have done this on a test model and it works.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[v-xida-msft]

Hi @RobH1 ,

Do you want to filter your Gallery records based on a ComboBox selected value (single selection)?

Could you please share a bit more about the Office column in your SP List? Is it a Choice type column?

I have made a test on my side, please consider take a try with the following workaround:

Set the Items property of the OfficeComboBox to following:

Choices('Your SP List'.Office)

set the SelectMultiple property of ComboBox to false.

Set the Items property of your Gallery to following:

AddColumns(
  GroupBy(
   AddColumns(
     SortByColumns(
           Filter(
                 'Your SP List',
                  If(
                    IsBlank(OfficeComboBox.Selected.Value),
                    true,
                    Office.Value = OfficeComboBox.Selected.Value
                  )
           ),
           "Modified",
           Descending
    ),
    "OfficeValue", Office.Value,
    "DoorNumber", 'Door number'.Value,
   ),
   "OfficeValue", 
   "DoorNumber",
   "GroupData"
  ),
  "StatusValue", First(GroupData).Status.Value,
  "IDValue", First(GroupData).ID,
  "ModifiedValue", First(GroupData).Modified
)

Within your Gallery, add several Labels, set the Text property to following:

ThisItem.IDValue

ThisItem.OfficeValue

ThisItem.DoorNumber

ThisItem.StatusValue

ThisItem.ModifiedValue

Note: I assume that the Office, Door number, Status columns are all Choice type columns in your SP List.

Best regards,

[WarrenBelz]

@v-xida-msft, thank you for taking the time to expand on the direction of my post.

@RobH1 already has two viable options to consider - @mdevaney and myself await his response as to which direction is best for him.

@RobH1 please tag whoever you want to continue this discussion with.

[RobH1]

Hi,  Hard to mark any one as an accepted solution just yet...

@mdevaney idea of 2 lists works.  Has the benefit of beating any delegation issues and only transferring minimal data as the audit trail list only needs to be written to if someone makes a change.  The current status can be read from a much smaller list.  As some of my users are on old mobile devices limited to 3G connectivity this could be a good solution.

@WarrenBelz solution works and was more of what I was originally looking but I found it best not to use the modified column as any two results with the same time entry or if someone uses quick edit to do bulk updates on the list it returns the wrong result.  Works better using "Created" or a separate "Status Time_Date" column populated with Now().  My end users want a manually entered time and date input and testing shows a few routinely rounded up to the nearest hour which again meant the possibility of incorrect results in the gallery (It only seems to take HH:MM into the sort rather than HH:MM:SS).  If my users would go with an automated time/ date stamp I would probably go for this option or I'm guessing I could modify it to use the ID column in some way.

@v-xida-msft I haven't tried this one yet but will give it a go on Monday.

All in all, a great response which is much appreciated and I let you all know which option worked for me.  How do I mark one as the accepted solution when I've got three different but all working and viable solutions?

[mdevaney]

@RobH1 
You can accept multiple solutions as the answer 😎

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[RobH1]

Hi @v-xida-msft ,  I couldn't get this working, the gallery Items gave the error StatusValue column does not exist, then gave IDValue does not exist, Then said there was an error with the First statement.  It also gave a delegation warning.

The options from @WarrenBelz  and @mdevaney both work and I have marked both as the solution.  In the end I will be using the two list solution from @mdevaney purely because the minimal data transfer works well with the poor quality data connection of my users devices.

Thanks for the replies.