Concept: Table and Dataset Field Types
Each table and dataset field has a field type attribute. The field type is often referred to as a data type in other data applications.
The field type determines:
- What kind of values the field can hold.
- Which functions can use the field as an argument. Example: The CONCAT function only accepts Text fields as arguments.
When you create a table, you define the field type of each field. When you load data into a table from a delimited file, Workday validates the data against the defined schema. If the value for a field doesn’t match the field type or other field parameters (such as date format), then Workday marks the entire row as invalid and doesn’t include the row in the table. Instead, Workday sends the row to an error file that you can download.
When you create a base dataset using external data, Workday attempts to guess the field type of each field by examining some of the data. However, you can change the field type if Workday assigned the wrong field type or if you want to apply a different field type.
If you change the input field type in the last stage before a Manage Fields stage, the new field type must be compatible with the changed field type.
When you create a base dataset from a custom report, Workday maps the Workday field types to dataset field types.
For Prism calculated fields, the expression result determines the field type for that field. If a Prism calculated field expression is
TO_INT(zipcode)
, then the field type for that field is Integer.Workday recommends using the Numeric field type in datasets where possible. When you use a different numeric field type, such as Double, you risk losing precision and getting erroneous results depending on the data and calculations in the dataset. In base datasets, set the field type as Numeric(x,y). You can also change the field type in a derived dataset using the CAST function. Example:
CAST([salary] AS decimal(12,4))
If you're new to Workday, you don't have access to create or edit base datasets.
Table and Dataset Field Types
Tables and datasets use these field types:
Table or Dataset Field Type | Description | Range of Values |
|---|---|---|
Boolean | A Workday specific field type that contains boolean values. | True , False
|
Currency | A variable length decimal value that supports a maximum of 20 digits before the decimal point and a maximum of 6 digits after the decimal point, combined with a Workday-recognized, 3-digit currency code. | Currency values can represent any valid positive or negative value given the specified number of digits before and after the decimal point. |
Date | Date combined with a time of day with fractional seconds based on a 24-hour clock. | Date range: January 1, 1753, through December 31, 9999
Time range: 00:00:00 through 23:59:59.997 Internally, Workday stores all Date type data in UTC format (coordinated universal time). If you bring in external data with time zone information, Workday converts the data to UTC. If you bring in external data with no time zone information, Workday doesn't convert the data and stores it as UTC. |
Double | Double-precision 64-bit floating point number. | 4.94065645841246544e-324d to 1.79769313486231570e+308d (positive or negative) |
Instance | A Workday-specific field type that contains Workday Instance values. Each field also retains information about the business object the Instance field is based on. Instance fields represent a 1-to-1 relationship between 2 objects. | A hexadecimal value that references a WID. |
Integer | 32-bit integer (whole number). | -2,147,483,648 to 2,147,483,647 |
Long | 64-bit long integer (whole number). | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
Multi-Instance | A Workday-specific field type that contains a set of (zero or more) Workday Instance values. Each field also retains information about the business object the Instance values are based on. Multi-Instance fields represent a 1-to-many relationship between 2 objects. | Zero or more instance values (hexadecimal values that reference a WID) separated by a pipe character (|).
When you bring in multi-instance fields from a delimited file, the pipe-delimited instance values must be enclosed in the configured quotation character. |
Numeric | A variable length numeric value that supports a total of 38 digits before and after the decimal point, but a maximum of 18 digits after. | Numeric values can represent any valid positive or negative value given the specified number of digits before and after the decimal point.
All Numeric fields have an associated number of digits before and after the decimal point (similar, but not identical, to precision and scale). When you create a table or dataset from a Workday report, Workday assigns the digit values to the Numeric field. When you create a table or dataset from external data, you can specify the digit values for Numeric fields. Example: Numeric(19,2) can store any value from 0.00 to 9,223,372,036,854,775,807.99 and Numeric(19,0) can only store whole numbers from 0 to 9,223,372,036,854,775,807. |
Text | Variable length non-Unicode text (also known as string) data. | Maximum text length of 2,147,483,647 characters. |
Mapping Report Field Types to Table and Dataset Field Types
When you create a table or dataset from a custom report, Workday:
- Creates a field in the table or dataset for every report field that uses a supported field type.
- Assigns a field type to the fields in the table or dataset.
Workday only retains report fields that have field types that tables and datasets support. Workday doesn't include any field that uses an unsupported table or dataset field type.
After creating a table or dataset from a custom report, verify that the assigned field types are correct for your table or dataset needs. Example: You might need to change a field type from Numeric to Integer to match the field type when joining the dataset with another dataset.
This table explains how a report field type maps to a table or dataset field type when you create a table or dataset.
Report Field Type | Table or Dataset Field Type | Notes |
|---|---|---|
Numeric | Numeric, Integer, or Long | The field type in the table or dataset depends on the number of digits before and after the decimal point in the report:
For Numeric fields, Workday assigns the number of digits before and after the decimal point based on the values in the custom report. |
Text | Text | |
Rich Text | Not supported | |
Date | Date | |
DateTimeZone | Not supported | |
Time | Not supported | |
Currency | Currency | |
Boolean | Boolean | |
Multi-Instance | Multi-Instance | Tables and datasets only include Multi-Instance report fields that are located in:
|
Single Instance | Instance | |
Self-Referencing Instance | Instance |
Mapping Table and Dataset Field Types to Prism Data Source Field Types
When you enable a table for analysis or publish a dataset, Workday creates a Prism data source.
This table lists the field types Workday uses in a Prism data source:
Table or Dataset Field Type | Prism Data Source Field Type | Notes |
|---|---|---|
Text | Text | |
Boolean | Boolean | |
Date | Date | Workday doesn't include any time information in the Date field in the Prism data source. Workday deletes any time information in the Date field when it creates the Prism data source. |
Currency | Currency | |
Integer | Numeric | |
Long | Numeric | |
Double | Numeric | |
Numeric | Numeric | Workday writes Numeric fields with a maximum of 20 digits before and 6 digits after the decimal point. If a Numeric field contains a value that exceeds these maximums, then Workday rounds down the value when it creates the Prism data source. |
Instance | Single Instance | Workday assigns the business object name associated with the Instance field in the dataset. |
Multi-Instance | Multi-Instance | Workday assigns the business object name associated with the Multi-Instance field in the dataset. |