cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bolin-stratus
Post Partisan
Post Partisan

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
bolin-stratus
Post Partisan
Post Partisan

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

bolin-stratus
Post Partisan
Post Partisan

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

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 (70,638)