Rubbish in, Rubbish out

Wether you use People Inc or any other database, there is one golden rule whic is more important that anything else and that is to keep your data tidy.

Some of you may be familiar with the phrase, ‘Rubbish In, Rubbish Out’, but do you really understand what it means? In essence it means if you enter your data in a ‘rubbish’ way, i.e. not consistant use of fields, poor spelling, bad use of upper and lower case text or punctuation, it will result in you not being able to produce the reports you require or find the information you are looking for during a filter, query or other search.

The Key to a well maintained, efficient and effective database is to enter the data as consistently as possible. I have on numerous occasions noticed when working on People Inc that even though Picklists have been set up, people have entered the data into fields by simply typing it into the field, completely ignoring the picklist. The results are therefore completely inconsistent resulting in not being able to pull off the information you require accurately.

To give you an example, I have noticed on fields such as the TITLE field that users have entered several variations of ‘Mr’. Some examples are. ‘mr’, ‘mr.’, ‘MR.’, ‘Mr.’ etc.

All of these values are effectively the same but because they have been entered inconsistently from not using the picklist, when we search for all the values which equal, ‘Mr’ in the database, not all the results will be picked up. This is because when searching databases the system has a very black and white approach. Most systems dont see ‘Mr’ and ‘mr’ as being the same and therefore when you search for a specific value, you will only get results which match based upon your criteria.

Often people say the system is rubbish because they cannot produce the reports they need, but often it boils down to the quality of the data entered.

The key to remember, is to enter the data correctly each and everytime you enter it, to make sure it is all consistent this making life easier for you when you do want to perform a search or do a report later.

This is often fairly easy when you only have a few users, but the problem can be much bigger if you have many users, this is overcome by proper training and by making use of the options available to you when setting up your pick list. For example, key picklists such as the TITLE field which is pretty fixed (you will rarely add other values) can be set up so that users can only choose an option from the picklist, so that providing the users have been correctly changed to use picklists, they will only be able to choose one of the values from the list.

Part 2 suggests other methods for keeping your data tidy.

Adding a custom field to show date of retirement on Personnel Manager

For Advanced Personnel Manager Users*

This top tip is something which has come up quite a lot over the past few years.  More and more customers have a requirement to add a field which calculates someone’s retirement date based upon their date of birth + 65 years.

To do this:

Create a new date field and add the following formula in the Value tab:

DATEADD([DOB],0,0,65)

Where 65 is the retirement age for example.

The screen should look something like:

Retirement Date Example

Save your changes and close Screen Designer. When you next log into Personnel Manager you will see the changes.

With this field you can then run reports each month or year to see who is coming up to retirement to ensure you meet your contractual obligation with these staff members.

It is also possible to create  a variable retirement age. Contact us for details.

NOTE: You will need to have purchased the Screen Designer Module in order to customise screens. Contact us for more information.

*Whilst obviously we are now fully focused on People Inc, we still have plenty to offer to existing customers still using Personnel Manager and Personnel Director hence the occasional post with useful information, hints or tips.

Data Auditing and Cleansing

In organisations of any size you will still have some margin of error when entering data. We can minimise this by making use of the tips shown in Part 1 and 2 of this post, but every now and then some small mistakes will get through, epecially if you have many users.

The problem is, that its very difficult to spot these errors unless you actually look for them meaning that many of your key reports will be wrong, thus messing up your management figures each month etc.

The last part of this Post to get the most from the data in your database is to carryout regular audits of your data. Now you may think this sounds like a lot of work, or that its not really necessary, but trust me, all this involves is to run a couple of Audit reports each month and then correcting any errors and you will have an extremely efficient database all year round. If you dont do this each month, what you may find that when you run a report in several months time, or when you audit your data at the end of the year, that the information is not correct and has many different problems, which could be a big job to fix.

I’m going to explain a simple process for auditing your data which will help towards keeping your data tidy throughout the year.

Firstly you will need to identify which fields are critical – the ones which are key to your main reports and which have to be up-to-date and correct. Then you will need to set up a “Headcount by <KEY FIELD>” report for each field. Fortunately several of these are already set up in the REPORTS>PERSONNEL section of the Reports Screen.

In this example we will use the DEPARTMENT field, which is often a key field. As mentioned, there is already a Headcount by Department report set up which we can use.

We run the report and look at the results. What we are looking for are any blanks with a number next to them. This shows us that x number of employees so not have a value in the Department field on their record. We are also looking for any duplications or any incorrect values, for example we may have ‘Admin’ and ‘Administration’ listed on the report. Obviously this is a duplication. Or we may have a department which no longer exists meaning that someone has the old value still selected in the Department field.

Another good reason for running this each month is that we can then file these key reports in a folder to show the figures for that month, incase you want to see how many people were in Administration 6 months ago, for example.

Once we have identified these ‘odd’ values, we now need to know how to fix the problem. Now we could design a report which instead of showing just the number of people in each Department (in this example) but which shows names etc instead. This is fine, but in order to skip this step and save time, we are going to take our Headcount by Department report and ‘SEARCH’ for the odd values and fix them straight away.

To do this we will use the SEARCH tool which can be found as a button on the toolbar.

When the tool pops up, simply choose the following options:

Screen = Employees,
Field = Department
Condition = Is Equal to
Value = [Click the Red 'suggest values' arrow button to see a list of values and select the one you want]

Then click the ADD TO LIST button
Then click FIND NOW
If searching for Blank values use the following:

Screen = Employee Details,
Field = Department
Condition = Is Blank
Value = [Leave blank]

Then click the ADD TO LIST button
Then click FIND NOW

The matching records will be displayed at the bottom of the Search screen. Simply double click on the first record to open the screen, then make the changes and save. Then repeat for each record with the incorrect value.

You can then repeat this process for each incorrect value on your report.

Done each month, you will only find the odd few incorrect values, so this shouldnt take long at all, but if left you may find this becoming a huge job to fix.

NOTE: Try doing this on the Course Title field on the Training History screen to make sure your course list is consistent!