none
using unique ID to match tasks with timesheet system RRS feed

  • Question

  • Hello,

    i just wanted to bounce my approach off of the experts here to see if i'm going in the right direction:

    i am planning on automating a process to get actual hours from an internal timesheet system and importing it into MS project professional 2010.

    - I managed to extract into excel the resource usage view with the information i want including the current rate of a resource using a macro.

    - i will be loading the entire plan into the internal system using excel (we have that capability)

    now my goal is to:

    - make a timesheet table in the internal system that records all of the actual hours and include the unique ID field (not sure if resource Unique ID or Task Unique ID)

    - once resources log in to that view they can see the tasks assigned to them

    - they will be able to put their actual hours against a task.

    - project managers will be notified to approve the time.

    - once approved, i am envisioning the process to:

        - Query from the timesheet system using the Unique ID field.

        - locate the unique IDs in the resource usage in MS project and update with actual hours. (This may be automated more in the future)

    my concerns are around the Unique ID:

    1) i noticed the Unique ID in the resource usage view is different than the one in the Task view

    2) if i use the one from the resource usage view, is there a risk this might change? i did some testing and i noticed that every time i add a resource to a task or delete a resource; the unique ID in the resource usage view increments.

    am i really restricted with this? is there any way other than making a custom field to make sure the Unique ID doesn't change.

    also, if my approach is not practical, can you please help me straighten out my approach?

    Thanks alot as usual

    Tuesday, November 12, 2013 7:02 PM

