Open Forum

Here's what I've got so far:

SELECT s.Workflow_Name
    , s.Workflow_Step_Name
    , s.Workflow_Step_Sequence
    , s.WF_Step_Description
    , s.Workflow_Step_Order
    , s.WF_Step_Predecessor
    , u.ADDisplayName
    , c.ConditionsGuid
    , c.SEQNUMBR
    , c.FromTable
    , c.FromField
    , c.QueryOperator
    , c.QueryConditon
    , c.StringFilter_1
    , c.NumericFilter_1
    , CASE
        WHEN RTRIM(c.StringFilter_1) > '' THEN
            CASE c.QueryOperator
                WHEN 1 THEN 'Where ' + RTRIM(c.FromField) + ' = "' + RTRIM(c.StringFilter_1) + '"'
                ELSE ' Or ' + RTRIM(c.FromField) + ' = "' + RTRIM(c.StringFilter_1) + '"'
            END
         ELSE
            CASE c.QueryConditon
                WHEN 7 THEN ' And ' + RTRIM(c.FromField) + ' >= ' + FORMAT(c.NumericFilter_1, 'N', 'en-us')
                ELSE ' And ' + RTRIM(c.FromField) + ' <= ' + FORMAT(c.NumericFilter_1, 'N', 'en-us')
            END
    END AS Clause
FROM dbo.WF100003 AS s
INNER JOIN dbo.WF40200 AS u
ON s.Workflow_Step_Assign_To = u.UsersListGuid
INNER JOIN dbo.CO00123 AS c
ON s.Workflow_Step_Conditions = c.ConditionsGuid
WHERE s.Workflow_Name = 'GLC PO Workflow'
ORDER BY Workflow_Step_Order
, WF_Step_Predecessor
, Workflow_Step_Sequence
, SEQNUMBR;

This code is certainly not generic. All of the conditions in our PO Workflow refer only to two fields in the 'Purchase Order Work' table and use only one StringFilter column and one NumericFilter column. The output has these final five columns:

... and this matches what you'd see in the Workflow designer, minus the references to the 'Purch Order Work' table.

Now I just have to remember how to string values from multiple rows together into one clause to really nail down this documentation.

Regards,

Here's what I've got so far:

SELECT s.Workflow_Name
    , s.Workflow_Step_Name
    , s.Workflow_Step_Sequence
    , s.WF_Step_Description
    , s.Workflow_Step_Order
    , s.WF_Step_Predecessor
    , u.ADDisplayName
    , c.ConditionsGuid
    , c.SEQNUMBR
    , c.FromTable
    , c.FromField
    , c.QueryOperator
    , c.QueryConditon
    , c.StringFilter_1
    , c.NumericFilter_1
    , CASE
        WHEN RTRIM(c.StringFilter_1) > '' THEN
            CASE c.QueryOperator
                WHEN 1 THEN 'Where ' + RTRIM(c.FromField) + ' = "' + RTRIM(c.StringFilter_1) + '"'
                ELSE ' Or ' + RTRIM(c.FromField) + ' = "' + RTRIM(c.StringFilter_1) + '"'
            END
         ELSE
            CASE c.QueryConditon
                WHEN 7 THEN ' And ' + RTRIM(c.FromField) + ' >= ' + FORMAT(c.NumericFilter_1, 'N', 'en-us')
                ELSE ' And ' + RTRIM(c.FromField) + ' <= ' + FORMAT(c.NumericFilter_1, 'N', 'en-us')
            END
    END AS Clause
FROM dbo.WF100003 AS s
INNER JOIN dbo.WF40200 AS u
ON s.Workflow_Step_Assign_To = u.UsersListGuid
INNER JOIN dbo.CO00123 AS c
ON s.Workflow_Step_Conditions = c.ConditionsGuid
WHERE s.Workflow_Name = 'GLC PO Workflow'
ORDER BY Workflow_Step_Order
, WF_Step_Predecessor
, Workflow_Step_Sequence
, SEQNUMBR;

This code is certainly not generic. All of the conditions in our PO Workflow refer only to two fields in the 'Purchase Order Work' table and use only one StringFilter column and one NumericFilter column. The output has these final five columns:

... and this matches what you'd see in the Workflow designer, minus the references to the 'Purch Order Work' table.

Now I just have to remember how to string values from multiple rows together into one clause to really nail down this documentation.

Regards,

Workflow_Step_Status

And lastly: Workflow_Action

We create Smartlists for the users and managers. The main workflow is Timesheet Approvals.
Three Smartlists are used:
1. Timesheets created, Not Submitted, Recalled, or Rejected (this is really used as a missing timesheets report)
2. Timesheets submitted and pending Approval- shows who and how long they have to approve (this used to ping approvers before the WF escalates)
3. Timesheets approved WF History - Who Approved it and When (used to see who approved something)

Because WF transactions only use about 4 tables and they get very full very fast and impact approval queries for users when doing 1,000 timesheets per week, we dump the WF histories of Completed Approvals after 100 days. SQL job for that. 

Here's what I've got so far:

SELECT s.Workflow_Name
    , s.Workflow_Step_Name
    , s.Workflow_Step_Sequence
    , s.WF_Step_Description
    , s.Workflow_Step_Order
    , s.WF_Step_Predecessor
    , u.ADDisplayName
    , c.ConditionsGuid
    , c.SEQNUMBR
    , c.FromTable
    , c.FromField
    , c.QueryOperator
    , c.QueryConditon
    , c.StringFilter_1
    , c.NumericFilter_1
    , CASE
        WHEN RTRIM(c.StringFilter_1) > '' THEN
            CASE c.QueryOperator
                WHEN 1 THEN 'Where ' + RTRIM(c.FromField) + ' = "' + RTRIM(c.StringFilter_1) + '"'
                ELSE ' Or ' + RTRIM(c.FromField) + ' = "' + RTRIM(c.StringFilter_1) + '"'
            END
         ELSE
            CASE c.QueryConditon
                WHEN 7 THEN ' And ' + RTRIM(c.FromField) + ' >= ' + FORMAT(c.NumericFilter_1, 'N', 'en-us')
                ELSE ' And ' + RTRIM(c.FromField) + ' <= ' + FORMAT(c.NumericFilter_1, 'N', 'en-us')
            END
    END AS Clause
FROM dbo.WF100003 AS s
INNER JOIN dbo.WF40200 AS u
ON s.Workflow_Step_Assign_To = u.UsersListGuid
INNER JOIN dbo.CO00123 AS c
ON s.Workflow_Step_Conditions = c.ConditionsGuid
WHERE s.Workflow_Name = 'GLC PO Workflow'
ORDER BY Workflow_Step_Order
, WF_Step_Predecessor
, Workflow_Step_Sequence
, SEQNUMBR;

This code is certainly not generic. All of the conditions in our PO Workflow refer only to two fields in the 'Purchase Order Work' table and use only one StringFilter column and one NumericFilter column. The output has these final five columns:

... and this matches what you'd see in the Workflow designer, minus the references to the 'Purch Order Work' table.

Now I just have to remember how to string values from multiple rows together into one clause to really nail down this documentation.

Regards,

Workflow_Step_Status

And lastly: Workflow_Action

We create Smartlists for the users and managers. The main workflow is Timesheet Approvals.
Three Smartlists are used:
1. Timesheets created, Not Submitted, Recalled, or Rejected (this is really used as a missing timesheets report)
2. Timesheets submitted and pending Approval- shows who and how long they have to approve (this used to ping approvers before the WF escalates)
3. Timesheets approved WF History - Who Approved it and When (used to see who approved something)

Because WF transactions only use about 4 tables and they get very full very fast and impact approval queries for users when doing 1,000 timesheets per week, we dump the WF histories of Completed Approvals after 100 days. SQL job for that. 

Here's what I've got so far:

