Join/ Overwrite specific fields with other data set, based on criteria

[aehrenwo]

I am trying to update certain record set a variety of fields- if  the Work_Type field is set as "Contingent" , I want to pull in the Field1, Field2, Field3 from another dataset and replace those fields in the original. In most cases they are blank in Dataset 1. 

The field I am doing the "lookup" on is called UserID in data set 1 and ManagerID in dataset 2. 

For example:

Dataset1

UserID	ManagerID	Field 1	Field2	Field3	Worker_Type
123	111				Contigent
234	222				Contigent
456	333				Contigent
789	444				Contigent

Dataset2

ManagerID	Field 1	Field2	Field3
111	abc	abc	abc
222	xyz	xyz	xyz
333	bbb	bbb	bbb
444	aaa	aaa	aaa

Result

UserID	ManagerID	Field 1	Field2	Field3
123	444	aaa	aaa	aaa
234	222	xyz	xyz	xyz
456	333	bbb	bbb	bbb
789	111	abc	abc	abc

Any suggestons? I am not having success with the Join or Find Replace tools. 

Thanks.

Adam

[JohnJPS · Accepted solution]

I assume you can join them on ManagerID... from there, you could use a Select Tool to deselect the original Field 1/2/3 and replace them with those that were joined in from the Manger table. (See attachment)

[aehrenwo]

I was thinking that approach but I thought it was too simple... :)

However, I only want to replace the values if the Worker Type is a certain value. Would that require a formula to determine which values from the new columns to use? 

OR - should I do that process of just those worker types a different flow and then join it back? 

Adam

[JohnJPS]

Either approach will work, and I'm not sure which would perform better.  If one does perform better, I'm guessing the difference would be minimal for small datasets (less than around a million rows).