Editing Multi-Selected Rows from a Table

Editing Multiple Table Rows

A common user experience is to select multiple rows of a table and then edit all of those rows from the database at once, such as deleting all of them. When a user selects more than one row in a table, there is a special function called getSelectedRows() available on both the Table and Power Table components that returns the row indexes of the table as a Python list. This list can be iterated through in order to delete the selected rows from the database.

Example - Deleting Selected Rows

We can use the list of selected rows to delete them from the database. Start with a table in the Database table that looks like this:

id	machine_name	area_number
1	Conveyor	1
2	Press	2
3	Tank	1
4	Packer	3
5	Loader	3
6	Oven 1	3
7	Oven 2	2
8	Wrapper	1
9	Mixer	3
10	Cold Storage	2
11	Dryer	2

1. Create a new Named Query that will be used to delete rows of data. Set up Security to fit your needs, and name it appropriately. For more information on creating Named Queries, see Using Named Queries - Example.

   1. Set the Query Type to Update Query.

   2. Create a single Value type Parameter that is an Int4 data type. This will hold the id of the row, so we can name it rowID.

   3. Create the Delete Query.

      SQL - Delete Row with Matching ID

      DELETE FROM machines WHERE id = :rowID

      

2. Create a second Named Query as in step 1, but this one will be used to select the data into a table.

   1. Set the Query Type to Query, no parameters, and add a basic select query.
      SQL - Selecting from a Table

      SELECT * FROM machine

3. On a new Main Window, add a Power Table component and a Button component.

4. On the Power Table's Data property, set up a Named Query binding to the Select Query that was made in step 2.

   1. Ensure that the Selection Mode property is set to Multiple Interval.

5. On the Button, change the Text property to say "Delete".

   1. Right click on the Button and select Scripting.

   2. Select the actionPerformed Event Handler and navigate to the Script Editor Tab.

   3. Here we need to call the getSelectedRows() function on the Power Table to determine what rows are selected, and then loop through those to grab the value of the id column in each row and delete the row based on that id.

      Python - Looping Through the Selected Rows and Deleting Them

      # Get the data from the table and assign it to the variable called data.
      data = event.source.parent.getComponent('Power Table').data
      
      # Get the rows of the data that the user has currently highlighted.
      rows = event.source.parent.getComponent('Power Table').getSelectedRows()
      
      # Iterate through each row of the list that is associated with the rows variable.
      for row in rows:
      
          # Get the value associated with the current row and the the column called "id".
          id = data.getValueAt(row, "id")
      
          # Run the query to delete from the database, where tableName is the name of the database table.
          system.db.runNamedQuery("Delete Machine Rows", {"rowID":id})
      
      # Refresh the table data.
      system.db.refresh(event.source.parent.getComponent('Power Table'), "data")

6. You can test it out by putting the Designer into Preview Mode, selecting a few rows, and then clicking the Delete Button.