It occurred to me that members of this group might being using a simple tracking system for electronics board manufacturing.
At the moment, we are using a simple excel spreadsheet to track our builds.
In our case, we build electronic assemblies using an automated SMT line and then add thru hole components. After this we have various test, programming and QC operations. If everything goes well, the board is placed into inventory. This may be a sub assembly or finished goods. If it is an item that is sold, we log the ship date.
We start by running a batch of boards in our SMT line. After they are washed, we add a barcode serial number. Each board is scanned into the spreadsheet along with the date of manufacturing. We also collect process time on the lot, which gives us an estimate of the time it takes to complete each assembly.
The next step is thru hole. The batch is often smaller than the SMT batch since it has less automation. After the thru hole components are added, each individual item is updated with its finish date, process time, assembler, etc.
The process continues thru test, programming, etc. Sometimes a board takes a detour for rework, etc. We want to track the flaws so that we can improve our process. For example, did a build get rejected for a placement error, screen printing problem, bad part etc.
I think you may get the basic idea. This is somewhat inefficient in Excel (at least given my basic excel skills). I am thinking of something similar to Parts & Vendor which we use for parts lists and purchasing. I am not looking for a full blown MRP system. Maybe we can get by with a better spreadsheet template, but my guess is that this is a common problem that has been addressed by many others and I see no reason to reinvent it.
It sounds like you have evolved a solution to your tracking problem and could now sit down and write out a full requirements specification with good knowledge of how you wish your tracking to run. Often, the final solution may end up being bespoke, but there may be some commercial packages out there that could meet your needs.
I am presuming that you have the means to scan and add data at each tracking point of your lines. A simple data collection application at those stations could just send the data to the central tracking system which then runs your tracking database and makes that information available to enquiry.
Once you have been able to write up your goals, and formulated a requirements specification document you have the basis for seeking your improved solution. Concentrate on just the needs you have for the system.
--
********************************************************************
Paul E. Bennett IEng MIET.....
Forth based HIDECS Consultancy.............
Mob: +44 (0)7811-639972
Tel: +44 (0)1235-510979
Going Forth Safely ..... EBA. www.electric-boat-association.org.uk..
********************************************************************
So, you are using a barcode reader "wedge" and counting on the user/operator to correctly position the "cursor" in the correct cell prior to scanning?
I.e., you are vulnerable to a user "accidentally" positioning the cursor in the "wrong" cell (for example, for the record associated with a board already "entered") and clobbering *good* data -- possibly irrecoverably?
So, absence of a "shipping date" signifies that the board is "IN inventory"? Or, is there a positive confirmation stage as the board is "added to inventory"?
How do you get the timestamp? Is this something manually entered or "scripted" in the spreadsheet to be captured coincident with the serial number's entry (scan)?
Again, how are the individual data identified as such? Are you relying on the user/operator to select the correct field (column) prior to entering/scanning the data?
Below...
Just make sure you consider how flexible that solution (COTS or otherwise) is and whether or not it is going to dictate to *you* how your workflow/recordkeeping will take place or if *you* will dictate to *it* (implicit in this is whether or not it can seemlessly accommodate changes in process -- from day to day or operation to operation! -- as may be the case)
I've done a few barcode-driven "systems" in the past decade. One thing I learned early on was to let the data "self identify".
For example, I "RANDOMLY" assign "identifiers" to "objects". The choice of identifier carries *no* significant information. So, "12HY7300G" tells *you* nothing, by itself. The next "issued" identifier may be "12HY7300H" -- or "6"! REGARDLESS OF WHAT IT IDENTIFIES!
Instead, a database tags each identifier with a description of what it is -- so the application, when encountering this identifier in ANY context, knows what it means (AND, whether it *should* exist in that context!).
E.g., there is not a "board" carrying the ID of "00001" *and* a staff member having the ID of "00001" *or* a component having the ID of "00001", etc.
If, for example, "00001" happens to be an employee's ID (as defined by the ID database that tracks *all* ID's and their meanings), then you will NEVER encounter "00001" in a field labeled anything other than "employee", "technician", etc. (i.e., something that is intended on having a "type" that corresponds with "employee").
Similarly, if a board has a serial number of "37GHG887", then you will only encounter that identifier in a field that expects to identify a "board" -- never as an "employee", "vendor", etc.
As the application is the only entity filling in these fields, there is no reason for *it* to ever mistakenly place an identifier KNOWN to reference a "board" in a field intended to reference an "employee"! Noticing a violation of this sort is a positive indication that data has been corrupted (or, the app is hosed).
By contrast, if you have employee ID's and board ID's that coincide, you never REALLY know if "00001" was the label on the board *or* the label on the technician's *badge*!
Once you have a way of "identifying objects", you can freely use it to identify everything of interest to your process! For example, scanning the badge of the technician who tested the board, the inspector who inspected it, the line it came off of, the *shipping case* (which, conveniently, ALSO carries one of your "globally unique" identifiers) that it eventually gets packed into, the customer it is being sent to (customer's have ID's!), etc.
The real advantage lies in the fact that you don't rely on user's to "assign meaning" to an identifier BY THEIR CHOICE OF THE FIELD IN WHICH IT IS PLACED! "37GHG887" happens to be a *board* so encountering it in this place (or at this "point" in the "process") inherently conveys meaning. E.g., a scanner that is associated with "test" encountering this identifier KNOWS that the board is being tested (or, depending on how *you* have defined your process, has JUST BEEN TESTED... or, HAS JUST PASSED TESTING... or, just FAILED!)
*You* define your process and the data collection system *follows* you (instead of LEADING you). This allows process to change over time as well as from individual to individual, or as the needs of the particular situation dictate.
To give you an example:
I designed a system along these lines for a local nonprofit. Donations had to be tracked (for tax purposes, etc.) from "intake" to "disposition".
A Donation had associated with it:
- a Donor ("Anonymous" if the Donor didn't want/need to be identified)
- type of goods (clothing, medical supplies, medicines, etc.)
- a weight (assigning value to items is fraught with "issues" but weights are easy to track "for accounting purposes" especially if the item will later be shipped somewhere)
- which scale was used to weigh it (a 30 pound donation probably gets weighed on a different scale than a 4000 pound donation!)
- staff person who received the donation
- timestamp etc.
A 1960's approach to the problem would be to SEQUENTIALLY enter each of these fields into a "donation record".
My solution:
- Frequent/repeat donors have their own "ID card" (barcoded). This allows me to track their donations over the course of a year and provide a summary to them at year end. It also lets me know who the big donors *are* and who I might want to single out for special recognition. It also gives clues as to which donors you might want to "hit up" for a special/particular donation ("We need to remodel the lavatory. Could you find it in your heart to donate some supplies or services -- or CASH -- to help facilitate that?")
- Each scale has an identifier (barcode label)
- each "conveyance" (cart) has an identifier
- each employee (donation processor) has an "employee ID"
- a chart on the wall has photos of each type of donation with barcode labels beneath each.
- each "display station" (user interface) has an ID
- each (wireless) barcode scanner/reader has an ID
- EVERY ID IS UNIQUE AND SELF-IDENTIFYING.
When a donor arrives, the goods are loaded onto one or more "conveyances". A "tag" (essentially, a chunk of plastic with its own barcode identifier) is placed on each "pile of stuff".
An "employee" picks up a wireless scanner from its charging cradle. The system knows each scanner's unique identifier implicitly (each has a different "broadcast channel/MAC address). So, when it sees the identifier "34297HHT76", the database tells it that this is an EMPLOYEE. The system now infers that the individual identified by that identifier is in control/possession of the barcode scanner.
When it encounters the identifier "434213" the database tells the application that a DONOR is being referenced ("Hello, Mr James! How are you today?").
When it encounters the identifier "345345088" it knows that a TAG is the object of interest. When it encounters "JJHH88787" it knows that a *particular* SCALE is being referenced -- so, it can go fetch the "current weight" displayed by that scale. When it encounters "QQ5234987" it knows which of the (different) CONVEYANCES is being referenced and, as such, knows the *weight* of that conveyance (cart) and can deduct this from the gross weight provided by the scale (now it has a net weight for that particular "tagged" donation. When the barcode beneath the photo of "clothing" is scanned, the system knows that this donation consists of clothing.
Eventually, all of the tags associated with this Donation are processed (i.e., each "pile of stuff"). When the system encounters the barcode "4444432", it knows that it indicates a DISPLAY STATION so it causes the "records" associated with the transaction(s) that this employee has been processing to appear on *that* display. He can then opt to print a receipt for the Donor, if required.
If another pile of items is encountered (perhaps all of the carts, save one, were in use when the Donor arrived so the Donation may need to be weighed a bit at a time -- perhaps reusing the same cart!), the process can continue. The employee may visit some
*other* display station and "summon" the record of the transaction (so far) *to* that display.
The point is, the employee has more flexibility in how he handles the donation. He can scan the Donor's ID at the beginning, middle or end of the transaction -- he's not *forced* to do "A, then B, then C". He can scan ("identify") the cart/conveyance
*before* weighing or after. He can identify the types of goods before, during or after weighing. etc.
And, he can't "accidentally" put "data" in the "wrong" field (important in allowing you to reduce their cognitive load; this is particularly important if you are counting on volunteers for this "labor"!)
You can then reconstruct all transactions just from an "event log": barcode scanned, time of scan, scanner.
It also lets you audit the data after-the-fact. E.g., if certain "weighings" always seem low, they might be traced to having been performed on a particular scale. Or, conveyed to that scale on a particular *cart*. Perhaps the scale is out of calibration? Or, the weight of the *cart* is incorrect (in the database)? Some of these may be recoverable actions (e.g., if a gain/offset error is identified in the scale, it may be possible to adjust the recorded weighings to reconstruct the *real* weights. Or, if the cart's weight is "off by 100 pounds" (think pallet jack), adjusting that would allow you to "freely" correct the values in error (assuming you can correctly *identify* those in error!)
It could also alert you to training problems ("Tony doesn't seem to get things weighed properly") or *theft* ("Five guys out there working yet Bill's AVERAGEs are always significantly lower than any of the other guys. I wonder if he's cherry-picking through the donations?")
You have to look at your own process to see how you can exploit this sort of "self identifying" capability. E.g., "Tim" scans his badge at the *generic* barcode reader station in the PCB wash area and the System now "knows" Tim is the one who is scanning the data for boards going through wash. The ID's of each board differentiate themselves from Tim's badge simply because they have been "registered" as being "boards" and not "people"!
I have been looking at a few Excel commands like Lookup, Vlookup, Match, etc.
Our barcodes (serial numbers) are preprinted and never ambiguous to another field.
Once a serial number exists, I want to rescan at a later point to the correct line and ideally enter default data. For example, the thru hole operation might just scan an existing serial number and fill in the date and operator. The operator might do this for a batch of boards that were just finished.
This would be faster than manually entering data and avoids Don's issue of entering into the wrong cells.
We will also be keeping data on each build lot. This will help us track average time (cost) per operation per unit.
Spreadsheets are relatively easy to design and flexible but ultimately a bit kludgy. I was hoping that someone has a inexpensive, more refined solution.
A long time ago, I wrote a parts management system in DBASE II & III. It was way too much work and I never regretted replacing it with Parts & Vendors which was very inexpensive and worked better. I would rather not reinvent the wheel, especially if my "wheel" is not that special.
Thx
Al
Don Y wrote in news:lof2no$qdr$ snipped-for-privacy@speranza.aioe.org:
ElectronDepot website is not affiliated with any of the manufacturers or service providers discussed here.
All logos and trade names are the property of their respective owners.