cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yplay27
Helper II
Helper II

Join two collections with lookup (w/ example code)

Hello and thanks in advance! I'm struggling to join two collections. 

Col_One: Main collection

Col_Two: data to join where EmployeeEmail is not in Col_One

Col_Join: final collection

 

 

ClearCollect(
    col_One,
    {
        EmployeeMail: "Jsmith@contoso.com",
        Department: "Information technology",
        Location: "Latta", 
        startdate:DateValue("8/23/2022"),
        enddate:DateValue("8/23/2022")
    },
      {
        EmployeeMail: "MJones@contoso.com",
        Department: "Production",
        Location: "Glenview",
        startdate:DateValue("8/22/2022"),
        enddate:DateValue("8/22/2022")
    },
      {
        EmployeeMail: "QLove@contoso.com",
        Department: "Engineering",
        Location: "Douglasville",
        startdate:DateValue("8/22/2022"),
        enddate:DateValue("8/22/2022")
    }
);
ClearCollect(
    col_Two,
    {
        EmployeeMail: "STella@contoso.com",
        Department: "Production",
        Location: "Glenview"
    
    },
      {
        EmployeeMail: "MJones@contoso.com",
        Department: "HR",
        Location: "Tampa"
  
    },
      {
        EmployeeMail: "JFelipe@contoso.com",
        Department: "Marketing",
        Location: "Chicago"

    }
)
;
Clear(col_Join);
Collect(col_Join,col_One);
Collect(col_Join,col_Two);

 

 

 I tried the following but nothing returns: 

 

 

ForAll(col_Two,UpdateIf(col_Join,EmployeeMail <> EmployeeMail,{EmployeeMail:EmployeeMail}))

ForAll(col_Two,UpdateIf(col_Join,EmployeeMail <> EmployeeMail,ThisRecord))

 

 

Any help would be greatly appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
PaulD1
Super User
Super User

Change the last line of you first code block from:

Collect(col_Join,col_Two);

to:

Collect(col_Join,Filter(col_Two, Not(EmployeeMail in col_One.EmployeeMail)) );

When you combine two table like this it is called a Union rather than a Join - using the term Union may help people get a clearer idea of the desired outcome.

This will work fine on local collections but due to Power Apps poor capabilities in translating such requests into delegable queries, this approach is unlikely to work well with external data sources (will be slow and may not return all desired records unless the tables are very small).

View solution in original post

4 REPLIES 4
jatcube
Solution Supplier
Solution Supplier

@yplay27 it might just be me, but I don't understand what you're trying to do, can you elaborate?

Thank you for the reply. Here is additional context:

12341234.png

PaulD1
Super User
Super User

Change the last line of you first code block from:

Collect(col_Join,col_Two);

to:

Collect(col_Join,Filter(col_Two, Not(EmployeeMail in col_One.EmployeeMail)) );

When you combine two table like this it is called a Union rather than a Join - using the term Union may help people get a clearer idea of the desired outcome.

This will work fine on local collections but due to Power Apps poor capabilities in translating such requests into delegable queries, this approach is unlikely to work well with external data sources (will be slow and may not return all desired records unless the tables are very small).

Thank you for the help and tips. It worked. Thanks!!!

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,750)