cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sasrsc
Resolver II
Resolver II

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

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








1 ACCEPTED SOLUTION

Accepted Solutions
VijayTailor
Resident Rockstar
Resident Rockstar

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

View solution in original post

2 REPLIES 2
VijayTailor
Resident Rockstar
Resident Rockstar

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

View solution in original post

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

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (32,270)