Cast options in Jitterbit App Builder

When working with Columns in App Builder, you may want to leverage Cast Options. Cast Options are used to assign a Data Type to an Expression, and are available for Columns when creating and modifying Business Rules. Cast Options allow you to change the Logical Data Type value for a selected Column while keeping the Physical Data Type in tact at the Data Storage layer. This article will provide an overview of the different Cast Options available, as well as provide an example of working with Cast Options for Percent.

To work with Cast Options, they are accessed from the Business Logic layer of App Builder. When you create a Rule and click into the Business SQL screen to define it, each Column added to the Rule has a corresponding pop-up icon that can be clicked to view and edit the corresponding Column detail information. Cast Options is an area on this Column definition screen.

The different Logical Data Types available to select as Cast Options are:

• Boolean - Yes/No
• Currency - Numeric values with decimal places,should be used to store currency values
• Date - Date only
• Date/Time - Date and time of day
• Decimal - Numeric data type with fixed scale and precision
• File - Binary attachment
• HTML - HTML
• Number - Any number
• Percent - Percent
• Photo - Picture
• String - String
• Time - Time of day only
• Unique ID - Unique identifier

The following values correspond with the Logical Data Type value selected, and are also available to be configured in Cast Options if applicable to the Logical Data Type:

• Length for a numeric data type is the number of bytes that are used to store the number. Length for a character string or Unicode data type is the number of characters. The length for binary, varbinary, and image data types is the number of bytes. For example, an int data type can hold 10 digits, is stored in 4 bytes, and does not accept decimal points. The int data type has a precision of 10, a length of 4, and a scale of 0.
• Precision is the number of digits in a number. For example: the number 123.45 has a Precision of 5
• Scale is the number of digits displayed to the right of the decimal point in a number. For example, the number 123.45 has a Scale of 2

Cast options for percent example

This example describes Cast Options when defining a Column in a Business Rule to be a Percent Logical Data Type.

The following values are able to be configured for a Percent Logical Data Type:

• Length for a numeric data type is the number of bytes that are used to store the number. Length for a character string or Unicode data type is the number of characters. The length for binary, varbinary, and image data types is the number of bytes. For example, an int data type can hold 10 digits, is stored in 4 bytes, and does not accept decimal points. The int data type has a precision of 10, a length of 4, and a scale of 0.
• Precision is the number of digits in a number. For example: the number 123.45 has a Precision of 5
• Scale is the number of digits displayed to the right of the decimal point in a number. For example, the number 123.45 has a Scale of 2

When dealing with percent, remember that 50% is 0.5. If you need to represent 12.34% that is 0.1234 and you need a Scale of 4 to represent it.

Additional resources

For more information see the Microsoft article Precision, scale and Length