How to Add Most Recent Linked Record from Secondary Table to Primary Table via Collection?

[jerrycrabb]

Hi!

I think there's some combination of ForAll() GroupBy() Patch() With() that will make this work, but I haven't been able to crack it. 

I have two linked data sources (SharePoint lists). The primary set of records are linked to the secondary set of records via ID with a one-to-many relationship. What's the best way to add columns to a new collection based on the primary table, that includes the most recent record from the secondary table?   I think the below illustrates this better than I can put into words.

Primary Table: Customers

CustomerID	CustomerName
1	Bob Builder
2	Dora Explorer
3	Ryder Sir
5	Doc McStuffins

Second Table: Tickets

TicketID	FKCustomerID	Modified	'Modified By’
1	2	9/30/2021 1856	Tony
2	3	7/25/2022 0547	Bruce
3	1	7/28/2022 1222	Steve
4	2	8/01/2022 1602	Natasha
5	1	8/05/2022 1001	Wanda
6	5	8/09/2022 0952	Clinton
7	2	8/15/2022 1339	Thor
8	1	8/18/2022 0744	Pietro
9	3	8/29/2022 0732	Tchalla
10	5	8/30/2022 1353	Hank

Solution Table

CustomerID	CustomerName	LastTicketID	LastTicketDTG	LastTicketTech
1	Bob Builder	8	8/18/2022 0744	Pietro
2	Dora Explorer	7	8/15/2022 1339	Thor
3	Ryder Sir	9	8/29/2022 0732	Tchalla
5	Doc McStuffins	10	8/30/2022 1353	Hank

[WarrenBelz · Accepted solution]

Hi @jerrycrabb ,

Try this format

With(
   {
      wTickets:
      AddColumns(
         GroupBy(
            Sort(
               Ticket,
               ID,
               Descending
            ),
            "FKCustomerID",
            "Data"
         ),
         "LastDTG",
         First(Data).Modified,
         "LastTech",
         First(Data).'Modified By"
      )
   },
   AddColumns(
      Customers,
      "LastTicketDTG",
      LookUp(
         wTickets,
         FKCustomerID = ID
      ).LastDTG,
      "LastTicketTech",
      LookUp(
         wTickets,
         FKCustomerID = ID
      ).LastTech
   )
)

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.

Visit my blog Practical Power Apps

[jerrycrabb]

@WarrenBelz 

Brilliant!  I hope that one day problem solving with these functions will become more natural for me. This is far simpler than anything I was trying. 

Thank you so much!

-Jerry

[WarrenBelz]

No problems @jerrycrabb ,

As long as you have taken the time to understand what all of that does, it will come to you sooner than you think.