This discussion is archived
3 Replies Latest reply: Oct 17, 2012 7:07 AM by RMG123 RSS

Extract for Activities and Owners at current step

RMG123 Newbie
Currently Being Moderated
Can you help point me in the right direction of how to extract activities, their current workflow step, and the owner(s) for that current step? I can easily get activities out of the DB, but not current workflow step and owners at that step. Even if you can point me to the right tables I can probably get it from there but sql is always appreciated :)

5.2 DB schema
  • 1. Re: Extract for Activities and Owners at current step
    Ron M Pro
    Currently Being Moderated
    By Activities, I am assuming you talking about NPD Activities. If so, the following is how you can get the Activity status:


    SELECT ml.Title, st.Caption, * FROM
    npdActivities act
    inner join npdActivityML ml on act.pkid = ml.fkActivity and ml.langID = 0
    inner join npdWorkflowStatuses st on act.fkStatus = st.pkid and st.langID = 0

    We'll get you answers for the Owners shortly.
  • 2. Re: Extract for Activities and Owners at current step
    941847 Newbie
    Currently Being Moderated
    To extract the owners at the current step, please try this script:

    SELECT ml.Title, st.Caption, users.FirstName + ' ' + users.LastName, * FROM
    npdActivities act
    inner join npdActivityML ml on act.pkid = ml.fkActivity and ml.langID = 0
    inner join npdWorkflowStatuses st on act.fkStatus = st.pkid and st.langID = 0
    inner join npdActivityTemplateSteps ats on act.fkActivityTemplate = ats.fkParent
    inner join npdMatrixItems mi on ats.pkid = mi.fkOwnerParent
    inner join npdMatrixItemUserJoin jo on mi.pkid = jo.fkMatrixItem
    inner join users on jo.fkUser = users.pkid
    order by act.pkid;

    Thanks
  • 3. Re: Extract for Activities and Owners at current step
    RMG123 Newbie
    Currently Being Moderated
    Thanks - this gets me closer. The output seems to pull all steps and owners, however, I am looking to output Activity title, npdActivity.fkstatus = npdWorkflowStatuses.caption, and related npdMatrixItems step owners. Basically the output would show for each activity the current workflow status and owners at that step. Thx!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points