cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MikeAnderson
Level 8

Add New Column to collection from existing collection

Hello,

I can't quite seem to figure out the syntax to add a new column into a collection from another collection.  I've looked at various threads, but for some reason I am failing at getting this to work.

Collection 1: Job and/or, UnitID / VIN create unique key
1.png

Collection 2:  Job and/or UnitID / VIN create unique key
2.png

When I try to add the "IsPending" column from Collection 2 based on Collection1.Job = Collection2.Job and Collection1.UnitID = Collection2.UnitID I am getting the wrong result:
ClearCollect(
   MergedList,
     AddColumns
    (
      Collection1,
      "IsPending", LookUp ( Collection2, Job = Job && UnitID = UnitID, IsPending)
   )
);


There are only 4 records that should show true for "IsPending" but they are all showing as "True". The records below should NOT show as true as there is NO MATCH match for them. The records you see in the screenshot for collection 2 are the only matching records.
3.png

 

Any tips on what I'm doing wrong? I've tried various methods and all end up with the same result (every record shows true).  
Appreciate the insight, thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
EricLott
Level 10

Re: Add New Column to collection from existing collection

Just taking a stab here (PowerApps may already handle the references automatically), but check the logic of the Lookup function. If you're comparing Collection1.Job and Collection1.UnitID on both sides of the equals sign, you'll get true for all. Try this using explicit references to the collections:

 

ClearCollect(
   MergedList,
     AddColumns
    (
      Collection1,
      "IsPending", LookUp ( Collection2, Collection1.Job = Collection2.Job && Collection1.UnitID = Collection2.UnitID, IsPending)
   )
);

View solution in original post

2 REPLIES 2
EricLott
Level 10

Re: Add New Column to collection from existing collection

Just taking a stab here (PowerApps may already handle the references automatically), but check the logic of the Lookup function. If you're comparing Collection1.Job and Collection1.UnitID on both sides of the equals sign, you'll get true for all. Try this using explicit references to the collections:

 

ClearCollect(
   MergedList,
     AddColumns
    (
      Collection1,
      "IsPending", LookUp ( Collection2, Collection1.Job = Collection2.Job && Collection1.UnitID = Collection2.UnitID, IsPending)
   )
);

View solution in original post

MikeAnderson
Level 8

Re: Add New Column to collection from existing collection

Bloody Brilliant!  Shame on me for assuming it would in order handle the references appropriately.  I had to make a slight syntax change, but this works as I was expecting:

ClearCollect(
   MergedList,
     AddColumns
    (
      Collection1,
      "IsPending", LookUp ( Collection2, Collection1(@Job) = Collection2(@Job) && Collection1(@UnitID) = Collection2.(@UnitID), IsPending)
   )
);

In fact, I removed the 3rd collection (MergedList) and condensed it into this:

ClearCollect
(
Collection2,
AddColumns
(
Filter
(
'[dbo].[datasource]', Job = conJob
),
"IsChecked", false,
"IsPending", LookUp
(
Collection1,
Job = Collection2[@Job] && UnitID = Collection2[@UnitID], IsPending
)
)
);

Thank you for the nugget!

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,321)