Join two collections

[tianaranjo]

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 

[RandyHayes]

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

[yashag2255 · Accepted solution]

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!

[tianaranjo]

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

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

[yashag2255]

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!

[RandyHayes]

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

[wyotim]

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.  😁