# Operator Data collection question

**URL:** <https://forum.inductiveautomation.com/t/operator-data-collection-question/2989>\
**Category:** Ignition\
**Created:** [June 1, 2011, 10:20pm UTC](https://forum.inductiveautomation.com/t/operator-data-collection-question/2989 "2011-06-01T22:20:45Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Matthew\_Temple](https://avatars.discourse-cdn.com/v4/letter/m/7ea924/32.png) [@Matthew\_Temple](https://forum.inductiveautomation.com/u/Matthew_Temple)\
**Post date:** [June 1, 2011, 10:20pm UTC](https://forum.inductiveautomation.com/t/operator-data-collection-question/2989/1 "2011-06-01T22:20:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [June 2, 2011, 5:34am UTC](https://forum.inductiveautomation.com/t/operator-data-collection-question/2989/2 "2011-06-02T05:34:08Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Matthew\_Temple](https://avatars.discourse-cdn.com/v4/letter/m/7ea924/32.png) [@Matthew\_Temple](https://forum.inductiveautomation.com/u/Matthew_Temple)\
**Post date:** [June 2, 2011, 6:51pm UTC](https://forum.inductiveautomation.com/t/operator-data-collection-question/2989/3 "2011-06-02T18:51:08Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Matthew\_Temple](https://avatars.discourse-cdn.com/v4/letter/m/7ea924/32.png) [@Matthew\_Temple](https://forum.inductiveautomation.com/u/Matthew_Temple)\
**Post date:** [June 6, 2011, 8:19pm UTC](https://forum.inductiveautomation.com/t/operator-data-collection-question/2989/4 "2011-06-06T20:19:11Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Kyle\_Chase](https://avatars.discourse-cdn.com/v4/letter/k/9de0a6/32.png) [@Kyle\_Chase](https://forum.inductiveautomation.com/u/Kyle_Chase)\
**Post date:** [June 7, 2011, 7:19am UTC](https://forum.inductiveautomation.com/t/operator-data-collection-question/2989/5 "2011-06-07T07:19:35Z")

</div>

```auto
runtimes.append(onerow)
```

should be

```auto
runtimes.append([onerow])
```

I believe.

---

<div class="post-metadata">

**Author:** ![Matthew\_Temple](https://avatars.discourse-cdn.com/v4/letter/m/7ea924/32.png) [@Matthew\_Temple](https://forum.inductiveautomation.com/u/Matthew_Temple)\
**Post date:** [June 7, 2011, 2:19pm UTC](https://forum.inductiveautomation.com/t/operator-data-collection-question/2989/6 "2011-06-07T14:19:15Z")

</div>

Mr. Chase,

That worked like a charm. Thank you very much.