Forum Discussion

Joseph_An's avatar
Joseph_An
Creator II
7 years ago

How to extract workflow & activity master info from database?

Hi expert, I try to extract workflow information using query. But, I'd like to extract only workflow information purely. For example, I create 'Express ECO' workflow and set activities as below: Workflow name activity         activity            activity                   activity                   activity             activity    activity 'Express ECO'       'Start'    'Submit ECO'    'Planning'     'Change Approval'    'Make Changes'    'Review'    'Close' I need to extract the workflow master info above. Would you help me know how to get workflow master and activities set on that workflow master? Using the query below, I can extract actual data but not master or template info. SELECT * FROM [innovator].[EXPRESS_ECO] A, [innovator].[WORKFLOW] B, [innovator].[WORKFLOW_MAP] D, [innovator].[WORKFLOW_PROCESS] C, [innovator].[WORKFLOW_PROCESS_ACTIVITY] E, [innovator].[ACTIVITY] F, [innovator].[ACTIVITY_ASSIGNMENT] G WHERE A.ID = B.SOURCE_ID AND B.RELATED_ID = C.ID AND C.COPIED_FROM_STRING = D.ID AND C.ID = E.SOURCE_ID AND E.RELATED_ID = F.ID AND F.ID = G.SOURCE_ID . Thank you.

1 Reply

  • kentonvk's avatar
    kentonvk
    Ideator III
    7 years ago
    Joseph, The Workflow Map has a different set of tables for the activities and templates. I am not sure whether you wish to map each activity from the workflow to the workflow map, or if you just want to return the template data used for that particular workflow map, so I am not sure how to correctly update your query as shown. If you simply want the workflow map data, you only need to change the following table names:
    • [WORKFLOW_PROCESS_ACTIVITY] -> [WORKFLOW_MAP_ACTIVITY]
    • [ACTIVITY] -> [ACTIVITY_TEMPLATE]
    • [ACTIVITY_ASSIGNMENT] -> [ACTIVITY_TEMPLATE_ASSIGNMENT]
    And you will also need to change the join clause from  C.ID = E.SOURCE_ID to  D.ID = E.SOURCE_ID. If you are hoping to connect activity data between the two, unfortunately, Innovator does not give a direct method of connecting the ACTIVITY_TEMPLATE with the resulting ACTIVITY. This means that you will need to join only on the name of both of those items, and if there are multiple activity templates with the same name, or you rename the activity in the map, you will not be able to connect those with 100% accuracy. I hope this helps.