Operator Data collection question

Ignition
1

I am working on my demo system and I am now in the operator data collection portion of it.

This is the part of the application where we throw the paper and clipboard away and enter runtime info, etc. directly into the SCADA application.

I have designed a “form” that has text boxes and selectors. The operator enters the hours, amps, etc. into these text boxes. When they click the submit button the following code is executed.

ID = event.source.parent.getComponent(‘lblID’).text
TIMESTAMP = ‘NOW()’
USER = system.tag.getTagValue("[System]Client/User/Username")
P1HM = event.source.parent.getComponent(‘txtP1HM’).floatValue
P2HM = event.source.parent.getComponent(‘txtP2HM’).floatValue
P3HM = event.source.parent.getComponent(‘txtP3HM’).floatValue
P4HM = event.source.parent.getComponent(‘txtP4HM’).floatValue
P5HM = event.source.parent.getComponent(‘txtP5HM’).floatValue
P6HM = event.source.parent.getComponent(‘txtP6HM’).floatValue
P1AMP = event.source.parent.getComponent(‘txtP1Amp’).floatValue
P2AMP = event.source.parent.getComponent(‘txtP2Amp’).floatValue
P3AMP = event.source.parent.getComponent(‘txtP3Amp’).floatValue
P4AMP = event.source.parent.getComponent(‘txtP4Amp’).floatValue
P5AMP = event.source.parent.getComponent(‘txtP5Amp’).floatValue
P6AMP = event.source.parent.getComponent(‘txtP6Amp’).floatValue
GENHM = event.source.parent.getComponent(‘txtGeneratorHM’).floatValue
GENFUELCODE =event.source.parent.getComponent(‘selGeneratorFuel’).selectedValue
NOTES = event.source.parent.getComponent(‘txtNotes’).text

system.db.runUpdateQuery(“INSERT INTO lsdc_stationinformation VALUES (%s, %s, ‘%s’,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, ‘%s’)” % (ID, TIMESTAMP, USER, P1HM, P2HM, P3HM, P4HM, P5HM, P6HM, P1AMP, P2AMP, P3AMP, P4AMP, P5AMP, P6AMP, GENHM, GENFUELCODE, NOTES), ‘WWFLINTROCK’)

I have tested and this works just fine, however it seems really clunky to me. Any tips are appreciated.

Thanks.

2

Your script is not clunky, however here are a few pointers. First you are inserting a value for the ID column. You can make that column auto-increment in the database and you can leave it out of the query. Secondly, you should use system.db.runPrepUpdate instead of runUpdateQuery so Ignition can handle datatypes for you. Lastly, you want to specify the columns you are using in your insert query. Putting that all together here is an exmaple:[code]USER = …
P1HM = …
…

system.db.runPrepUpdate(“INSERT INTO lsdc_stationinformation (timestampcol, usercol, p1hmcol, …) VALUES (‘NOW()’, ?, ?, …)”, [USER, P1HM, …])[/code]Of course replace the … with the rest of your columns. Hope this helps.

3

Travis,

Thanks for the help, I am going to update my function called to run the insert query.

The column name ID is misleading. ID is actually the MOSCAD site ID of the RTU. This table is a pump run log, etc for all 60 of our liftstations. The ID that is inserted into the table is generated off of a dynamic property called RTUID when the window is opened. There is not an auto incremental / primary key for this table.

I have started with a large catch all table for all of the stations as it was the easiest design that i could think of. I’m sure once I start detailed testing and logging, I might find it better to break up the tables into a more efficienct structure.

4

Continuing on with the next problem that I am stuck on…

I am storing the pump hour meters in a table. each column is a pump, etc. I don’t want to store calculated values (runtime from day 1 to day 2 etc), just the meter readings.

I have a screen where I want to show the operator the min, max, and average of the runtimes of the last 30 entries. I am using a prep query limiting the results to 30 and sorting from newest reading to oldest reading. I want to keep my code dynamic as for example, I only have 10 records as I am playing around.

I am trying to use a for loop to pull my 10 hour readings and create a dataset that would have 10 rows in it.

row 1 = today hours - yesterdays hours
row 2 and so on.

I use the following code to put my individual runtimes into a new dataset so I can min, max, and average it.

pdata = system.db.runPrepQuery(“SELECT * FROM lsdc_pumps WHERE site = ? ORDER BY 3 DESC LIMIT 0,30”, [site])

#Use X to index rows based off of 0 instead of 1

x = 0
for row in pdata:
x = x + 1
x = x-1

runtimes = []
y = x
for y in range(x):
onerow = pdata[y][“p1h”] - pdata[y+1][“p1h”]
runtimes.append(onerow)
y = y+1

head = [“runtime”]
result = system.dataset.toDataSet(head, runtimes)
And I get this error…

PyFloat cannot be cast to org.python.core.PySequence when it tries to write the “result” variable.

5 
runtimes.append(onerow)

should be

runtimes.append([onerow])

I believe.

6

Mr. Chase,

That worked like a charm. Thank you very much.