How do you move data from one field to another based on a criteria?

[Dom]

Scenerio, I want to move all persons [ESSN]  to the [Insurance_ID] if there is any person with [Relation] = S.  Otherwise no data moves.

Thanks in advance!

[MarqueeCrew · Accepted solution]

Formula: 

select your insurance Id field and use the formula:

IF [relation] == "S" THEN [ESSN] 

ELSE ""

ENDIF

another version is:

IIF([relation]=="S",[ESSN],"")

Another:

SWITCH([relation],"","S",[ESSN])

thanks,

mark

[Dom]

I don't want the actual essn field in the output file. so, will the formula work without having this field in the Select tool? I don't want this field on the output file at all but I want the data to move for all peson if there is one person with Relation = S to InsuranceID field. 

[jdunkerley79 · Accepted solution]

You will need to have a select tool after the formula tool as the ESSN field needs to be there when evaluating the formula.