Creating an Editable Table in Ignition

The table component in Ignition makes displaying data retrieved from SQL queries a snap, however there are many times when you want to do more than just display the data to the user.  In many cases you would like the user to be able to interact with the table directly, edit data within the cells and then have those edits be reflected in the database.  Many first time Ignition users think that merely making a column "editable" in the table customizer will achieve this.  They quickly find out that this is not the case and are often confused as to why.  The table component does not have any built in functionality to write back to your database.

To make a table truly "editable" you have to use a combination of making the columns of the table "editable" in the customizer and then responding to the cellEdited events with scripting.  Below is a brief overview on how to create and editable table.  This example references a hypothetical table "customers" that exists already in the database.  You would of course modify this example to fit your needs.

Part 1 - Set up the Table Component

The first step is to drag in a Table component, from the Tables tab on the Component Palette, into a Window.

Once the table is in the window, the next step is to bind the Data property to a SQL query pointing to the table you want to edit in the database. To do this click on the Bind icon to the right of the data property and select the SQL Query binding type. Then, type in a SQL query for that table and click OK.

Now that the table component is showing data, you need to set which columns will allow the user to edit in the run-time. This way a user can double-click in any cell in those columns to change the value. To do this, right-click on the table component and select Component Customers -> Table Customizer.

The Table Customizer is where you configure the columns' display properties, as well as any row mapping configuration. When you open the Table Customizer, you will see a table that has all of your data's columns across the top, and all of the column display properties across the left. You can configure each column to have its own display properties. Once such column display property is called Editable. By checking the box you are allowing that column to become editable in the run-time.

Part 2 - Add the Scripting

Any time a user double-clicks in an editable cell and changes the value, all valid changes will be reflected back in a change to the table's data property. The SQL table does not get updated automatically. At this point, all changes can be mapped back to a database in scripting.

Once a valid change has been made, the table will fire a cellEdited event that contains the row, column, previous value, and new value for the cell. Remember, if the table's data is bound to a polling query binding, the edited dataset will be overwritten with whatever is in the database. You can use the cellEdited event to issue a SQL UPDATE query that will make the edit in the database as well.

To create a script that will issue the SQL UPDATE query, right-click on the table component and select Event Handlers. Here you can respond to events that get fired, such as a mouse-click or cell edited event. On the left-hand side, you will see a cellEdited event under the cell folder. Select the cellEdited event and then select the Script Editor tab on the right-hand side. Here you can create a small script that will issue the UPDATE query. The following is an example:

# The row index of the edited row

row = event.row

# The column index of the edited column

col = event.column

# The column's name

colName = event.source.data.getColumnName(col)

# The new value

value = event.newValue

# The primary key's value (first column), so that the appropriate row can be updated 

# in the db

id = event.source.data.getValueAt(row, 0)

# Run an update query to the table that is being edited to reflect any changes

query = "UPDATE customers SET %s = ? WHERE ID = ?"

system.db.runPrepStmt(query % colName, [value, id])

Again, this script will run any time a user makes a valid edit to one of the editable cells in the table.