I have created an alarm table that is populated by a named query from mysql.
I have an embedded view within a column to select the reason the alarm is present. I would like for the selection of the dropdown to send to the database.
I tried accomplishing this with the preexisting alarm components but could not get anything to work.
What exactly is not working? What are the error messages? Do you have a separate table that you want to insert all your alarms (along with the user inputted reasons) into? It will be easier to help with some extra context about your situation and goals.
Also please check out this How to post code on the forum post.
I don't know where you got your code from, but it was not meant to work without any modification. You have lines like this:
If your code code came from an LLM AI (like ChatGPT), I'd advise you to refrain from using AI for Ignition scripting. Ignition is simply to niche, and AI (today) does not have adequate knowledge on it. It will quietly and confidently just give you bad or bogus code, (especially since so much of Ignition scripting is dependent on the context that it is in). Inductive Automation does a good job with the manual; don't hesitate to read it.
I am attempting to update this:
I am mainly looking for a strategy to write to my dB. This is the table my named query came from, and I would like to update the reason column with the embedded dropdown.
I would like to use this: Perspective - Table Scripting | Ignition User Manual (inductiveautomation.com)
maybe use something like this
def runAction(self, event):
# Retrieve parameters from the embedded view
reason = self.view.params.value # The selected reason from the dropdown
event_id = self.view.params.RowID # The event ID of the alarm
# Prepare the SQL update query
query = "UPDATE ignition.alarm_events SET reason = ? WHERE eventid = ?"
args = [reason, event_id]
# Execute the update query using the 'ignition' database
system.db.runPrepUpdate(query, args, "ignition")
# Optionally, refresh the table or provide feedback
system.perspective.sendMessage('TableRefresh')
I would not recommend modifying the table that your AlarmJournal is writing to, because it is managed by the gateway itself. IA, could in the future change some internal implementation details, overwrite or break your data, and it would not be their fault because what you're trying to do is unsupported. If you want to store alarm "reasons" in a database, I'd store them in a separate DB table.
I'd also recommend using Named Queries if can, as they are much easier to maintain.
What are you expecting to happen that is not happening? Is there an error message?
Am i able to write the value to the database with a named query?
Yes, Named Queries can be update queries. You can setup parameters that you pass to your query when calling it in a binding or script. See Named Query Parameters | Ignition User Manual.
Kinda new to helping out but here's what I would do. There's a lot of assumptions here, and you WILL need to tweak the code. First create a new table alarm_reason. We will create a relation for each "reason" to a specific alarm.
CREATE TABLE alarm_reasons (
reason_id INT AUTO_INCREMENT PRIMARY KEY,
alarm_id INT NOT NULL,
reason TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (alarm_id) REFERENCES alarms(alarm_id) #make sure the Foreign Key "alarm_id" and the alarm table "alarms" actually match your system.
);
Next create a Named Query to select data from both the alarms and alarm_reasons table. Something like this: (again you'll need to make sure the fields match the actual column names in your setup)
SELECT
a.alarm_id,
a.alarm_name,
r.reason
FROM
alarms a
LEFT JOIN
alarm_reasons r ON a.alarm_id = r.alarm_id;
Create a table on a view(i'm using Perspective). Bind the data prop to a query, and select the named query you just created. This will be how you display the data.
On the Columns prop, click the plus and add as many columns as you are displaying. Expand each column and in the "filed" add the name of the column as it exists in the actual table. Enable editing.
Create another named query that will update the alarm_reasons table.
INSERT INTO alarm_reasons (alarm_id, reason)
VALUES (:alarm_id, :reason)
ON DUPLICATE KEY UPDATE reason = :reason;
Right click on the Table > Configure Event > OnEditCellCommit > Add a script. Every time a user edits a cell and presses enter, this event fires. We can use this to call the query we just made.
def onCellEdited(self, event):
# Get the new value and the identifiers from the event
newValue = event.value
alarmID = self.props.data[event.row]["alarm_id"]
# Create a dictionary of parameters to pass to the Named Query
parameters = {
"alarm_id": alarmID,
"reason": newValue
}
# Run the Named Query
system.db.runNamedQuery("UpsertAlarmReason", parameters)
Post your actual fix so we can all learn! Thanks!
hello, I have gone in this direction, but have simplified some things. It is not functional yet. It is unresponsive.
I now have a separate dropdown component. and am using a named query.
The button has a onActionperformed script:
def runAction(self, event):
# Get the values from the relevant components
reason = self.custom.dropdown # Get selected reason
event_id = self.getSibling("Entry_Table").custom.eventid # Get event ID value
event_time = self.getSibling("Entry_Table").custom.eventtime # Get time
# Create a dictionary to hold the parameters for the named query
parameters = {"reason": reason, "eventid": event_id, "eventtime": event_time}
# Call the named query to update the downtime reason
system.db.runNamedQuery("Alarm Data Machine Information/UpdateDowntimeReason", parameters)
the update query is
SET reason = :reason,
event_time = :eventtime
WHERE eventid = :eventid
The script that I used to create the table:
CREATE TABLE ignition.senntech_202_downtime_reasons (
eventid VARCHAR(100) PRIMARY KEY, -- Event ID as a string
machine_id INT NOT NULL, -- ID of the machine
reason VARCHAR(255), -- Reason for the downtime
event_time DATETIME NOT NULL, -- Timestamp for the downtime event
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Automatically set timestamp for record creation