# Delete records from a table

**URL:** <https://forum.inductiveautomation.com/t/delete-records-from-a-table/4688>\
**Category:** Ignition\
**Created:** [June 22, 2012, 2:02pm UTC](https://forum.inductiveautomation.com/t/delete-records-from-a-table/4688 "2012-06-22T14:02:25Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![cgele](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cgele/32/3453_2.png) [@cgele](https://forum.inductiveautomation.com/u/cgele)\
**Post date:** [June 22, 2012, 2:02pm UTC](https://forum.inductiveautomation.com/t/delete-records-from-a-table/4688/1 "2012-06-22T14:02:25Z")

</div>

I’m trying to delete records from a table that the user selects. I’m able to delete the records from MySQL, but not Ignition. I’ve looked at the Editable Table window from the web site, but still can’t make it work. I’ve attached snapshots of the database table, the popup window that allows the deletion and the script I’m trying to use with the delete button.

Thanks!

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/1/1b9f9c7ee7f0864bb5e091fc7aa1f0e1190466f5.jpg)  
  
 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/c/c9ac1a39efa33099bb4a5e9063d1556eb1265051.jpg)  
  
 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/4/4152ca59e313c16dfcb7d1deae60e2ce6c918826.jpg)

---

<div class="post-metadata">

**Author:** ![greg.simpson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/greg.simpson/32/3135_2.png) [@greg.simpson](https://forum.inductiveautomation.com/u/greg.simpson)\
**Post date:** [June 22, 2012, 3:33pm UTC](https://forum.inductiveautomation.com/t/delete-records-from-a-table/4688/2 "2012-06-22T15:33:10Z")

</div>

Need a little more information from you. What is the names of

The name of the window the table is on  
The name of the table you want to update  
The name of the popup window

at first glance everything looks ok, but I think your not referencing the window where the table is located. Just a little more info will make it a little more clear…

---

<div class="post-metadata">

**Author:** ![cgele](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cgele/32/3453_2.png) [@cgele](https://forum.inductiveautomation.com/u/cgele)\
**Post date:** [June 22, 2012, 4:08pm UTC](https://forum.inductiveautomation.com/t/delete-records-from-a-table/4688/3 "2012-06-22T16:08:21Z")

</div>

The popup window contains the table I want to update and the popup window name is User Email. The database table name I’m trying to change is emails.

Here is the script to open the popup window.

param1 = event.source.parent.UnitNumber  
if event.source.parent.getComponent(‘UnitTable’).selectedRow == -1: #No row selected in Unit Table  
system.gui.warningBox(“You must select a unit before modifying email table!”,“No Unit Selected”)  
else:  
system.nav.openWindow(‘Popups/User Email’, {‘UnitNumber’ : param1, ‘UserType’ : ‘User’})  
system.nav.centerWindow(‘Popups/User Email’)

Here’s a screen shot of the Overview screen that launches the popup window. I click on Change to execute the above script.

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/3/3c2d24d5cb4d0e8123f2c674f4c9120d0761dfb5.jpg)

---

<div class="post-metadata">

**Author:** ![James.Lorenz](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/james.lorenz/32/3290_2.png) [@James.Lorenz](https://forum.inductiveautomation.com/u/James.Lorenz)\
**Post date:** [June 22, 2012, 4:48pm UTC](https://forum.inductiveautomation.com/t/delete-records-from-a-table/4688/4 "2012-06-22T16:48:14Z")

</div>

Can you email in an export of your project to support at inductiveautomation dot com so we can take a closer look?

---

<div class="post-metadata">

**Author:** ![James.Lorenz](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/james.lorenz/32/3290_2.png) [@James.Lorenz](https://forum.inductiveautomation.com/u/James.Lorenz)\
**Post date:** [June 22, 2012, 7:31pm UTC](https://forum.inductiveautomation.com/t/delete-records-from-a-table/4688/5 "2012-06-22T19:31:25Z")

</div>

I was able to look at your project and I think I figured it out. You want the email tables on the “Overview” window to update, right? Since those tables are not set to poll, you can manually refresh the table that has been altered using this at the bottom of your “Delete Email Button” script:

```auto
overviewWindow = system.gui.getWindow('Overview').getRootContainer()

if EmailRole == "User":
	system.db.refresh(overviewWindow.getComponent('CustomerEmailTable'), "data")
elif EmailRole == "Dist":
	system.db.refresh(overviewWindow.getComponent('DistEmailTable'), "data")
elif EmailRole == "Nass":
	system.db.refresh(overviewWindow.getComponent('NassEmailTable'), "data")
```

This will ensure that only the table that has been changed will be refreshed. You will need to put this script on your “Add Email Button” as well in order to refresh those tables when emails are added.

---

<div class="post-metadata">

**Author:** ![cgele](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cgele/32/3453_2.png) [@cgele](https://forum.inductiveautomation.com/u/cgele)\
**Post date:** [June 25, 2012, 1:39pm UTC](https://forum.inductiveautomation.com/t/delete-records-from-a-table/4688/6 "2012-06-25T13:39:59Z")

</div>

I need to be able to delete an email from this table. I have not be able to do this.

I will add the script you posted to update my tables.

Thanks.

---

<div class="post-metadata">

**Author:** ![cgele](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cgele/32/3453_2.png) [@cgele](https://forum.inductiveautomation.com/u/cgele)\
**Post date:** [June 27, 2012, 3:45pm UTC](https://forum.inductiveautomation.com/t/delete-records-from-a-table/4688/7 "2012-06-27T15:45:05Z")

</div>

Here is some additional information on my issue that you may need.

I would like to allow Ignition clients to be able to add/view/delete email address via a SQL table. I have created a Popup window “User Email” to do this. I am passing two parameters from the calling window “Overview” to the “User Email” popup. They are UnitNumber and UserType. The UnitNumber (int) tells me which unit to associate with a particular email address(foreign key to my units table). The UserType(string) tells me which group to associate with each email.

I have been able to add records to the MySQL table “emails”, but I not been able to delete them from within Ignition. I am able to add/delete records from MySQL Workbench.

Please review the code I created in the Delete Email Button on the “User Email” popup. The code is shown in the following image posted earlier in the topic. "Delete Button Script.jpg " I believe the syntax is correct as I am getting no errors in either the console window or red bordered error window.

I also have tried using the Delete Query as shown in the User Manual and from the Editable Table window on the website with no success.

I am running Ignition V7.5 and MySQL V5.5 on CentOS.

Thanks

---

<div class="post-metadata">

**Author:** ![James.Lorenz](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/james.lorenz/32/3290_2.png) [@James.Lorenz](https://forum.inductiveautomation.com/u/James.Lorenz)\
**Post date:** [June 27, 2012, 4:12pm UTC](https://forum.inductiveautomation.com/t/delete-records-from-a-table/4688/8 "2012-06-27T16:12:08Z")

</div>

The issue appears to be with your actionPerformed script on the Delete Email button. You do not need to add single quotes around the string values for EmailValue and EmailRole, the runPrepUpdate() function makes sure those strings are properly passed to the DB. So remove these lines:

`EmailValue = "'"+EmailValue+"'"
EmailRole = "'"+EmailRole+"'"`

And you should be ok. Adding those single quotes will search for the literal string with the single quotes attached, and naturally, none of the emails in your table will have those single quotes added in, which is why the delete query wasn’t doing anything.

---

<div class="post-metadata">

**Author:** ![cgele](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cgele/32/3453_2.png) [@cgele](https://forum.inductiveautomation.com/u/cgele)\
**Post date:** [June 28, 2012, 3:21pm UTC](https://forum.inductiveautomation.com/t/delete-records-from-a-table/4688/9 "2012-06-28T15:21:04Z")

</div>

James:

Made your changes. Same problem. Anything else we can try?

Thanks!

---

<div class="post-metadata">

**Author:** ![James.Lorenz](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/james.lorenz/32/3290_2.png) [@James.Lorenz](https://forum.inductiveautomation.com/u/James.Lorenz)\
**Post date:** [June 28, 2012, 4:29pm UTC](https://forum.inductiveautomation.com/t/delete-records-from-a-table/4688/10 "2012-06-28T16:29:33Z")

</div>

Just to be clear, you’re talking about the “Group Email Table” component on the “User Email” popup, right? I simplified the Delete Email script, since all you need to know to delete an item in the database is its primary key id (assuming it’s unique, which based off of your database screenshot and your scripting, it should be). You also don’t need to have a hidden component querying a PK value for the drop down value. Here is what I did:

[ul]- changed the polling mode of the query for the “data” property binding of the “Group Email Table” to off (your script should have system.db.refresh in the add/delete scripts, so it only refreshes the table when necessary)

- changed the query for that same “data” property to the following so it brings back the primary key with its associated data: `select emailkey, email`
- opened the table customizer for “Group Email Table” and hid the emailkey column
- changed the script to reference the selectedValue (notSelectedStringValue) of dropdown list, which is the primary key of data to be deleted[/ul]

Here is the new script for Delete Email, a lot simpler and just requires that primary key value from the dropdown list. Let me know if the data in the table still doesn’t refresh properly for you.

```auto
table = event.source.parent.getComponent("Group Email Table")
EmailKeyValue = event.source.parent.getComponent('Delete Email Dropdown').selectedValue

system.db.runUpdateQuery("DELETE FROM emails WHERE EmailKey = %d" % EmailKeyValue)

event.source.parent.getComponent('Delete Email Dropdown').selectedValue = -1
system.db.refresh(table, "data")

overviewWindow = system.gui.getWindow('Overview').getRootContainer()

if EmailRole == "User":
	system.db.refresh(overviewWindow.getComponent('CustomerEmailTable'), "data")
elif EmailRole == "Dist":
	system.db.refresh(overviewWindow.getComponent('DistEmailTable'), "data")
elif EmailRole == "Nass":
	system.db.refresh(overviewWindow.getComponent('NassEmailTable'), "data")	
```

---

<div class="post-metadata">

**Author:** ![cgele](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cgele/32/3453_2.png) [@cgele](https://forum.inductiveautomation.com/u/cgele)\
**Post date:** [June 28, 2012, 5:01pm UTC](https://forum.inductiveautomation.com/t/delete-records-from-a-table/4688/11 "2012-06-28T17:01:26Z")

</div>

Can we set up a GoToMeeting for this?

---

<div class="post-metadata">

**Author:** ![cgele](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cgele/32/3453_2.png) [@cgele](https://forum.inductiveautomation.com/u/cgele)\
**Post date:** [June 28, 2012, 5:10pm UTC](https://forum.inductiveautomation.com/t/delete-records-from-a-table/4688/12 "2012-06-28T17:10:12Z")

</div>

Nevermind.

I misread your code, once entered correctly it work great!!!

Thanks for all your help on this.

Chris

---

<div class="post-metadata">

**Author:** ![James.Lorenz](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/james.lorenz/32/3290_2.png) [@James.Lorenz](https://forum.inductiveautomation.com/u/James.Lorenz)\
**Post date:** [June 28, 2012, 5:29pm UTC](https://forum.inductiveautomation.com/t/delete-records-from-a-table/4688/13 "2012-06-28T17:29:19Z")

</div>

No problem, glad you got it working.