SELECT s.Workflow_Name
    , s.Workflow_Step_Name
    , s.Workflow_Step_Sequence
    , s.WF_Step_Description
    , s.Workflow_Step_Order
    , s.WF_Step_Predecessor
    , u.ADDisplayName
    , c.ConditionsGuid
    , c.SEQNUMBR
    , c.FromTable
    , c.FromField
    , c.QueryOperator
    , c.QueryConditon
    , c.StringFilter_1
    , c.NumericFilter_1
    , CASE
        WHEN RTRIM(c.StringFilter_1) > '' THEN
            CASE c.QueryOperator
                WHEN 1 THEN 'Where ' + RTRIM(c.FromField) + ' = "' + RTRIM(c.StringFilter_1) + '"'
                ELSE ' Or ' + RTRIM(c.FromField) + ' = "' + RTRIM(c.StringFilter_1) + '"'
            END
         ELSE
            CASE c.QueryConditon
                WHEN 7 THEN ' And ' + RTRIM(c.FromField) + ' >= ' + FORMAT(c.NumericFilter_1, 'N', 'en-us')
                ELSE ' And ' + RTRIM(c.FromField) + ' <= ' + FORMAT(c.NumericFilter_1, 'N', 'en-us')
            END
    END AS Clause
FROM dbo.WF100003 AS s
INNER JOIN dbo.WF40200 AS u
ON s.Workflow_Step_Assign_To = u.UsersListGuid
INNER JOIN dbo.CO00123 AS c
ON s.Workflow_Step_Conditions = c.ConditionsGuid
WHERE s.Workflow_Name = 'GLC PO Workflow'
ORDER BY Workflow_Step_Order
, WF_Step_Predecessor
, Workflow_Step_Sequence
, SEQNUMBR;

This code is certainly not generic. All of the conditions in our PO Workflow refer only to two fields in the 'Purchase Order Work' table and use only one StringFilter column and one NumericFilter column. The output has these final five columns:

... and this matches what you'd see in the Workflow designer, minus the references to the 'Purch Order Work' table.

Now I just have to remember how to string values from multiple rows together into one clause to really nail down this documentation.

Regards,

Workflow_Step_Status

And lastly: Workflow_Action

We create Smartlists for the users and managers. The main workflow is Timesheet Approvals.
Three Smartlists are used:
1. Timesheets created, Not Submitted, Recalled, or Rejected (this is really used as a missing timesheets report)
2. Timesheets submitted and pending Approval- shows who and how long they have to approve (this used to ping approvers before the WF escalates)
3. Timesheets approved WF History - Who Approved it and When (used to see who approved something)

Because WF transactions only use about 4 tables and they get very full very fast and impact approval queries for users when doing 1,000 timesheets per week, we dump the WF histories of Completed Approvals after 100 days. SQL job for that. 

Here's what I've got so far:

SELECT s.Workflow_Name
    , s.Workflow_Step_Name
    , s.Workflow_Step_Sequence
    , s.WF_Step_Description
    , s.Workflow_Step_Order
    , s.WF_Step_Predecessor
    , u.ADDisplayName
    , c.ConditionsGuid
    , c.SEQNUMBR
    , c.FromTable
    , c.FromField
    , c.QueryOperator
    , c.QueryConditon
    , c.StringFilter_1
    , c.NumericFilter_1
    , CASE
        WHEN RTRIM(c.StringFilter_1) > '' THEN
            CASE c.QueryOperator
                WHEN 1 THEN 'Where ' + RTRIM(c.FromField) + ' = "' + RTRIM(c.StringFilter_1) + '"'
                ELSE ' Or ' + RTRIM(c.FromField) + ' = "' + RTRIM(c.StringFilter_1) + '"'
            END
         ELSE
            CASE c.QueryConditon
                WHEN 7 THEN ' And ' + RTRIM(c.FromField) + ' >= ' + FORMAT(c.NumericFilter_1, 'N', 'en-us')
                ELSE ' And ' + RTRIM(c.FromField) + ' <= ' + FORMAT(c.NumericFilter_1, 'N', 'en-us')
            END
    END AS Clause
FROM dbo.WF100003 AS s
INNER JOIN dbo.WF40200 AS u
ON s.Workflow_Step_Assign_To = u.UsersListGuid
INNER JOIN dbo.CO00123 AS c
ON s.Workflow_Step_Conditions = c.ConditionsGuid
WHERE s.Workflow_Name = 'GLC PO Workflow'
ORDER BY Workflow_Step_Order
, WF_Step_Predecessor
, Workflow_Step_Sequence
, SEQNUMBR;

This code is certainly not generic. All of the conditions in our PO Workflow refer only to two fields in the 'Purchase Order Work' table and use only one StringFilter column and one NumericFilter column. The output has these final five columns:

