Help with MySQL

[Pulsar2003]

Hello,

In my search on this forum I found one relevant one that shed a bit of lite on what I need, it’s: New to SQL.

Around Christmas I downloaded MySQL. Lately I’ve been trying to create a first database.

I know that this is Inductive Automation forum and the product I work with is Ignition. Probably I should seek more help on their forum…

From “New to SQL” I found out that I also needed to download “Workbench”. I found some tips in the help. I got to say that it’s rather well done.

Also in Travis’ Webinars he often work with existing databases, the best example is the one that lists clients. He often uses it, makes modifications etc.

Now I created a database for the “STATION #1”. Production status will be stored there. The employees ID who worked there, the product being assembled, time to complete an assembly and time stamps.

I added two examples made ruffly in Excel. The first one is for a list of employees which is rather simple. The second one is quite similar to what we have already. From these two databases I can easily make tags for most of the projects. It would be easy to take a drop down text box to select an employee and a product to associate it to a work station for an example. These informations would be stored in some N7 files in the PLC. Where it’s interesting for our factory is to be able to associate the ability of an employee to complete a task and also evaluate the whole group for a particular product. Make some statistics!!

Here are some questions:

1. Can new databases be created directly from an Ignition Client?

2. Can all the columns be created directly from an Ignition Client?

3)Is there a way to fill an entire row with lets say three or four columns (index which is automatically done, name and ID)(Index, item,client’s ID, description, box per palettes)?

I want to start only with these few questions to avoid overwhelming those who offer their help. I probably will have to answer questions and will definitely generate some.

Thanks.

P.S.: desp, lann, picp, turm is for the three first letter of the last name and the first of the first name. So my last name starts by DES and my first name is Patrick. Just a hint.

[AutomationNC]

I’m not sure I exactly understand what you are doing and I am far from a database expert, but I will make a stab with the questions I can answer. First, since you are new to databases, understand one small bit of terminology that may help. You mention database several times in your question when actually you are referring to data tables. With mysql a database is made up of one or more data tables. Your excel spreadsheet example is a good example of two of the data tables you want to create in your database. A little nitpicky, but using those terms properly may keep you from having a misunderstanding in the future.

There are a couple ways you can push and pull data from the database in Ignition. You can do it via scripting using SQL commands and via transactions. I understand the transactions to be able to create data tables directly in cases where they dont exist though I have never used this feature. The number of columns created is dependent on the configuration of your transaction. I dont know of any ways to create a data table from the scripting language. I tend to like to setup my tables manually and then operate on them. You can use MySQL workbench to achieve that. Transactions and the scripting language can also be used to fill your rows as required - also update, delete and select them. You may want to read up on the transactions and the scripting commands available (system.db and system.dataset are two you will need to be familar with). You also may want to find a good site on SQL language to better understand some of the scripting functions.

[Pulsar2003]

Automation NC, you are correct. I did make a silly mistake by associating all to database and not data tables.

For a new comer it’s a bit overwhelming. There is 15 hours of webinars. I try to be organised. When I come about some details I know I’m going to need I take down some details, the webinard’s session number and the time elapsed. That way I can narrow down where I need too look up again.

