Update SQL through text box connected to powertable

Greetings. Have a (hopefully) quick question here. I have a power table that is connected to a SQL query that holds our raw paint data. I am having our engineers use the power table columns, some of them to update directly in the power table the descriptions that we use so they can easily distinguish them. This is non-essential data that I’m not to worried about them entering correctly as they’ll use different terminology for them throughout time, as in the program name could change, paint be the same. In that I also have a text box that is pulling a specific column from SQL so that when they select one of the rawpaint it will be bring in the current value at “LBGal” in SQL.

What I need is for them to be able to select a raw paint selection from the power table it show what is the current value in the “LBGal” column for that rawpaint, but they are also able to update this amount within that text box, once they’ve updated it will then write back to the database instead of having some form of a “Go” trigger attached to a button on the screen. Hopefully that makes sense, attaching image below.

Additionally I should add that I have the following expression in the text box data field currently :

try({Root Container.Power Table.data}[{Root Container.Power Table.selectedRow},“LBGal”],“Nothing Selected!”)

You could add an event handler to the Text Area with a script to write back to the database but the issue is you’ll need a way of determining when the user is done typing and ready to save the change. That’s why a “commit” button would be nice.

You could use system.gui.inputBox from an event handler on the power table or a separate button to get the new value, then commit it.

2 Likes

Maybe instead of using a text box, use another power table that will return the SQL query results from LBGal. You obviously can reduce the size of the power table to resemble a text box. Then enable editing in the table customizers, and enable the onCellEdited event trigger to update the db. You should be able to type in a new value in the cell and press enter which should commit it. Not sure if this is exactly what you where looking for.

1 Like

Honestly that’s a really good idea!

We’re using this particularly as a scenario tool as well, so if our engineer get’s a notion to fiddle and see what would happen he has that ability; by their request. Personally I think it’s a bad idea but…yea

I have a few power tables that are editable like this. I also have script attached to the db update script to log the change in another db table, and sends out an email noting the change. This way if something gets screwed up, I can track what changes happened. I also make them select an edit button so they don’t change things by accident.

	if self.parent.getComponent('Edit Button').selected == 1:
		self.data = system.dataset.setValue(self.data, rowIndex, colIndex, newValue)
		
		if newValue == oldValue:
		    return
		else:
			ndx = self.data.getValueAt(rowIndex,0)
			system.db.runPrepUpdate("UPDATE table SET %s=? WHERE table_ndx=?"% colName, [newValue,ndx])
			system.db.refresh(self,"data")
			
			style = self.data.getValueAt(rowIndex, 'column')
			field = colName
			smtp = "mail@example.com"
			sender = "Big Brother Is Watching"
			subject = "Edit logs"
			body = "<HTML>There has been an edit to blah blah.<br/> Style Edited: "+ style + "<br/>Column Edited: "+field+"<br/> New Value:" + newValue + "<br/> Old Value:" + oldValue + "</HTML>" 
			recipients = ["bigbrother@example.com"]
			system.net.sendEmail(smtp, sender, subject, body, 1, recipients) 
	
			action = "Change At: "+ field + ' Old: ' + oldValue + ' New: ' +newValue  
			user = system.tag.read("[System]Client/User/Username").value
			time = datetime.datetime.now()
			system.db.runNamedQuery('Styles/LogChange', {'action':action, 'user':user, 'time':time, 'style':style})
1 Like

Thank you my man! I appreciate it! I’ll definitely check this out and probably implement this in as I’m positive at some point an edit will be made that needs to be un-made and the old info won’t be available haha!

1 Like