Design Like a Pro: Combining SQL Databases with HMIs

About this Webinar

You probably know that SQL databases are great for storing data, but did you know that the power of SQL can dramatically improve your HMI development? In fact, there are many powerful ways to use SQL databases in conjunction with your HMI/SCADA system.

In this "Design Like a Pro" webinar, learn how to supercharge your HMI/SCADA system by leveraging the power of SQL databases to accomplish tasks that simply couldn’t be done otherwise.

Expert HMI/SCADA project developer Kevin McClusky of Inductive Automation will explain and demonstrate creative ways to use SQL databases and HMI/SCADA together, including:

• Building dynamic HMIs
• Document storage (manuals, SOPs, instructions, etc.)
• Electronic records instead of paper
• Inventories and lists
• Operator notes and feedback
• Adding Wikis in your HMI

Webinar Transcript

00:00
Don: Good morning, everyone, and welcome to today's webinar, Design Like a Pro: Combining SQL Databases with HMIs. I think we got a good program lined out for you today, appreciate your time and attention with this this morning or afternoon, depending on where you are in the world. My name is Don Pearson. I'm Chief Strategy Officer with Inductive Automation, and I'll just serve as the moderator for today's webinar. I'm gonna start off by briefly introducing our company, a little bit about our software, Ignition, and then of course, our guest speaker. And then we'll move through the agenda that we have lined out for today. We wanna talk about some of the principles and practices for using SQL databases alongside your HMI.

00:38
Don: Just a little bit of background on the company, Inductive Automation. We were founded in 2003, been around for a dozen years. And we're actually very pleased because the response to our software platform, Ignition, has been extremely positive from integrators and industrial organizations from around the world. We serve the HMI/SCADA/MES software needs of a variety of enterprises across a large number of industries. In fact, I don't even think it's... It's possibly virtually every industry; oil and gas, water, wastewater, food and beverage, transportation, data centers, packaging, any database kind of work whatsoever in companies across those industries. We're also supported by in excess of 1250 integrators across, I think in excess of 95 countries now. So we've really found a lot of good responses to it, and we're very pleased to be able to serve this industry. Thousands of companies around the world are depending upon us for their HMI/SCADA/MES systems and have been. We'll continue to work to bring you, through our development and evolution of the Ignition platform, what you need to get the job done and meet the challenges that you face in your organizations.

01:55
Don: I just wanna take a second, really, to just maybe make a statement that sets the context for the subject of today's webinar. And additionally, maybe points out a little bit why Ignition is being adopted so quickly across so many industries. One of the reasons is 'cause our integrators are really realizing that the Ignition platform gives them an industrial platform that allows them to really build almost anything, the solutions that you need. It gives you the technology, the licensing model, and the architecture and flexibility with that architecture to do what you... Would just can't be done with other systems. It's powerful enough so that really, in your hands, you can take that flexibility and create almost any architecture for almost any industry you may want. And really, it has to be put in the context of how we started as a company because in the beginning, the company, it was really our... Our founder took a look and said he wanted to find a way, as a very first question, to meet a challenge, which was, "How do I get data into databases, out of databases? How do I get the bidirectional power that I need to make the connection to PLCs, the connection to the plant floor to make it so powerful that you can do anything you want and leverage the power that exists inside the SQL database?"

03:13
Don: So in the beginning of Inductive Automation, there was only the beginning legacy product, which was called FactorySQL, which is currently the SQL Bridge Module in the Ignition platform. It was, Inductive Automation was founded on the leverage point of SQL databases. And SQL Bridge is really, it's the differentiator that really puts the database in the middle. We used to call ourselves as data, the first database-centric, from the ground up, developed HMI/SCADA software. And that is a big differentiator. And really, over the years, we've been on the vanguard when it comes to empowering SCADA connectivity to databases and to really use the power of what SQL can give you as an organization. So it really unleashes a tremendous amount of capability, and you're gonna see some of that today with what Kevin goes into as we go into this, that's today's topic.

04:09
Don: And today's webinar is just our latest installment of our Design Like a Pro Series. We've had tremendous response from you in our Ignition community to want us to continue to bring these to you. And we're really dedicated to conveying really fundamental ideas, tips, best practices that can help you to build the best HMI/SCADA projects with Ignition that you possibly can. In previous parts of this series, we discussed, one was laying the foundation for a successful HMI/SCADA project, and developing with speed and precision, and about best practices for launching projects, also. In most recent installments, we looked at SCADA alarming, optimizing HMI and utilizing templates in our HMI/SCADA projects. But the whole series of webinars and white papers is available at inductiveautomation.com, if you haven't had a chance to be part of them in the past, they're certainly still available to you.

