Deleting and Adding Rows to a Database using Ignition

Ignition
1

Hello,

I’m currently trying to develop a user interface in ignition that will allow users to both add and delete rows. So far I have managed to add a table from my database, add a ‘delete’ and a ‘save’ button (which currently do nothing), and I have added in a text box and a numerical box for the user to input data into the fields of the row they want to add.

I want to make it so that when the user click the ‘delete’ button, the selected row within the table is deleted. I also want to make it so that when the user clicks the ‘save’ button, a new record is added using the credentials within the text and numerical boxes.

I am familiar with C# and event handlers within Visual Studio, but I have only just begun to use the Ignition designer and have not had any experience with Python.

What I am ideally looking for here is the code to put in the event handler for the delete and save button.

Thanks!

2

The Ignition Demo Project (7.5) has a good example of what you’re trying to do in the “Database Manipulation” section, open the project up in the designer and take a look. You can download the demo here:

inductiveautomation.com/downloads/extras

Basically, the premise is you’ll have a table pulling back all data from the database (including the primary key/“id” column), but selecting the “id” column as hidden using the table customizer. You’ll need to know that “id” so that you can send a proper query to the database for editing and deleting. In the demo, you’ll see that a custom property for the table called “id” that automatically returns the value in the “id” column of the currently selected row, which is nice because you won’t have to figure out the “id” over and over again, you can just reference that property directly.

For adding data, you just put a script that grabs the data from the components (i.e., event.source.parent.getComponent(“Text Field”).text), and sends that data off to the DB using system.db.runPrepUpdate(). You’ll see what I mean when you look at the event handlers for the buttons in the demo. If you’re unclear on how to work with components, or how to use runPrepUpdate(), check out the manual:

inductiveautomation.com/support/ … update.htm
inductiveautomation.com/support/ … onents.htm

3

Some ideas… probably not the best practice, but it should be a good start. This is evaluated when actionperformed event occurs on a button “submit” in my case. I am very new to python and had a decent background in C#. Python is the easiest thing ever to pick up… the new 3.x Python is even more friendly but 2.7 syntax is still easy to pick up. Please do point out any areas of concern in my example below… as I am learning as well. I hope this helps you with quick start.

Update_Record_Id = event.source.parent.Record_ID
#In Bound Info

Hauler_In = event.source.parent.getComponent('Group 3').getComponent('Group').getComponent('Text_Hauler_In').text
Driver_In = event.source.parent.getComponent('Group 3').getComponent('Group').getComponent("Text_Driver_In").text
Load_In = event.source.parent.getComponent('Group 3').getComponent('Group').getComponent("Text_Load_In").text
Seal_In = event.source.parent.getComponent('Group 3').getComponent('Group').getComponent("Text_Seal_In").text
Set_Point_In = event.source.parent.getComponent('Group 3').getComponent('Group').getComponent("Text_Set_Point_In").text
Temp_In = event.source.parent.getComponent('Group 3').getComponent('Group').getComponent("Text_Temp_In").text
#Check_In_User = event.source.parent.parent.getComponent('Group 3').getComponent('Group').getComponent("Text_Check_In_User").text

#Out Bound Update Info
Hauler_Out = event.source.parent.getComponent('Group 2').getComponent('Group').getComponent('Text_Hauler_Out').text
Driver_Out = event.source.parent.getComponent('Group 2').getComponent('Group').getComponent('Text_Driver_Out').text
Load_Out = event.source.parent.getComponent('Group 2').getComponent('Group').getComponent('Text_Load_Out').intValue
Seal_Out = event.source.parent.getComponent('Group 2').getComponent('Group').getComponent('Text_Seal_Out').intValue
Set_Point_Out = event.source.parent.getComponent('Group 2').getComponent('Group').getComponent('Text_Set_Point_Out').intValue
Temp_Out = event.source.parent.getComponent('Group 2').getComponent('Group').getComponent('Text_Temp_Out').intValue
Check_Out_User = event.source.parent.getComponent('Group 2').getComponent('Group').getComponent('Dropdown_User').selectedLabel

Comments = event.source.parent.getComponent('Text_Area_Comments').text
IsLocked = event.source.parent.getComponent('Toggle_Locked').selected
FuelLevel= event.source.parent.getComponent('Slider').value

#Status Update Info
Status_OnLoad = event.source.parent.Status
Status = event.source.parent.getComponent('Dropdown_Status').selectedStringValue

#check to see if status was already shipped, if so don't do anything
#if status was changed to shipped then update shipped time.
if Check_Out_User == '<Select One>':
	system.gui.messageBox("Must Enter Who Checked In Trailer")
else:
	if (Status_OnLoad not in ('Shipped_Empty', 'Shipped_Loaded')) and (Status in ('Shipped_Empty', 'Shipped_Loaded')):
		Time_Out = app.MyScripts.MyNow()
		system.db.runPrepUpdate("UPDATE Trailer_Log SET  Time_Out=? WHERE Record_Id=?", [Time_Out, Update_Record_Id])

	
	system.db.runPrepUpdate("UPDATE Trailer_Log SET  Hauler_In=?, Driver_In=?, Load_In=?, Seal_In=?, Set_Point_In=?, Temp_In=?, Hauler_Out=?, Driver_Out=?, Load_Out=?, Seal_Out=?, Set_Point_Out=?, Temp_Out=?, Check_Out_User=?,  Status=?, Locked=?, Fuel=? WHERE Record_Id=?", [Hauler_In, Driver_In, Load_In, Seal_In, Set_Point_In, Temp_In, Hauler_Out, Driver_Out, Load_Out, Seal_Out, Set_Point_Out, Temp_Out, Check_Out_User, Status, IsLocked, FuelLevel, Update_Record_Id])

	#Update Trailer_Log
	UA_Table_ID = Update_Record_Id
	#Add Entry To Update_Audit
	#Update_Audit info
	UA_Comments = "<%s> %s" %(Status,Comments)
	UA_Table_Name = 'Trailer_Log'
	UA_Update_User = system.security.getUsername()
	UA_Time = app.MyScripts.MyNow()
	system.db.runPrepUpdate("INSERT INTO Update_Audit (Table_Name, Table_ID, Update_Time, Update_User, Comment) VALUES (?, ?, ?, ?, ?)", [UA_Table_Name, UA_Table_ID, UA_Time, UA_Update_User, UA_Comments])
	
	system.gui.messageBox("Record Updated")
	system.nav.closeParentWindow(event)