Gallery Filter by value and distinct by another value

[CVannest]

Filter(Outages,Status.Value="Outage",Created>=DateAdd(Today(),-7,Days))

I'm filtering my gallery by the Status value being "Outage" and only displaying the last 7 days from today.

What I'm trying to figure out how is how to do also include a "Distinct" function on 'What Is The Ticket Number' column from the same SharePoint list.  In the gallery, I only want to show the latest entry for that Ticket Number where it's listed as an "Outage".

SharePoint List Source = Outages

Status Field on SP List = Status

What Is The Ticket Number on SP List = What Is The Ticket Number

Gallery in PA = TemplateGalleryList1

Background: This is for an outage reporting system.  We use it to input information that's sent out to various people depending on what area of the business has an outage.  We send status updates throughout the day where the Status may remain as "Outage" until it moves to "Monitoring" or "Operational".  While in "Outage" we may have several notifications submitted all with the same "What Is The Ticket Number".  I'm putting together a Gallery where we show all the Outages for the past seven days, but I don't want to have duplicates in there.

[cwebb365]

If you want the latest ID, distinct isn't delegable, but you should be able to do this by getting the list of TicketNumbers into a collection then putting Distinct on that in a ForAll. This ForAll goes through the Distinct TicketNumbers and gets the list of the matching TicketNumbers, sorts them by descending Modified date/time and grabs the First one which should be the latest. 

ClearCollect(colDistinctTickets,Filter(Outages,Status.Value="Outage",Created>=DateAdd(Today(),-7,Days)).TicketNumber);
ForAll(Distinct(colDistinctTickets,TicketNumber) AS OutageTickets,Collect(colLatestOutages, First(Sort(Filter(Outages, TicketNumber = OutageTickets.TicketNumber),Modified, Descending))))

 Might not be the best way, but it works. If your ticket list can return more than 500 records in that 7 day window then you might have to approach it different. 

[CVannest]

So for my setup, "What Is The Ticket Number" is the name of the column in SharePoint where TicketNumber is stored.  Do I replace "TicketNumber" in your code with 'What Is The Ticket Number' so it looks that column?

[cwebb365]

Yes, but it'll be wrapped in '' probably. You should get type ahead so you can see the column show up. 

[cwebb365]

Note this will also need to go into your Screen On Visable property or something. SO it runs when you go to the screen etc. Not sure the app but that usually is where you want it when the screen loads it processes those collections. Then you want to take the collection generated (colLatestOutages) and put it in the "Items" property of the gallery. 

[CVannest]

This returns no results.

ClearCollect(colDistinctTickets,Filter(Outages,Status.Value="Outage",Created>=DateAdd(Today(),-7,Days)).'What Is The Ticket Number');
ForAll(Distinct(colDistinctTickets,'What Is The Ticket Number') AS OutageTickets,Collect(colLatestOutages, First(Sort(Filter(Outages,'What Is The Ticket Number'=OutageTickets.'What Is The Ticket Number'),Modified,Descending))))

[cwebb365]

Where did you put this statemetn? Make sure you Navigate off the screen and back so it executes and populates the collections. Or create a temporary button with the same statement and the click it to "generate" the collection. 

[BCBuizer]

Hi @CVannest ,

Slightly different approach where tickets are grouped by ticket number and then only the last item per group is returned:

Filter(
   ForAll(
      GroupBy(
         AddColumns(
            Outages,
            "Distinct",
            'What Is The Ticket Number'
         ),
         "Distinct",
         "GroupedTickets"
      ),
      Last(GroupedTickets)
   ),
   Status.Value="Outage",
   Created>=DateAdd(Today(),-7,Days)
)

The Distinct column is only added to group the tickets by as it is dropped in the ForAll loop. In case the 'What Is The Ticket Number' column would be used for that, you wouldn't be able to see the ticketnumber anymore.

[CVannest]

BCBuizer, I like this approach more however inserted that into "Items" for the gallery gave me no results.

[BCBuizer]

Hi @CVannest ,

I have tested the part that returns the last values, but not in combination with the filter. Can you try the below to see if the issue is in the "distinct" part, or the filter:

//Filter(
   ForAll(
      GroupBy(
         AddColumns(
            Outages,
            "Distinct",
            'What Is The Ticket Number'
         ),
         "Distinct",
         "GroupedTickets"
      ),
      Last(GroupedTickets)
   )
//   ,
//   Status.Value="Outage",
//   Created>=DateAdd(Today(),-7,Days)
//)