05:08
Don: Before I introduce Kevin and turn it over to him, I just wanna make just one more comment about SQL databases overall because it really is at the core and the root of what Inductive Automation was founded on as a company. And this device is just the next step in sort of an educational process we've been involved in, and we've had thousands of integrators be able to understand more how they can leverage, expand their knowledge of SQL. In fact, the entire nerve center of Inductive Automation is actually an Ignition project, it's our CRM system, and it is... Entirely leverages the power of the SQL Bridge to bring database connectivity to an entire organization as its nerve center. So today, we have a particular goal, and that's just to continue to do something we've been doing for quite a while, it began a dozen years ago, and that is to push the idea forward in a variety of different ways of how that SQL database is a perfect center for your automation system within your organization.

06:07
Don: And with that being said, as sort of a foundation, I wanna introduce Kevin McClusky. He's Co-Director of Sales Engineering at Inductive Automation. Prior that, he was a Director of Design Services here at Inductive Automation. He has a decades' worth of experience in working in the arena of a variety of different industries. So Kevin, you are the guest speaker today. I'm gonna turn it over to you, and you could tell the audience maybe a little more about yourself, and then get into today's topic.

06:34
Kevin: Sure, well, thanks a lot, Don.

06:35
Don: No problem.

06:37
Kevin: My name's, as Don mentioned, my name is Kevin McClusky, and I am here with you today to talk about SQL databases. I've worked with a lot of our customers, end users and integrators alike, to do lots of different things with databases over the years. As Don mentioned, we're a very database-centric company, we grew out of those roots, and that's a lot of what we're about at Inductive Automation. So with that said, I'll launch into... What we're gonna do is I'm gonna give you three or four demos here of different types of things that you can do with databases. But before I get into that, gonna give you a little bit of an intro to databases in general. So for those of you in the audience who aren't so familiar with databases, we'll go through a little bit of what that is. And then for those of you who are familiar with databases, we'll get into a little bit more of the meat and potatoes in just a little bit here.

07:37
Kevin: So one of the previous webinars in this series, the speaker talked about focusing on making your HMI system into the best tool it can be for the operator. When you introduce a SQL database into the HMI, you get the operator power to accomplish a lot more and a lot more easily. So as I mentioned, I'll start talking today about the database itself and how we can use it with the HMI. First, let's establish what a database is. It's basically a collection of data that's stored on a computer. And it's organized in such a way that's easy to access, manage and update that data. Most of the time that you hear people talking about databases, they're referring to what we call relational databases. A relational database is a type of database that's structured to recognize relationships among different tables of data and relationships with that data. This can help to eliminate redundant data, in some cases, if you're using database normalization. But the important thing is that it has relationships between different types of data to make that data associated with each other. SQL databases are the most widely used type of relational databases, and that's what we'll be focusing on today. These databases are called SQL databases, the ones that use the SQL language, because they use the powerful... They use the SQL language, which allows for very efficient querying and retrieval of data.

09:13
Don: Kevin, I just wanna interject here only because I know that many people are familiar, but some maybe aren't. Can you just take a minute, I know we've had conversations about this, just about a little bit more about the power of SQL language and what makes it so powerful, because I think that sets the context for what you're gonna be showing today.

09:32
Kevin: Sure, sure. So before SQL databases were around, a lot of the data storage was in flat files or was in files that you might have lines and lines and lines of data. You might need to search through the line 235, pull information out. What SQL gives you, it's a language that gives you a common way to retrieve information across all sorts of different databases. So if you have Microsoft SQL Server, MySQL, Oracle, IBM DB2, PostgreSQL, the list goes on. All of them subscribe to this standard language that all of... That you can write a query for a database and it will be able to execute against any of these databases. So you'd be able to write something in the SQL language, and it's a standard way of communicating with databases to very quickly pull back that information. So instead of searching through row 238, you might say, "I want the information where I'm looking at tank 23," and then it knows, behind the scenes, what information that is and just sends it back to you. It's kind of close to a human language type of language in terms of when you read it, it makes some intuitive sense, too.

10:47
Don: Great, thanks for elaborating just a little bit on that.

10:49
Kevin: Sure. So what else is unique about SQL databases? Here are some of the big features: They store data centrally. They're designed for large amounts of data. They make data available in real time, which is very important for the types of things that we're gonna be talking about shortly. And many people can access SQL data at the same time. That's not limited to one access per moment. So SQL databases provide more data to more people. But what kind of data? That's a question that someone who's new to databases often has. Well, the answer is basically anyone who knows anything about SQL databases knows they can store historical data. That data includes data such as your login history, alarm history, audit logs, tag history, and MES data. But the thing a lot of people don't realize is that SQL databases can actually store any kind of information. So you're not limited to that. This information that you're gonna store, it can really be anything. So this could include contextual data, inventory data, customer data, configuration data, documents and files, and just about anything.

12:21
Kevin: So given that databases can store just about anything, if we only think about SQL databases for the purpose of the data that we were talking about earlier, historical logs, that type of thing, we're not really using the databases to the fullest extent in the organization. There are creative ways to use the capabilities of SQL database to your advantage in an HMI/SCADA system. Blending databases with HMI/SCADA applications really opens up a world of possibilities for data processing and analysis because you can put your time series data in context with the rest of your enterprise. So get creative. So today, I'm gonna show you how to use some of the out-of-the-box ways of combining SQL databases with an HMI. And I really hope it inspires you to think of a few creative ideas of your own. Certainly, don't see the things that I'm presenting here as the full list of things that you can do because, as I keep mentioning, you can do just about anything. So none of the ideas I'll show you are really difficult to do, but in general, people don't tend to think of them because they just associate their database with storing historical information.

