Starting & advancing workflows

A workflow is started and advanced from your SQL. A view’s <SubmitSql>, <InsertSql>, row action or import AfterSql returns a callback row, and the engine executes it inside the same request before the response is sent:

SELECT 'startWorkflow' FunctionName, 'PurchaseOrderApproval', 'PurchaseOrders', @Id;

These are server callbacks. Unlike the UI callbacks in Actions & dispatch, they are stripped from the response and never reach the browser — which is why the workflow endpoints aren’t part of the client API at all.

Why not a declarative XML block?

Earlier versions used <WorkflowStartup> / <WorkflowTransition> elements on a view. They are gone. A callback is strictly more capable: the condition is ordinary SQL rather than a six-operator expression grammar, the entity id is passed explicitly instead of being inferred from the first cell of the result, and a failure surfaces instead of being swallowed. A view still carrying either element fails to parse, with a message naming the replacement.

The callbacks

startWorkflow

(WorkflowName, EntityType, EntityId [, …context])

INSERT INTO [Inventory].[PurchaseOrders] (…) VALUES (…);
DECLARE @NewId BIGINT = CAST(SCOPE_IDENTITY() AS BIGINT);

SELECT @NewId;                    -- value result set (see "Returning a value too")

IF @Status = 'Submitted'          -- the condition is plain SQL
  SELECT 'startWorkflow' FunctionName,
         'PurchaseOrderApproval', 'PurchaseOrders', @NewId,
         po.Number, s.Email AS SupplierEmail
  FROM [Inventory].[PurchaseOrders] po
  JOIN [Inventory].[Suppliers] s ON s.Id = po.SupplierId
  WHERE po.Id = @NewId;

A record that already has an Active instance is skipped, so an edit form that fires this on every save doesn’t stack up duplicates. Alias a column AllowMultiple (1 AS AllowMultiple) to opt out.

Batch start needs no separate callback. The envelope is a list of rows, so a set-based SELECT starts one instance per record, each with its own context — this is the whole of the bulk-import story:

-- ImportConfig <AfterSql>
SELECT 'startWorkflow' FunctionName,
       'PurchaseOrderApproval', 'PurchaseOrders', po.Id, po.Number
FROM [Inventory].[PurchaseOrders] po
WHERE po.ImportBatch = '{GUID}';

transitionWorkflow

(InstanceId, TransitionLabel [, Comment] [, …context])

For a form that already knows the instance — the task and activity forms opened from WF_Tasks, which are bound to the instance’s state row:

UPDATE [Inventory].[Inspections] SET Result = @Result WHERE Id = @Id;

SELECT 'transitionWorkflow' FunctionName, @Parent__InstanceId, 'InspectionPassed', @Comment, @Result;

transitionEntityWorkflow

(EntityType, EntityId, TransitionLabel [, Comment] [, …context])

For a form bound to the business record rather than the instance. The engine resolves the record’s Active instance, and throws when it has none:

UPDATE [Inventory].[PurchaseOrders] SET Status = @Status WHERE Id = @Id;

SELECT 'transitionEntityWorkflow' FunctionName, 'PurchaseOrders', @Id, 'Resubmitted', @Comment;

workflowSubmitApproval

(InstanceId, NodeKey, Decision, Comment)

Records a decision, which advances the flow once the node’s gate is satisfied. This is what the built-in WF_ApprovalForm submits, so the standard task → approve path needs no authoring:

SELECT 'workflowSubmitApproval' FunctionName, @InstanceId, @NodeKey, @Decision, @Comment;

The approver is always the signed-in user — authored SQL cannot nominate someone else.

Returning a value and a callback

A cart parent’s InsertSql must SELECT its new key, and a callback envelope’s first column must be FunctionName. One result set can’t be both, so return two:

SELECT @NewId;                                                       -- value
SELECT 'startWorkflow' FunctionName, 'Flow', 'Orders', @NewId;       -- envelope

They are matched by shape, not position — the first envelope-shaped set is the callbacks, the first other set is the value — so the order you write them in doesn’t matter. A single result set behaves exactly as it always has.

What the workflow context carries

Context is a flat dictionary of scalars, layered in this order (later wins):

1. The submit’s own parameters, with the @ stripped — so a workflow’s @Var guards and {{Var}} templates resolve without re-projecting every field.
2. The callback’s trailing columns, keyed by the name you aliased them with.

Only aliased columns become context: an unaliased projection arrives as Column1 / ?column?, which would invent a variable you never declared, so it is skipped.

@Session* values (@SessionUserId, @SessionCompanyId, @SessionRoles, …) are deliberately excluded. They are re-supplied live at every execution point, so a copy frozen at start time would go stale and shadow the real one. @RowVersion, @FormLoadTime, PageViewId and ViewId are dropped for the same reason.

Read values that exist now

A callback emitted from an InsertSql runs before the cart’s child rows are written, so re-reading a trigger-maintained total from the row would capture 0. Project only what is already settled — a sequence number the AFTER-INSERT trigger has expanded, say — and let everything else come from the submitted parameters.

Where the context is then read:

Consumer	Form
Transition guards	@Var
Action-node and transition <Sql>	@Var bound parameters
Email / notification subject, body, recipients	{{Var}} Handlebars
FieldValue approver resolution	a @ContextVar holding a user id
Assignment nodes	RoleFromContext / UserFromContext

A key that isn’t a valid SQL parameter name (spaces, punctuation) stays available to {{templates}} but is skipped when binding to Action SQL. FlowStatus is always injected from the instance and overrides any same-named context key.

The REST endpoints

POST /api/v1/genie/workflow/instances/{start,{id}/transition,{id}/approve,{id}/cancel} still exist for programmatic and administrative callers, but they are not part of the client API — the browser has no methods for them. They require the caller to be the instance’s assignee, a member of its assigned role, a pending approver on it, or hold System/Admin.