Ignition 8.1.5 - Perspective Table - How to populate Data Property (Dataset vs JSON)?

I have been reviewing other forum threads on this, but can’t seem to find good “guidance” on how best to work with the Perspective Table Component when it comes to mapping data to the Data property in the component… The easiest method I have found to populate the Data property of the Table Component is using Datasets, however, in this format it seems to be inflexible in terms of being able to edit cells, change colors etc. vs a pure JSON object. (See illustration).

My question to the forum users and experts that have used this Component is:

  1. If you are querying data from a database to subsequently display in a Table Component, the most common return object from a Named Query is a Dataset. How do you manipulate a Dataset to add properties to make fields “Editable” or change colors etc. like you can with a pure JSON object.
  2. Is there a easy way to return a JSON object from a query that would map more readily into the Data property that would make it easier to than manipulate and Append properties like “editable” and “style”?

Many thanks in advance to the community for there advice.

I think (perhaps) part of the solution may lie in the following code to transform datasets to dictionaries. I will look into this more and see where this leads:

# convert the incoming value data

pyData = system.dataset.toPyDataSet(value)
# get the header names
header = pyData.getColumnNames()
# create a blank list so we can append later
newList = []
# step through the rows
for row in pyData:
# create a new blank dictionary for each row of the data
newDict = {}
# use an index to step through each column of the data
for i in range ( len (row)):
# set name/value pairs
newDict[ header[i] ] = row[i]

# append the dictionary to list

# return the results
return newList

Reference: Script Transform - Ignition User Manual 8.1 - Ignition Documentation

Bind against the dataset you want to use, then apply a script transform in this shape:

    py_data = system.dataset.toPyDataSet(value)
	data = []
	headers = py_data.getColumnNames()
	row_count = py_data.getRowCount()
	for i in range(row_count):
		row_dict = {}
		for header in headers:
			row_dict[header] = py_data.getValueAt(i, header)
	return data

This will convert your dataset into what is essentially a json object.



Have utilized this approach and it seems to be working.

Although, if you’re using a query binding, you can just change the return format to json - you don’t need to bring in as a dataset and then attempt to convert.

1 Like

Not sure if overkill or not, but I have added a function in incendium to transform Datasets, even those with nested Datasets if it were to be the case (see wiki) “into what is essentially a json object”.

Expect it on version 2022.8.2 in Ignition Exchange, or you may just clone the project branch under your {IGNITION_DIR}/data/projects directory (instructions can be found here), or download the ZIP file from Releases and install it on your Gateway.

And remember, PRs are welcome.