13:41
Kevin: So the first idea I'd like to discuss with you is dynamic HMIs, which is one of the more powerful things that you can do with your database. Usually, an HMI's screen is static, meaning that it doesn't really change because the components have been pre-configured. So a static HMI will always display the same components, even though the values of the levels within those components can fluctuate. You might have a screen that's designed just for a specific area of a plant floor. You might have a screen that's designed for four or five different areas, that's the same screen, but the components on there are the same components, regardless of what area you're looking at.

14:24
Kevin: A dynamic HMI is different because its display will change based on the configuration of the database. Its display is not just what you see on the screen, necessarily, but it's the components that are on the screen as well. You can use the SQL database to store this configuration. So whenever you add something new to the database, your screen automatically shows it. For example, if you're an integrator and your customer might have a tank farm, you could use the database to let them out of tank. And I'll actually show that as part of the demonstration here. You can store these items. Other types of items you might store: Navigational items, user preferences, themes, startup windows, etcetera, plant configuration like tanks, monitors, compressors, and more all in your database. And then drive the screen from there, so the database can dictate what goes on in the component so that components can be added dynamically from runtime, rather than being configured first in the designer application.

15:26
Kevin: So I'm gonna show off a little bit of that, and as I just mentioned, I'm going to give you an example with tanks here. So I'll come back over to my desktop and what I'm gonna do first is launch up the Ignition Designer and launch up a database software. So I have Ignition installed on the local system here, so I'm just clicking "Launch Designer" here and I'm going to pull this up. And at the same time, I'm going to pull up, I have MySQL installed here, so I'm going to pull up MySQL Workbench. So MySQL Workbench is MySQL's installation. So MySQL is a SQL database. So you may have MySQL in your organization, you may have Microsoft SQL Server, you may have Oracle. All SQL servers work in a similar way. This is MySQL and MySQL Workbench is MySQL's tool for interacting with MySQL. So I'm opening this up, this is going to give me a list of tables right down here where I can store data, and I'll come back to this in a second. And then what I'm opening as well is the Ignition Designer.

16:37
Kevin: So right here is the Ignition Designer, I just logged in, and I set up a demo project here already. In the case of this demo, I'll just walk you through doing this from scratch. In one of the other demos, I have something pre-built that I'll show you. But right here, I'll just set up a new main window, which is just a blank window. And we'll set this up to have dynamic tanks on it, so come across, I always like to make it look a little bit better than just the standards here, so come down tanks, center this, give it a slightly nicer font, maybe give it a little bit of anti-aliasing, something that's gonna pop a little bit more. I'll go up to a 24-point font. I always like to give it just a little bit of eye candy, but I'll come down now. And what I'm going to do is first show you a tank template that I have created, and it's right over here under indirect. So I have this tank template. I can pull this tank template out, and all of my different tanks can use this tank template. If you take a look on the left-hand side here, we have our tag database, and inside here, we have a number of different tanks.

17:54
Kevin: If you're familiar with the Ignition platform at all, you'll also notice that under the OPC browser here, this is straight OPC browsing to Control Logic's platform, Control Logic's processor that under the global, we have some UDTs inside that Control Logics that have these tanks values. So this is the live field, so yeah, it's not a simulator; this is something that we have running in our background here. And these are all different tanks that are inside there. So for when I pull out this tank template, it's connected to tank 100 because we're using index zero here. I can pull out the tank temple again and I'm... My apologies, I double-clicked on that template right there and pulled the template into a template. So I'll undo that right now. So right over here, the tank template, I'll pull that out onto the screen again, and I can pull several of these onto the screen, so this is tank 100, set this up to be tank 101 right here by changing the index to one. Then I can set, pull this out again, tank 102, tank 103. And this is building up a static screen, so you're gonna have six different tanks here if I actually set this up. So it went all the way through, tank 103, 104, 105 here, and I've got six tanks, tank 100 through 105. And then I could set this up to be indirect, so you could have a next button and it would go tank 106 through 111, etcetera, but the screen design of this doesn't change dynamically.

19:38
Kevin: So what I really wanna show you is, how can you make the screen design change dynamically? Well, the first thing that we'll do is we'll use a template repeater. So right over here, we have a component that we call our template repeater, we have a little search thing up at the top that lets you pick, search for whatever components you're looking for. And then this template repeater, I'm gonna give it a specific template path. So I'm going to give it our tank template, and I could give it a count of things that I wanna repeat, but that count isn't necessarily going to be what we want to see. So this count is going to repeat over and over again on the same template, so it'll be tank 101, 102, etcetera.

