Submit a request

Configure Workflow Rule

Avatar
Kepion Support
  • Updated

Introduction

A Workflow Rule allows you to run SQL statements upon actions, such as Post, Approve, Reject, etc. In this article, let's look at how we can configure a Workflow Rule.

 

 

Use Case

In this example, we have an Advanced Workflow configured for our Application. We would like to archive Forecast data when an Approver clicks Approve in the Dashboard Action menu.

 

action_.png

 

 

Add Workflow Rule

1. Go to your target Application in the Modeler.

 

Modeler.PNG

 

2. Expand Application and select All Workflow Rules.

3. Click Add.

 

Addworkflow.png

 

4. Give the rule a name, and click Save.

 

workflowruleconfigure.png

 

You now have an empty Rule that you can configure.

 

 

Configuration

1. Click Edit  edit.png and enter a SQL script in the Rule window.

 

The key is to use the system parameter @ActivityID, and the system table [dbo].[Activities] and [dbo].[Constants]: 

 

  • @ActivityID: System parameter of type INT that you can use directly without declaration.
  • [dbo].[Activities]: System table that tracks user interaction with a Workflow, e.g. SubmissionType, SubmissionID, UserID, Timestamp, etc.
  • [dbo].[Constants]: System table that lists out the possible options for SubmissionType, and options for other objects.
  • [dbo].[Authorizations]: System table that lists out configuration details about a Workflow, including Dashboard and Workbook.

 

script.PNG

 

DECLARE @SubmissionAction NVARCHAR(20)
 
SELECT
   @SubmissionAction = B.[Name]
FROM
   [dbo].[Activities] A
   INNER JOIN [dbo].[Constants] B ON A.[SubmissionAction] = B.[Value]
   INNER JOIN [dbo].[Authorizations] C ON A.[AuthorizationID] = C.[AuthorizationID]
WHERE
   B.[Group] = N'SubmissionAction'
   AND A.[ActivityID] = @ActivityID
   AND C.[AuthorizationName] = N'Revenue Planning'
 
IF @SubmissionAction = N'approve'
BEGIN
   -- DATA MOVEMENT LOGIC
   EXEC [dbo].[USR_ARCHIVE_FORECAST]
END

 

2. Click Save save.png to confirm your changes.

 

 

Other Use Cases

A Workflow Rule can be triggered by the following actions:

 

  • Approve
  • Discard
  • Post
  • Recall
  • Reject
  • Save
  • Submit

 

Here are some SQL scripts to help explore the system tables:

 

-- Options for SubmissionAction
SELECT *
FROM [dbo].[Constants]
WHERE [Group] = N'SubmissionAction'
 
-- Useful information from the system tables
SELECT
A.[ActivityID]
,B.[Name] AS 'SubmissionAction'
,C.[AuthorizationName]
,D.[UserName]
,A.[ActedOn]
,A.[SubmissionID]
FROM
[dbo].[Activities] A
INNER JOIN [dbo].[Constants] B ON A.[SubmissionAction] = B.[Value]
INNER JOIN [dbo].[Authorizations] C ON A.[AuthorizationID] = C.[AuthorizationID]
INNER JOIN [dbo].[Users] D ON A.[UserID] = D.[UserID]
WHERE
B.[Group] = N'SubmissionAction'

Related articles