cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Dual Super User III
Dual Super User III

Re: Join two collections

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
Highlighted
Super User
Super User

Re: Join two collections

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Dual Super User III
Dual Super User III

Re: Join two collections

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

Highlighted
Continued Contributor
Continued Contributor

Re: Join two collections

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

Highlighted
Continued Contributor
Continued Contributor

Re: Join two collections

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

Highlighted
Dual Super User III
Dual Super User III

Re: Join two collections

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!

Highlighted
Super User
Super User

Re: Join two collections

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Super User
Super User

Re: Join two collections

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
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,400)