cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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)
ClearCollect(colMergeDealIntakeAssignedUsers2, 
    AddColumns(
    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.

2 REPLIES 2
Anonymous
Not applicable

// ***************** 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
ClearCollect(
   colMergeDealIntakeAssignedUsers2, 
   AddColumns(
      colDashboardActiveDI, 
      "AssignedFullName",
       LookUp(
          colDashAssignedBidMgrs, 
          Title = Deal_x002d_Request_x002d_ID
       ).Assigned_x002d_Full_x002d_Name
    ) 
);​

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. 

Anonymous
Not applicable

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

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (4,539)