none
Custom field changes in Project 2010 RRS feed

  • Question

  • Is there a last-modified-date indicator in one of the databases to indicate when a custom field was changed? For example, I want to know when a custom field called project-phase (not workflow) has changed from one value to the next within a PDP.  The purpose is to produce a report that indicates the progression of a project from one phase to the next (plan-analyze-design, etc.).
    Monday, October 20, 2014 12:39 PM

Answers

  • Hi Tom,

    The Reporting DB has the [ProjectModifiedDate] in the [dbo.MSP_EpmProject_UserView]. But be aware that it is at project level, thus it will not tell you wheither or not the custom field has been updated.

    You could use an event handler to catch the custom field update and store the modified date into another custom field.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, MCP |

    Monday, October 20, 2014 12:45 PM
    Moderator
  • The report you want to build is doable; however, you're going to need a lot more than a sql query to get this information.  There are some issues (at least in my opinion) you will run into if you just rely on the predefined tables within MSP/SQL.  

    You'll need to create another table that stores the information you want to track.  If you store the information in a separate table, this will give you the report every month/quarter/X-time period when you want with the exact information you want.  

    Overall, Guillaume made a good suggestion for what you're looking for 



    • Marked as answer by Tom_Herrington Wednesday, October 22, 2014 1:18 PM
    Wednesday, October 22, 2014 12:35 PM

