Barcoded Assembly Verification

[seth350]

Morning folks,

We are getting into tracability of parts and such at our facility and was wondering if anyone else has the same or has implemented a system for that?

What database did you use for part lookup? Any PC software involved? Interfaces to database?

[Noe]

I have heavily modified a few apps to trace parts using barcode scanners and data matrix (2D code) labels. Original programmer used Access database to store data, it works fine but our production volumes are really low so we keep around 10 thousand records and backup the data and start over with the database to keep it agile. I have read that Access has that problem, that if you add to much data, it will become very slow and eventually unsuable. Never got to that point.

There are several options for DB's, depends on your needs. Like MS-SQL, you have to install and setup the software (not too hard, but may be for a maintenance technician), that is why we used Access (just one file embedes the whole DB without the need of a server).

If you want a file contained DB without having to set up servers, you may try SQLite, its a combination between Access and SQL, as it does not require anything else but the file, but much more robust than Access, and uses SQL commands. Can hold much data very alike server based SQL. I did some tests before, but have not had the time to change our Access DBs to SQLite.

[Archie]

I've implemented several traceability systems and been involved with the implementation of a couple. One in particular I was involved in was implemented using SAP. The company spent millions of dollars and many months implementing the system. In the end it did not track down to the level they needed, so they contracted me to implement a sub-system that provided traceability down to the level they needed.

In the systems I completely implemented, they all used SQL Server as the backend database. Some used SQL Server Express (the free version) and others the full version that was administered by corporate IT. I used the typical Visual Studio forms for editing the database and the ReportViewer to print job tickets and other reports.

For barcoding I've done a number of different systems. One that I did about 10 years ago used a handheld scanner that ran Windows CE. They would scan pallets on the shipping dock and it would store the information in a local DB on the scanner. When the scanner was docked, it transferred the data to the main SQL Server.

Another system I did was part of a batch production system. As the operator proceeded through the process, they would scan the various materials as they used them. The barcode used an RS232 interface to an industrial PC. The scan was verified against what was valid in the DB then written it to the PLC. Once the batch was complete, the PLC would launch an application on a remote PC that would retrieve all of the process data, transfer it to the SQL Server, then print a report.

If I were to give advice to someone that is about to build a database application for the first time, I would tell them to get a good grasp on relational database design. This is your foundation and affects many things such as how easy it is to generate reports on the data and how versatile the system will be. If your foundation is shaky, your system will suffer in the long run. Before I create a large database, I like to fully understand the process and what it will be expected to do so I can create tables and relations with all of those details in mind.

[seth350]

Thank you gentlemen.

I have been tasked with implementing a low level verification system. I already have the ms-sql server setup on an industrial pc with data I exported from SAP. I am using Factorytalk Transaction Manager to get data from the sql server to the plc. I will be using Advanced HMI to show what is going on.
I am at a roadblock as to how to compare what is scanned to what it should be. Should that be handled in the PLC or should the server be doing that?

As of now, I wait for a new scan to come into the plc. I then decide if it is a package code or a component code. If it's a package, I then copy the result to Factorytalk Transaction Manager and then trigger a transaction from the plc. This trigger sends the package code as a parameter to a stored procedure that will find all component codes that belong to the package code. The component codes are then sent to the plc.

The component codes are what operators will be scanning as they place them into a package. Should they scan a component that does not match the components from the server, the system should fault. Like wise if the same component is scanned twice then also fault. 

If you don't mind sharing, how did you guys manage comparing multiple codes to a database and also keep up with duplicate scans and mismatch scans? It makes me wonder where the line is drawn, as if it should be handled in the plc, the database, or in advancedhmi using vb .net script and linq.

[Archie]

In my opinion anything more than a simple exact match is overly complex to do in a PLC with ladder logic. I'm currently working on a project that accepts 3 inputs per material.... Material code, Lot Number, and Expiration date. The material code is very simple and only compares an exact match to make sure it is the correct material called for in the recipe, so it is merely stripped of a prefix then written to the PLC for the comparison against a recipe that was loaded into the PLC. The Lot Number is not compared against anything so it is also very simple to strip the prefix and write directly to the PLC. The date code is the trickiest because it must be intelligent enough to handle any kind of date format. For example:

1/10/17
01/10/17
01/10/2017
10-JAN-17

After parsing it must then determine if the material has expired. To do all this in a PLC could result in mess of ladder logic programming. I tackled the problem by parsing the date in AdvancedHMI, checking it for expiration, formatting it to a common format, then finally sending it to the PLC. With just a few lines of VB code this was all done in no time using the built-in date parser in .NET

The VB code for parsing any of those date formats is as simple as this:

Code: [Select]

Date.TryParse(Prefixes(index).Result, m_ExpirationDate)
Imagine that same task in ladder logic.


To add another level of complexity, they needed the option of using a 2D barcode scan that consolidated all 3 items while still having the ability to scan the individual 1D barcode. The items in the 2D barcode are parsed using prefixes. Once again in my opinion this would be way to complex for trying to write in ladder code.

Another scenario would be the requirement to check if a scanned barcode existed within a database table. Doing this in a PLC may work with a limited number of items, but you will soon be caught in a corner once that table grows to thousands of items. This can only be reasonably done on a PC.

So in a nutshell, I like to do some preprocessing in a PC and leave the PLC with a very manageable task.

[Noe]

I agree with Archie, as we do process the scanned part number in the VB.net application, to search the database, but also print another label, pulling data from DB like torque and angle that has been stored in a previous machine.

Process your data in visual basic, use the database and respond to the PLC, let the PLC be the muscle this time, and just react to PC app decisions. Leave just some very basic process like part number comparison against recipe to PLC, to save comms bandwidth, if the part is incorrect and you do not need to store the error in the DB, then you can discard it directly from PLC code.

[seth350]

I see. Thank you both for sharing.
I found that I have been bouncing control back and forth from the plc to the server and to the .net app.

Centrally controlling the entire thing should be my main goal and going off what you shared, it seems the ideal place is the .net app.

I have the system working and the .net is doing some minor leg work. Mainly just indications.
The PLC along with FTTM are doing the brunt of the work.