I posted this earlier, but I think I need to do a new thread since I understand more of what I need to ask.
I have four collections:
I was told once that I should always pull data into a collection so I don't have to keep going to the data table. So I am trying to learn this right.
AllDrivers (pulled from a Dataverse table called Drivers)
Drivers,DriverGUID,Email,Name,'Number of Passengers','Phone Number','Vehicle Type'
AllDestinations (pulled from Dataverse table Destinations)
Destinations,DestinationGUID,Name,Address,URL,Notes
AllEvents (pulled from Dataverse table Events)
Events,DestinationGUID,EventGUID,'Start Date','End Date','Number of Scouts','Map URL',Notes,Stops
AllDriversToEvents (pulled from Dataverse table DriversToEvents)
DriversToEvents,DriverGUID,EventGUID,DriverDirection
Here is how I get them:
ClearCollect(AllDrivers,ShowColumns(Drivers,DriverGUID,Email,Name,'Number of Passengers','Phone Number','Vehicle Type'));
ClearCollect(AllDestinations,ShowColumns(Destinations,DestinationGUID,Name,Address,URL,Notes));
ClearCollect(AllEvents,ShowColumns(Events,DestinationGUID,EventGUID,'Start Date','End Date','Number of Scouts','Map URL',Notes,Stops));
ClearCollect(AllDriversToEvents,ShowColumns(DriversToEvents,DriverGUID,EventGUID,DriverDirection));
I want to create a collection of Driver Details, But the DriverGUID is not stored in the AllEvents Collection (If I did that I would end up with a very large repetitive table since I could have 10 drivers per event). So I store the correlation of Events to Drivers in the AllDriversToEvents collection. What should happen is the following flow:
I have a gallery bound to AllEvents
1. Click on Name (I get the name as follows:
LookUp(AllDestinations,ThisItem.DestinationGUID = DestinationGUID,Name))
2. Get the EventGUID (Already in the AllEvents collection)
3. Find all the DriverGUID for that EventGUID (I have this figured out, This also gets me the DriverDirection). I put this into a Gallery called DriverGUIDtoEvent That is also uses Lookup:
LookUp(AllDriversToEvents,ThisItem.DriverGUID = DriverGUID)
So now I have all the GUIDs and Direction.
I cannot figure out how to combine the AllDriverstoEvents and the AllDrivers Details. I've seen this post (and other;s like it) https://www.matthewdevaney.com/powerapps-collections-cookbook/join-all-columns-from-another-collection/
However, this doesn't work when pulling from a table due to all the system tables, even if I Drop All the system columns. I have spent the last 5 days trying to figure this one part out, and I am just at a loss, totally confused. In simple terms I just want to do something like this
Foreach (GUID in DriverGUIDtoEvent) {
Collect (DriverDetails, {
Name:LookUp(Drivers, DriverGUID = GUID, Name),
Email:LookUp(Drivers, DriverGUID = GUID, Email),
...
}
);
Then the last step is once I have this in a collection, populate a gallery with all of this data.
