Showing results for 
Search instead for 
Did you mean: 
Continued Contributor
Continued Contributor

Perplexing Collections Merge/JOIN - Successful But Missing Some Records

I have a canvas app and 2 SP lists ('Deal-Intake-App' & 'Assigned-Users') as my data sources and the deal id is the common field between the two.  I successfully merged the collections only to discover there are missing records.  I am still struggling to find a solution.
DI Dashboard Merged Collection Missing Records.PNG
Here is the code for reference.

// Deal-related data (Title is the deal id)
ClearCollect(colDashboardActiveDI, AddColumns(ShowColumns('Deal-Intake-App', "Title", "Close_x002d_Date", "SFDC_x002d_Opp_x002d_Name", "SFDC_x002d_Opp_x002d_ID" ), "Active", If(Close_x002d_Date > Today(), "Yes", "No")) );

// Deal-assignment data (Deal_x002d_Request_x002d_ID is the deal id)
ClearCollect(colDashAssignedBidMgrs, ShowColumns( Filter('Assigned-Users', Role_x002d_Assigned = "Bid Manager"), "Deal_x002d_Request_x002d_ID", "Assigned_x002d_Full_x002d_Name", "Role_x002d_Assigned"));

// Merged collection (successful but missing some records from 'Assigned-Users'/colDashAssignedBidMgrs)
    colDashboardActiveDI, "Assigned_x002d_Full_x002d_Name",
    LookUp(colDashAssignedBidMgrs, Title = colDashAssignedBidMgrs[@Deal_x002d_Request_x002d_ID], Assigned_x002d_Full_x002d_Name)

 Any recommendations are greatly appreciated.

Continued Contributor
Continued Contributor

// ***************** WKS ********************** //
// Show select columns and add an Active column to represent if a deal is active Yes/No
ClearCollect(colDashboardActiveDI, AddColumns(ShowColumns('Deal-Intake-App', "Title", "Close_x002d_Date", "SFDC_x002d_Opp_x002d_Name", "SFDC_x002d_Opp_x002d_ID" ), "Active", If(Close_x002d_Date > Today(), "Yes", "No")) );

ClearCollect(colDashAssignedBidMgrs, ShowColumns( Filter('Assigned-Users', Role_x002d_Assigned = "Bid Manager"), "Deal_x002d_Request_x002d_ID", "Assigned_x002d_Full_x002d_Name", "Role_x002d_Assigned")); // Select columns
// ***************** WKS ********************** //

// 03/12/21
          Title = Deal_x002d_Request_x002d_ID

Here is some additional information and the merged collection has been slightly updated per a recommendation from @WarrenBelz.  

I am at a loss why certain records are not in the merged collection. 

The screen shot below shows 3 galleries:
Collections JOINING Two Collections MISSING ITEMS.PNG

Top Left: colDashAssignedBidMgrs with three highlighted records for user x

Top Right: colDashActiveDI = Deal records with gallery filtered to display only active deals

Bottom: Merged collection (colDashActiveDI + colDashAssignedBidMgrs) Note there are 2 missing records for user x even though we know there should be 3.  I know that the 3 deals listed for user x in this colDashAssignedBidMgrs are active deals.  Also note that this merged collection will include active (close date still in the future) as well as inactive deals (close date is in the past) although I can add a filter to display only active deals (i.e., close date not reached). 

Any help is appreciated. 

Continued Contributor
Continued Contributor

I had missing records since in the assigner users table, the Deal-Request-ID is not unique.  @WarrenBelz made this suggestion and it was the root cause.  

Helpful resources

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.


New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (2,190)