20:22
Kevin: I'm instead going to drive this from a database. So we're going to have a data set here. I'll fill this out specifically for these tanks. Each one of these tanks has a property that we called index. So it's a small I index right there. I'm just gonna set up my data set to have that index in here as well. So I'm gonna add a column, I'm gonna call it "index." And then for this index, I'm gonna give it specific tank numbers. So maybe I wanna show tank four, tank five, tank seven, tank eight, and tank nine on the screen. I have 24 tanks that I can play with. So you can see, we just repeated these and we have tank four, five, seven, eight and nine right there, and this is a dynamic screen, this has a dynamic number of templates on it. We're still driven, specifically, by this data set that I set up. So what if we wanna come from a database? Well, we pretty much need this information or database, and then we can allow our users to change that information if we wanted to.

21:33
Kevin: So I'll come back over to MySQL at this point. I'll switch over to MySQL Workbench, and then I'll come up with a new table here. So I'm going to create a table. This table, I'm going to call it "tank display," and the column is going to be "index," in this case, and I'll give that an integer. And maybe I'll also have this set up to do it so it would have different screens. And what that means is I could have different configurations based on what screen someone's looking at. So I'll give this something called "screen," and I'll call this a character there, so this will be a varchar 45. Varchar is a variable character 45. So it can be up to 45 characters in length. I'll hit "Apply." This writes the SQL statement for us, and this is our first introduction to actual SQL syntax here. So create a table, this is our database name, this is our table name. So we're going to create a test tank display, and then we're going to create an index, which we just did right there, which is an integer. NOT NULL just means that it has to have a value. This is a screen. This is our variable or a column called "screen" and it's going to be a character up to 45 varchar. And it can be null in this case. And then we're going to have a primary key. As you get more advanced with databases, indexes are very important, and primary keys can be those indexes. So that's what that primary key was referring to.

23:15
Kevin: So we have that index right there. I actually don't want this primary key in this case and... As Don mentioned about the end, we're going to have an offering for something that will give you a little bit more SQL training if you really want it. So I'm going to... So if you're interested in why I'm removing the primary key, that'll be a place that we can dig into that a little bit more. But so we've got the database now. We can see the tank display over here. And we have zero records in this database right now. So I'll add a few different records. What I'm going to do is I'm going to edit this and I'll write SQL to insert the first record. So I'm going to do "insert into tank display" and I'll give it the columns, in this case, "index screen values," and then I'll tell it what values I want. So maybe I wanna display tank 101 first, and then the screen, I'll say our main display right there. So I'll take out the column list, I don't really need that, and I'll just insert into these and it says, "One Row Affected." Now, if I display this, I can see that I now have one row in the database. I'll do the same thing again, and I'll add in tank two, I'll add in tank five, I'll add in tank six. Now, if I display this, I can see that I have all of these different rows.

24:57
Kevin: In order to work with these, I can pull this out now and I have everything that I need in order to display these dynamically on this screen, or I can come back over here, go into the template parameters here, and instead of having these just fed in like that, I can pull this from the database. So I go to "Database Browse," take a look at our tank's display database that we just set up there, and what I wanna do is I wanna pull in the... Pull in this data set right there. I hit "Okay" and you can see immediately, this is updated with those tanks that I have specifically set up there. So this is pulling, it's in relative mode. So if I go back to the database and I want to insert a new one here, I wanna add tank seven, just insert a new value unto there, automatically pops up on the HMI. If I wanna add another one here, nine, save that off, automatically pops up on the HMI again.

26:03
Kevin: Now, if you had a couple of different screens, so maybe you have a main screen and then you have a secondary screen, this secondary screen, you might wanna have different tanks on it. And so I'll insert a few into the secondary screen and then we can filter based on the screen that we're in. So tank 14, tank 15, and I started with 12 here, and I'll go up through tank 18. And as I enter these, they're all going into the database. If we take a look at the database table again, and we can see that now, we have a lot of different records. So main, we have those tanks to show; secondary, we have those tanks. If I come back over here, I can change my query so that that screen is taking a look at which one we're pulling from. So we can do "where." So you can see this is Select Star, we're selecting everything from our tank display table. And then I wanna say "where screen equals," and then in this case, I'll go, I'll start with main. So if I'm going "where screen equals main," it's gonna show us our main ones.

27:08
Kevin: So secondary is the other one. I might have a simple drop-down here. And then this drop-down, we might have a list of the different things that you can pull from. So "do main in secondary." And in this drop-down, if we pick main, we can feed that in here. So when we're binding this, instead of typing in "main," we can bind it over to that drop-down that I just had, which is right here, the selected string value. And now, if I switch this over to secondary, we get our tanks for secondary. Go back to main, we get our tanks for main. And you get the idea that you could certainly add these to the database, you could... You can add that yourself. You could come in and change the database. Or you can have it set up where the user has a screen where they're able to select another tank, feed it into that database, and modify their own screen.

