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