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 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 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))
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 hexadecimal values that reference a WID. |
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. |