28:15
Kevin: And so that is the first demonstration that I have here. I'll minimize this, switch back over to PowerPoint, and I have a few others as well. So the next thing that I'd like to show you, once I get my slides in order, is... [chuckle] Well, here's another useful thing that you can do with your HMI/SCADA. You can store documents. So you can store documents like machine manuals, work instructions, standard operating procedures forms. A lot of times, when there's a problem with the machine in the plant, the operator needs to refer to the machine manual and maybe the SOP document so he could deal with the situation properly. To find the documents that he needs, he has to go on the hunt for it, normally. Go to some other server that's not connected to the HMI, go to a network drive somewhere, have to take his eyes off the HMI, and maybe even search a file system. But what if you can have these documents accessible directly in the HMI?

29:26
Kevin: As I said earlier, SQL databases can hold any type of data. So documents are just data, so why not store documents there? You can store many types of documents such as Word, Excel, images, videos, and more. Databases can store any set of bytes, which is what documents are. Once the document is saved in the database, everyone who's connected to the database can access it. Well, you don't need to worry about shared directories, getting special permissions to a file server; the document you need is right in the HMI. The only setting that you might need to adjust is to make sure the database's max allowed packet size is large enough to upload large files.

30:10
Kevin: So I'll give you a quick demonstration of this as well. This is something that we've done in the past. So we had a demonstration already put together for this, and it's actually something that if you're already using the Ignition software, you can download from our website. It's right under the demo project. But I'll pull this up and walk you through how it works. So this is the main screen that we have set up for this, document storage. We have a folder list over here. And the way a user might use this is you might set up a folder, and this could be a pressing folder for the pressing area of the system. So I'm gonna switch the Designer over to read-write mode right up here. So if it's in read-only mode, you can't adjust anything with the databases, so I'm gonna switch it to read-write. I'll hit "Add" here and that gives us pressing. That's what that little error message was that popped up, I didn't have it read-write.

31:09
Kevin: And then I'm gonna come over, add a new tank, I'll hit "Okay." And then I might have different tanks for holding things, holding liquids that are going into the pressing machines. And then I might have a few other folders in here that I have as well, so whatever the equipment might be that is being used for pressing. So we might have a stamping machine right there. It could be 100 other things. Over here, if I hit plus, this is going to allow me to add a document here. And we have a few documents that we put on a flash drive, we have a few things that you're seeing there as well. But I will add in my flash drive here into the computer, and we'll grab a few of the documents, one of them's a... One of our PDFs, one of our white papers, and then we have a few other things here, too. So I'll add this home first, which it doesn't actually want to do an SVG, so I'll pull that back. [chuckle] So I'll come back to the computer and right here is the flash drive, and we'll add this Design Like A Pro Part One, Laying the Foundation. All of our previous presentations like this one are accessible online. So you can go to our website, you can access these if you... And this recording will be accessible online as well later on. So if you happen to miss the Design Like A Pro Part One, the Laying Foundation webinar, or if you just wanna see the PDF, you can download it from there.

32:47
Kevin: So what I'm doing is adding this to the database right now. If I switch over to the database, I can see inside the database that I... Before this webinar, I had set up a few different tables here. So I've got the documents table, and then I have a document folders table. And this document folders table is the folders, where the folders are being stored that I just added. So we've got nine pressing, 10 tank pressing, and ID 11 stamping pressing. These IDs are internal to the database, and they're useful for being able to select individual records. But this is the main area of the data that you're looking at. So this is the pressing folder and tank is inside pressing, stamping is inside pressing.

33:35
Kevin: So I'm adding that, I'll come back, and I'll pick another one here, pick one of these Ignition logos, add that. I believe that the PDF that I was adding, as I mentioned earlier, you have to add the max upload size to the database. I don't believe that I set that up here. So the PDF was a very large PDF, which is why it's not showing up here. But each one of these guys, this P&G, I can now take a look later, I can open this file, and it'll pop up in whatever photo viewer that we have. If you're using Ignition, you could even set it up where they showed this file inside Ignition, displaying it inside a component in Ignition. This one, the same thing. So this is our demonstration that we have with our Ignition logos here. And you could do this with any file type. We have this restricted right now. So you saw earlier that we weren't able to upload the other file that I had selected when I hit "Browse" right over here. And I went to the default directory, there was a "Home" file, a file called "Home" that I wasn't able to open. But if I come over here to "Home" and I hit "Add" and I switch this over, I can see the script behind the scenes. So what we're doing, we're taking this information, we have some allowed file types up here that we're letting it upload it, and I could just add SVG to that list, and it'll let me upload that SVG now. So I hit "Add" and "Home" SVG. Now, if I take a look at this file, I can open it up. It's a big old house. [chuckle]

35:22
Kevin: So all of these things are accessible and very easy to change, very easy to upload new files. And you'd probably get the idea that these aren't limited to an interface like this. This is just built out of components inside the Ignition Designer. These components can be moved around, change the location, put on other screens. And then you could also limit the selection down. So you could take certain files, we could copy this, for example, and put it over here on our tank screen and show all of the files that are related to these tanks. Or if you wanted to join that information, and you had something that said, "Associated Tanks," you could even show documents that were associated with any of the tanks that we're showing up over on this side.

