I have a line where our machine will not output a state change for rework .
Meaning I get good outfeed counts when an item is passed through more than once .
We are seeing issues where the line has packed 15 physical items but we may have recorded a count of 17 as one of the items had to be processed twice for some reason (sometimes just in error the machine is cycled twice after break etc ..
This is something that we will have an issue with for many of our lines so I want to build something scalable to handle this.
My architecture is -
Highbyte being used mostly as an MQTT broker but also picking up other data sources and outputting to a standard MQTT topic for ignition to handle
Then ignition to ingest in to our MES
MQTT tags are
Outfeed count
Infeed Count
Waste count
State
State code
Production order
Material
Serial number
These feed in to a UDT that we then update the MQTT path in to automatically populate all of the tags on each line.
I want to identify when a serial number has been seen on a machine more than once & if so change the outfeed count to waste count.
I want advice on the best way to do this - should I handle it in Highbyte and convert the tag prior to it coming to ignition or do I do it in ignition.
My thoughts are to manage using an SQL table that has the columns:topic namespace,serial number , seen before and a date time stamp
Then lookup against the topic namespace within a date range of 3 months from now backward to see if the serial number is unique
I want to be able to read the database to see if it is unique if it is write a record if not update the seen before column with a count of 1 - if the seen before already contains a number increment by 1 then update the outfeed count tag to 0 and the waste count tag to 1
Or something similar. I am going to brainstorm with my team tomorrow but also wanted to reach out here to see how you all would or have approached similar problems.
Ideas welcome!
I probably don't understand all the constraints of your project, but my inclination would be to record the raw data coming in and sort out the repeats during the reporting queries. Make sure you create the appropriate indexes to optimise the queries.
I have done something similar to this to force a machine to reject a part that it had seen before. It’s called part tracking and done in many industries.
Make sure your table has a primary key (for indexing) and I would have both a “createdTimestamp” and “updatedTimestamp” column. Also add in any other production data that you might have, like a batch number or order number.
If the table is going to get big then the topic namespace list should be in a separate table, with a foreign key to the main part table (read up on database normalization).
Make sure you install the database on a separate machine so it doesn’t fight Ignition for resources.
As for the part count adjustment I would think that would be at the machine level:
- Machine requests Ignition to process the serial number and determine if duplicate.
- Ignition returns True/False to the machine.
- Machine does what it needs to do with that information.
Since you're already using HighByte, it makes sense to handle this logic there before the data ever reaches Ignition or your MES - meaning both systems receive corrected counts from the start, with no compensating logic needed downstream.
There is some functionality that you could lean on in your pipeline:
-
Pipeline State Variables — persist key-value state to disk across pipeline executions and restarts. A crude but fast way to keep track of what has been processed and execute logic against. A potential place for cycle info or serial numbers.
-
Embedded SQLite — in-memory or disk-based, can be used as relational “scaffolding” similar to the state variables above to locally execute Pipelines against. A potential place for cycle info or serial numbers.
-
On Change stage (with Persistent Mode) — detect when a serial number value actually changes vs. re-fires
-
Read stage — call SQL queries / stored procedures or use CDC to detect and replicate changes. An external database could be a potential place for those previously used serial numbers.
-
Write New stage — dynamically create or update UDTs in an Ignition tag provider based on metadata (e.g., line identifiers)
Let’s first work backwards from what ultimately needs to consume data and what might constrain the solution.
-
Ignition usage: Is this feeding an HMI that needs cycle-by-cycle accuracy, or a periodically refreshed production tally?
-
MES integration: Is the MES a database you write to directly, or a specific application? Does it expect individual cycle transactions or batched updates?
-
Cycle time: How fast is the line running? What's the expected event rate, and does your database need to handle that load in real time?
The two approaches others have suggested are both valid. The right choice depends on your answers above.
Periodic / batch pattern: One pipeline publishes raw events to a database as they arrive. A second pipeline runs on a configurable interval, queries with your deduplication and count logic, keep track of what has been processed, breakup / process the results, model for targets, distributes to to targets — dynamically updating Ignition UDTs, posting transactions to the MES, etc. This scales well to fast cycle times and many lines because the processing is batched. The tradeoff is latency: counts are accurate as of the last query interval, not the current cycle. This may or may not be an issue depending on the speed of your process and how users are consuming data.
Event-driven / cycle-by-cycle pattern: A single pipeline handles each incoming event in sequence — detects the serial number change, checks it against a stored set of previously seen serials (held in external database or, for performance reasons, Pipeline State Variable or SQLite), corrects the outfeed/waste values if it's a duplicate, models for targets, then writes corrected tags/UDTs to Ignition and posts to the MES. This gives you real-time accuracy in the HMI but requires more careful pipeline design at higher event rates.
With either approach, the deduplication and reclassification happen. Ignition and the MES only ever see correct data.
Use a UNIQUE constaint on what uniquely defines a record - it sounds like your serial number is that. Then you can write what is called an UPSERT statement within jython where you can do something like the following with seenBefore
try:
# Insert statement sets seenBefore value = 1, lastSeenTimestamp=system.date.now()
except java.lang.Throwable, e:
if "unique constraint violation" in str(e.cause): # This will depend on your db
# update the appropraite record and sets seenBefore +=1, lastSeenTimestamp=system.date.now()
Define your data integry in the database. Thats the only place you can do it where you can be 100% sure it will mean anything.
Some databases let you write UPSERT statements directly but I do like separating it within jython as above personally so I can do extra logging if needed.
In your situation where you had 15 boxes but 17 records, what you would have now with the above is 15 records, but 2 of them would have seenBefore with values of 2.
You probably want to incorporate @lane.duncan createdTimestamp and updatedTimestamp as well where updatedTimestamp is essentially your “lastSeenTimestamp” - the little extra bit of data can help to determine when something was first created and when it was last seen.
1 Like