Conversion Functions

Conversion functions transform values from one data type to another. These functions are essential for ensuring data compatibility when combining different types of data in expressions, or when preparing data for specific output formats.

AsBoolean function

The AsBoolean function converts an expression to a Boolean value (True or False). This function is useful for evaluating conditions, checking for empty values, or converting numeric and string values to logical values.

This function returns a Boolean data type.

Syntax

AsBoolean(expression)

Parameter	Description
expression	Required. The value to convert to a Boolean. Can be any data type (number, string, date, or null).

Conversion Rules

Input Type	Conversion Rule
Null	Returns False
Number	Returns False if the value is 0; otherwise returns True
String	Returns False if the string is empty (""); otherwise returns True
Date	Always returns True (any valid date is considered true)
Boolean	Returns the value unchanged

Examples

1. Convert numeric values to Boolean:

   AsBoolean(1)

   Result: True

   AsBoolean(0)

   Result: False

   AsBoolean(-5)

   Result: True

2. Convert string values to Boolean:

   AsBoolean("Hello")

   Result: True

   AsBoolean("")

   Result: False

3. Check if a recipient field has a value. Suppose the MiddleName field may be empty for some recipients:

   AsBoolean(|->[MiddleName])

   If MiddleName = "Robert"

   Result: True

   If MiddleName = ""

   Result: False

4. Use in a conditional expression to check for valid data:

   If (AsBoolean(|->[PhoneNumber])) {"Contact available"} else {"No contact info"}

   If PhoneNumber = "555-1234"

   Result: "Contact available"

   If PhoneNumber = ""

   Result: "No contact info"

AsDate function

The AsDate function converts an expression to a Date value. This function interprets strings as dates, converts numbers to dates based on a serial date system, and handles null values by returning the current date and time.

This function returns a Date data type.

Syntax

AsDate(expression)

Parameter	Description
expression	Required. The value to convert to a Date. Can be a string, number, or null.

Conversion Rules

Input Type	Conversion Rule
Null	Returns the current date and time
Number	Interprets the number as the count of days from December 31, 1899. For example, 1 = January 1, 1900.
String	Parses the string as a date using standard date formats
Date	Returns the value unchanged

Examples

1. Convert a date string to a Date value:

   AsDate("02/07/2006")

   Result: 02/07/2006 (July 2, 2006 or February 7, 2006 depending on locale)

2. Convert a date string with different format:

   AsDate("23/02/1994")

   Result: 23/02/1994 (February 23, 1994)

3. Convert a number to a date. Numbers represent days since December 31, 1899:

   AsDate(1)

   Result: 01/01/1900 (January 1, 1900)

   AsDate(44197)

   Result: 01/01/2021 (January 1, 2021)

4. Handle null values (returns current date):

   AsDate(Null)

   Result: [Current date and time]

5. Convert a recipient's date field stored as text. Suppose BirthDateText contains the text string "15/03/1985":

   AsDate(|->[BirthDateText])

   Result: #15/03/1985#

6. Convert a recipient's date field stored as text and use the FormatDate() function to format the date string any way you want. Again, suppose BirthDateText is "15/03/1985":

   FormatDate(AsDate(|->[BirthDateText]), "MMMM dd, yyyy")

   Result: March 15, 1985

7. Calculate age from a birth date string:

   Floor(Age(AsDate(|->[BirthDateText])))

   If BirthDateText = "15/03/1985" and current year is 2024:

   Result: 39 (or 38, depending on whether the birthday has occurred this year)

Note: Date string parsing depends on the system's regional settings. Ensure your date strings match the expected format for your environment. For predictable results, use ISO format (YYYY-MM-DD) when possible.

AsJsonArray function

The AsJsonArray function takes multiple comma-separated expressions and returns a JSON array containing their values. This function is useful for passing multiple values to other functions, creating structured data for web services, or building dynamic content that requires array formatting.

This function returns a String data type (formatted as a JSON array).

Syntax

AsJsonArray(expression1, expression2, ...)

Parameter	Description
expression1, expression2, ...	Required. One or more expressions to include in the JSON array. Can be any data type including field references, literals, and calculated values.

Behavior

• String values are wrapped in double quotes in the output

• Numeric values appear without quotes

• Boolean values appear as true or false (lowercase)

• Null values appear as null

• Expressions are evaluated before being added to the array

Examples

1. Create a JSON array with literal values:

   AsJsonArray("apple", "banana", "cherry")

   Result: ["apple","banana","cherry"]

2. Create a JSON array with mixed data types:

   AsJsonArray("John", 25, True)

   Result: ["John",25,true]

3. Create a JSON array with recipient data and calculated values:

   AsJsonArray(|->[CustomerID], @{FirstName}, 1+3)

   If CustomerID = "ML343" and FirstName ADOR returns "John":

   Result: ["ML343","John",4]

4. Build an array of product IDs for a web service call:

   AsJsonArray(|->[Product1], |->[Product2], |->[Product3])

   If Product1 = "SKU001", Product2 = "SKU002", Product3 = "SKU003":

   Result: ["SKU001","SKU002","SKU003"]

5. Create an array with numeric calculations:

   AsJsonArray(|->[Quantity], |->[UnitPrice], |->[Quantity] * |->[UnitPrice])

   If Quantity = 5 and UnitPrice = 19.99:

   Result: [5,19.99,99.95]

