by Steve Ferranti
As a metal finisher with the requirement to have detailed information at our fingertips for State and Federal reporting, we seem to find ourselves spending more and more time on record keeping. In our effort to become more efficient and spend our time doing those things that will produce more customers and make us more competitive in our industry, we needed to find a way to automate those tasks that are repetitive from job to job. We also wanted to be able to customize the program to fit our need. Since I am not a developer, I wanted a program that was simple to use yet still powerful enough the do the job required. The use of Alpha's database programs has proven to be the easiest way to maintain these records and speed up the retrieval of process information to complete jobs.
Our system, which was originally written in Alpha 4 for DOS and subsequently updated three times until we got to Alpha5 V6, consists of 3 tables linked together in a set, along with 2 separate tables that interact with the main set across our network. The first table in the set called 'Inhouse' contains customer information, such as their name and purchase order number, the date, and the job number we use to track the job. The next table in the set is called 'Lineitem.' It contains the information about each item sent in, such as quantity of parts, part number and paint used. It is linked to 'Inhouse' in a one-to-many link, since each purchase order may have more than one item. The final table in the set is called 'Parts'. It contains all the information needed to complete the item. It includes the paint number used, the silkscreen number, and pricing information, along with the instructions to complete the part. The 'Parts' table is linked to the 'Lineitem' table in a one-to-one link.
When a customer delivers a job to be finished, the receiver enters a new record on a form from the 'Inhouse' table called "job sheet" that contains sub-forms from both the 'Lineitem' and 'Parts' tables. A field called "active" is filled with a "Y" to mark the record as a current job. This field allows us to keep a history of past jobs and be able to print out a list of jobs that are still active and in the shop. The next available job number is automatically filled into that field. This number is placed on the actual parts to identify them by means of a printable form that contains the job number along with the part number, paint, and silk-screen number. The current date is also automatically filled in but is editable in the event it needs to be changed. The customer name and the purchase order number are typed in. The cursor advances to the 'Lineitem' sub form. The quantity is typed in as well as the part number. At this point the database searches the 'Parts' table and retrieves the data in the matching part number record. That information fills in the sub-form from the 'Parts' table and a job sheet is printed to travel with the job through the different processes. If there is no matching part number, a button click opens the 'Quotes' table and searches it for a match. When it is found, it is then used to create another record in the 'Parts' table and in turn to update the "Job sheet".
After the job is complete, the amount of paint used is entered into the 'Lineitem' field for paint use and the job is marked as inactive by putting an "N" in the active field. At the end of each month, an operation is run that adds up all the paint used by part number, and enters it into a field in the 'Parts' table. As an example, a part from the XYZ company with a number 12345 uses the color black which is number 241 in our system. The part is done more that once a month. At the end of the month, the operation goes through all the line items that were completed and adds the quantity entered as paint used to the field in the 'Parts' table for the same part number. This is done by an operation that adds to the existing number operation, rather than a replace the existing number, because a part number may be processed multiple time in a month. The information in each record in the 'Parts' table contains the paint number used. That field is the link to the 'Paint' table. An operation from the 'Paint' table is run which zeros out last year's data in the current month and then another operation retrieves the total from each part by paint number and updates the paint table with an operation to add to the existing number, giving a total amount of paint used that month broken down by individual paint number, and at the same time replaces the value in the 'Parts' table with zero to get it ready for next month.
The 'Paint' table contains all the information necessary to complete the monthly and annual report-keeping requirements, such as, total paint used per month and year, volatile organic compounds per gallon, total toluene, etc.
In the past, the paint data was entered manually for each month on a spreadsheet. However, the process could take an entire day to complete. With the use of Alpha's database, the process can be completed in less than 30 minutes and several reports can be generated, allowing us the keep the proper inventory based on past use along with deleting inventory that is no longer used.