36:07
Kevin: So that's the quick demo right there of the file storage. I'll come back over to this side. And I think I'm doing pretty good on time. If I have to rush a couple of the later demos, there are two demos left, I'll try to go through those fairly quickly. And apologies if I have to rush them just a little bit. But I wanted to introduce you to two more things that we can do here. So another way we can use HMIs and databases is to change how we record data. The old way of doing things is to record information on paper. A lot of companies are still using that old method. For example, there's a guest list at the reception area that visitors write their name on. And on the plant floor, the process information is being written down manually on a clipboard or a whiteboard. So why deal with that paper? It takes longer to search through the paper records, you can't really relate that information to anything else. Sometimes, you have people who transfer that information into a system. But if you have the ability to do it right in your HMI, why not?

37:20
Kevin: So we can switch from handwritten records to electronic data capture, and we can display live data instead of having to write it down. Then we can get that information from the database so we can find it quickly and do more with it. It shouldn't be hard to implement because you should already have enough computers and devices around that can be used for entering the information. So if someone's sitting right next to a clipboard, they're probably sitting pretty close to an HMI that they can walk over to. Or if there's a receptionist at the desk, they probably have a computer right there where if you were entering information into the system, you could put it in right there, or even someone could type something in on a tablet and you can have that available for somebody to check in manually. So I'm going to give you a quick demonstration of that as well. This one, I'm going to build from scratch, too. And what that's going to end up looking like is I'll start out with a database this time. And I'm going to end up with a table that I put information into, and I'm going to end up with a screen where users can type in that information. So we'll call this "process log," and inside this process log table, I will set up an ID, which is gonna be a primary key in the database. I'm going to set up a time, and I'll set this up to a date-time type inside the database.

38:50
Kevin: Databases have different types of storage. So we looked at varchar earlier, which is storing characters, storing texts that you might type in. We looked at integers earlier where we had the tank number. You can see in this drop-down list, there are a lot of different types. And if you're getting pretty advanced, if you have to do really fast data processing, you can optimize things by picking the correct types for different things. Most of the time, all you really need is either an integer, a character, or something that might be a float, something that's a floating point, a decimal. So you have a 23.5 or something like that. So in this case, I'm picking integer and date-time there. And then I'm going to add a few more fields. So I'll call this "process order." And the idea is somebody might be typing this in on the plant floor, and they might have a clipboard where they're taking down the process order and the temperature every time that something happens. So I'll switch this temperature over to a floating type. And that will allow me to put it in a floating point number. So that looks to me, hit "Apply," this is going to create that table, and I'll finish.

40:05
Kevin: And just for those of you who are getting to know a little bit more about databases, I'm going to add an index to timestamp. If you're ever going to search on this information, you should always have an index on the time, so I'll add that in here, too. And that's all set up. So now, what I wanna do is get that information into this process log. So we've got this process log table down here, I can see there's no information in it. So I'll switch over to the Ignition Designer. I'll set up a new window. And inside this window, I'll set up a few fields. So I'm going to insert the date-time and I'm going to insert a few other things. I have the ID in there already, if I take a look at the table format, I'll set it up to auto-increment so I don't need to actually pass in the ID, the database will do that for me. And then I will add in a few fields. So this text field's going to be the process order. And the next text field is going to be the temperature. And this temperature, I'm just going to set it up so it has a numeric text field right here.

41:24
Kevin: Before I go any further, I'm going to make this look a little bit better, give it a container right here, give it a simple-titled border, and we'll say "temperature entry." Each one of these goes in here. Expand this out. And I want to type in that process order and the temperature. I don't necessarily want to type in the timestamp because I want this to actually capture the time when somebody enters it. I come up here, add in a button that says "Save," and this will save it off to the database. So in order for that button to work, I need to put a little bit of scripting behind it. I'll type in an example process order. I'll type in a temperature, and I'll make sure that this box is set up to do a float instead of an integer so I can do a 23.5 instead of a 24. And then behind the save button, what I'm going to do, I'll take this box and that box and insert that data into the database.

42:35
Kevin: So I'm going to write a quick script, it's going to do a system.db.oneupdatequery. And prepared queries are always a good idea, actually. So I'll do a run prep update and I'll do an "insert into process order," take a look at, or process log is what I call it. So process log, and then I'll give it the field names that I wanna insert into. I want to do the time process order and temp. Time, process, order and temp, and then the values. And I'll pass in the current timestamp for the first one for that, that's going into time. And then question mark, question mark is going to allow me to pass in the two other values that I have on my screen. So my screen has process order and temperature. I'll do “process order”, temperature. And in order to get process order and temperature, I'm just going to grab those from my components right up here. So I'm going to say "process order equals'' what's in that text box, come into the container, text field, text, and then the temperature I'm going to set up so it's pulling from whatever's in that field there. So the numeric text field, and then this is going to be our float value.

