Group by from detail tables....(complex) then merge to create one master table

[sasrsc]

My goal is to have one row per attendee with info derived from numerous tables. I need a sanity check to get me kick started here...

Master Table (parent) = Attendee
One row per attendee
Mickey Mouse - VIP - Mickey.Mouse@disney.com
Minnie Mouse - VIP - Minnie.Mouse@disney.com
Mr. Villain - - badguy@acme.com

Child Table = Transport
One row per flight per attendee 
Typically that means 2 rows (if someone flies non stop)
Mickey Mouse - ATL to MCO - 24DEC - AA 123
Mickey Mouse - MCO to ATL - 26DEC - AA 567
Mickey Mouse - LON to MCO - 23DEC - DL 53
Mickey Mouse - MCO to LON - 27DEC - DL 5
Mr. Villain - EWR to MCO - 24DEC - WN 22
Mr. Villain - MCO to EWR- 27DEC - WN 361

Child Table = Hotels
One row per hotel booking per attendee
Mickey Mouse - Check in 24DEC - Check Out 26DEC - The Grand Floridian - Suite - $500
Minnie Mouse - Check in 23DEC - Check Out 27DEC - The Polynesian - ROH- $250
Mr. Villain - - Check in 24DEC - Check Out 25DEC - BadHotel - Broom Closet- $50
Mr. Villain  - Check in 25DEC - Check Out 27DEC - The Secret Hotel - Corner Suite- $99

What I need to do is get the one row for each attendee and bring into that row the min check in date of their hotel(s), the max check out date of their hotel(s) - Notice that Mr. Villain has 2 hotels so I want to return 24DEC and 27DEC which is the min/max of "all his rooms". Then I want to combine into this the flight arrival date into MCO and the flight departure date from MCO for each person. 

Why - you ask?
I'm performing quality control to make sure that everyone has a hotel for every night that they are staying in Orlando based on when the arrive and depart. Make sense?

The data source is SP Lists right now. 

Right now, I'm planning on trying to create a collection with the hotel data, a collection with the flight data and then add columns to the master attendee table. I'm struggling a little ... advice welcome...

[VijayTailor · Accepted solution]

Hi @sasrsc ,

You can use the approach like below.

ClearCollect(colStudentClassMap, 
     AddColumns(
          StudentClassMap,
          "Class", Filter(Class, ClassId=StudentClassMap[@ClassId]),
          "User", Filter(User, UserId=StudentId)
     )

)

 Thanks,

Vijay

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."-Vijay

[sasrsc]

Thanks Vijay... that just helps reduce the number of steps. 
It's good to learn with baby steps but then combine when you grasp the concepts like this (as it reduces the code and makes it more efficient).