Tuesday, March 19, 2019

Convert a String to Format of Last name Comma First name and Set the value to a field using Formula in a Workflow

To convert a string to format of last name comma first name using formula in a workflow, we can use two different formula depending on the trigger that we'll be using. If client side triggers (e.g. "After Field Edit", "Before User Submit", etc...) are used, we would be using javascript functions while for user event triggers (e.g. "Before Record Load", "Before Record Submit") we can use SQL expressions.

Below is a sample formula for Client Side Triggers.

({custentity16}.substr(parseInt({custentity16}.lastIndexOf(' ')+1),parseInt({custentity16}.length)-parseInt({custentity16}.lastIndexOf(' ')+1))).concat(', '+{custentity16}.substr(0,parseInt({custentity16}.lastIndexOf(' ')+1)))

You can substitute "{custentity16}" with the internal id of the field in which the name string will be coming from.

Here's the formula we can use for User Event Triggers. Also, you can use this formula if you want to set a custom field's default value.

CONCAT(CONCAT(regexp_substr({custentity16},'\S+$'),', '),SUBSTR({custentity16},0,TO_NUMBER(REGEXP_INSTR({custentity16},'\S+$'))-2))

No comments:

Post a Comment