I am not even sure which heading to put this under FPMI or FSQL. I am leaning toward FSQL but again am unsure. Here is my question:
I am looking at doing some changing on some of my project. However I know where I want to start, and I know where I want to end. It is the stuff in the middle that is throwing me a little. I would appreciate that whomever reads and responds to this can put the solution into somewhat laymans terms. I will do my best to describe what the change will entail.
We have Microsoft SQL 2005. There is a table which contains all employee ID numbers. Currently I have a screen in FPMI where the supervisor does the scheduling and assignments. When we first started working on this system this schedule was an easy way, but since starting a 3rd shift and all the other increases that have gone on this might not be the best approach any longer.
What we were talking about doing at todays meeting was to have the employees arrive at the machine, they would scan their badge, the PLC or course is connected to OPC then to FSQL then to the Microsoft SQL. Well anyway once the employee scans their badge at the machine level it would send the ID number over to some point, most likely FSQL where it would do a compare of the ID number. If the ID number matches one in the database then an enable type signal of sorts will be sent back to the machine. So my big question is would an idea like this work? If so, how?
I mean I can scan the data at the PLC, that is not a problem. And I can send the data back and have it go all the way to SQL. But from this point I need a push in the right direction. What help can you supply in regards to this undertaking?
What you’re describing seems relatively easy to implement, just perhaps a bit unintuitive. Here’s what I would do:
-
Bring the barcode data and the write-back point into a FSQL group. Set the items as “read-only”. Set the group to update the first row of a dummy table - it won’t actually be writing to a table in the DB.
-
Create an action item. Set it to “SQL Query” mode. The goal here is to write a query that uses the barcode input and results in a 1 or 0 that we can write back to the write-back item. To do this…
o When writing the query, you can hit “CTRL-Space” or right-click to insert a reference to the barcode item.
o After enabling the “write back to item” field, you can again hit “CTRL-Space” or right click on the text box to add a reference to the write-back item, which will cause the result of the query to be written to the OPC item.
The actual query could be as simple as:
SELECT count(id) FROM employees WHERE barcode={barcode_item}
Assuming that your table is setup correctly with unique ids, you’ll end up with 0 or 1 depending on whether or not the barcode exists.
Hope that gets you going,
Colby, thanks for the quick reply. I do however have one other question in regards to this same process. I am wondering can this same type of scenario be done within a block group?
As you may recall from seeing the project we have a block group that send the data to all the production floor equipment. So do I need to break that apart again or can this same scenario be used on block groups?
Thanks again and have a great day.
I guess I should start by asking you for more information about what you’re trying to transfer. The way you originally specified it, it sounded like you had one datapoint down (the barcode) and one up (the OK bit). In this situation, a single standard group is appropriate.
Are you trying to send this data to all of the devices? Do you have to read from any given PLC? What does the OK bit actually do in the PLC?
Regards,
Yeah that is one area where I need to work on my communication skills. I need to try to make things more clear. So I appologize for not being as clear as I required. I will try to make this post as clear as possible.
On the production equipment there is one plc per machine. We have upto 99 pieces of production equipment. Each piece will have its own IP address and its own set of tags.
For our current setup we have a screen in FPMI where the supervisor goes and does the schedule for the daily shift. While they are editing this it changes the data in an SQL table. Once they have done this and are confident this is the layout they then exit out of the edit screen. On another screen they have a program/load button. When they press this button it takes the data from the edit table and copies it over into the download table. THe download table is the one that is connected thru OPC to each PLC unit on the floor and is keyed off of the machine location number. So data that was editied for machine location one is sent to machine location one, and so on. While this method works some of the mgmt persons have argued that to sit there and assign between 20 and 40 machine locations upto 3 persons per machine location takes to much time. They dont want to have to go thru this every morning. I have tried arguing, to no avail that since they know the day before the next shift what their goals and such are they can go into the edit screen and preset it up. This way the bulk is done prior to the next business day. But alas no one wants to hear this they want it even more simple. Which beings us to the proposed way of wanting to do the schedule.
What they want to be able to do is just send the people to the machine location. When the person arrives they will scan their ID badge. At this point in time the PLC will send the data back to the SQL and compare it to the database of employees. If there is a match it will send out a flag that the number was valid and will allow that employee to complete the login. If there is no match then the login is denied. Now granted there will be a number of machines attemping to do this at one time so I am concerned with bandwidth and such but it still should be within available parameters or so I think.
Now as for why the login, well we are a bulk mail mailhouse, we charge the customer by the amount of work we produce for them, so the data of amount produced must be fairly accurate. Also some of the clients we have do what is called walking route and/or match mailing. In these two cases real strick criteria is in play so we dont want someone comming along and operating the machine and possibly messing up the layout. Which is why we have the machine disabled unless an appropriate person logs in. This way we can see whom was on the machine last and what they done and we also keep tabs on the amount of work performed.
Hopefully this clears things up.
We do have a historical group that is triggered off of a start collect bit. So this group would probably not be the best area to place the action item it, or does action items run independantly of what the actual group setting are?
What type of barcode scanner are planning on using? I think it would be easiest to have scanners at each FPMI client machine rather than in FSQL. In FPMI you can auto login and open up a scan window where you scan into a non-editable text field that will check the users creditentials in the database. Once the scan goes through it can open up the necessary windows otherwise it will just close or stay at the scan window.
The barcode readers are located at each piece of production equipment. They are used for the employees to log into each work center (machine). They are tied to a PLC at each work center which collects the data about how the machine is operating. If a valid person logs into the PLC it will enable operation of the machine. As mentioned the current way is a person of mgmt experience (QC or Supervisor) will log into FPMI, the will go to a certain screen, on this screen they will select which employees go to which work station(s). This is the part the mgmt types are complaining about. They say it takes to long now that we have almost 50 machines tied online. And the other problem is that on other shifts we have more contract workers than regular workers and the way the contract is laid out is the HR dept calls up and says we need X number of workers for tonight. So they do not know exactly which group of workers they will receive. So to do this type of scheduling they can go thru and put the people they think are arriving, but if those are not the ones then they have to go back and do the schedule again, and since the PLC’s are not loaded with the correct worker assignments that means these people can not log in and start to operate the machine. This upsets the owner if he see people standing around. So an idea that was brought up was instead of having the supervisor go thru and assign the employees why not have them validated against the SQL table which contains the same information as used in the assignments screen of FPMI. This way the supervisor would not have to assign the people they could just clock in at the time clock, goto the machine the are told to, scan their ID in the bar code reader, if their number is valid and in the SQL table send back an enable signal and allow operation of the machine. The bar code readers are not used for any type of login or other operation within the FPMI environment. Hope this helps and clears things up. As Colby pointed out it is fairly easy to do it on a one to one ratio but how would that affect the fact that I use block groups? Can the same type of scenario be applied to Block groups or will the blocked portions have to be broken back down to individual settings?
Thanks again and have a great day.
Ok, the barcode scanner is tied to the PLC. You can trigger a group in FactorySQL when someone scans their id and you can write the id number to a database table that basically has two columns, one for station and one for current id number. The first screen you see in FactoryPMI reads the table and displays the id number. If the number matches the id number assigned to that station it has a finish login button or logins automatically. If not you can display an error that says id number does not match the one for the station. There could also be a screen to allow supervisors to assign id numbers to stations.
Yes, the method that Travis just proposed would probably be the easiest: Write the barcode to the database, and then use PMI to make the decision as to whether it’s valid - the login screen could check the row for the ID, and then enable a “finish login” button if everything is good.
Upon pressing this button, the enabled bit could be written using SQLTags. That is, you would have all 100 enabled bits as SQLTags, and then you could just write to the appropriate one in the button’s script.
Using this methodology use could use a block group to bring your scanned barcodes in from the PLC.
The big problem with the original post that I mentioned is that block groups don’t execute action items on a per-line basis. Therefore, you could write data down and back up, but there isn’t a good way to execute the check in the middle. If the user has to be standing in front of a PMI screen to do this anyhow, I’d do the logic there.
Regards,