cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jerrycrabb
Helper II
Helper II

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

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

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

 

Second Table: Tickets

TicketIDFKCustomerIDModified'Modified By’
129/30/2021 1856Tony
237/25/2022 0547Bruce
317/28/2022 1222Steve
428/01/2022 1602Natasha
518/05/2022 1001Wanda
658/09/2022 0952Clinton
728/15/2022 1339Thor
818/18/2022 0744Pietro
938/29/2022 0732Tchalla
1058/30/2022 1353Hank

 

Solution Table

CustomerIDCustomerNameLastTicketIDLastTicketDTGLastTicketTech
1Bob Builder88/18/2022 0744Pietro
2Dora Explorer78/15/2022 1339Thor
3Ryder Sir98/29/2022 0732Tchalla
5Doc McStuffins108/30/2022 1353Hank
1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User
Super User

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

View solution in original post

3 REPLIES 3
WarrenBelz
Super User
Super User

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

@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

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.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,986)