Text Transformations

Text transformations can be used to create custom strings based off an individual user’s properties. For example, the text transformation `LEFT(String, Num of Left Char)` can be used in conjunction with the field variables %givenName% and %surname%, to output a userPrincipalName consisting of the first 3 letters of the givenName, followed by the surname and domain. To achieve this, the original text transform is modified with the field variables, as shown below:

Note: Text transformations are denoted by back ticks (`) which are not advised for use in OneSync to minimize errors.

The following is a list of provided text transformations:

• `CURRENT_DATE()`: outputs the current date and time in the format MM/DD//YYYY HH:MM:SSS TT (Note: this transform will change with the date)
• `FORMAT_DATE(DateString, MM/dd/yyyy hh:mm tt)`: takes the input date and returns it in the inputted format
• `GRADE_TO_YOG(String)`: calculates the graduation year (Note: Kindergarten = "0")
• `GRADE_TO_YOG_ALT(String, Year of Grad)`: calculates the year of enrollment
• `INDEX_OF(Search In String, Search For String, Start Index)`: returns the index number of a substring within a string, searching at and after the starting index
• `IS_EMPTY(Value If Not Empty, Value If Empty)`: if string is empty, take on the second value, else take on the first value; can be used for fields that do not always have values, e.g. middle name
• `LEFT(String, Num of Left Char)`: selects the left characters of a string based on the provided number
• `LOWER_CASE(String)`: change string to all lower case
• `PREVIOUS(Field)`: Given a destination field, returns the previous value before the most recent sync
• `PROPER_CASE(String)`: change first letter of string to uppercase, and the rest lower case
• `REQUIRE(String)`: turns an optional field into a required field and will throw an exception if that optional field is left blank
• `RIGHT(String, Num of Right Char)`: selects the right characters of a string based on the provided number
• `STRIP(String, Remove String, Remove String, ...,)`: removes a desired character or substring from a string
• `SUBSTRING(String, Start Index, Length)`: selects part of a string according to the desired starting index and desired length of the part
• `TRIM_AFTER_SPACE(String)`: selects all of a string before the first space, trims everything after the first space
• `UPPER_CASE(String)`: change string to all uppercase
• `HASH_ALPHA(String, Num of Chars)`: generates a hash based on an alphabetic string and desired number of characters
• `HASH_NUM(String, Num of Chars)`: generates a hash based on a numeric string and desired number of characters
• `HASH_ALPHANUM(String, Num of Chars)`: generates a hash based on an alphanumeric string and desired number of characters
• `LOOKUP_DN(String)`: retrieves an Active Directory user's distinguished name (DN); this function can only be used on users that have already been exported