Searching By Department ID on Employees Tab

[royalpublishing]

I am trying out CE version 9.0.4 on Linux and followed the automatic installer. I imported my departments via a csv file and imported all of my employees and populated all of the pertinent data into the users table with SQL queries and as far as I can tell, I think I may have stumbled upon a bug.

Under Company > Departments, if you manually set a code for a department that corresponds with the manual_id field in the database, when you do a search by Default Department on the Employees tab > Employee > BASIC SEARCH or ADVANCED SEARCH screens, it appears to be searching by the department table's internal "id" field rather than the "manual_id" field.

So if I create a department with a manual_id of "30", give it a name of "IT Dept", take note of it's internal id number, and do a search, instead of returning all of the users with the default_department_id of 30, it returns all of the users with the internal id number of that department. Basically it is returning the wrong data set.

[shaunw]

If you are using SQL queries to populate the database (or make any changes to the database) you are doing it wrong and there is a very good chance you have already or will corrupt your database. You should import the employees through the import wizard or manually enter them instead.

[royalpublishing]

If you are using SQL queries to populate the database (or make any changes to the database) you are doing it wrong and there is a very good chance you have already or will corrupt your database. You should import the employees through the import wizard or manually enter them instead.

Thanks for the smack on the wrist from the SQL police, I will surely take that under advisement. As long as you do a little research into the data types of each field before you go just randomly inserting values, you're not going to corrupt the database and even if you did somehow, if you're smart, you would have made a backup of the data beforehand otherwise it should be a lesson well learned.

That being said, in this case specifically, you made an excellent point and did point me to my mistake. It turns out that the value of default_department_id should be the internal id of that department rather than the manual_id, my thought process was just backwards, so thank you!

I do understand where you are coming from from a support perspective but sometimes in situations you have to be a grab the bull by the horns kind of guy who is not afraid to get his feet wet. Next you're going to tell me that I shouldn't be manually adding fields or making changes to the php or javascript source in any way but the whole point of open source software is so that you can customize it to fit your organization's needs. Since I am evaluating the software and doing interoperability testing on porting my data from my old timeclock software to this one, now is the opportune time to make any mistakes and catch them beforehand before attempting to go live with a system. Thanks again for your help.

[shaunw]

The issue that you ran into is just one of the many reasons why we don't recommend that you manually modify the database. Other reasons is that its pretty unlikely that you are intimately familiar with the hundreds of related tables in the database and that when you modify one column of one table you need to update several other columns of other tables to keep everything "connected" and "intact" so it functions properly. Just because you inserted some data and you see the records in the UI doesn't mean things are working either, many issues you won't notice for weeks or months later, at which point restoring from a backup weeks or month would be a major problem.

One other small example, when you inserted the user records, did you update the "first_name_metaphone" and "last_name_metaphone" columns using the proper metaphone algorithm? If not you just broke searching and TimeTrex's ability to match employee records in certain cases. Did you also ensure that all data you inserted matched the regular expressions defined in the business rules? If not that could prevent the employee records from being modified for completely unrelated reasons down the road (ie: password resets, or email notifications)... Again, you may not have noticed this issue for months down the road until it has caused a major problem, and fixing it at that point would be painful.

Not only that but normally it should be easier, safer, and faster to create a CSV file out of whatever data you want to get into TimeTrex then simply importing it from there, that way avoid all the risks of manually modifying the tables. If you need more programmatic way of inputting data, there is the TimeTrex API instead. It may take a few extra minutes to complete at the beginning, but it will easily save hours, days or even weeks later on... We see this happen all the time and its extremely unfortunate and costly for customers to correct.

Obviously you are free to do whatever you like, but all we can do is offer you the "right" way to do something and recommend against doing something the "wrong" way that is almost certainly guaranteed to cause you problems later on. We want everyone using TimeTrex to set themselves up for success, not failure.

[royalpublishing]

The issue that you ran into is just one of the many reasons why we don't recommend that you manually modify the database. Other reasons is that its pretty unlikely that you are intimately familiar with the hundreds of related tables in the database and that when you modify one column of one table you need to update several other columns of other tables to keep everything "connected" and "intact" so it functions properly. Just because you inserted some data and you see the records in the UI doesn't mean things are working either, many issues you won't notice for weeks or months later, at which point restoring from a backup weeks or month would be a major problem.

One other small example, when you inserted the user records, did you update the "first_name_metaphone" and "last_name_metaphone" columns using the proper metaphone algorithm? If not you just broke searching and TimeTrex's ability to match employee records in certain cases. Did you also ensure that all data you inserted matched the regular expressions defined in the business rules? If not that could prevent the employee records from being modified for completely unrelated reasons down the road (ie: password resets, or email notifications)... Again, you may not have noticed this issue for months down the road until it has caused a major problem, and fixing it at that point would be painful.

Not only that but normally it should be easier, safer, and faster to create a CSV file out of whatever data you want to get into TimeTrex then simply importing it from there, that way avoid all the risks of manually modifying the tables. If you need more programmatic way of inputting data, there is the TimeTrex API instead. It may take a few extra minutes to complete at the beginning, but it will easily save hours, days or even weeks later on... We see this happen all the time and its extremely unfortunate and costly for customers to correct.

Obviously you are free to do whatever you like, but all we can do is offer you the "right" way to do something and recommend against doing something the "wrong" way that is almost certainly guaranteed to cause you problems later on. We want everyone using TimeTrex to set themselves up for success, not failure.

Completely understood and you are totally right, there would be no way for me to know any of those things that you stated unless they were blatantly documented somewhere and I just so happened to stumble on it. Thank you for the timely and thorough response. I do indeed want to do it the right way and set ourselves up for success as you mentioned but ultimately we will still have to do some customizations of our own and unfortunately there is a fine line there that might get blurred along the way. I'm going to check into adding the users via the API, otherwise I will focus on exporting my data to csv first then import it that way so it doesn't break the entire system lol, obviously we don't want that. Thanks again!

[royalpublishing]

I think the exact reason that I opted to do the SQL statements in the first place was that not all of the column names from the database were included on the example csv file and I didn't want to have to manually input a bunch of data as we all know how time consuming that can be. Also, when migrating some of the date/timestamp columns from the differing systems I was able to do it programmatically with SQL more easily to get it to the exact format it needed to be in the database as opposed to the format it is looking for in the csv file which will require converting it to dd/mm/yyyy format only just for it to get reformatted back to epoch when it is imported. It just seemed inefficient and like a bunch of extra unnecessary steps to have to take if you know what I mean.

[shaunw]

I think the exact reason that I opted to do the SQL statements in the first place was that not all of the column names from the database were included on the example csv file and I didn't want to have to manually input a bunch of data as we all know how time consuming that can be. Also, when migrating some of the date/timestamp columns from the differing systems I was able to do it programmatically with SQL more easily to get it to the exact format it needed to be in the database as opposed to the format it is looking for in the csv file which will require converting it to dd/mm/yyyy format only just for it to get reformatted back to epoch when it is imported. It just seemed inefficient and like a bunch of extra unnecessary steps to have to take if you know what I mean.

I understand that you think its faster/easier to use SQL directly, but I can't stress enough, that is the wrong path to take for long-term success. The examples I mentioned in my previous post are just the tip of the iceberg, there are literally thousands of such examples that can come back to bite you...

Importing from CSV or using the API are the proper approach, both of which can be fully automated from TimeTrex's perspective as well.

Anyways, I'll leave it at that.