Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
MS Project and legacy timesheet system
- Thread starter whoffman
- Start date
The distinctly non-trivial issue: how do you plan to cross-reference the tasks and resources so that the hours in posted in your existing system will be correctly cross-posted to the task and resource in Project.
Bear in mind that a given task may have multiple resources assigned to it and that the ID field for a task and the ID field for a resource are transient and will change when tasks (or resources) are added/moved/deleted.
I _may_ have a solution for you.
Bear in mind that a given task may have multiple resources assigned to it and that the ID field for a task and the ID field for a resource are transient and will change when tasks (or resources) are added/moved/deleted.
I _may_ have a solution for you.
- Thread starter
- #3
I assumed that if the names are the same in both systems for the resource (employee) and the named project and task are the same, that would be the only fields needed (and obviously the date). If you are saying that MSProject can only use its unique identifier for these fields and these identifiers change often, how on earth would you ever be able to accomplish what I am investigating? It looks as if we need to investigate a different project management tool if we want to eliminate the redundancy of dual input.
I think that this would be possible. First of all we use an excel file for estimating purposes. This gives us the estimated hours for each task on a summary page. These values are then imported into project, for this they are dropped into our project template. Within the template have the resources (employees) already existing. If you are using a standard calendar or even a custom calendar for specific tasks, define that in your template also. I am not certain if this is exactly what you are looking for but hopefully it is a start.
- Thread starter
- #5
Thanks! I haven't personally worked with the software as far as setting up a project, but I have had to update my actual hours against tasks. I just had to be sure what I wanted to do was possible before we "tweaked" our legacy system to add the necessary projects and tasks. I appreciate your taking the time to respond - it lets me know I'm heading in the right direction!
whoffman
There are two issues here. First: matching keys. A task has three potential identifiers:
1. ID (this is the sequential number from 1 to whatever). If you drag a task to a different position in the project, you'll note that the ID changed. If you insert a new task between two existing tasks, you'll see that the ID has changed. This, therefore, is not a good key.
2. Name. People regularly correct spelling errors or change the description of a task. This, too, is not a good key.
3. UniqueID. Open a project and display the column "Unique ID". Now, drag a task to a different location in the project. The Unique ID does not change. Insert a task and you'll see that existing Unique IDs do not change. Cut&Paste a task and you'll see that the task gets a new Unique ID. This is the best key you'll find but it, as you can see, has limitations.
Ditto, naturally for Resources and Resource IDs, Resource Names, and Resource Unique IDs.
The other issue, which no one has addressed:
Create a task with 5d duration and 5d work (make it run Monday to Friday).
Now ... post 3d of work. What do you want to have happen? Do you want the duration to extend? do you want to have the duration fixed? if the duration is fixed what happens to the missing two days of work? Now ... what happens if the task is constrained with a "must finish on" constraint?
When you're importing hours of work you need to consider how you want Project to respond to cases where the actual work doesn't match the forecast work.
Finally, click on File | Save As ... and set the type to XLS and then play around with export maps. Once you have a map exporting the data the way you want (you'll almost certainly have to create a custom map) then you can use that map to import the data.
The mechanics of doing this are really quite simple; the issue is: what happens when Actual Work doesn't match planned Work.
I expect you'll find that a simple import/export works for all the trivial cases; anything beyond that requires coding with VBA and a good understanding of the underlying algorithms that Project uses when posting Actuals.
There are two issues here. First: matching keys. A task has three potential identifiers:
1. ID (this is the sequential number from 1 to whatever). If you drag a task to a different position in the project, you'll note that the ID changed. If you insert a new task between two existing tasks, you'll see that the ID has changed. This, therefore, is not a good key.
2. Name. People regularly correct spelling errors or change the description of a task. This, too, is not a good key.
3. UniqueID. Open a project and display the column "Unique ID". Now, drag a task to a different location in the project. The Unique ID does not change. Insert a task and you'll see that existing Unique IDs do not change. Cut&Paste a task and you'll see that the task gets a new Unique ID. This is the best key you'll find but it, as you can see, has limitations.
Ditto, naturally for Resources and Resource IDs, Resource Names, and Resource Unique IDs.
The other issue, which no one has addressed:
Create a task with 5d duration and 5d work (make it run Monday to Friday).
Now ... post 3d of work. What do you want to have happen? Do you want the duration to extend? do you want to have the duration fixed? if the duration is fixed what happens to the missing two days of work? Now ... what happens if the task is constrained with a "must finish on" constraint?
When you're importing hours of work you need to consider how you want Project to respond to cases where the actual work doesn't match the forecast work.
Finally, click on File | Save As ... and set the type to XLS and then play around with export maps. Once you have a map exporting the data the way you want (you'll almost certainly have to create a custom map) then you can use that map to import the data.
The mechanics of doing this are really quite simple; the issue is: what happens when Actual Work doesn't match planned Work.
I expect you'll find that a simple import/export works for all the trivial cases; anything beyond that requires coding with VBA and a good understanding of the underlying algorithms that Project uses when posting Actuals.
- Status
Similar threads
- Locked
- Question
- Locked
- Question