Using system.util.invokeAsychronous on Power Table Extension Functions

Ignition
1

I am trying to split my extension functions into database and GUI activities to speed up the GUI. I’ve used system.util.invokeLater and system.util.invokeAsychronous successfully on a button’s actionPerformed eventHandler. However, I’m trying to do this on the extension functions of a power table with no success. I don’t get any errors, just that the configureEditor doesn’t display dropdown nor does the configureCell replace text or color coat the cell, which was working prior to implementing these (2) functions.

configureEditor

	def async():
		def getlist(clist):
			#move the data into a python list of tuples
			cust_list = []
			for row in clist:
				thisList = []
				for item in row:
					thisList.append(item)
					thisTuple = tuple(thisList)
				cust_list.append(thisTuple)
			return cust_list
			
		query = "SELECT TypeID, TypeName FROM PMType ORDER BY TypeName DESC"
		clist = system.db.runPrepQuery(query)
		type = getlist(clist)
		
		query = "SELECT FreqID, FreqName FROM PMFreq"
		clist = system.db.runPrepQuery(query)
		freq = getlist(clist)
		
		query = "SELECT ComponentID, ComponentName FROM PMComponent ORDER BY ComponentName DESC"
		clist = system.db.runPrepQuery(query)
		comp = getlist(clist)
		
		query = "SELECT SiteID, Site FROM PMSite ORDER BY Site DESC"
		clist = system.db.runPrepQuery(query)
		site = getlist(clist)
		
		def later():
			#Return drop down list of tuples
			if colName == 'TypeID':
				return {'options': type}
			elif colName == 'FreqID':
				return {'options': freq}
			elif colName == 'ComponentID':
				return {'options': comp}
			elif colName == 'SiteID':
				return {'options': site}
			elif colName == 'SystemID':
				return {'options': [(1, 'P1'), (2, 'P2'), (3, 'P3'), (4, 'P4')]}
			elif colName == 'Criticality':
				return {'options': [(1, 'Low'), (2, 'Medium'), (3, 'High')]}

		system.util.invokeLater(later)
	system.util.invokeAsynchronous(async)

configureCell

	def async():
		from datetime import datetime
		def render(value,col,tbl,ID):
			query = "SELECT %s FROM %s WHERE %s = ?" % (col,tbl,ID)
			results = system.db.runScalarPrepQuery(query,[value],'MSSQLtest')
			return results		
		
		def later(value=value):
			if colName == 'TypeID':
				if value:
					return {'text': render(value,'TypeName','PMType','TypeID')}
				else:
					return {'text': ''}
			elif colName == 'FreqID':
				if value:
					return {'text': render(value,'FreqName','PMFreq','FreqID')}
				else:
					return {'text': ''}
			elif colName == 'ComponentID':
				if value:
					return {'text': render(value,'ComponentName','PMComponent','ComponentID')}
				else:
					return {'text': ''}
			elif colName == 'SiteID':
				if value:
					return {'text': render(value,'Site','PMSite','SiteID')}
				else:
					return {'text': ''}
			elif colName == 'SystemID':
				if value == 1:
					return {'text': ("P1")}
				elif value == 2:
					return {'text': ("P2")}
				elif value == 3:
					return {'text': ("P3")}
				elif value == 4:
					return {'text': ("P4")}
			elif colName == 'Criticality':
				if value == 1:
					return {'text': ("Low"), 'background': system.gui.color(255,255,71)}
				elif value == 2:
					return {'text': ("Medium"), 'background': system.gui.color(255,172,71)}
				elif value == 3:
					return {'text': ("High"), 'background': system.gui.color(255,71,71)}
			elif colName == 'LastCompletionDate':
				if value:
					value = system.date.parse(value, 'yyyy-MM-dd')
					#return {'text': (type(value))}
				#else:
					#return {'text': (type(value))}
		system.util.invokeLater(later)
	system.util.invokeAsynchronous(async)

Any thoughts would be appreciated.

Thanks,

Josh

2

I think that the return value inside of the invokeLater does not get passed out to the extension function. It is just returned to the invokeLater, which itself has no return. If you run a version of this from the script console, you can see it doesn’t return. The extension functions are immediately returning None, and then running these functions in a separate thread that, as far as I know, have no way of getting back to the extension function (which has already returned anyways).

Can you just place the queries inside of component properties on the table, especially for the editor? Right now it looks like it is going to run all those queries for every cell, will definitely help speed it up if you only run those queries on a poll.

1 Like
3

Thanks Nick! That really did speed things up. Below is my updated code. I did as you suggested and created custom properties for the power table, some with SQL bindings and some user-defined. This speeds things up since I’m only querying one time, I am not using polling in this situation as I don’t anticipate these datasets changing while a user is doing data entry.

Going to see if there is any further optimization I can do with the onCellEdited and onPopupTrigger in addition to making the configureEditor and configureCell into project scripts.

image
image1827×1080 144 KB

configureEditor

	def getlist(clist):
		#move the data into a python list of tuples
		clist = system.dataset.toPyDataSet(clist) #convert dataset to PyDataSet
		cust_list = []	#empty list
		for row in clist:
			thisList = []	#create empty list for each row in PyDataSet
			for item in row:
				thisList.append(item)	#append value in each row into thisList
				thisTuple = tuple(thisList)	#convert thisList to tuple
			cust_list.append(thisTuple)	#append tuple to list cust_list
		return cust_list	#return list of tuples
	
	#Get Dropdowns menus based on colName from datasets in custom properties of powertable
	if colName == 'TypeID':
		return {'options': getlist(self.type_drop)}
	elif colName == 'FreqID':
		return {'options': getlist(self.freq_drop)}
	elif colName == 'ComponentID':
		return {'options': getlist(self.comp_drop)}
	elif colName == 'SiteID':
		return {'options': getlist(self.site_drop)}
	elif colName == 'SystemID':
		return {'options': getlist(self.sys_drop)}
	elif colName == 'Criticality':
		return {'options': getlist(self.critic_drop)}

configureCell

	if value:	#Only proceed is cell is not Null
		#Get String associated with ID based on colName from dataset in customer properties of powertable
		if colName == 'TypeID':
			return {'text': self.type_drop.getValueAt(value-1,1)}
		elif colName == 'FreqID':
			return {'text': self.freq_drop.getValueAt(value-1,1)}
		elif colName == 'ComponentID':
			return {'text': self.comp_drop.getValueAt(value-1,1)}
		elif colName == 'SiteID':
			return {'text': self.site_drop.getValueAt(value-1,1)}
		elif colName == 'SystemID':
			return {'text': self.sys_drop.getValueAt(value-1,1)}
		elif colName == 'Criticality':
			text_over = self.critic_drop.getValueAt(value-1,1)
			r = self.critic_color.getValueAt(value-1,1)
			g = self.critic_color.getValueAt(value-1,2)
			b = self.critic_color.getValueAt(value-1,3)			
			return {'text': text_over, 'background': system.gui.color(r,g,b)}
	else:
		return {'text': ''}	#return empty string if cell is Null
4

Try to avoid defining functions within your event routines. def is a relatively slow operation that you really want to keep in project or shared scripts.

3 Likes