Update Perspective Table - unicode indices must be integers

Ignition
,
1

I’ve gotten stuck on how to make my table in Perspective editable and write back to the database. Here’s the process:

  1. Table data bound to query (default format)

  2. The following script runs on the query, making all cells except month editable

# convert dataset to dict where specified columns are editable
	editableCols = ["Budget","Monthly Actuals","Monthly Forecast"]
	ds = value
	colNames = ds.getColumnNames()
	d = []
	for i in range(ds.rowCount):
		rowDict = {}
		for col in colNames:
			editable = False
			if col in editableCols:
				editable = True
			rowDict[col] = {"value":ds.getValueAt(i,col),"editable":editable}
		d.append(rowDict)
	return d

This seems to be successful.

  1. Use onEditCellCommit to update the database. (I opted to update all the columns to avoid SQL injection, but maybe that’s not necessary?)
	#update perspective table
	# Get the value that was typed into the cell
	valueToSet = event.value
	self.props.data[event.row][event.column] = valueToSet
	
	#define table values
	month=self.props.data[event['row']]['Month']['value']
	budget=self.props.data[event['row']]['Budget']['value']
	monthlyActuals= self.props.data[event['row']]['Monthly Actuals']['value']
	monthlyForecast=self.props.data[event['row']]['Monthly Forecast']['value']
	
	#update database table
	query='Program Metrics/R and D/UpdateRDDataByLocalId'
	parameters={
		"local_Id":'247',
		"metric_Month":month,
		"budget":budget,
		"monthly_Actuals":monthlyActuals,
		"monthly_Forecast":monthlyForecast
	}
	system.db.runNamedQuery(query, parameters)
  1. The error occurs

Error running action ‘component.onEditCellCommit’ on Embedded_Program_Views/R&D@D/root/FlexContainer_4/FlexContainer_0/Table: Traceback (most recent call last):
File “function:runAction”, line 8, in runAction
TypeError: unicode indices must be integers

I’ve tried using float() to convert the values retrieved from the table to float, but that didn’t improve things. Any suggestions?

3

I see [event.row] in the fourth line, then [event['row']] in the seventh line. IIRC, adding the def at the top would make that line 8

2 Likes
4

That's funny.
You'll probably fix it by having self.props.data[event.row] instead of what you have.

EDIT: oops, didn't realize Jordan already had the fix.

EDIT2: actually, is it instead your column index that's wrong?
In props.data, I can't create an object/row with a key with a space in it in the Designer.

EDIT3: no, it works in script. Hm.

5

Hm I don’t think those are the issues. If i change to event.row and directly write a value for the columns with a space (instead of trying to pull the value from self), I still get the same error. See below for the edited script.

EDIT: If I directly write a value for budget instead of trying to pull the value from self, the script works fine. so the problem is somehow related to pulling the value from self.

	#update perspective table
	# Get the value that was typed into the cell
	valueToSet = event.value
	self.props.data[event.row][event.column] = valueToSet
	
	#define table values
	month=self.props.data[event.row]['Month']['value']
	budget=self.props.data[event.row]['Budget']['value']
	monthlyActuals= 200 #self.props.data[event.row]['Monthly Actuals']['value']
	monthlyForecast=300 #self.props.data[event.row]['Monthly Forecast']['value']
	
	#update database table
	query='Program Metrics/R and D/UpdateRDDataByLocalId'
	parameters={
		"local_Id":'247',
		"metric_Month":month,
		"budget":budget,
		"monthly_Actuals":monthlyActuals,
		"monthly_Forecast":monthlyForecast
	}
	system.db.runNamedQuery(query, parameters)
6

Ah, I just reproduced your issue.

You’re setting the new value directly to your column, effectively replacing your object {'value': ..., 'editable': ...}.

So the fix is probably:
self.props.data[event.row][event.column]['value'] = valueToSet

7

I will add that you don’t have to convert your DataSet to a dictionary if you don’t need cell-specific settings. You can just configure table.props.columns and set which one is editable in there.

8

Thanks so much for your help! Such a simple solution.

About converting the dataset to a dictionary… From what i understand the editable property doesn’t work quite right for columns in perspective, so you have to use the cell-specific setting unfortunately.