Alphanumeric Functions

Updated 2 years ago by dustin keir

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”.

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.


How did we do?