REST

This section lists all the tables and views supported when ConnectionType is set to REST.

Entity Relationships

The Workday REST API contains different types of entities, each of which must each be accessed separately. The performance, structure, and requirements for inserting and modifying roles varies depending on the category of the table.

Base entities

Base entities are entities that can be read directly, such as Requisitions.

Child entities

Child entities are entities that are children of other entities. For example, RequisitionsWorktags is a child entity of Requisitions. If the cmdlet needs to read RequisitionsWorktags, it must first look up Requisitions (the parent table), then extract the list of worktags.

Owned entities

Owned entities are entities that can only be read by going through another entity. For example, RequisitionRequisitionLines is owned by the Requisitions table. If the cmdlet needs to read line items from RequisitionsRequisitionLines, it must go through a specific Requisition.

Owned child entities

Owned child entities are a combination of both Owned and Child entities. For example, RequisitionsRequisitionLinesWorktags is both a child of the RequisitionsRequisitionLines table and owned by the Requisitions table. If the cmdlet needs to read RequisitionsRequisitionLinesWorktags, it must look up a specific Requisition, then look up its RequisitionsRequisitionLines, finally extracting the worktags from each line.

Schema Differences

Each type of table has its own rules for determining primary and foreign keys. Foreign keys are always prefixed with the name of the table they refer to. For example, the Requisitions_Id column on the RequisitionsWorktags child table refers to the Id column of the Requisitions parent table.

• Base entities have one primary key column called Id.
• Child entities have a compound primary key containing Id and another column that references the parent Id column.
• Owned entities have a compound primary key containing Id and another column that references the owner Id column.
• Owned child entities have a compound primary key containing Id and two reference columns, one to the parent and one to the parent's owner.

In addition, each type of table exposes different prompt columns. The next section goes into detail on when these prompts take effect.

• Base entities expose their own prompt columns.
• Child entities expose their parent's prompt columns.
• Owned entities expose their prompt columns and their owner's prompt columns.
• Owned child entities expose their parent's prompt columns and their owner's prompt columns.

Filtering Support

Each type of table handles filters on its Id columns differently:

• If a base entity includes an Id filter like WHERE Id = 'abcdef123456', the Id value is pushed down so that Workday only returns that specific row.
• Child entities only push down filters on the parent Id. Workday does not support filtering on child Ids directly.
• Owned entities and owned child tables only push down filters on the owner Id.

Each type of table also has its own behaviors around prompts:

• Base entity prompts are sent to Workday unless an Id filter is also provided. This means that a filter like WHERE Id = '...' AND Search_Prompt = 'Logan' will return the row with that Id, even if it is not reported with the prompt only: WHERE Search_Prompt = 'Logan'.
• An owned entity contains its own prompts and inherits the prompts from its owner. The owner prompts are sent to Workday as long as there is no parent Id filter. Other prompts are always sent to Workday.
• Child and owned child entity prompts are all inherited from the parent (and owner) and follow the same rules.

Owned and owned child tables have the additional requirement that their owner's Id must be given in order to read from them. This can be done in several ways:

-- Provide one or multiple owner Id values
SELECT * FROM RequisitionsRequisitionLines WHERE Requisitions_Id = '...'
SELECT * FROM RequisitionsRequisitionLines WHERE Requisitions_Id IN ('...', '...')

--- Provide a collection of owner Ids via subquery
SELECT * FROM RequisitionsRequisitionLines WHERE Requisitions_Id IN (SELECT Id FROM Requisitions)

-- If no subquery or values are given, the cmdlet automatically adds the appropriate subquery
SELECT * FROM RequisitionsRequisitionLines

Prompts are also considered when the cmdlet generates subqueries for owned and owned child entities. For example, if this query is executed:

SELECT * FROM RequisitionsRequisitionLines WHERE FromDate_Prompt = 'January 1 2020' AND ToDate_Prompt = 'March 31 2020'

The cmdlet includes the date prompts in the generated subquery because they are inherited from the Requisitions table:

SELECT * FROM RequisitionsRequisitionLines WHERE Requisisions_Id IN (
  SELECT Id FROM Requisitions WHERE FromDate_Prompt = 'January 1 2020' AND ToDate_Prompt = 'March 31 2020'
)

Note that some owned tables include prompts with a _Prompt_For_ suffix. This means that both the owned table and the owner table have a prompt with the same name. For example, DataSourcesDataSourceFilters includes a prompt called Alias_Prompt which accepts the alias of the filter, as well as a prompt called Alias_Prompt_For_DataSources which accepts the alias of the data source that owns the filter.

Modifying Data

Some REST entities are tables that can be used with INSERT, UPDATE, or DELETE. However, note that not every table supports every operation.

To perform an INSERT, the query must specify owner and parent Ids (when the table has them) in addition to writable data fields. An INSERT must never include values for the Id column or any prompts. For example, all of these queries are valid INSERTs:

-- No Id on a base table
INSERT INTO ExpenseEntries(Amount_Currency, Amount_Value, Date) VALUES ('USD', 185.67, 'June 1 2021')

-- Parent Id on a child table
INSERT INTO ExpenseEntriesAttachments(ExpenseEntries_Id, ContentType_Id, FileName) VALUES ('...', '...', 'receipt.jpg')

-- Owner Id on an owned table
INSERT INTO WorkersBusinessTitleChanges(Workers_Id, ProposedBusinessTitle) VALUES ('...', 'Head of Security')

Ids are not required to perform an UPDATE or a DELETE. If the Id, parent Id or owner Id is missing the cmdlet will first find all matching rows and then update or delete them individually:

-- Delete a single row by Id
DELETE FROM ExpenseEntries WHERE Id = '...'

-- Delete a single child row by Id
DELETE FROM ExpenseEntriesAttachments WHERE Id = '...' AND ExpenseEntries_Id = '...'

-- Delete all children belonging to the same parent
DELETE FROM ExpenseEntriesAttachments WHERE Id = '...'

Prompt Value Tables

The Workday REST API exposes several resources that allow you to discover ID values, which can be used for certain fields. All value tables have names ending in Values and share the same basic structure:

• An Id column that contains the Workday ID which may be used for the field or prompt.
• A Descriptor column that gives a human-readable description of the entity.
• A CollectionToken column and Collection_Prompt input, discussed below.
• Some value tables contain additional prompts.

CollectionToken column and Collection_Prompt are used for hierarchical resources like location data or organizations. You must issue multiple queries to get prompt values out of these resources:

1. Query the table as normal while providing any prompt values. For example, SELECT * FROM JobChangesGroupWorkersValues WHERE EffectiveDate_Prompt = '2020-01-01' returns a list of categories that contain workers organized by different attributes (such as location, organization, or manager).
2. Read the CollectionToken value from the row representing the category you are interested in. In this example, the CollectionToken for the "Workers By Manager Hierarchy" category has the value abcxyz123.
3. Query the same table while providing a value for Collection_Prompt using the token from step 2. This returns a list of entities that belong to category with that token. For example, SELECT * FROM JobChangesGroupWorkersValues WHERE Collection_Prompt = 'abcxyz123' returns a list of entites in the "Workers By Manager Hierarchy" category.
4. If the row you are interested has a NULL value for CollectionToken, you have reached the last level and an use the ID value for the prompt. Otherwise repeat steps 2-4 to navigate all the levels of the hierarchy until you reach the bottom.

Change Tables

The Workday REST API has a concept of change resources, which are used to configure and execute certain business processes. The cmdlet supports these change resources by providing a family of tables and stored procedures that act on each business process.

For example, the Change Organization Assignment business process has two stored procedures, BeginOrganizationAssignmentChange and SubmitOrganizationAssignmentChange, along with several tables that control the parameters of the business process. At a high level, the procedure for invoking these business processes is as follows:

1. Execute the Begin procedure. Refer to the documentation for the specific Begin procedure for the list of available inputs.
2. If it is successful, the Begin procedure outputs a change ID. This value must be provided for all queries to the change tables as well as the Submit stored procedure.
3. Modify the change tables using the change ID from step 2.
4. Execute the Submit procedure. Only the change ID is required, although other inputs may be set to control how Workday applies the process. For example, the action ID input controls whether Workday executes the process immediately or saves it for later review.

The cmdlet exposes two types of change tables: collection change tables and single-value change tables.

Collection change tables support SELECT, INSERT, UPDATE, and DELETE. They represent bulk changes that are applied to an entity, such as adding multiple addresses or phone numbers to a single worker.

SELECT * FROM WorkContactInformationChangesWebAddresses WHERE WorkContactInformationChange_Id = 'abcxyz123'

INSERT INTO WorkContactInformationChangesWebAddresses( WorkContactInformationChange_Id, Url, Usage_Primary, Usage_UsageType_Id) 
VALUES ('abcxyz123', 'https://www.company.com/~bobsmith', true, '...')

UPDATE WorkContactInformationChangesWebAddresses
SET Usage_Comment = 'Worker home page'
WHERE WorkContactInformationChange_Id = 'abcxyz123'
AND Id = '...'

DELETE FROM WorkContactInformationChangesWebAddresses
WHERE WorkContactInformationChange_Id = 'abcxyz123'

Single-value change tables support only SELECT and UPDATE. They represent parameters that can have only a single value in the entire business process. For example, when changing a job's organization, the job can only be moved to a single target company.

SELECT * FROM OrganizationAssignmentChangesCompany WHERE OrganizationAssignmentChange_Id = 'abcxyz123'

UPDATE OrganizationAssignmentChangesCompany 
SET Company_Id = '...'
WHERE OrganizationAssignmentChange_Id = 'abcxyz123'

For details on supported operations for each change table, see its documentation.