44:13
Kevin: The last thing I wanna do after I have this is I wanna clear out these values, so I don't want people to see the same values there that they saw either. So I'll set that to be nothing, set that back to be zero; for the process order, it'll be blank. Hit "Okay" here. And as you write these, normally, you'll have an error or two that pops up, we'll see if I wrote it right here, and I hit "Save" and it looks like I did. So if I take a look at the process log now, there's my entry. I have a timestamp, I have a process order, and a temperature. Now, anybody can use this from anywhere on your network. So if I have a process order, that process order, and I do 23.7, hit "Save," and then I refresh here, that 23.7 has gone into the database. If I do another one, I can get that into the database. And now, you really have a form that you can use anywhere. I can copy this, I can paste it unto other screens. I can put it next to my set of tanks that I have over here. I can have my temperature entry right alongside everything else. And all that information is going to be stored. You can report on it, very easy to work with.

45:29
Kevin: The last thing that I wanted to talk to you about a little bit is something that we call SQL Bridge. So in order to pull in all of this process information, one of the most important modules that we have for Ignition is SQL Bridge. In fact, it's one of the building blocks of Ignition. The SQL Bridge Module has many uses, the most important of which is connectivity between OPC data and SQL databases. And this module accomplishes this through its high-performance historian, which is one way of doing it, and its transaction groups, which is the other way, and to move data bidirectionally between the PLC and databases, as well as react to triggers, right back to triggers to do resets, and a lot of different options that make the transaction groups really, really powerful. So it can do a lot more, and I'm going to show you a little bit of that right now.

46:29
Kevin: So right over here, we have our SQL Bridge Module installed, which gives us these transaction groups. And transaction groups, as mentioned, are one of the things that we started out with, with Inductive Automation. All of our software really stems from this. And it's a fantastic way of pulling in information, storing information, and making information available in ways that you can do quick reports on it. So I'm gonna set that up, a new transaction group here, set up a historical transaction group, and we'll call this tank log. This tank log is going to go to a table that will call the table tank log as well. And I'm going to take the tank information here and feed it in there. I'll make this one just for tank 100, so I'll do tank log 100 right here, and take a look at tank 100. I have all these tags in tank 100, just drag them right in here, pull the information from these, and you can see this has a target. And rather than walk you through all the things on this screen, all I'm gonna do is... It's set up to go every second, I'll hit "Enabled," hit "Save" right here, and this is going to start executing. We can see the executioner going up. And if I take a look at my database table, I can now see that information is automatically feeding into this table. So I get information coming in every second, in this case, every second or two, that is pulling back the temperature tank status, all of these tag values are just going into this table automatically.

48:11
Kevin: You can take that information, that's similar to our historian, but this is done in a way that it's very easy to query against this. You can take this information, put it on any screens, put it on any charts or graphs. But the thing that makes these transaction groups really powerful isn't necessarily the fact that you can do historical information with it, which is really, really nice; it's the fact that you can do it in a way that you control. So you can choose if this is going out every second. You can also choose if it's going on certain triggers, and that can be really useful for doing reports. So if we take a look at tank 100 and we had somebody had another tag in here that we carried about in tank 100, and let's just say that that tank 100 tag was something that had a trigger. Or let's say it was something separate, maybe you wanna record the information for all the tanks at the same time. So we can have a tag in here that was a trigger tag. And that was just a Boolean that when this went I, you wanted to record the information.

49:14
Kevin: So we can take this tag, I'll move this trigger in here, and I'll log to something else. I'll call this tank "empty." So this is gonna be our tank empty log, let's say. So every time that trigger goes high, I'm going to do something about that tank. So I'm gonna execute this on a trigger, I'm gonna take a look at that trigger. I'm gonna execute once when that trigger is active, it's gonna be active when it's true, and then I'm going to write back to that, back at zero. So I'm gonna reset that as soon as it's entered something. Hit "Save" right here, and I'm going to have this enabled, it's writing down to here. We can see the trigger is not true. I'll toggle the trigger on, hit "Executes," and then the trigger's off. If I take a look at the data in the database, every time I toggle that trigger, I'm going to get a new record. Trigger is on, it gets a new record. Trigger goes on, it gets a new record.

50:17
Kevin: So tank empty is a good example of what this might be useful for. It might be useful for if a temperature is above a certain amount, log some things. And this can also be used in relational databases in order to relate different things. So if you're selecting a timestamp in one table, you can select something in this table, you can have different tank IDs in here, so you're joining this information over to something else that you're doing somewhere else, somebody has some manual entry somewhere that's going into this table. So very easy to set up, very powerful. And if you're storing this information in databases, it's very, very powerful in your data system that you can pull that data back and actually display it on any screen. And of course, if you're familiar with Ignition, you already know how easy that is. In case you're not, you can just put a table on the screen, select the table in the database that you want to view this from. In this case, I'm going to select our tank display right there and hit "Okay" right there, and then it's going to pull back that information.