All replies

  • There are actually 3 sets of unique IDs at play.  Each task has a unique ID - you can view that ID in a Task view. (Gantt chart for example).  There is also a resource unique ID that is visible if you look at the Resource Sheet view.  When you create an assignment by pairing a resource to a task, an assignment unique ID is created.  The assignment unique ID is consistent between the assignment row in the Task Usage view (represented by the resource's name) and the assignment row in the Resource Usage view (represented by the task name.)

    To make sure you are recording the correct hours worked by a resource on an assignment, I would think you'll need the assignment unique ID.

    • Proposed as answer by John - Project Sunday, November 17, 2013 8:11 PM
    Friday, November 15, 2013 10:44 PM
    Moderator
  • eagle one3,

    Just to add a technical point of interest. If you are going to use VBA to import the timesheet data into Project you will find that the Assignment Unique ID displayed in the Resource Usage view and the Assignment Unique ID you see in VBA do not match. As I recall they differ by a factor of 10 to the 20th. This may or may not impact what you will be doing.

    John

    Sunday, November 17, 2013 1:44 AM
  • Ooops.  Sorry - I just re-read your comment and see my confusion.....Thanks John.

    Sunday, November 17, 2013 1:54 AM
    Moderator
  • Julie,

    ?? There is no confusion that I can see in your response. Matter of fact, I thought it was an excellent response. My only reason for butting in was to relay something I stumbled upon recently. While writing a macro for someone I had a need to capture the Assignment Unique ID. What I found was that whenever I tried to read the Assignment Unique ID value through code, that value did not agree with what was being displayed in the Resource Usage view. With a little sleuthing I found the difference between the two values was a power of 2, the 20th power to be exact. I then queried Microsoft about that little anomaly and was told that the Assignment Unique ID is given a value that will assure it does not conflict with a task or resource unique ID, hence the very large values. I assume that's why the Unique IDs shown in a dynamic master file are very large numbers. The contact at Microsoft also noted that there is a glitch in Project 2010 with regard to the displayed and stored Assignment Unique ID value. Project 2013 apparently creates the Assignment Unique ID a little differently.

    Since the poster, eagle one3, indicated the use of a macro to transfer the data, I just wanted to alert him/her about the discrepancy, although he/she did not specify which version of Project he/she is using.

    Okay, that's my story and I'm sticking to it.

    John

    • Proposed as answer by Martyn Iv Monday, October 12, 2015 4:37 PM
    Sunday, November 17, 2013 4:20 PM
  • My first post (edited out) was due to me not reading closely enough.  I noted that when looking at Project - the assignment IDs match in both the Task Usage and Resource Usage view.

    Then, of course, I read your post more closely and saw you were referring to the VBA value.

    Sunday, November 17, 2013 5:51 PM
    Moderator
  • The next part of the solution is to train PMs never to use cut and paste in Project as this creates new Unique Ids for tasks.

    To get around this you either need to have unique task names, or store a copy of the unique Id in a number custom field. So when a Unique Id can't be found, search for it in the custom field.

    I tend to use resource names and Task Unique Ids in the timesheet data then find the correct assignment for the task based on the resource name.

    The devil is in the detail and timesheet integrations take a lot of work to bed down.


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Sunday, November 17, 2013 7:39 PM
    Moderator
  • All,

    Thanks alot for the info.

    John, thanks for the tip but i have to be frank, i'm still confused on how do i make sure i'm capturing the right task ID.

    my constraint is that the data we're extracting (via VBA) is only taking from the resource usage view.

    in a different thread; Rod was helpful enough to help me and give me a VBA code that stores the task ID from a TASK view (Gantt Chart) and saves it in a cusotm field in a resource level (Resource usage). my only concern is that i need to make sure i run this macro all the time and it's not a real time connection.

    my ultimate goal is to be able to link my tasks in MS Project with my timesheet system (External system) by using a consistent TASK ID.

    Ofcourse i need to make sure PMs are trained enough to not cut and paste tasks.

    Thanks all for your input

    Thursday, November 21, 2013 1:42 AM
  • eagle one3,

    Read Julie's first response carefully. There are three types of unique IDs. On the Resource Usage view you will see two of them - the Resource Unique ID on each resource line and the Assignment Unique ID on each assignment line. There are no task Unique IDs shown on the Resource Usage view, unless you use the macro Rod provided separately to copy the task Unique IDs to a custom field for each assignment line.

    If you copy task Unique IDs to a custom assignment field then the value will be correct both in the view and if you read it with VBA code. My point was that the Assignment Unique ID (assigned by Project) on the Resource Usage view is incorrect. You will see one value if you look at the Unique ID field for an assignment row and a different value if you read that Assignment Unique ID with VBA.

    If you are using the task Unique ID, as copied to a custom field in the Resource Usage view, as your reference, then the glitch I brought up is of no concern to you. However, as you noted, the task Unique IDs in the custom field will not be generated automatically by Project. You will need to run the macro to copy the field any time a new resource or assignment for a resource is changed. That's why it would ordinarily be better to use the assignment Unique ID directly, because it IS automatically generated by Project for any new resources and assignments. And, if you are aware of the assignment Unique ID glitch, then you can compensate with a correction factor, namely 2 x 10^20.

    Got it?

    Hey, if this were all straightforward and easy, we wouldn't be needed, would we.

    John

    Thursday, November 21, 2013 2:13 AM
  • John,

    thanks for your patience with me :). i will do some extensive testing as i need to make sure all the glitches are covered or i have work arounds for before i roll this out to the team.

    i think i will stay with the custom field method because in our day to day work; resources change more often on the same task and i can't afford to have a different assignment unique id everytime i take somebody off a task and replace him/her with somebody else.

    i'll keep you posted on my progress. Again, thanks.

    Thursday, November 21, 2013 4:37 AM
  • The concern about copying the Task ID to a spare field in the Resource Usage view is that the value will not be unique if you have multiple resources assigned.
    Thursday, November 21, 2013 2:49 PM
    Moderator
  • eagle one3,

    Julie makes an excellent point. There is only one Unique ID per task so if you use a macro to copy that to a custom assignment field in the Resource Usage view, you will have several identical unique IDs for multiple resource assignments. Based on that I'd say, the custom field approach probably isn't going to work.

    The whole point of the Unique ID field is that it is UNIQUE. If your intent is to import timesheet data from each resource, then you will need a unique identifier for that particular assignment. If a resource assignment changes, (i.e. "take somebody off a task and replace him/her with somebody else"), then that is a different assignment and it will have its own unique ID.

    John

    Thursday, November 21, 2013 4:12 PM
  • Hello,

    In case anyone is wrangling with this question today, it appears Microsoft changes how they determine an Assignment's UniqueID from Project 2010 to Project 2013. This appears to only be an issue if you are alternating between Project 2010 and 2013. 

    To put it in a simple formula, 

    Project 2013 Assignment Unique ID = Project 2010 Assignment Unique ID + 2^20

    or

    Project 2013 Assignment Unique ID = Project 2010 Assignment Unique ID +1,048,576

    Hope this helps another noble coder figure out why their Unique IDs aren't matching.

    Monday, October 12, 2015 4:43 PM