[color=#4080FF]Perhaps I should construct a Window where it could be more visual than just words and explanations. Before I posted a reply I did one quickly. At the bottom of that window there would be what is already stored in the PLC…
[/color]
I could simply use Ignition to write to the PLC and use an Excel sheet as a cross reference.

N7:0 = 1; N7:1= 0 ; N7:2 = 970-017; N7:3 = 1 etc.

For me that would translate as: Station 1, employee #0 (lets say Peter), Product code is MTV 2K DLX Red, type of work would be assembly. Sure you can remember all of this after a while but it’s not user friendly and there is room mistakes.

It would be much easier to use an EXPRESSION to do the translation and show text instead.

It would also be much easier to use a dropdown input to write to the PLC.

So now I need to invest more time learning more on MySQL. From the little I need to do and at the stage of learning I am in I don’t think that I need to learn so much about scripting; it’s more the HOW TO.

Lets see what comes next.

Thank you again.

[AutomationNC]

Ok, I think I understand what you are trying to do. Maybe we should talk about why. What does the PLC do with this information? Does it act on it in some way? If not then why not just interface with the database and leave the PLC out of it. I think what you are doing is pretty straight forward. You will probably need 3 tables - employee and product that are just lookup information and one to put information about type of work and some of your statistical information. The third table will need to be related back to the first two on your reports which brings of the point of related tables. If you are unfamiliar with that term, then add that to your homework. It is basically a method of tieing together related information that is stored in different tables. You dont have to fully understand that to get started collecting data, but you do need to make sure you have a field in your statistical table for the employee number and product number. Those will be used to tie back to your other two data tables. Keep in mind that you are learning and you may have to back up and redo some stuff as you learn more. It’s my belief that doing is the best way to learn so you are on the right track there. So that said, your statistical table could have the following columns: statisticalid, employee, product, worktype, startdatetime, enddatetime.

I think this is probably a good first project for this kind of stuff. Just handle it in small chunks like any automation project. Break what you have to do down into manageable parts and tie them all together in the end. Such as:

1. Research and create tables

2. tie your drop down lists into your employee and product data tables. You can pull in 2 columns and show just one. In this way you show your employee name but also have easy access to the employee number if required for the PLC or statistical data table.

3. get your radio buttons working with data to tag/PLC (if necessary)

4. research event scripts for your accept button.

5. research system.db scripts and write script for accept button to insert into statistical table

6. finally you have to come up with a way for the operator to indicate they are done and update the statistical table.

You have some learning to do, but when you break it down into 6 tasks, it becomes more manageable. Sometimes with a new piece of software its hard to figure out what the tasks are. Good Luck.

(also, I’m sure you know that you cant store a number with a dash to a integer in the PLC)

[Pulsar2003]

Hello again AutomationNC,

Before I forget it I want to point out that yes indeed I know about the integer files. I still have from -32768 to 32 767. So, for example, 970017 is too high as well as 971017. For this particular client the identification starts by either 970 or 971 which is a variation; the ending starts at 001 and ends at 042. In an N7 file I can easily store either 0017 or 1017; the 97 is dropped. I didn’t go all the way with these details as I didn’t think it was relevant, but you caught it right away. I give you 100% on that.

As for the use of the PLC!! Lets get to that.

I will repeat myself as I have a few topics on that same project. At each stations there will be two buttons, one is a on and off while the other is a momentary opened. Once the employee arrives at the work table and is ready to work: ON. This action gets a “time stamps” in a database for the log time. So we can know at what time the employee was ready to get to work. At the same time a RTO timer start adding up the seconds.

Once that employee has completed the assembly, he or she presses the N.O. The action here is more relevant. This takes the stored values in a few N7 files and copies them into a [color=#FF4040]data table[/color]. Yes, a data table. The station number, product id, the total time to assemble, the total count of completed parts at that time (C5), time stamp are now some real data that can be verified and later used for statistical reasons.

Another interesting thing is that this allows us to have a count for each stations to be seen on a CLIENT. All the employees on the assembly line can see their count as well as the others and the total count till then of the day(a total for all the stations).

What is also interesting is to be able to see how an employee performs on a particular type of product, each different products can be compared, etc.

A very interesting feature is that no employee cannot really cheat as there is the fabulous “time stamp” option in the action field. No one can increment their counter if the assembly is not complete. If the mean for assembly for a particular product is 22 minutes well there is only 11 minutes between the increments that will stand out. I think I don’t need to write more on this wonderful feature.

I was to buy a few not to expensive counters on Ebay but as I got the project futher in my mind I knew it was not good enough.

Now to change the subject just a bit, the ACCEPT button (forgot to erase the OK) is just to get out of the window/popup. Sure I don’t know yet but I’m quite certain that it is easy to move the selected values to the targeted N7 file.

Does this project make sense?

Thanks.

[AutomationNC]

Yeah I figured you understood the integer file stuff, but I have learned to never assume. Better to just point it out or ask.

So the process is this:

1. Employee or someone goes to the SCADA and sets up the station for employee, product and type of work. That information is written directly to the PLC.

2. Employee goes to their station, presses start button and begins work. This button is maintained pushbutton. Not sure why but I assume that gives positive feedback to the worker. I lighted pushbutton might give more visible feedback but you know your plant and I do not. It might provide additional information to the operator such as no light if they fail to setup their station on the SCADA. upon pressing the PB, a database entry is created.

3. The rest is less clear. I think you are saying that after each assembly the operator will press the other button to initiate some sort of complete indication to the PLC. But then you mention logging number of parts completed so I am a little lost. Does the PLC increment the assembly complete from the PB or internally. I am ok with how you plan to log your data. Copying the data files to a separate group of registers that are ready to be logged makes sense. With all your operations happening in the PLC, you may want to look at transactions in Ignition. For what you are doing, they are fairly simple. They will create the tables for you if the tables don’t exist. They will automatically create date/time stamps
   on your records if you so wish. With what you are doing, I think I would have a transaction to create a record and one to update that same record. The way a transaction would work would be for you to have your registers you want to log be tied into columns in your table. The PLC would also have a way to trigger or tell the transaction to work. One PLC trigger would create a record at the beginning of the assembly and time stamp the start and another trigger would update that record when the assembly was complete. Having time stamps at the beginning and end of an assembly would negate the need to have a timer in the PLC track assembly time (though it might be a good backup). You also could use those records to determine when the employee started work as the earliest record would contain that information also. So I guess this adds transactions to your homework… You shouldn’t really need any scripting though.

4. operator pulls up the maintained PB when their work is complete

5. Operator or manager resets the station at the SCADA - necessary to make sure erroneous information is not logged

[Pulsar2003]

AutomationNC I think we are doing progress.

I will go with what hits me first (probably because it’s written farther in your response…). The reason why I have to use a RTO is because at the end of the shift they clean up. At 15:50 all assembly ends. How do I keep track of the time spent on the part currently being assembled? To make it user friendly it’s easier to use this type of timer. The next morning it will just pick up where it was at and will be RESETED once the part is completed (i.e.: half assembled at 15:50, next morning the next half is completed).

I want to point out also that when the employee is away on break time, bathroom or lunch they will have to switch the button back to off. This will maintain their available time more obvious. The idea is to put a bit more stress on each worker. Some tend to rely on the group to keep a good record. This will probably stop them from conversing and some times play trick to one another.

I had to read the third enunciation a few times to make a mental image of it.

The PLC will keep track of the increment. I have the ladder but all the tags are in French. I don’t know if we can attach a *.RSS file?

I do like your suggestion of using a transaction group. If I understand well it’s there that the details of what is being done, what product is being assembled at what work station. The PLC’s inputs will be the trigger for the group to act.

Station #1:
I:0/0 is active = the data table would register the time stamp.
I:0/1 is pressed = the action will be to add a line to the table with the proper information recorded in each columns. The ACC in the respective RTO will be copied to the table before the (RST) happens.

[color=#FF4080]With all your operations happening in the PLC, you may want to look at transactions in Ignition. For what you are doing, they are fairly simple. They will create the tables for you if the tables don’t exist. They will automatically create date/time stamps
on your records if you so wish. With what you are doing, I think I would have a transaction to create a record and one to update that same record. The way a transaction would work would be for you to have your registers you want to log be tied into columns in your table.[/color]

Would you happen to have an example? A *.proj that you can share?

There are 15 hours of webinar, I’ve watch most at leat two times. This means the I have more than 30 hours of stuff in my mind and I get confused some times. It even fell like I’ve been married to Travis. Worse part: I don’t even know what he looks like Just a bit of humor here.

You can also check out another one of my post: Tags problems in Ignition Designer 7.2.2

I’ve done some progress with MySQL.

So I’m on track.

Thanks.