51:20
Kevin: So I'm going to select the star from the tank display in this case, and I don't actually have anything in there right now. But if I go back to my DV browse and take a look at my empty log right here, for example, I can pull that back, hit "Okay," and then that's going to pull back the date information that we have that's associated with it. And I'm actually, I bound that to the column attributes; I should be binding this to the data, so that's why you're not seeing anything. You'll see this right here, tank empty log, there we go. So all that information is there, easy as that. If you wanna take a look at any of these other ones, you can click "Okay," that'll pull that back. Any of these tables can just be shown on any screen easily. This can go back on the tank screen, too.

52:10
Don: Great!

52:11
Kevin: Alright! So that brings me to the end of the demonstrations here. There's a lot of different types of things that you could do, and hopefully, this just whets your appetite for the types of things that are available. So you can do many other things. So for example, a point of sale system, we have a customer who's doing that type of thing. Inventory control, you can convert Microsoft Access databases and use those inside Ignition. And customer relationship manager systems, so we actually have our internal CRM built in Ignition and it uses all of these databases extensively. And we have a number of our customers who are using Ignition as a CRM as well.

52:53
Don: I just wanna make two quick announcements before we go. I always wanna invite you to try Ignition for yourself. Some people may be new to Ignition today; go to our website, download the full version for free, and you can install, build your own system, work with some of the stuff that Kevin was talking about today before you ever purchase anything. So it really is a way that we wanna make sure that you get comfortable with the product. Additionally, I do wanna say that if you're just getting acquainted with us, a year ago, we launched Inductive University. It's really an online training service that offers discounted training in Ignition anywhere in the world, obviously, for free. You can watch one to five-minute training videos. You can search for videos in different areas. You wanna look at everything we've done on alarming, just search and you'll find the videos on that. You can test your knowledge with challenges. And then you could get Ignition credentials. We launched this a year ago, and we got over 3200 users now. We've done 570,000-plus training videos have been viewed. And 12,000 specializations in various courses have been earned. So really, I just wanna emphasize that we realized, over the years, that if we were gonna work globally, we had to then make that knowledge available.

54:10
Don: So with the little bit of time we have here, I wanna basically go into a Q&A. You can see these also along with me, Kevin, and we'll just pick a couple and try and get to as many questions we have. So first question, I'll just grab a couple. Instead of writing the command, is there a way to add information from a template? I know you're not gonna have time to show this, but can you... So a way to add information from a template.

54:36
Kevin: Is what you're referring to is instead of writing the SQL command, certainly, yeah. So I believe that question was asked when we were taking a look at the tanks, and that can be done through a template that's on the screen, yeah.

54:50
Don: Great, thanks. How do you associate the value that the SQL is reading? Another question there.

55:00
Kevin: I'm not sure I understand the question exactly, but the way that the values that are coming into SQL Bridge, so we've shown that at the end, that had to do with what was being pulled in. The value that SQL is reading, yeah, I don't exactly understand the question. If you wanted to type that again, we might be able to get to it in here.

55:25
Don: Yeah, that sounds good. And then do you recommend putting configuration information into databases?

55:30
Kevin: It depends on the application. So some of the configuration databases, if it's a one-time configuration and you're just designing a screen, then you might just have it on the screen. If it's things that you might want to be changed user-per-user, it can make a lot of sense to have it in the databases, and then load that configuration out of the database when the user logs in.

55:48
Don: Okay, if we have Oracle Database and ERP, do we need, too, MySQL to run Ignition Gateway for more technical control? You got that?

55:57
Kevin: Absolutely not, yeah. We work with any database. So if you're using Ignition, then you can use Oracle, you can use MySQL, you can use Microsoft SQL Server, or any other SQL database that has a JDBC driver, to be technical.

56:15
Don: Okay. Do you have to store documents in a database? Couldn't you equally just store links to documents on your server?

56:21
Kevin: You certainly could, but we have a lot of customers who have issues with having the person access those documents that they're stored on the server. So active directory credentials can be required. Sometimes, mapped network drives don't map nicely every time the system restarts, that type of thing. So it can make sense to have it on the database. If it works well on a fileshare, you can do that as well, and you're gonna know if it works well in your organization or not.

56:48
Don: We're getting acquainted with Ignition and we are of the opinion that this is a great product. We are presently preparing to do some testing. Oh, I'm sorry, I thought that was a question, but it's a nice acknowledgement. Thank you, we're glad you're getting acquainted with Ignition, and anything you need from us... Actually, on the screen up here, I'll just say that you can now contact any one of our account executives or Melanie Moniz on the top Account Services Manager if you actually want a demo. So that's what those numbers are up there for. Next question, Kevin.

57:15
Kevin: Does Ignition have an embedded report designer tied to the database? Yes, so our reporting module has the report designer and you can tie that directly to the database.

57:25
Don: Good. Okay, great. I know we overran about five, six minutes today. We have more questions here. Really appreciate the responses from all this. I'm glad you took the time to do the demo you did, too, Kevin. What I will say is that we will get to all of these questions and follow up and make sure they get answered for you so that we can sort of bring our webinar to a conclusion today. Thank you very much, and have a great rest of your day.