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 TableComponent 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:
If you are querying data from a database to subsequently display in a TableComponent, 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.
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 forrow inpyData: # create a new blank dictionary for each row of the data newDict ={} # use an index to step through each column of the data fori inrange(len(row)): # set name/value pairs newDict[ header[i] ] =row[i] # append the dictionary to list newList.append(newDict) # return the results returnnewList
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)
data.append(row_dict)
return data
This will convert your dataset into what is essentially a json object.
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.
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.