Fixing records with blank index values in Micro J Products



These instructions are for PcHunter or Tempus Fugit version 9. I am not bothering to write up separate pages for PcHunter and Tempus Fugit because the instructions are identical.

On this page the terms datafile and database are used synonymously, they refer to the same thing, the omnis .df1 (and .df2 etc. if you have multiple segments) file which is what you log into when you are running pchunter or tempus.

In another article I talked about running datafile maintenance to check for corrupt indexes in table structures within a pchunter or tempus datafile. This page talks about a different kind of problem, one that can cause problems similar to those caused by corrupt table indexes, but are caused by something completely different.

There is a quirk in the design of PcHunter and Tempus datafiles, there are actually two index fields defined for most of the major tables. I have no idea why it was done this way, but it can cause problems, in the following paragraphs I'll explain how to identify these problems and fix them.

The three main types of records in pchunter/tempus datafile are the people, company and job order tables. Within each one of those tables the main index values are as follows:

People = Acode AKA People Number (there is also a semi hidden system value called Aseq, which is the second index field I described above)

Company = Cocode AKA Company Code or Number (second index field is Coseq)

Job Order (or Search Assignment) = Jobnumber AKA JO Code (second index field is Joseq)

The secondary index fields will ALWAYS have a value (this is the aseq, coseq and joseq fields). However, in some cases records are created which are missing the main index value. This can result in people records with a blank Acode field, company records with a blank Cocode field, and Job Orders with a blank Jobnumber field.

Without going into too much detail as to why it is a bad thing to have records with missing index values, suffice it to say that it confuses the system. When you look up a person who is missing a value in the acode field, when you try and jump to that record from a lookup screen it is very likely you will end up on a completely different person's record. This is disorienting to say the least and if you're not paying attention you may end up editing the wrong record. The same thing applies to company and job order records.

To find people records missing acode values, from a people lookup screen, change the lookup option from Last Name to People Code. Leave the lookup value blank and hit enter or click OK. If there are no people records with a blank acode you will see the message "No Match found!". This is fine, this is what you want. If you do see a list of matches, here's what you have to do.

Select the first entry on the lookup list and double click it. Once you're looking at the record, you should be able to visually verify that the people code field is blank.

You cannot delete a record with a blank index value, the system will not allow for this. You need to edit the record and manually assign an index value. Usually what I do is add a number like '9999' as the people code for a record I'm going to delete. Once I've assigned the people code and saved the record, I make sure there is nothing useful attached to the record and then delete it.

In some cases a record is a valid record and the only thing wrong with it is that the people code is missing. In that case you also need to assign a people code manually. You can either duplicate the normal system logic for assigning people codes (the last name with the first letter of the first name at the end) or create a unique numeric code, such as one based on the date you are editing the record (for an example, 11121801 for a record edited today, 11/12/2018, with the 01 being added at the end if you need to assign multiple people codes to multiple records, the next one could be 02, etc.).

Whether you are deleting a record or assigning a valid people code to it, you need to keep rerunning the lookup until there are no more people records left without people codes.

The steps for company records are similar, except that you do the lookup from a company screen and change the default lookup from Company Name to Company Code. Ditto for Job Orders, change the default lookup from Company Name to JO Code.

The same logic applies to those records in terms of adding the correct codes to the company and job order records, whether you are deleting the records or keeping them. Once you're done cleaning any of those up you should have no company records missing the company code and no job orders missing the JO Number or Jobnumer field.

There is an additional step you should check with the job orders, just to be on the safe side. When working on the job orders, also lookup the JO Code and enter the value AUTO to see if any job orders were created with an AUTO jobnumber rather than a numeric entry. If you find any like that assign them unique numeric values for the jobnumber field.

There is an additional problem you may run into, if you find this go ahead and contact me rather than trying to sort it out by yourself. You may also find records where there are multiple records that are sharing the same index value. You can't just delete one of the two if that happens if it's a people record, as you will then lose the people notes and any activities for both records. Contact me and I will walk you through fixing the problem correctly.

That's all I can think or for the moment about dealing with this problem. If I think of anything else later I will add it to this page.

If you have any questions about these procedures or problems contact me with the information on the Micro J Products Support and Training page following the link below:

Micro J Products Support and Training

Return to Micro J Systems page

Page created 12 November 2018