Manipulate timesheet data - what to do next ideas please?

Dear Peers.
Its been eight months with ERPNext, since I convinced my partner that we should migrate from Filemaker. It has been quite an adventure and now the time has come to to do a bit of spring cleaning. Below are my chores:

One user created a ton of timesheets, one for every task he started out to do.
Yet, instead of using tasks, he created projects instead. One for every task, and he also created duplicates, where one project is to be billed to the customer, another is not to be billed, instead of using the check mark on timesheet detail.
Some rates for billing are just simply wrong (added VAT, subtracted VAT, and so on)

Another user, used timesheets in a more correct fashion, yet he too did a mess of some sort. By default, he newer stopped the timesheets doing lunch and other breaks. He did not use track tasks, only projects. At one time, he selected a task, that somehow got stuck on quite a few timesheet detail lines for different customers. Billing rates are mostly wrong. Cost rates are somewhat wrong as well.

A third user, has more restricted access to ERPNext, she cannot change billing rates herself. Yet I have not done the correct monitoring of her timesheets and what billing rates customers have default, and now they are wrong for most of the part.

So now I am stuck with a bunch of submitted timesheets, maybe up to ten pr. day, a bunch of projects for every customer, where I only would need one or two, no reuseable data for tasks, so it is literally impossible for me to check on status on each project regarding deadlines an stuff, and basically no time to do all of those changes “by hand”.

As mentioned first in this topic, we migrated from a filemaker solution I created some years ago. I haven’t had the time yet to import all the old data into Filemaker, but as an accountant, I am forced by law to keep all records for five years even after the customer relationship is lost. The data is serialized, but not sorted into projects and tasks as in ERPNext. The task of sorting the records can easily be done from within Filemaker it self, so importing the records should be, at least in theory, be a breeze. I reality, I recon that I need to create all former employees, customers, projects and tasks, to accomplish the import. Yet, another problem arises with this: Most of the Filemaker records have journal notes in them. I have not yet found a method to import the journal notes into comments on the customer, and as a matter of fact, I would love to find a way of doing the journal in a better way in ERPNext.

So, what are my options?

I could use the “manual” method and do all the changes inside the ERPNext forms, but it will honestly be quite a task, which I am not up to right now.

I could export the database to sql, make all changes, drop the old database and import the new. But it I make mistakes in the summery fields, they will most likely be stuck, and worst case, I would make matters worse than they are.

I could maybe use the data import tool, but can I with this tool overwrite timesheets that is already submitted? And will summeries be corrupted doing this?

I was thinking about creating a Filemaker solution using either a SQL connector or the API to review data, manipulate and update in ERPNext. Such a tool would be very handy for doing maintenance and migrating data from other systems into ERPNext, but I will use quite some time to develop just that, and basically, I need the data to be correct within ERPNext as soon as possible.

What do you guys think? What are my best option to sort out the mess?

Best regards, Anders

You are brave to share your reluctance of how or why timekeeping as a benefit or evil etc.

@wojosc kindly posted and gave a talk on his ideas and trials to adopt and adapt ERPNext out of the box to his workplace.

One strategy is to take your timesheet data sample for import into one or two popular time sheet tools for eg 15 Time Tracking Software for Ubuntu - time tracker app for linux - TimeCamp

That might help you with insight that would fire you interest to identify what or where to go next to resolve your apparent dilemma.

Hope this help and good luck - please report here on your findings!

Hello Anders,

from reading your problem I guess you’ll be able to split the whole thing in two subjects.

  1. you have your timesheet/project problem
  2. is your data-import problem for review reasons.

To 1.:
I have had my trouble with timesheets to. I found it confusing, when using several different activities in one sheet so I tend to just use one activity per sheet.

To 2.:
You would probably have to make a virtualbox instance of some sort and then just try things with exporting existing data and making your old data suitable via LibreCalc or so. I have found the built-in data-import-tool very usefull, but it will need a little trial and error.