... and this matches what you'd see in the Workflow designer, minus the references to the 'Purch Order Work' table.

Now I just have to remember how to string values from multiple rows together into one clause to really nail down this documentation.

Regards,

ALTER FUNCTION [dbo].[udfConcatenateAcctNumbersForOneCategory]
(
@cat varchar(100)
, @postingType smallint
, @typicalBalance smallint
)
RETURNS varchar(MAX)
AS
BEGIN

DECLARE @Accts varchar(MAX)
SET @Accts = '';

SELECT @Accts = (
SELECT DISTINCT TOP (100) PERCENT RTRIM(g0.ACTNUMBR_1) AS 'data()'
FROM dbo.GL00100 AS g0
INNER JOIN dbo.GL00102 AS g2
ON g0.ACCATNUM = g2.ACCATNUM
WHERE g2.ACCATDSC = @cat
AND g0.PSTNGTYP = @postingType
AND g0.TPCLBLNC = @typicalBalance
AND g0.ACTIVE = 1
FOR XML PATH('')
);

SELECT @Accts = RTRIM(@Accts);

RETURN @Accts;

END

So I just have to adapt that method for the Workflow and that should do it.

Regards,

Workflow_Step_Status

And lastly: Workflow_Action

We create Smartlists for the users and managers. The main workflow is Timesheet Approvals.
Three Smartlists are used:
1. Timesheets created, Not Submitted, Recalled, or Rejected (this is really used as a missing timesheets report)
2. Timesheets submitted and pending Approval- shows who and how long they have to approve (this used to ping approvers before the WF escalates)
3. Timesheets approved WF History - Who Approved it and When (used to see who approved something)

Because WF transactions only use about 4 tables and they get very full very fast and impact approval queries for users when doing 1,000 timesheets per week, we dump the WF histories of Completed Approvals after 100 days. SQL job for that. 

Here's what I've got so far:

SELECT s.Workflow_Name
    , s.Workflow_Step_Name
    , s.Workflow_Step_Sequence
    , s.WF_Step_Description
    , s.Workflow_Step_Order
    , s.WF_Step_Predecessor
    , u.ADDisplayName
    , c.ConditionsGuid
    , c.SEQNUMBR
    , c.FromTable
    , c.FromField
    , c.QueryOperator
    , c.QueryConditon
    , c.StringFilter_1
    , c.NumericFilter_1
    , CASE
        WHEN RTRIM(c.StringFilter_1) > '' THEN
            CASE c.QueryOperator
                WHEN 1 THEN 'Where ' + RTRIM(c.FromField) + ' = "' + RTRIM(c.StringFilter_1) + '"'
                ELSE ' Or ' + RTRIM(c.FromField) + ' = "' + RTRIM(c.StringFilter_1) + '"'
            END
         ELSE
            CASE c.QueryConditon
                WHEN 7 THEN ' And ' + RTRIM(c.FromField) + ' >= ' + FORMAT(c.NumericFilter_1, 'N', 'en-us')
                ELSE ' And ' + RTRIM(c.FromField) + ' <= ' + FORMAT(c.NumericFilter_1, 'N', 'en-us')
            END
    END AS Clause
FROM dbo.WF100003 AS s
INNER JOIN dbo.WF40200 AS u
ON s.Workflow_Step_Assign_To = u.UsersListGuid
INNER JOIN dbo.CO00123 AS c
ON s.Workflow_Step_Conditions = c.ConditionsGuid
WHERE s.Workflow_Name = 'GLC PO Workflow'
ORDER BY Workflow_Step_Order
, WF_Step_Predecessor
, Workflow_Step_Sequence
, SEQNUMBR;

This code is certainly not generic. All of the conditions in our PO Workflow refer only to two fields in the 'Purchase Order Work' table and use only one StringFilter column and one NumericFilter column. The output has these final five columns:

... and this matches what you'd see in the Workflow designer, minus the references to the 'Purch Order Work' table.

Now I just have to remember how to string values from multiple rows together into one clause to really nail down this documentation.

Regards,

Here's what I've got so far:

