Report showing the last updated task

[ScottSa1]

We are trying something new out where we are doing weekly status updates on a custom form attached to a task. Each week, the PM will create a new task for their status update, add the custom form and then fill out the fields of what they did this week, whats next week, etc. That part all works fine. I created a report that shows all the tasks on projects that have this custom field attached to the tasks but what I'd like to be able to do is just show the last updated task with the custom field. I know that I can do a filter on entry date and put in parameters for this week but cause some problems. For instance, if I missed last week and didn't send my status update on Friday and I do it on Monday, then I do another one on Friday for the current week, my report would then show 2 status updates for this week. Ideally i'd like it to just show the task that was last updated with that custom form, not based on the date but rather than was just updated. Any thoughts on how I can do this?

[GregBe1]

Do you "complete" the tasks when you create a new one? If you do, could you display the information from the only "open" task?

[ScottSa1]

Hey Greg, Thanks for the reply. We do it complete when we are finished with the status update. There is calculated columns in the custom form that say when the task is complete, make the field equal something else, like the budget field or schedule status or something like that. We do this to take a "snapshot" of where the project is at that moment when you created your status update. I'm not sure what you mean though by using the only open task. Can you elaborate? Is that a filter option? Thanks

[GregBe1]

Hey Scott, Maybe I'm not understanding, but I think you're saying that the PM's create a new task everytime they are going to create a project status, and that these tasks have a custom form attached to them. I was thinking that the PM would leave the task in a non-complete status (say, In progress). When they create a new project status, they create a new task and "complete" the previous one. If so, then my filter would be to show just those tasks that have an "in progress" status (for example) and also have that custom form attached and then show the values from that custom form of that task.

[ScottSa1]

Oh, i understand. You got it correct on what they are doing, but we have them click the complete so we can get that snapshot data. I know that i could have a custom field in there with a checkbox or something to designate it is the latest status update, but then that would require them going in and changing the last status update to remove that field from that one and was just trying to avoid that extra step. I'll still have to play with it some.

[GregBe1]

I'm curious as to what you mean by them clicking complete so you can get a snapshot. A snapshot of what? How does clicking complete on the task give you a snapshot? It could be reset to in progress at anytime. From what you've said so far, I don't know why what I said wouldn't be a solution for you.

[ScottSa1]

Hey Greg, Sorry for the confusion. I've attached a screenshot so you can see the custom data calculated field. Basically it says: IF(Status='CPL', Progress Status) So when the task itself is set to complete, it makes that field equal what the progress status is of the schedule. So lets say my schedule was behind last week and I did my status update, when i hit complete, that field would equal to Behind. Then this week i created my new status update and my schedule was on time or whatever the status says, then that field would equal that. So essentially it is creating a snapshot of the schedule progress status when that status update was created. That is why we have people click on the 100% so the task status equals CPL. I did add in a custom field to select Yes or No if its the current status so people can at least select that and change the other one and that will make the correct one show up but its still that extra manual step that I'd like to avoid. Does that make sense?

[GregBe1]

Thanks Scott, that helps. I would say though that you could do the same except change it to "INP" (In Progress) then get Project Status. Therefore you have your snapshot. The next week the user changes this task to "Complete" and creates a new one and puts it to "In Progress", etc.