Open Forum

Expand all | Collapse all

OK, I give up: How do you document your WorkFlow?

  • 1.  OK, I give up: How do you document your WorkFlow?

    GPUG ALL STAR
    Posted Apr 23, 2020 08:08 AM
    Dear Collaborators,

    I cannot seem to locate a WorkFlow documenter report. That is, I want to see a report that shows all the Steps and sub-Steps along with the conditions for each one. Am I missing an SSRS report that has this?

    Something like this:

    Production
    where Purchase Order Work.Buyer ID is 'Bob'
    and Purchase Order Work.Subtotal is greater than and includes 5000.00
    and Purchase Order Work.Subtotal is less than and includes 9999.99
    or Purchase Order Work.Buyer ID is 'Frank'
    and Purchase Order Work.Subtotal is greater than and includes 5000.00
    and Purchase Order Work.Subtotal is less than and includes 9999.99

    Prod over 10000
    where Purchase Order Work.Buyer ID is 'Bob'
    and Purchase Order Work.Subtotal is greater than and includes 10000.00
    and Purchase Order Work.Subtotal is less than and includes 24999.99
    or Purchase Order Work.Buyer ID is 'Frank'
    and Purchase Order Work.Subtotal is greater than and includes 10000.00
    and Purchase Order Work.Subtotal is less than and includes 24999.99

    etc.

    Am I missing something obvious? Is there such a report? I've been putting one together with SQL, but hey! How about a little relief here?

    Sincerely,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 12-Nov-2019
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: OK, I give up: How do you document your WorkFlow?

    GPUG ALL STAR
    Posted Apr 24, 2020 07:52 AM
    Look in this table:  CO00123

    The Key in 'ConditionGuid' will link back to the WF100003 table on the 'Wordkflow_Step_Conditions" field.

    The CO00123 holds the actual steps, though most of them are integer-represented and I can't find any documentation on the integers.

    ------------------------------

    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: OK, I give up: How do you document your WorkFlow?

    GPUG ALL STAR
    Posted Apr 24, 2020 08:38 AM
    Why, hello, @Jo deRuiter! Thank you for chiming in!

    I've been playing with the WF tables and I've almost got it.​ I've figured out the "QueryCondition" enumeration -- at least, for the query terms we use in our Workflow.

    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,


    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 12-Nov-2019
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: OK, I give up: How do you document your WorkFlow?

    GPUG ALL STAR
    Posted Apr 24, 2020 09:14 AM
    @Steve Erbach

    You should really publish this as soon as you have it 'nailed down'  It looks pretty good!!!​

    ------------------------------

    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: OK, I give up: How do you document your WorkFlow?

    TOP CONTRIBUTOR
    Posted Apr 24, 2020 02:27 PM
    If you ever need to know what the status code mean...here they are:
    Workflow_status
    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. 


    ------------------------------
    Thaddeus Suter
    Retus, Inc
    HELOTES TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: OK, I give up: How do you document your WorkFlow?

    GPUG ALL STAR
    Posted Apr 24, 2020 04:06 PM
    Thaddeus,

    Interesting to see the difference in scope between your company and ours. We only have 45 employees!

    Working on this Workflow documenter I can see why Microsoft elected not to produce one of their own. The Workflow tables are pretty generic; so specific documentation of a Workflow would be problematic.

    Regards,​

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 12-Nov-2019
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: OK, I give up: How do you document your WorkFlow?

    GPUG ALL STAR
    Posted Apr 27, 2020 12:43 PM
    @Steve Erbach
    This is good stuff that you brewed in your (hell) kitchen !! certainly worth a blog entry once tackled as suggested by @Jo deRuiter​​​

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: OK, I give up: How do you document your WorkFlow?

    GPUG ALL STAR
    Posted Apr 27, 2020 01:52 PM
    @Beat Bucher and @Jo deRuiter,

    All right, all right, already! I'll blog about it! I just have to string the clauses together for each step in the workflow.

    I know I can do it because I made a refex (that's REE-fex, Jo!) using that technique a few years ago based on the chart of accounts at my former employer. It has tabs for:

    ​Accounts -- just a raw list of all account numbers
    Accts by Dept -- a list of the Department numbers (our segment 2) with the base account numbers (segment 1) clumped together in one cell, strung together and separated by spaces
    Accts by Category -- a list of the account Categories with the account numbers separated by spaces in one cell per category
    Departments by Acct -- and a list of the base account numbers with the departments for each strung together and separated by spaces in one cell per account.

    I created a function to return space-delimited strings for each of the "by" tabs. The functions use the FOR XML PATH dodge to string multiple values together. E.g.,

    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,



    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 12-Nov-2019
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: OK, I give up: How do you document your WorkFlow?

    GPUG ALL STAR
    Posted Apr 30, 2020 09:08 PM
    @Beat Bucher and @Jo deRuiter and @Thaddeus Suter,

    I figured it out, finally. Sheesh! Not only is it a cascading SQL self-join problem, it's a string concatenation and string replacement problem.

    I'm too pooped to blog about it now (I've been working since 6:00 am), but when I do (Real Soon Now!) I'll want to see comments!

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 12-Nov-2019
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 10.  RE: OK, I give up: How do you document your WorkFlow?

    Posted 4 days ago
    Wow!
    Thanks a lot for this, I was missing table CO00123 and couldn't figure the conditions!
    You just saved me hours of work!

    ------------------------------
    Nancy Pageau
    Stingray Group Inc.
    Montreal QC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 11.  RE: OK, I give up: How do you document your WorkFlow?

    GPUG ALL STAR
    Posted 4 days ago
    @Nancy Pageau,

    You're most welcome! I'm glad to see that others want to document those Workflows, too.

    Regards,​

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of June 10, 2020
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role