Timesheets and assignments–updating status isn’t just for the current week

Following on from my previous post on Project Server timesheet line status and project plan and assignment deletions, this blog covers another scenario that can catch you out – the relationship between the assignments and the timesheet with regards to status updates, and the idea that this was worth blogging about came from reading the comments in that previous post – and the thread in the forum referred to in the comment where Treb and Elli had discussed the ramification of this issue.  I had explained it in the thread – but will post here to hopefully reach a wider audience.

Imagine you have 4 timesheets, and one assignment that happens to span all of them.  The first week – and lets call this one w/c 2/17/2014 – has been submitted and approved.  The timesheet line status is 1, and for all other timesheets the line status is 0.  We have a handy table in the reporting (dbo) schema called MSP_TimesheetLineStatus and this shows us that one line is Approved, and all the remainder are Pending.  So far so good.

Line Status Table

Now I have entered time in the next 3 timesheets – and some of these happen to be in the future – so not always a good thing to get ahead of yourself – but this could equally happen if they were in the past.  The key issue is having multiple timesheets in progress at the same time – but I appreciate it isn’t always easy to keep this from happening.  To visualize this better here is a representation of where I stand – with Timesheet 1 approved and time entered in the other 3 as follows:

Showing 4 timesheets and one assignment

Next I go to Timesheet 2 and submit status.  Once I do this then I see that each of my three timesheets says Awaiting approval against my assignment.

As an example, here is a screenshot of Timesheet 3 – for w/c 3/10

Timesheet 3

But I didn’t click send anything on this timesheet? Taking a look directly in the database we can see what is going on.  The line with the comment and the status of 1 is our approved timesheet 1 – and the status of 4 (pending approval) is for timesheet 1.  But why do the other 2 say 0 (Pending) and not 4?

Database with 1 awaiting approval

This is a little like the issue of the deleted tasks in the previous posting – if I go to Timesheet 3 for example, and add a vacation and just save – I then see this…

Database with 2 awaiting approval

I have an extra row of course, but my previous line with 0 is now showing a 4.  So once I opened my timesheet I can see awaiting approval, but only when I save (and I need to make a change such as adding a line or changing time) does this get persisted back to the database. 

This scenario is often commented on in the approval center where it is more obvious that time from multiple timesheet periods is being presented.  And if I scrolled right I would see more red 7’s.

Approval center shows multiple timesheet periods

And if I now approve?  Hmm – I now see I have 3 approved lines from timesheets 1 to 3 – and my vacation on timesheet 3, but still don’t see approval of the 4th.

Database with 3 approved

Even though this ‘said’ awaiting approval on the timesheet, as I hadn’t saved it, it was really still in the Pending status (as seen in the DB) – and if I look in the timesheet now it says Not Submitted, as no part of this assignment is actually in the awaiting approval status.

Timesheet 3 not submitted

So the question that started all this was how do I check to see what has been submitted even if it isn’t approved – and without using timesheets per se – and overcoming the scenarios above.  One thought I had was to use planned work – assuming that was somewhat accurate – then do a left join to see if there is matching submissions.  In the forum thread Elli had another approach – and I can’t think of a better way – that uses the fact that a comment is present as indicative of submitted time.  I’d be interested on any other workarounds or issues people are experiencing with this behavior.  This was 2013 – but same for 2010 and Project Online.