Unanswered

Perplexing Collections Merge/JOIN - Successful But Missing Some Records

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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.

Categories:

Building Power Apps

All responses (2)

Answers (0)

Sort by

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
// ***************** 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:

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.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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.