I have a view in perspective that is intended to be a way for production workers to submit downtime. I have 2 numeric entry fields and one drop down. I am trying to figure out how to store all the data that is input by the worker into a table in my SQL database whenever the submit button is clicked. Could anyone provide guidance on how I could accomplish this?
Overall I'd like the downtime information submitted by the worker to be stored in my "downtime_data" table. I'd like to capture the following information: Downtime reason, downtime in minutes, timestamp of when it was submitted, employee number, ip address of the plc.
Create a named query with an insert statement and parameters for the information you want to log and call that named query from your button action, where you can bind the parameters to your input fields.
I usually do the timestamp on the database side, it will depend on your flavor of sql.
Whatever about anything else, t_stamp should be a DATE or DATETIME object.
It might also be useful to,
Add an auto-increment integer field to provide a unique record ID for each row.
Create an index or several indexes on columns or column combinations likely to be used in SELECT queries. It will speed them up.
For IP addresses there are several ways to do this. Saving as text is messy and requires validation unless it's automatic. The most flexible seems to be to save the four octets as TINYINT. See the thread below for more ideas. Datatype for IP addresses – SQLServerCentral Forums
This is what I have currently but I can't seem to get the data to store in the table. I'm very new to this type of work in general so I'm having trouble tracing where my error is. If anyone has any suggestions or sees any mistakes I would greatly appreciate it.
PLEASE SELECT A DOWNTIME CODE is named "Code"
PLEASE INPUT AMOUNT OF DOWNTIME (MINUTES) is named "DowntimeMinutes"
PLEASE INPUT EMPLOYEE # is named "EmployeeNumber"
def runAction(self, event):
# Fetch values from your Perspective components
downtimeReason = self.getSibling("Code").props.value
employeeNumber = self.getSibling("EmployeeNumber").props.value
timedown = self.getSibling("DowntimeMinutes").props.value
# Generate the current timestamp
from java.util import Date
tStamp = str(Date())
# Prepare parameters for the named query
params = {
"Code": downtimeReason,
"EmployeeNumber": employeeNumber,
"DowntimeMinutes": timedown,
"tStamp": tStamp
}
# Run the named query to insert data into the downtime_data table
system.db.runNamedQuery("InsertDowntimeData", params)
Tyler, please see Wiki - how to post code on this forum. Then hit the pencil icon link below your post and format the block of code using the </> button.
Tip: this stuff can get ragged quite quickly when you start using nested containers. A simpler, less brittle solution is:
Create custom properties on the view for downtimeReason, employeeNumber and timedown.
Bind each of the controls on your downtime form bidirectionally to the custom properties.
Now reference the custom properties in your script.
It's much easier to follow, you can move stuff around and you can monitor all the values in Designer in the view's custom properties.
Regarding the layout, consider something a bit more polished. Remember that your users are used to Windows / Mac / Android / iOS, etc.
I've opened up the vertical spacing more than the defaults in case you're using touch screen and you have users with phat phingers.
Put an expression binding on the Save button's enabled property so that it only works when all the fields have valid data.
Use the onActionPerformed event on the Save button, not the onClick event. onClick fires whether the button is enabled or not. onActionPerformed only fires when the button is enabled.
Use color sparingly and with purpose. The massive green SUBMIT button in your screengrab is confusing. Why is it green? Is something on? What color will it turn if I press it? Gray (or whatever your theme is) will be fine and the operators will quickly get used to good consistent HMI. Try to make buttons the same size and same location, where appropriate. Note that in my sample the user starts at the top and does four steps in a vertical sequence. Yours requires a horizontal reverse motion to reach the SUBMIT button.
This is the plan for the future. My team is trying to learn how everything works before finalizing any designs. Some very good suggestions that I hadn't thought about yet though so I appreciate it!
No, just the value prop which is the selected value (or null, I think).
Get the team to style as much as possible using the built-in themes and define styles rather than setting styles on individual components. It makes consistency and modification far, far easier. Hammer it into them!
def runAction(self, event):
# Fetch values from your Perspective components
downtimeReason = self.custom.Dropdown
employeeNumber = self.custom.EmployeeNumber
timedown = self.custom.DowntimeMinutes
# Generate the current timestamp using Ignition's built-in function
tStamp = system.date.now()
# Prepare parameters for the named query
params = {
"Code": downtimeReason,
"EmployeeNumber": employeeNumber,
"DowntimeMinutes": timedown,
"tStamp": tStamp
}
# Run the named query to insert data into the downtime_data table
system.db.runNamedQuery("InsertDowntimeData", params)
The keys in the parameters dictionary that you send to system.db.runNamedQuery() have to match exactly what you used as the names in the NamedQuery. The parameters also have to match in the query.
change the query to a Select type query and run it in the testing tab and insure you are getting the expected columns and values returned, then check it as an update.
When I execute my named query it will fill the table with 0's. But the actually data I'm inputting is not being sent whenever the submit button is clicked.
Button onActionPerformed script:
def runAction(self, event):
# Fetch values from your Perspective components
downtimeReason = self.custom.Dropdown
employeeNumber = self.custom.EmployeeNumber
timedown = self.custom.DowntimeMinutes
# Generate the current timestamp using Ignition's built-in function
tStamp = system.date.now()
# Prepare parameters for the named query
params = {
"Code": downtime_reason,
"EmployeeNumber": employee_number,
"DowntimeMinutes": timedown,
"tStamp": t_stamp
}
# Run the named query to insert data into the downtime_data table
system.db.runNamedQuery("InsertDowntimeData", params)
t_stamp is the name of the column in the downtime_data table that the timestamp from ignition should be sent to. I changed values to Int4. I'll play around with some logging.
