Alphanumeric Functions
Use alphanumeric functions to manipulate alphanumeric fields in formula user-defined fields and in rules for mandatory user-defined fields. When you use alphanumeric functions, you can insert alphanumeric fields as the parameters for the function or you can use any text strings. When you insert strings into functions, enclose the text of the string in quotation marks.
You can add the following functions to formulas using alphanumeric user-defined fields.
- Len – Returns the number of characters in a text string.
- Lowercase – Converts a string to lower-case characters.
- Mid – Returns a specific number of characters from the text string starting at the position you specify.
- Search – Searches for text in a string and returns the position of the text.
- Substitute – Substitutes existing text with specified text.
- Text – Changes a numeric value to an alphanumeric text string. You can manipulate the returned string using any of the other alphanumeric functions.
- Uppercase – Converts a string to upper-case characters
- Value – Converts an alphanumeric value to a numeric value. Alphanumeric values beginning with alphabetical characters return a value of 0. You can manipulate the string using any of the numeric functions.
Len
Returns the length of a string.
Syntax
Len(Alphanumeric)
Parameters
Parameter | Data Type | Description |
Alphanumeric | String | The string to return the length of. |
Example
Len([Company])
- This example returns the number of characters in the company’s name.
Lowercase
Converts a string to lower-case characters. Use this function or the Uppercase function with the Search function and with equal (==) statements to compare alphanumeric values when you do not care about the case of the string.
Syntax
Lowercase(Alphanumeric)
Parameters
Parameter | Data Type | Description |
Alphanumeric | String | The string to convert to lower case. |
Example
Lowercase([City])=="vancouver"
- This example of a mandatory rule checks for the value "Vancouver" in the City field. The rule validates if an entry has the values "Vancouver", "vancouver", or "VANCOUVER" in the City field.
Mid
Returns a specific number of characters from a text string starting at a specified position.
Syntax
Mid(Alphanumeric, StartInt, LengthInt)
Parameters
Parameter | Data Type | Description |
Alphanumeric | String | The string that you want to search through. Normally, this would be a merge field. |
StartInt | Integer | The position of the first character in the string you want to return. |
LengthInt | Integer | (Optional) The length of the string you want to return. |
Example
Mid("First Last", 7)- This example returns the text starting at the 7th position in the field. In this case, it returns “Last”.
Search
Searches a string for text and returns the position of the text in the string. This function is case sensitive.
Syntax
Search(SearchString, Alphanumeric, StartInt)
Parameters
Parameter | Data Type | Description |
SearchString | String | The text to search for in the string. |
Alphanumeric | String | The string to search through. To ignore the case of the string, include the Lowercase or Uppercase function in this parameter. |
StartInt | Integer | (Optional) The starting character to search. |
Examples
Search("Wine", [Company_Name])- This example searches for the text “Wine” in the company name and returns the place in the name where the text starts as an integer.
Search("Partner", [Category]) > 0- This example of a mandatory rule searches for the value "Partner" in the Category field. This rule validates if an entry has the category set to "Partner" even if the there are other values set in the Category field as well.
Search("wine", Lowercase([Company_Name])) > 0- This example of a mandatory rule searches for the text “wine” in the company name ignoring the case. This rule validates if the Company Name field includes the text "Wine", "wine", or "WINE".
Substitute
Substitutes existing text with specified text. This function returns the entire string containing the new text.
Syntax
Substitute(Alphanumeric, OldText, NewText)
Parameters
Parameter | Data Type | Description |
Alphanumeric | String | The string containing the text you want to change. |
OldText | String | The text you want to change. |
NewText | String | The text substituted into the string. |
Example
Substitute([Address], "St", "Street")
- This example returns the address with the abbreviation “St” substituted with the text “Street”.
Text
Converts a numeric value to a string. You can then use the other alphanumeric functions to manipulate the returned string.
Syntax
Text(Number, DecPlace)
Parameters
Parameter | Data Type | Description |
Number | Decimal | The numeric value that you want to convert. |
DecPlace | Integer | (Optional) The number of decimal places to return. |
Example
Text([Revenue], 2)
- This example returns the revenue as an alphanumeric string with two decimal places. If revenue, a numeric field, is 50000, the Text function in the above example returns 50000.00 as an alphanumeric value.
Uppercase
Converts a string to upper-case characters. Use this function or the Lowercase function with the Search function and with equal (==) statements to compare alphanumeric values when you do not care about the case of the string.
Syntax
Uppercase(Alphanumeric)
Parameters
Parameter | Data Type | Description |
Alphanumeric | String | The string to convert to upper case. |
Example
Uppercase([City])=="VANCOUVER"
- This example of a mandatory rule checks for the value "Vancouver" in the City field. The rule validates if an entry has the value "Vancouver", "vancouver", or "VANCOUVER" in the City field.
Value
Converts an alphanumeric value to a numeric value. If the value contains alphabetic characters, it returns 0. You can then manipulate the numeric value using any numeric functions.
Syntax
Value(Alphanumeric)
Parameters
Parameter | Data Type | Description |
Alphanumeric | String | The alphanumeric string to convert. |
Example
Value([Contract Number])
- This example returns the alphanumeric user-defined field, Contract Number, as a numeric value. If the field contains an alphabetical character, it returns 0.
