Text Transformations

   Product: OneSync   

Audience: ClassLink Administrator

Text transformations can be used to create custom strings based off an individual user’s properties. Transformations are most commonly used to format strings in destination mappings, but can also be used to generate formulas for data transforms values or conditions used in collections or destination events. As an 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:

• ADD_DAYS(DateString, Num of Days): Given a date string of the format "{dd/mm/yyyy hh:mm:ss}" and a number of days, this function will output a date string plus the number of days inputted.
• CURRENT_DATE(): Outputs the current date and time in the format MM/dd/yyyy hh:mm:ss tt. 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): Given a grade, returns the year of graduation. This function used to be only able to handle grades 1-12. You can input values "KG" and "PK" for kindergarten and pre-school. which will resolve to the values "0" and "-1", respectively, to output a year of graduation. The Year Rollover's default setting is January 1st, but you can change to your SIS rollover month and day. The year rollover date for this function can be modified in OS Settings.

• HASH_ALPHA(String, Num of Chars): Generates a hash based on an alphabetic 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.
• HASH_CUSTOM(String, Char Set, Num of Chars): The first parameter ("String") is the string to be encrypted. The second parameter ("Char Set") is a string of characters that you want to use in the encryption. The last parameter ("Num of Chars") is a digit that determines the length of the encrypted string.
• HASH_NUM(String, Num of Chars): Generates a hash based on a numeric string and desired number of characters
• HASH_WORDS(String, Num of Words): Given a string and a number of words, this function will output a hash string generated from an uploaded list of words. To use this function, you must create a CSV or text file then navigate to OS Settings > General Settings and click the blue Import Words button which will prompt for a file selection -> select a file and your file will be uploaded. You can download a sample file by clicking on the grey Download Sample File button also there is an attached sample file linked at the bottom of the article.

• 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 the 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.
• 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.
• LOWER_CASE(String): Change the string to all lower case.
• PREVIOUS(Field): Given a destination field, returns the previous value before the most recent sync.
• PROPER_CASE(String): Changes the first letter of a string to uppercase, and the rest lower case.
• REPLACE(String, Old Value, New Value): Given a string, this function will replace the old values from that string with the new values. The Old Value parameter cannot be left empty, but the New Value can.*
• 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 the desired character or substring from a string.
• STRIP_DIACRITIC(String): Removes diacritical (accent) marks from letters in the given string.
• STRIP_SUFFIX(): Removes case insensitive predefined suffixes and/or additional suffixes from the given string. Each optional suffix should be separated by commas and those that are white space sensitive should be entered in double quotes. Suffixes that include commas must include an escape character. Suffixes that are included in the default list:
  • jr
  • jr.
  • sr
  • sr.
  • ii
  • ii.
  • third
  • iii
  • iii.
  • iv
  • iv.
• SUBSTRING(String, Start Index, Length): Selects part of a string according to the desired starting index and desired length of the part.
• TO_INT(String): Converts numeric values into a properly formatted integer. This function can be used to remove leading zeros. If a decimal is given as an input, the function will output the value rounded down to the nearest integer.
• TRIM(String): Removes leading and trailing whitespace (spaces, tabs, etc.) from a string. This includes spaces from the left and right of the string but it will not impact any whitespace within the string.
• TRIM_AFTER(String, Char, ...): Starting at the beginning of the string, we look for the first occurrence of ANY of the characters in the list, and trim off everything after that (including that character) i.e. TRIM_AFTER(bob@classlink.com, @, l) results in "bob".
• UPPER_CASE(String): Changes the string to all uppercase.
• YOG_TO_GRADE(YOG): Given a graduation year, returns a grade. The year rollover date for this function can be modified in OS Settings.

*Note: All functions will trim leading and trailing white space within the parameters. There is one function that will NOT trim leading and trailing white space which is REPLACE(). Functions will not trim any white space between characters (John Smith will not become JohnSmith). Also pure white space without any characters will not be trimmed.

Example: TO_INT(  002  ) will output 2 without any leading or trailing white space.  

Example: REPLACE(%department%,-, - ) if department is equal to Student-1 this function will output Student - 1. The white space before and after '-' within the function will not be trimmed.

Updated: Nov 2019