Change data type based on condition

[UnknownUser]

Hi all,

I have dozens of fields with initial data type "String". For every field that contains the word "DATE", I want to parse the string to a date format. I currently use the multi-field formula with this formula:

IF Contains([_CurrentFieldName_], "DATE")
THEN
DateTimeParse([_CurrentField_],"%d-%b-%Y")
ELSE
[_CurrentField_]
ENDIF

Now I want to change the field type of those fields, where the condition was true, to "Date". If I check the box "Change Output Type", unfortunately, all fields (also the remaining string fields) get converted. How do I only convert the desired fields?

Best

[patrick_mcauliffe · Accepted solution]

@UnknownUser I think you're headed down the same path I would have gone with this one.  Multi-field tool is great, but since you need to be more precise about which columns you select, I would use the Dynamic Select to narrow to just those columns and an index to join on.  Once those are converted, you can use the index to join back to the rest of the fields and a Dynamic Select again to remove the fields of the old format (since their name will start with 'Right_').

See the attached.

[jdunkerley79 · Accepted solution]

I'd do similarly but you can avoid the RecordID and improve Join performance by Joining on Record Position.

 A second dynamic select avoids the right_ issue.

(Stole @patrick_mcauliffe example)