How can I avoid errors in my database?

There are several common problems that might arise in your MainBoss Basic database:

Bad dates

For example, you might accidentally type 1007 or 3007 instead of 2007. In some contexts, MainBoss will detect when you've typed in a date that is obviously wrong, but in others, MainBoss will accept whatever you type. Unfortunately, this may lead to problems later on (especially if you migrate from MainBoss Basic to MainBoss Advanced). Other examples of bad dates include "Work Start Dates" that come after the corresponding "Work End Dates" and labor dates that fall outside the period when a job was actually done.

Deleted records that are used by other records

For example, suppose you accidentally delete the record for a Building. Then every other record that refers to that Building (e.g. records for spaces and equipment in the building) now refer to a non-existent building. This can cause numerous problems.

Corrupted data

There are various other ways that data can be messed up, such as computer crashes, disk errors, or using some program other than MainBoss to change alter MainBoss files.

Meters without units

All meters should have associated units of measure; otherwise, the process of transferring data from MainBoss Basic to MainBoss Advanced will create invalid data values. Go through your list of meters to make sure that each one has a unit of measure associated with it.

There are two keys to dealing with such errors: auditing and archiving.

• MainBoss's audit facilities are available through Administration -> Database -> Audit. Running an audit will tell you about inconsistencies in your database. The audit window also gives you facilities for correcting different types of inconsistencies.
  
  However, be warned: the corrections offered by the audit window may be too simple-minded to deal with complicated situations. For example, remember that example about accidentally deleting a Building record. The audit window will offer you the option of deleting all the records (Spaces, Storerooms, etc.) that refer to the missing Building. Usually, you don't want to delete those records; if you delete all the Spaces that were in the missing Building, you'll end up with a whole new set of problems (e.g. in all the work orders that refer to the deleted Spaces). Instead of deleting records, you should re-create the missing Building record and link all the existing "disconnected" records to the new record.
  
  When you use the audit window facilities to create the missing Building record, you don't get a complete record with building name, address information, etc. You just get a minimal fill-in record; you should then go to the Buildings table and fill in the fields of the new record with useful information. The same principle applies with any record created through the auditing facilities: after a record is created, you should examine the record and edit it if necessary.
  
  The point is that you should think about each audit result before you take any action; understand the reason for the problem rather than ruthlessly getting rid of it. Don't assume that the audit window's facilities will fix any problem perfectly.
  
  After you've made corrections in an audit, perform another audit to see if any new problems have popped up. In the course of correcting one problem, it's possible to create new problems. For example, suppose you accidentally delete a Storeroom record. All items that were stored in that storeroom will not be "disconnected", referring to a non-existent Storeroom. You can solve this problem by creating a new Storeroom record...but if you don't edit that record after you create it, the Storeroom won't have a valid Building field. When you create any record during an audit, you should always check that record and make sure it has all the required information.
  
  The audit process will also tell you when the quantity of an inventory item is less than zero. This can occasionally happen for brief periods of time—for example, if you buy an item and immediately use it, then record the use before you record the receipt. However, if an inventory value remains negative for a long period of time, it indicates that your database doesn't match reality and the numbers should be fixed.
  
  
• MainBoss's archive facilities remove old records from the database, including old work orders, requests, and purchase orders. The archiving process also replaces detailed inventory transactions with a short summary. In this way, archiving reduces the contents of various tables, allowing MainBoss to work faster and decreasing the amount of material that you have to look through while using MainBoss.
  
  However, be warned: the archiver also removes old price quotes from the database (price quotes whose dates precede the "Archive Date"). If there are no price quotes on a certain item after the archive date, MainBoss will no longer have price information on that item. For example, if you archive all records that are older than six months, and some items have had no price quotes in the past six months, then you'll lose price information on those items. (By checking your archives, you can find the information; then you can create new price quotes to assign values to the items.)
  
  Note that the archive process doesn't remove open work orders and purchase orders, no matter how old they are. This suggests that you shouldn't leave work orders and purchase orders open indefinitely—review old WOs and POs occasionally, and close those that really aren't going anywhere.

We recommend that you run audits on a regular basis (at least once a month), so that you can catch problems soon after they occur. This is particularly important because some types of errors can multiply as time goes on; for example, if you have an error in a task record, you'll get erroneous work orders every time you generate a work order from the task. By eliminating errors early, you can reduce the number of corrections you have to do later on.

You should also run an audit after any unexpected shutdown (e.g. a computer crash or power outage). After running an audit, and after making corrections, run another audit to make sure the corrections haven't caused more inconsistencies.

We also recommend that you archive old data from time to time. This does more than just improve MainBoss performance; if some of your old records contain corrupted data, the archiving gets rid of those records and makes your database "clean" again.

Migrating to MainBoss Advanced

It's particularly important to have a clean database if you're migrating from MainBoss Basic to MainBoss Advanced. If you're doing this, make sure you're working with a copy of your main database, not the database itself. (See Making a Test Database for instructions.)

The less data to export/import, the less likelihood that mistakes will interfere with the process. Therefore, archiving to a very recent date may save you a lot of headaches by removing old erroneous data. However, as noted above, archiving gets rid of price quote information. You may therefore want to experiment on several test databases, trying to find an archive date that gets rid of bad data with a minimal loss of price information. If any information is lost, you can always copy it by hand from your 2.9 database (which will still be available because you're working from a copy of the database, not the original).

Back to FAQ index