All replies

  • Hi Tom,

    The Reporting DB has the [ProjectModifiedDate] in the [dbo.MSP_EpmProject_UserView]. But be aware that it is at project level, thus it will not tell you wheither or not the custom field has been updated.

    You could use an event handler to catch the custom field update and store the modified date into another custom field.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, MCP |

    Monday, October 20, 2014 12:45 PM
    Moderator
  • Hello Guillaume,

    I will test to see if the ProjectModifiedDate will reflect the timestamp I need.  At any rate, I don't want to get into coding an event handler at this time.

    Thanks for the quick reply and helpful information. 

    • Marked as answer by Tom_Herrington Monday, October 20, 2014 8:28 PM
    • Unmarked as answer by Tom_Herrington Tuesday, October 21, 2014 11:25 AM
    Monday, October 20, 2014 1:03 PM
  • You're more than welcome.

    Note also that the draft DB contains the [PROJ_LAST_SAVED] field which might be different from the published date. Be aware that querying the draft DB is not supported.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, MCP |

    Monday, October 20, 2014 1:33 PM
    Moderator
  • Per MS:The following should be done on a development database and any further queries should be completed through the PSI if done against production

    If you look under the draft database 

    MSP_TASKS_CUSTOM_FIELD_VALUES has a modified date field 

    if you know the project GUID, you can do a select top 1000 rows from SQL Server, under the From statement at the bottom type

    where PROJ_UID='GUID OF PROJECT'

    At the very end of all the items is a MOD_DATE column 

    If you need the guid - do a selct top 1000 from the MSP_PROJECTS table, under from type ORDER BY PROJ_NAME, the PROJ_UID next to the appropriate project is what you need to put in  the above where statement...

    again all should be done on a DEV server - 

    HTH

    Monday, October 20, 2014 2:58 PM
  • KBWrecker,

    Specifically, do you mean selecting the following from the Tables of the Draft DB?  Thank you.

    Monday, October 20, 2014 3:22 PM
  • KBWrecker,

    Forget my last post.  I located the table you described.  I will try this.

    Thank you.

    Monday, October 20, 2014 3:26 PM
  • KBwrecker,

    This returns to much information and is still not reflect a change on a specific custom field among the hundred thousands returned.  I am going to go with the project modified date suggested by Guillaume above.  It is not the exact solution, but will suffice for this report (with some explanation).

    Thank you for the information.

    Monday, October 20, 2014 8:27 PM
  • Hi Tom, you're welcome.

    I think you meant to mark my reply as an answer.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, MCP |

    Monday, October 20, 2014 10:11 PM
    Moderator
  • this question is closed - but for what you asked for the basic query provided  gave you the custom field modified date which would be more precise then the project modified date - 

    The below query will get you the information you want

    YOU put in the project GUID  OR the GUID of the Custom Field and this will return the tasks and project name it will also show you the difference (at least it did in my DB) the project modified data versus the task/custom field modified date which is also what you asked for...


    USE ProjectSevrerDraft
    
    SELECT 
    mscf.MD_PROP_NAME as "Custom Field",
    mstcv.MD_PROP_ID as "MD PROP ID - can remove",
    proj.PROJ_NAME as "Project Name",
    proj.MOD_DATE as "Project Modified Date",
    mst.TASK_NAME as "Task Name",
    mstcv.CREATED_DATE as "Task Created Date",
    mstcv.MOD_DATE as "Task Modified Date"
    
    FROM MSP_TASK_CUSTOM_FIELD)VALUES mstcv
    
    JOIN MSP_PROJECTS proj 
    ON mstcv.PROJ_UID=proj.PROJ_UID
    
    JOIN MSP_TASKS mst
    ON mstcb.TASK_UID=mst.TASK_UID 
    
    JOIN [ProjectServerPublished].[dbo].[MSP_CUSTOM_FIELDS] mscf
    ON mscf.MD_PROP_ID=mstcv.MD_PROP_ID
    
    -- You can select either below un-comment out the '--' 
    -- And insert what you want - the project or Custom field 
    -- GUID will help.  The PROJ_UID is found under MSP_PROJECTS
    -- THE MD_PROP_ID is found under the published database in
    -- the MSP_CUSTOM_FIELDS table
    --WHERE mstcv.PROJ_UID =' GUID OF PROJECT'
    --WHERE mstcv.MD_PROP_ID = 'GUID OF CUSTOM FIELD'
    
    

    • Proposed as answer by kbwrecker Tuesday, October 21, 2014 3:34 PM
    • Unproposed as answer by kbwrecker Tuesday, October 21, 2014 5:53 PM
    Tuesday, October 21, 2014 3:22 PM
  • KB,

    Thank you for the SQL.  However, what I am looking for has nothing to do with tasks.  What I want to know is the create and modified date of a custom lookup table field called "project phase".  The values are 7 text values (plan, analyze, design, etc.).  This custom field is maintained on one of our PDP's in PPM2010.   My report is designed to show the progression of a project through these "project phases" with the dates.  Is there a way to get this information from the DB?

    Thanks again

    Tuesday, October 21, 2014 5:10 PM
  • Hi Tom,

    Here is how I'd handle it.

    Propose a WBS to your project managers with milestones corresponding to the 7 phases completion dates (such as "analysis completed"). For each of those milestones, set the task custom field to the right value. Create a simple report filtering on the tasks which have a non-blank value for the custom field and insert the actual finish date.

    The report could be in SSRS or Excel Services and would have to be more precisely defined based on your need but this could be a valid solution.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, MCP |

    Tuesday, October 21, 2014 5:15 PM
    Moderator
  • Guillaume,

    Great suggestion, but the changeover for so many PM's would be too great a task.  I am going to stick with the last modified date for the workflow phase/stage and show the "project phase" custom field value.  I may include the project's last mod date as well.

    Thanks for the information

    Tuesday, October 21, 2014 6:26 PM
  • Tom:

    Thanks for the clarification, sometimes things are not clear on the interwebz

    Can you tell me what the end result would be you are looking for?

    X column would have this value, Y column this value - 

    I think I have a value which is in the Published Database in the Project Custom Field Values table, which MIGHT be what you are looking for. 

    Do you JUST want the time/date/modified/created?
    Are you looking to see also the stage? 

    kbwrecker

    Tuesday, October 21, 2014 6:29 PM
  • KB,

    I am building a portfolio progression report.  I want to show projects that have progressed from one workflow phase and stage by showing the before/after phase/stage and the date of the modification.  Secondly, I want to show the same progression for the lookup custom field "project phase".  The report will have selective filtering parameters to narrow the scope, including a date range (ideally for both workflow and custom field) selector.

    Project      Workflow Phase/Stage From/To    Modification Date   Project Phase From/To   Modification Date

    xxxxx         Define/Proposal to Plan/Execute       mm/dd/yyyy     Plan      to     Analyze       mm/dd/yyyy

    Wednesday, October 22, 2014 11:35 AM
  • The report you want to build is doable; however, you're going to need a lot more than a sql query to get this information.  There are some issues (at least in my opinion) you will run into if you just rely on the predefined tables within MSP/SQL.  

    You'll need to create another table that stores the information you want to track.  If you store the information in a separate table, this will give you the report every month/quarter/X-time period when you want with the exact information you want.  

    Overall, Guillaume made a good suggestion for what you're looking for 



    • Marked as answer by Tom_Herrington Wednesday, October 22, 2014 1:18 PM
    Wednesday, October 22, 2014 12:35 PM
  • KB and Guillaume,

    Thank you.  I don't want to create custom tables that would be unusable when we move to PPM2013 or Online.  I have discussed with the requestor and what I am providing with given table information will be sufficient.  If there is a question about any project from the report, additional reports or views would suffice.  Your quick responses and helpful tips have helped me immensely.

    Tom

    Wednesday, October 22, 2014 1:18 PM
  • You're more than welcome Tom!

    Hope this helps,


    Guillaume Rouyre, MBA, MVP, MCP |

    Wednesday, October 22, 2014 1:27 PM
    Moderator