Question

Photo of Jeremy Turgeon

0

Accessing Workflow Data Via Dynamic Data Block

1

My understanding is that only the Dynamic Data block can display information recorded in a workflow entry form.  I've attempted, with my limited skill in SQL, to pull this information from the database without success. 

 I've also tried to use a workflow to write the desired values to a custom Person Attribute which works and is accessible from Reports but I don't know how to access it from SQL.  How can I reference this information both inside a custom attribute and inside a workflow?

Thanks in advance!

Blocks
  • Photo of Rock RMS

    0

    Here's some SQL to list details for workflows. Note you will need to update it to show the workflow attributes you want. To do so change the "a.[Key] = 'PositionTitle') AS [Position Title]" filter in the SELECT to be the key you want to show and the heading title you want on the grid. Copy and Paste the subselect lines below for each attribute you want to list (the same shows 2 from the sample workflow for positions). You'll also want to update the [WorkflowTypeId] to the workflow type you're interested in.

    Hopefully this gets you closer.

    SELECT
        w.[Id]
        , w.[Name]
        , [Status]
        , [InitiatorPersonAliasId]
        , p.[Lastname] + ', ' + p.[NickName] AS [Initiator]
        , (SELECT TOP 1 [Value]
            FROM [AttributeValue] av
                INNER JOIN [Attribute] a ON a.[Id] = av.[AttributeId] AND a.[EntityTypeId] = 113 AND a.[EntityTypeQualifierColumn] = 'WorkflowTypeId' AND a.[EntityTypeQualifierValue] = w.[WorkflowTypeId]
            WHERE [EntityId] = w.[Id] AND a.[Key] = 'PositionTitle') AS [Position Title]
        , (SELECT TOP 1 [Value]
            FROM [AttributeValue] av
                INNER JOIN [Attribute] a ON a.[Id] = av.[AttributeId] AND a.[EntityTypeId] = 113 AND a.[EntityTypeQualifierColumn] = 'WorkflowTypeId' AND a.[EntityTypeQualifierValue] = w.[WorkflowTypeId]
            WHERE [EntityId] = w.[Id] AND a.[Key] = 'PositionDescription') AS [Position Description]
    FROM
        [Workflow] w
        INNER JOIN [PersonAlias] pa ON pa.AliasPersonId = w.[InitiatorPersonAliasId]
        INNER JOIN [Person] p ON p.[Id] = pa.[PersonId]
    WHERE w.[WorkflowTypeId] = 17

    • Jeremy Turgeon

      Jeremy Turgeon

      This is working well for people whose accounts I've manually created internally. But when someone creates a web account and completes the workflow that this query reports on their information doesn't show up.


      Also, new accounts created through the Register external button of the Login page are created without any connection status or RecordStatusValueId.

      • Rock RMS

        Rock RMS

        Yes, workflows entered from people who are not logged in will not have an initator property. We'll look into the lack of connection status and record states on the register page.

        • Jeremy Turgeon

          Jeremy Turgeon

          I figured out what the problem was. The original script seems to have mixed up pa.AliasPersonId with the correct value which is pa.Id. The line should read INNER JOIN [PersonAlias] pa ON pa.Id = w.[InitiatorPersonAliasId]