Questionnaires can be used as Dynamic forms when running a workflow rule. They can be used to prompt the user for multiple pieces of information before then running the SQL Update Statement.
In your SQL Update you can use the following types of bookmarks:
{PopupDQ.DQUserCode.DQColumnName}
Replacing “DQUserCode” with the actual user code of the DQ you want to popup as criteria, and “DQColumnName” with the question column name in the DQ that will be used to populate the bookmark. When the workflow rule runs, the DQ specified will pop up so you can enter the values, and the bookmarks in the SQL Update will be replaced with the values you entered on the DQ.
When multiple {PopupDQ.*.*} bookmarks are used in the same workflow rule the order matters. The system will first make a list of the different DQ User Codes it needs to pop up. And it will pop up those DQs in the order the bookmarks appeared in the SQL Update. We recommend declaring variables to hold the bookmark values at the beginning of your SQL Update statement and surround bookmarks with quotes for the SQL formula to work properly.
--This example adds a formatted issue note that will be marked as Show On Web by default.
DECLARE @pIssueID INT
SET @pIssueID = {IssueID}
DECLARE @FirstName NVARCHAR(100)
SET @FirstName = '{PopupDQ.DQ1.FirstName}'
DECLARE @LastName NVARCHAR(100)
SET @LastName = '{PopupDQ.DQ1.LastName}'
DECLARE
@City NVARCHAR(100)
SET @City = '{PopupDQ.DQCityPicker.City1}'
DECLARE @DateFrom NVARCHAR(100)
SET @DateFrom = '{[Global]PopupDQ.DQGlobal.DateFrom}'
DECLARE @DateTo NVARCHAR(100)
SET @DateTo = '{[Global|SavedCriteria]PopupDQ.DQGlobal.DateTo}'
DECLARE @Category NVARCHAR(50)
SET @Category = '{WorkFlow.Category}'
DECLARE @TaskStatus NVARCHAR(50)
SET @TaskStatus = '{WorkFlow.Status}'
DECLARE @Comments NVARCHAR(max)
SET @Comments = '{[Global]PopupDQ.DQGlobal.Comments}'
DECLARE @pNotes NVARCHAR(MAX)
SET @pNotes = 'DQ 1 First Name: ' + @FirstName+ 'and DQ1 Last Name: ' + @LastName + '. Bookmark for DQCityPicker City: ' + @City + '.
This is a Global Date From: ' + @DateFrom
+ ' and Global Date To: ' + @DateTo + '. This is a column from the workflow rules Category: ' + @Category + ' and Status: '
+ @TaskStatus + '. These are the Global comments: ' + @Comments
EXEC IMSP_Add_Issue_Note @IssueID=@pIssueID, @Notes=@pNotes
Defining the bookmark variables at the top means you can easily re-order the variable statements and alter the order in which the DQs will pop up. The only exception to the order of the DQs is the Global bookmark modifier described next.
[Global] Bookmark modifier
You will notice in the example above that a few bookmarks start with {[Global]PopupDQ.
For example {[Global]PopupDQ.DQGlobal.DateFrom}
This modifies the behavior of the PopupDQ bookmark and tags it as a Global bookmark. This only applies to bulk actions. When a rule processes multiple records (using the Bulk Actions dropdown from the search screens), the Global DQs will only popup ONCE, before any records are processed. Then the same value will be used in every record that is processed.
This means that Global DQs always pop up FIRST, regardless of when they appear in the statement.
So in the example above, DQGlobal will pop up first, then DQ1, then DQCityPicker.
[SavedCriteria] Bookmark modifier
You will notice in the example above that one of the bookmarks, {[Global|SavedCriteria]PopupDQ.DQGlobal.DateTo} , has two modifiers inside the brackets. You can use two modifiers by separating them with a pipe symbol | . So that bookmark has both a
Global modifier and a SavedCriteria modifier.
By default, when a DQ is used in a PopupDQ bookmark, the DQ values are NOT SAVED to the database in the usual Custom_DQ_* table. However, SavedCriteria modifiers alter this default behavior and force
the DQ values to be saved to the database as any regular DQ does when you use it anywhere else. So in the example above, the values of the DQGlobal that pops up will be saved to the DB.
For a rule to be eligible to be run on a particular record, the SQL SELECT must return a row. If the SQL SELECT does not return a row then the rule is not eligible. When you run a rule, you can use the data returned on that row in your SQL UPDATE by using the {Workflow.*} type bookmarks.
For example, if your SQL SELECT is something like:
select IssueID, [Status], Category
from issues
where issueid = {IssueID}
AND ClosedDate IS null
Then you would be able to use {Workflow.Status} and {Workflow.Category} in your SQL UPDATE and the bookmark would be replaced with the values of those columns. In the example above those values would come from the Issues table for the current issue
being processed (since {IssueID} is replaced with the issue number being processed by the rule at the time)
You can also use values from the SELECT in your DQ Form in a Read Only Question. Using the example above i can show the Status in my questionniare by putting the following:
select @result = '{WorkFlow.Status}'