Apr 11 2023 10:03 AM - edited Apr 11 2023 10:04 AM
We have a timecard db that ties into QuickBooks. All employees must bill their time to a valid [Service Item]. Using that timecard db we can access the ListID field used by QuickBooks to identify each [Service Item] as well as the actual hours worked on each [Service Item] for any given day.
I'm looking for a way to take advantage of the fact that employees are already reporting how they spend their time in the timecard db so we don't need to also record actual work in the Project file.
I don't believe it's possible to import [Actual Work] into Project because it involves multiple tables, right? But is there any other clever approach that might work to allow us to use the data from the timecard db to update tasks status without the redundant data entry?
We are currently using Project Desktop. But hope to move to the cloud next year some time.
Thanks for any ideas or references.
Rich P
Apr 12 2023 08:09 AM
@Rich_Powers OK, so it seems that there is no obvious solution to this broad question. What if I were to be more specific... Say I use one of Project's custom text fields to store the QB ServiceItemID (as a foriegn key) for some tasks. And let's say I have a table in an Excel file that summarizes the hours/employee/ServiceItemID/date. Can anyone think of a way to use the task's resource + custom text field's ServiceItemID to lookup the appropriate hours in the Excel file and then update the actual hours in the project file?
And I guess it bears asking, if it IS possible using the Desktop version of Project, will it STILL be possible once we move to the cloud?
Thanks.
Apr 12 2023 08:26 AM
Apr 12 2023 05:17 PM
Apr 13 2023 10:14 AM
Apr 18 2023 11:26 AM - edited Apr 18 2023 11:43 AM
I agree that the VBA approach would be best. Carry the [Service Item] as a task field within project.
Add a VBA form that uses a current Cell Value field as the filter for the recordset that returns when you put the mouse there and run a form.
So the entire project when filtered for Service Item_000X (previous state) would also return Service Item_000X (filter from Quickbooks current state). The Project scheduler could then update the Project interface task from the returned current set in the form and save the .mpp. There are going to be discords that exist. Time card entries that = 3 different tasks, or Tasks named in such a way that it will never match. You need a baby step to guarantee "At Least it is better than it was" That is why the filtered previous (project) vs current (timecard db vba form recordset) filtered tasks list is so important. After that is present the same model can add iteration with "LookUp()" and then you could work your way up the automation tree to have this maybe become a fully automated process.
You mentioned a "timecard db" more often than not those have a driver for them and the VBA code could take advantage of direct access to it via an OLDB or DixectX library reference.
skipping Excel. Even if you needed to build an exported reporting source from timecard to somewhere readable by VBA (normally your user account); the export can exclude personnel information often in timecard DB's. I've noticed accounting audit's are asking about this every visit now.
I believe in this day and age. Your option going the other way back to the consumer is to publish to Project Online and allow your enterprise to read the published project probably in a team(s) share.. You did not mention having Project Server on premises
About the accounting system vs project. timecard db may get you actual work, a work order may get you an estimate but only project can get you daily remaining work. I solved this problem by not solving it. People fill in timecard and PWA. everyday. I apologize to them that is the way it is but they found they only have 2 concerns now. 1.) getting paid, 2) my Scheduler has my back with regard to my actions because there is no disconnected representation of reality coming out of accountings monthly WIP. (or even daily labor reports) The work takes as long as it takes. I'd be curious to know if your salary people are contributing to Service Items. These folks only have to fill out PWA in my environment but that is a bunch of infrastructure to relieve those users of that dual entry.