6. Build customer contact information array:

   AsJsonArray(|->[Email], |->[Phone], |->[Address])

   If Email = "john@example.com", Phone = "555-1234", Address = "123 Main St":

   Result: ["john@example.com","555-1234","123 Main St"]

AsNumber function

The AsNumber function converts an expression to a numeric value. This function is essential for performing mathematical operations on data that may be stored as text, or for ensuring numeric comparisons work correctly.

This function returns a Number data type.

Syntax

AsNumber(expression)

Parameter	Description
expression	Required. The value to convert to a number. Can be any data type (string, Boolean, date, or null).

Conversion Rules

Input Type	Conversion Rule
Null	Returns 0
Boolean	Returns 1 for True, 0 for False
String	Extracts the leading numeric portion. If the string doesn't start with a number, returns 0.
Date	Returns the serial date number (days since December 31, 1899)
Number	Returns the value unchanged

Examples

1. Convert string numbers to numeric values:

   AsNumber("42")

   Result: 42

   AsNumber("+24")

   Result: 24

   AsNumber("-15.5")

   Result: -15.5

2. Extract numbers from strings with trailing text:

   AsNumber("23ab")

   Result: 23

   AsNumber("100 units")

   Result: 100

   AsNumber("$50.00")

   Result: 0 (string starts with non-numeric character)

3. Handle strings that don't start with numbers:

   AsNumber("James")

   Result: 0

   AsNumber("ab23")

   Result: 0

4. Convert Boolean values:

   AsNumber(True)

   Result: 1

   AsNumber(False)

   Result: 0

5. Handle null values:

   AsNumber(Null)

   Result: 0

6. Convert a recipient field stored as text to perform calculations. Suppose QuantityOrdered is stored as text "5":

   AsNumber(|->[QuantityOrdered]) * AsNumber(|->[UnitPrice])

   If QuantityOrdered = "5" and UnitPrice = "19.99":

   Result: 99.95

7. Calculate total with numeric conversion for safety:

   AsNumber(|->[Subtotal]) + AsNumber(|->[Tax]) + AsNumber(|->[Shipping])

   If Subtotal = "150.00", Tax = "12.00", Shipping = "8.50":

   Result: 170.5

8. Count responses by converting Boolean answers:

   AsNumber(|->[OptIn1]) + AsNumber(|->[OptIn2]) + AsNumber(|->[OptIn3])

   If OptIn1 = True, OptIn2 = False, OptIn3 = True:

   Result: 2

Important: When converting strings that contain currency symbols or other leading non-numeric characters (like "$50.00"), the function returns 0. Use the Replace() or FindAndReplace() function to remove such characters before conversion, or use the CleanNumber() function.

AsString function

The AsString function converts an expression to a string (text) value. This function is useful for concatenating different data types, preparing data for text-based output, or ensuring consistent string formatting.

This function returns a String data type.

Syntax

AsString(expression)

Parameter	Description
expression	Required. The value to convert to a string. Can be any data type (number, Boolean, date, or null).

Conversion Rules

Input Type	Conversion Rule
Null	Returns an empty string ("")
Number	Returns the string representation of the number
Boolean	Returns "True" or "False"
Date	Returns the date formatted as a string using the system's default date format
String	Returns the value unchanged

Examples

1. Convert numeric values to strings:

   AsString(12)

   Result: "12"

   AsString(3.14159)

   Result: "3.14159"

   AsString(-42)

   Result: "-42"

2. Convert Boolean values to strings:

   AsString(True)

   Result: "True"

   AsString(False)

   Result: "False"

3. Handle null values:

   AsString(Null)

   Result: "" (empty string)

4. Concatenate a number with text:

   "Order #" + AsString(|->[OrderNumber])

   If OrderNumber = 12345:

   Result: "Order #12345"

5. Build a formatted message with mixed data types:

   "You ordered " + AsString(|->[Quantity]) + " items for $" + AsString(|->[Total])

   If Quantity = 3 and Total = 59.97:

   Result: "You ordered 3 items for $59.97"

6. Create a unique identifier by combining fields:

   AsString(|->[CustomerID]) + "-" + AsString(|->[OrderID]) + "-" + AsString(|->[LineItem])

   If CustomerID = 1001, OrderID = 5432, LineItem = 2:

   Result: "1001-5432-2"

7. Build a salutation using null-safe conversion:

   If (AsString(|->[Title]) != ""){AsString(|->[Title]) + " "} else {""} + AsString(|->[LastName])

   If Title = "Dr." and LastName = "Smith":

   Result: "Dr. Smith"

   If Title = Null and LastName = "Johnson":

   Result: "Johnson"

8. Format a phone number by converting and concatenating parts:

   "(" + AsString(|->[AreaCode]) + ") " + AsString(|->[PhonePrefix]) + "-" + AsString(|->[PhoneSuffix])

   If AreaCode = 555, PhonePrefix = 123, PhoneSuffix = 4567:

   Result: "(555) 123-4567"

Tips:

• For more control over how numbers and dates are formatted as strings, consider using the FormatNumber() or FormatDate() functions instead of AsString(). These specialized formatting functions provide options for decimal places, date patterns, and locale-specific formatting.

• When concatenating ADORs or Variables, using the & operator instead of + will force the values to be treated as string. For example, "Order #" & |->[OrderNumber] is the same as "Order #" + AsString(|->[OrderNumber])

More topics

• Numeric Functions - Functions for mathematical operations

• String Functions - Functions for text manipulation

• Date Functions - Functions for date and time operations