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 connector 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 the 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 can 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 connector 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 connector 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.