SELECT s.Workflow_Name
    , s.Workflow_Step_Name
    , s.Workflow_Step_Sequence
    , s.WF_Step_Description
    , s.Workflow_Step_Order
    , s.WF_Step_Predecessor
    , u.ADDisplayName
    , c.ConditionsGuid
    , c.SEQNUMBR
    , c.FromTable
    , c.FromField
    , c.QueryOperator
    , c.QueryConditon
    , c.StringFilter_1
    , c.NumericFilter_1
    , CASE
        WHEN RTRIM(c.StringFilter_1) > '' THEN
            CASE c.QueryOperator
                WHEN 1 THEN 'Where ' + RTRIM(c.FromField) + ' = "' + RTRIM(c.StringFilter_1) + '"'
                ELSE ' Or ' + RTRIM(c.FromField) + ' = "' + RTRIM(c.StringFilter_1) + '"'
            END
         ELSE
            CASE c.QueryConditon
                WHEN 7 THEN ' And ' + RTRIM(c.FromField) + ' >= ' + FORMAT(c.NumericFilter_1, 'N', 'en-us')
                ELSE ' And ' + RTRIM(c.FromField) + ' <= ' + FORMAT(c.NumericFilter_1, 'N', 'en-us')
            END
    END AS Clause
FROM dbo.WF100003 AS s
INNER JOIN dbo.WF40200 AS u
ON s.Workflow_Step_Assign_To = u.UsersListGuid
INNER JOIN dbo.CO00123 AS c
ON s.Workflow_Step_Conditions = c.ConditionsGuid
WHERE s.Workflow_Name = 'GLC PO Workflow'
ORDER BY Workflow_Step_Order
, WF_Step_Predecessor
, Workflow_Step_Sequence
, SEQNUMBR;

This code is certainly not generic. All of the conditions in our PO Workflow refer only to two fields in the 'Purchase Order Work' table and use only one StringFilter column and one NumericFilter column. The output has these final five columns:

... and this matches what you'd see in the Workflow designer, minus the references to the 'Purch Order Work' table.

Now I just have to remember how to string values from multiple rows together into one clause to really nail down this documentation.

Regards,

Here's what I've got so far:

SELECT s.Workflow_Name
    , s.Workflow_Step_Name
    , s.Workflow_Step_Sequence
    , s.WF_Step_Description
    , s.Workflow_Step_Order
    , s.WF_Step_Predecessor
    , u.ADDisplayName
    , c.ConditionsGuid
    , c.SEQNUMBR
    , c.FromTable
    , c.FromField
    , c.QueryOperator
    , c.QueryConditon
    , c.StringFilter_1
    , c.NumericFilter_1
    , CASE
        WHEN RTRIM(c.StringFilter_1) > '' THEN
            CASE c.QueryOperator
                WHEN 1 THEN 'Where ' + RTRIM(c.FromField) + ' = "' + RTRIM(c.StringFilter_1) + '"'
                ELSE ' Or ' + RTRIM(c.FromField) + ' = "' + RTRIM(c.StringFilter_1) + '"'
            END
         ELSE
            CASE c.QueryConditon
                WHEN 7 THEN ' And ' + RTRIM(c.FromField) + ' >= ' + FORMAT(c.NumericFilter_1, 'N', 'en-us')
                ELSE ' And ' + RTRIM(c.FromField) + ' <= ' + FORMAT(c.NumericFilter_1, 'N', 'en-us')
            END
    END AS Clause
FROM dbo.WF100003 AS s
INNER JOIN dbo.WF40200 AS u
ON s.Workflow_Step_Assign_To = u.UsersListGuid
INNER JOIN dbo.CO00123 AS c
ON s.Workflow_Step_Conditions = c.ConditionsGuid
WHERE s.Workflow_Name = 'GLC PO Workflow'
ORDER BY Workflow_Step_Order
, WF_Step_Predecessor
, Workflow_Step_Sequence
, SEQNUMBR;

This code is certainly not generic. All of the conditions in our PO Workflow refer only to two fields in the 'Purchase Order Work' table and use only one StringFilter column and one NumericFilter column. The output has these final five columns:

... and this matches what you'd see in the Workflow designer, minus the references to the 'Purch Order Work' table.

Now I just have to remember how to string values from multiple rows together into one clause to really nail down this documentation.

Regards,