Views:
TypeRead/writeAuthorAvailability
Data providerWriteFINBOURNEProvided with LUSID

Providing you have sufficient access control permissions, the Workflow.Task.Writer provider enables you to write a Luminesce SQL query that creates or updates one or more tasks in LUSID's Workflow Service.

You must construct a valid table of data to write, one task per record. Workflow.Task.Writer lists the fields (columns) available to populate with values for each record, and has a set of parameters to help you construct a valid table.

See also: Workflow.Task, Workflow.Task.Field

Basic usage

@table_of_data = <select-statement>;
select * from Workflow.Task.Writer where toWrite = @table_of_data;

Query parameters

Workflow.Task.Writer has parameters that help you construct a valid table of data to write.

Note: The ToWrite parameter is mandatory and describes the data for the tasks you are creating or updating.

To list available parameters, their data types, default values, and an explanation for each, run the following query using a suitable tool:

select FieldName, DataType, ParamDefaultValue, Description from Sys.Field where TableName = 'Workflow.Task.Writer' and FieldType = 'Parameter';

Data fields

Workflow.Task.Writer lists the fields you can populate in your table of data to write.

Depending on the operation you want to perform, the following fields are mandatory to include in the table of data:

OperationMandatory fields
Create a new task

DefinitionScope
DefinitionCode
Any fields defined in the task definition that are required for the task to reach its initial state.

Update a taskId

To list all available fields, their data types, whether fields are considered 'main', and an explanation for each, run the following query using a suitable tool:

select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Workflow.Task.Writer' and FieldType = 'Column';

Write errors

We recommend examining the results of every write query using the WriteError field.

For each record in the table of data, if unsuccessful, Workflow.Task.Writer returns a WriteError message containing an error code and explanation to help you discover why LUSID considers the operation invalid. 

Examples

Note: For more example Luminesce SQL queries, visit our Github repo.

Example 1: Create a task

In this example, Workflow.Task.Writer is used to kick off a new task using the task definition created here. As DataToApproveDescription is a required field for this particular task definition, you must also pass in a value in order to successfully create the task.

@data_to_write = select 
  'approvals' as DefinitionScope, 
  'dataApproval' as DefinitionCode,
  'Data x for x client' as DataToApproveDescription,
  'Joe Bloggs' as Assignee;

select * from Workflow.Task.Writer where ToWrite = @data_to_write;

The query returns a table of data that contains information on the task. Note the unique Id, which can be passed into other queries to update the task, or retrieve information on the task, such as its current state. 

Example 2: Update a task

In this example, the Id of an existing task, along with a Trigger, is passed into Workflow.Task.Writer to prompt the task to change state. 

@data_to_write = select 
  '409d58b3-adca-402f-a5dc-68f146fc9898' as Id,
  'grant' as Trigger,
  'New portfolio for x' as DataToApproveDescription,
  'Finbourne-Examples' as PortfolioScope,
  'My-New-Approved-Portfolio' as PortfolioCode,
  'GBP' as BaseCurrency,
  'My New Approved Portfolio' as DisplayName; 

select * from Workflow.Task.Writer where ToWrite = @data_to_write;

Example 3: Create multiple tasks at once

In this example, three tasks are created at the same time using the same task definition.

@vals = values
('New EU Portfolio', 'Joe Bloggs'),
('New UK Portfolio', 'Joe Bloggs'),
('New US Portfolio', 'Jane Doe');
@table_of_data = select 'approvals' as DefinitionScope, 'dataApproval' as DefinitionCode, column1 as DataToApproveDescription, column2 as Assignee from @vals;
select * from Workflow.Task.Writer where ToWrite = @table_of_data;