Linking to Actual Hours from Timecard Database

[Rich_Powers]

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

[#2]

@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.

[#3]

Rich_Powers,
I suspect the reason no one responding to your original post is because no one on this forum has ever worked with Quikbooks database and Project. However, now that you have "adjusted" your approach the answer is a definitive "yes". Depending on how the data is set up in Excel it might be possible to use an import map to pull the data from Excel into Project. If that doesn't quite pan out it is definitely possible through VBA.

Once the process is set up for the desktop version, it should work fine with equivalent cloud versions of Project (i.e. Project Online). However it will not work if you go with Project for the Web which is different than Project Online and, as far as I know, does not support import maps or VBA.

John

[#4]

Thanks for the reply John. I suspected that the QuickBooks concept might have thrown people off. I can use Power Query in Excel to read the native QuickBooks timetracking table and project that data into a table on a sheet. Every time the sheet is opened it will refresh the data to expose the latest entries from users.

So the question comes down to how I map that info to the correct tasks/resources/work records in Project? Do you think it involves round-trips from Project --> Excel and then back to Project? I've already set up a custom table filtered for just the task records which have a QB ServiceItem code in the CustomText10 field. So I suppose I could export that to an Excel file, user Power Query to join that export to the QB data and then import/merge the data back into Project based on the UniqueID field? It seems a lot of steps if there is some way to use some sort of 'LookUp()' function as a formula in the Project table.

[#5]

Rich_Powers,
Project has three main objects, Tasks, Resources and Assignments. The import wizard allows a user to import data for each of those objects from designated Excel Worksheets. You will need separate worksheets for each of the objects.

As far as having to go back and forth, I don't think that is necessary if you have already identified a custom field that you can use as a merge key but I'd have to see what you have (i.e. Excel format & Project file) before I could definitively say, "yeah, you're good to go".

If you want the most flexibility with the least amount of interaction, I would use VBA to import the Excel data into Project. You don't need separate worksheets, and data transfer can be pick and chose based on a custom identifier.

John

[#6]

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.