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.
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 the text string starting at the position you specify.
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
Changes a numeric value to an alphanumeric text string. You can manipulate the returned string using any of the other alphanumeric functions.
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. Alphanumeric values beginning with alphabetical characters return a value of 0. You can manipulate the string using any of the 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.