Data Sets

Archived Topics
1

I am not really understanding how to set up a data set.

2

I am not even sure how to ask the question and make sense. I really need some help here. :confused:

3

Thanks for your help, Colby. I am good and dangerous now. :laughing:

4

Hey, no problem. For posterity & the sake of clarity, I’ll post a bit about what we did:

Essentially, after creating a window that pulled 10 seperate values from a single row, jet realized that there must be a more efficient way of getting the data in. After all, all the data is together in one row- why peform 10 sql queries when 1 should suffice.

Datasets popped to mind. After all, it is a “set of data”, right? Surely there must be some way to get the data into one, and reference it from a different property. Now, how to set it up…

The only problem that jet had in this case was that he tried to “hand craft” the set he wanted, by adding columns and rows manually in the dataset viewer. This is essentially equivilent to creating a property, binding it to the database, and then trying to set the value manually- it will get overwritten when the query runs. Instead, you have to “craft” it in your sql query. The important thing to remember is that a custom dataset property is just like any other property, except it handles more than one value. When you create an integer property bound to the database, you are using an sql query that selects 1 integer (hopefully). Even if you use the “browse” page, it’s still creating a query.

Now, for a dataset, you can select all the data you want. This includes multiple columns, by listing them out or using * (not recommended, and I’ll say why in a second), or multiple rows by using (or not using!) a where clause. This query will populate the dataset in the exact same way you’d expect to see it in a database browser. Columns will be returned in the order you request them, and rows in the order they are (naturally, or how you want if you used an ORDER BY clause).

This predictability is important- now you KNOW that the first column in your SELECT query is column [0] in your dataset. That’s why SELECT * isn’t good… you probably don’t know how the db will return them! (It should be the order they were created, but why make it confusing to maintain?).

Now, you can refer to the data just like any other property. The only difference is that you have to add a bit of extra data: the row and column you want. Down below is a link to the help file page that explains how all the ways you can do this. Here’s a simple example though:

Goal: Select the current and expected state. Create a property that indicates whether they’re the same.

Setup:
2 custom properties on a panel/label/etc:
“InData” - Dataset
“CurState” - boolean

Binding:
“InData” is bound to an sql query:

SELECT current_state, expected_state FROM data_table WHERE machine_id=1

This will give us 2 columns, and 1 row.
“CurState” is bound to an expression:

toInt({Root Container.Panel.InData}[0, 0])=toInt({Root Container.Table.data}[0,1])

The only trick to this is that by default, FactoryPMI has no clue what kind of data is held in the specified row/column. Therefore, we need to tell it how to treat it with the toInt/toBoolean/toDate etc functions. In this case, we just picked integer for the sake of comparision. CurState is a boolean value- thus the expression is a simple logic statement: column 0 is equal to column 1? This will be true or false, and will be the value of CurState.

Hope this helps someone out!

Regards,

Colby

References:
http://www.inductiveautomation.com/products/factorypmi/usermanual/source/technical/expression/overview.htm