Quick help - editable Perspective Table

Continuing the discussion from onEditCellCommit help:

I have a table with username string and a boolean for “access” that I’d like to be able to check/uncheck the access box and have it write to the db.

Using the example script in onEditCellCommit event, setting the field for each column, and converting the dataset to JSON, I’ve been able to get the data to “look” like it’s interacting, but it’s not actually affecting the database.

A few quick questions for you folks:

  1. Does editing a boolean work with the script:

    valueToSet = event.value

    self.props.data[event.row][event.column] = valueToSet

  2. Do I need to have my ID column included for this to work?

  3. Do I have to have all columns editable for this to work? I only want to edit the boolean column if possible.

  4. In preview mode it looks like it is not committing and the selection reverts back when I select another row. It also looks like I have to select the cell first before I can check the box. Assuming I get this to work and that behavior persists, is there a way around that?

Thanks in advance! I spent a couple of days getting this to work in Vision, thinking that I could embed a Vision Window inside a Perspective Page. Oops!

PS - In the meantime I’m going to look at using an Update script, assuming that will work similarly to the Vision Window.

Edit - using Ignition 8.0.4

Edit 2 - added picture

Update - now using the following code, but I think that I need to script a different event since I’m clicking a checkbox - still looking:

r = event['row']
c = event['column']
v = event['value']

id = self.props.data[r].id

system.db.runPrepUpdate("UPDATE wtaccess SET access = ? WHERE id=?" [v, id]

self.refreshBinding("props.data")

Update 2 - doesn’t seem to like that last part. So, if I include the ID column in the table, I’m unable to click anything on Preview, but if I include it, I select the cell then check the box (can also then hit enter), but nothing is updated and if I move away the box goes back to unchecked. I think I’m missing something - tried onRowClick, onClick and a couple others. Maybe a custom event?

Any pointers would be appreciated. We love the power table read/write in Vision and it would be a huge benefit to be able to do this in Perspective.

I’m far from an expert on the Perspective table, but I can probably answer some of these. @cmallonee may also be able to help :slight_smile:

That will (probably) update the table’s data, but won’t actually commit anything to the database, as it looks like you noticed from your update script. It’ll also get overridden the next time the query to populate the table runs.

Yes, I’d say you should only expect any kind of ‘dynamic’ editing to work if you’ve got a fixed ID column. You can retrieve it, but hide it from display, using some combination of properties in the columns property.

I wouldn’t think so.

That might just be a consequence of not ‘really’ committing the data back to the database.

This looks like, at least roughly, the correct approach, although your syntax for runPrepUpdate is a little off; should be:
system.db.runPrepUpdate("UPDATE wtaccess SET access = ? WHERE id=?", [v, id])

1 Like

Thanks so much for the reply! I appreciate any input. I will keep looking into this and hopefully in the future the perspective tables will more closely resemble the Vision power table.

r = event['row']
c = event['column']
v = event['value']
	
my_id = self.props.data[r].id. # name change shouldn't matter
	
# Table "deletable" contains columns "bool" and "id"
system.db.runPrepUpdate("UPDATE deletable SET bool = ? WHERE id=?", [v, my_id])
	
self.refreshBinding("props.data")

This code worked for my table and wrote the values back to the database. I did run into some undesirable behavior while using “double-click” for allowEditOn - mainly that I had to click three times just to toggle the checkbox, but it should still work.

3 Likes

Thanks, I’ll jump back into this project soon and see if I can get it up and running. Have a great weekend!

1 Like

Hello,
I am doing something similar, in my case is an int.
I dont know how to do it. i have adapted the syntax to many database. And it doesn’t work. i have treated other ways but it didn’t work also.


Your indentation is off. Indentation is very important in Jython. That’s not to say there aren’t other problems, but you need to correct the indentation issue before I can assist any further.