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 |
Solved! Go to 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
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
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.
User | Count |
---|---|
263 | |
110 | |
92 | |
55 | |
41 |