Delete records from a table

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!






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…

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.


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

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:

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.

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.

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

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.

James:

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

Thanks!

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.

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")	

Can we set up a GoToMeeting for this?

Nevermind.

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

Thanks for all your help on this.

Chris

No problem, glad you got it working.