OnCellEditCommit Scripting - Changing database table

Ignition
, ,
1

Hello,

I'm currently working on a table I'd like to be editable. I have the following script:

#Variables
	FirstName = self.props.selection.data[0].Fname
	LastName = self.props.selection.data[0].Lname
	ClockNumber = self.props.selection.data[0].EID
	Status = self.props.selection.data[0].Status
	ApprovedShift = self.props.selection.data[0].ApprovedShift
	ApprovedSecondShift = self.props.selection.data[0].ApprovedSecondShift
	params = {"FirstName" :FirstName, "LastName" :LastName, "ClockNumber" :ClockNumber, "Status" :Status, "ApprovedShift" :ApprovedShift, "ApprovedSecondShift" :ApprovedSecondShift}

	#Allows table value to be set
	valueToSet = event.value
	self.props.data = system.dataset.setValue(self.props.data, event.row, event.column, valueToSet)

	#Runs query to update database table
	system.db.runNamedQuery("Overtime/Trade Tables/Table Update Query", params)

This script allows me to edit the values of status, approved shift, approved second shift against first name, last name and clock number. My problem is that if I commit, it doesn't send the new and updated number, instead it sends the old value. So, for example, if I were to change the "Approved Second Shift" column value for an employee to the value of 2, it would keep the value of 0 -- then if I were to re-submit a value of 0, it would keep the previous value of 2. The script doesn't seem like it has time to let that value change and then insert to the database since it is all during the "OnCellEditCommit" action. I've tried using a sleep(3) timer and that didn't seem to do anything, along with a while counter. Does anyone have any tips for how to get this to work?

2

Try using the data of the table and reference what fired the event. This might allow your script to work.

For example,

FirstName = self.props.data[event.row].Fname

I do it a little differently in that I append to a list of rows that have been modified. Then I have a save changes button that runs through the dataChanged custom property and does a SQL update from each row of data.

1 Like
3

That sounded like a great idea so I tried it out and it returned "object is not scriptable". Could you give me an example of how you did it? If the manager needs to click one submit button, I don't see it being the end of the world. In your case, doing it the way you do, what happens if they don't press the submit button? Do the values stick in the table and throw numbers off or reset when they leave the page?

4

First save what rows have changed. I get the event.row int and append it do a custom prop. Don't forget to search in the property that the value doesn't already exist.

Then when the save button is pressed, I send all of the data from the table and the list of changed rows to a function that runs a sql update on each row number from the change list.

If you don't press submit, all data is lost if the user go to a different page or changes the inputs that generates the table's data. I make sure all of the data is clear at startup of the page.

1 Like
5

I solved this on my own. The answer to this would be to leave the OnCellEditCommit script to only set the change of the value in the table, then on that specific value being changed (Example: Status) I would go to the Status under props > selection > data > 0 > Status and add a change script to that value. I made the change script run the query and voila! It works.