Hi guys I am after some design help with a barcode project. I have a MS SQL server 2008 with a database that has a table with hundreds of EAN128 barcodes for products. I have connected Sick scanner and have succesfully scanned value into OPC tag in FSQL.
What I want to do is compare this with table of products and send either a 0 or 1 back to PLC to make a desicion on what direction to send carton. Also want to log all scanned cartons for end of shift and transfer back to 3rd party software that is connected to SQL.
Operator may need to sometimes select a barcode(carton) to go in other direction as well.
Can someone help with ideas on good design/principle on set up of this.
A lot of this depends on your requirements…we can get into specifics if any option sounds good
Your scan every 4 seconds would work with a triggered group that uses action items to write the 0 or 1 based on a DB query. You’d also want to use a handshake to let the PLC know which read you’re acknowledging. Does everything necessarily stop if the PC quits responding? I don’t get a warm fuzzy feeling if the process requires a very high availability - uptime depends on the PC, database, and network.
Alternatively, you may be able to “load” the database or part of it into arrays in the PLC, then have it do the “heavy lifting”. With FactoryPMI you could still allow the DB to be user editable. If you have a way of partitioning the data, you could trigger a “load” of subsets of the data from the DB to the PLC via FSQL. Of course, if the PLC has enough memory just load it all. The advantage here is that the PLC doesn’t depend on anything external to operate.
As far as the logging goes, a FSQL group would work fine. You could even use a handshake so that operators could be alerted, or the process could be stopped after some certain amount of missed records (which could be one). You could even set up a triggered queue in the PLC. Chances are that this group will be working as long as the first option is.
Nathan thanks. Just got the EAN info from company and they have over 4000 product codes, so storing in PLC might be to taxing on memory when comparing with 5 digit product code and 5 digit comapny code (company produces product for differnet companies with same product code, so both have to be compared!!)
If connection is lost, PLC code will keep cartons flowing, they may just go left instead of right (not a major problem for short periods).
What is a good way to design the selection of products for left or right? Have the operator scan down the table of products and then have extra column for selection? Is this done thorugh PMI?
You'll have another column in the table that indicates left/right. I would store this as a 0/1. You'll probably want to use a spreadsheet to set the initial values in the database, then allow operators to make changes via FactoryPMI.
I'll post again with more specifics on implementing the FSQL piece of this.
Consider the following in FSQL. {p_code} is the tag that represents the latest barcode scanned. Tag substitution is done with right clicking in FSQL or typing the name in curly braces.
Drag your “OPC items” (PLC tags) into the group. Set them to “read only” since they won’t be stored in the database. This includes tag locations that you want to “write back” to.
The crux of your group is an action item (SQL query) that looks something like this
SELECT direction FROM table WHERE prodcode={p_code} and compcode={c_code}
2a. Use another action item (expression) or fix the query to protect from NULL values, catches with the different codes, etc.
In the final action item, you’ll enable “writeback” (Store result to DB or OPC…) of the result (Left/Write) to the PLC.
The group should trigger on {p_code} or an expression combination containing it.
You can have the group: reset the trigger, use a handshake, or writeback the result from another action item to acknowledge completion to the PLC. I would suggest that you handshake {p_code} to another register that the PLC clears upon acknowledgment.
Play with the different concepts separately before trying to combine everything. It should become pretty intuitive once you get started.
