System connecting automatic checkweighing scales on 9 packing lines to the production database, for a consumer goods manufacturer in Thailand

How the brief came about

The brief in this case was to get weight values from all 9 packing lines into the production database automatically, along with which product each one was and which plan it was produced under. This consumer goods manufacturer in Thailand already had automatic checkweighing scales (Checkweighers) installed on its packing lines, but the machines could record only the weight value.

Which product was weighed and which line it came from had to be entered by hand, and the sample weighing every 30 minutes during production was also still done by hand on the scales. With weight values not linked to products and production plans, looking back at the weights of any given product batch was difficult, especially when several lines were producing at the same time.

Requirements and constraints

The system had to work with the scales and database the factory already had. The main requirements were as follows:

• Use all 9 existing automatic checkweighing scales without replacing any of them
• Receive weight values through the ODBC service (a database connectivity standard) the factory already uses, and record them in the factory's CIM database on SQL Server
• Use product codes and production plans (Production Order) from the existing database as the reference for matching
• Keep the sample weight recording (Weight Sampling) interval at every 30 minutes of production, the same interval the factory used for manual weighing
• Reports must be able to filter by line, product and time period, show tables of up to 1,000 rows and export to an Excel file of up to 20,000 rows

Why this work did not require new scales

This work connected data from the existing scales, because all 9 scales could already weigh and send weight values through the ODBC service the factory uses. What was missing was the information on which product and production plan each weight value belonged to. The scope of work was therefore software and database work, not machine replacement.

Recording was written as automatic commands in the database (Trigger and Stored Procedure) on SQL Server, so weight values are recorded when new data arrives, with nobody having to enter them. The system works in the following steps:

1. 1

   Receiving weight values from the existing scales

   Each line's scale sends the weight value and weighing time through the ODBC service the factory already uses. The system receives this data and separates it by line code for all 9 lines.

2. 2

   Automatic recording in the CIM database

   Weight values are written to the CIM database on SQL Server whenever there is new data, using Trigger and Stored Procedure, so weight values do not need to be entered by hand.

3. 3

   Matching to products and production plans

   The system links weight values to product codes and production plans from the existing database, making it possible to look back at which product and production plan a given batch of weights came from.

4. 4

   Recording sample weights every 30 minutes

   Sample weights, previously taken by hand on the scales, are added to the system automatically every 30 minutes of production, using values from the automatic scales.

5. 5

   Viewing and exporting reports on the web

   Users open the Weight Checker page in a web browser and select the line, product and time period. The system displays a table sorted with the latest data first and can export it to an Excel file.

Deliverables

We delivered the software, the database recording component, the web report and supporting documents. The deliverables are as follows:

• Auto Weight Link CIM software
• Trigger and Stored Procedure in the CIM database on SQL Server
• Weight Checker web report
• User manual
• Software test report

Results confirmed by the project closure test

The project's software test report records that the Auto Weight Link CIM system passed all 4 items in the scope of work. One of these was usage testing with the scales on the packing lines, which passed on all 9 lines. We closed the project in June 2023, with the work divided into 3 instalments.

After go-live, weight values from the packing lines enter the production database automatically, together with product and production plan information, and users can view or export historical weight reports from a web browser. This case has no business performance figures, so we report only results backed by documents.

What we learned

This work offers 3 principles for connecting line measuring equipment to a database.

• Measurements without context cannot be used for traceability. If the measuring equipment on the line can already send data, the first task is to add context to the measurements, namely product, line and production plan, before considering replacing the equipment.
• Data should be recorded in the database as soon as a new value arrives, without relying on re-entry. In this work, Trigger and Stored Procedure in the CIM database do this, so the data does not depend on whether people have entered everything.
• A new system should start from the quality control cycle the factory already uses. In this work, the system records sample weights every 30 minutes, the same interval the factory used for manual weighing, so the system fits the factory's existing quality control cycle.

How this approach can apply to your factory

If your factory already has automatic checkweighing scales on its packing lines, you can take the same approach as this case: receive weight values from the existing machines, record them in the production database, then match them to products and production plans. What needs adapting is how the data is received, when your scales or database differ from this case.

Besides the points in the table, you should also prepare the following before starting:

• Coordinate with the factory's IT team on the network and database access rights from the outset, because the work has to connect line-side machines to servers in line with the organisation's network policy
• Agree with report users what data they need to see, such as required filters and the volume of data per export, before starting to develop the web report