cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tianaranjo
Continued Contributor
Continued Contributor

Join two collections

I have been stuck on something that should be fairly straightforward.

I have two collections and I would like to update one with values from the other.  

Collection1 (name will only be in this list 1 time)

User: Smith, Joe, dept: Operations

User: Smith, Betty, dept: Finance

User: Jones, Rachel, dept: Shipping

 

Collection2 (name may appear more than once)

Title: Smith, Joe, dept: ""

Title: Smith, Betty, dept: ""

Title: Jones, Rachel, dept: ""

Title: Smith Betty, dept: ""

Title: Jones, Rachel, dept: ""

 

The only related fields are User:Title

I cannot get, for the life of me, get the second collection dept to update with the correct values from Collection 1.  

Any thoughts?
@RezaDorrani @wyotim @RandyHayes 

1 ACCEPTED SOLUTION

Accepted Solutions
yashag2255
Dual Super User II
Dual Super User II

Hey @tianaranjo 

 

Can you try the below expression:

ForAll(RenameColumns(Collection1,"User","User1","dept","dept1"),UpdateIf(Collection2,Title = User1,{dept:dept1}))

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

7 REPLIES 7
RandyHayes
Super User
Super User

@tianaranjo 

You might consider a ForAll in this case.  Something similar to this should do the trick:

ForAll(Collection1,
   UpdateIf(Collection2, Title=User, {dept: dept})
   )

Since you have duplicates, it will hit those as well with the UpdateIf.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
yashag2255
Dual Super User II
Dual Super User II

Hey @tianaranjo 

 

Can you try the below expression:

ForAll(RenameColumns(Collection1,"User","User1","dept","dept1"),UpdateIf(Collection2,Title = User1,{dept:dept1}))

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

tianaranjo
Continued Contributor
Continued Contributor

@RandyHayes Firstly, thank you for the quick response. It is greatly appreciated. This 'almost' worked. The dept column in Collection2 was updated; however, the dept was the same for everybody. 

I tried @yashag2255 and that worked.  Again, THANKS!

tianaranjo
Continued Contributor
Continued Contributor

@yashag2255 This worked great; can you provide a quick explanation of what you did here? Thanks.

Hey @tianaranjo 

 

Here, we are looping over the first collection to update the values in collection2. UpdateIf function updates all the records in the collection matching the criteria with the provided set of values.

Eg. The loop goes like, get the first item in Collection1 and updates all the records in the Collection2 which has the same name as the selected first record of Collection1 and updates its department value. RenameColumn is used to uniquely identify the column as both the collections had same name.

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

@tianaranjo 

Yes, in some cases you need to disguise the column name.  Luckily you can do that with a rename columns.

So, more like this:

ForAll(
   RenameColumns(Collection1, "dept", "col1Dept"),
      UpdateIf(Collection2, Title=User, {dept: col1Dept})
   )

See if that does it for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
wyotim
Resident Rockstar
Resident Rockstar

Hey all, a little late to the party but what about using disambiguation rather than renaming? Something like:

ForAll(
    Collection1,
    UpdateIf(Collection2, Title = User, {dept: Collection1[@dept]})
)

 

@tianaranjo I know this is solved but I thought I would offer another take on it.  😁

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,382)