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.
Hi PGriffith
What happens is that I have 2 datasets that I bring directly from the database, these datasets have respectively the information that I want to show in the table and the backgrount color for each cell in a particular way, the datasets are 1500Rx37C but when I apply a script to read the color data that I want to place in each cell and then transform to JSON, Ignition stops working and I get error of limitation of columns in the loop, how I could solve this to print the colors that I have in the datase.
This is the code:
def perspectiveTableConfig(tableDataFilt, tableConfigFilt):
py_data = system.dataset.toPyDataSet(tableDataFilt)
py_config = system.dataset.toPyDataSet(tableConfigFilt)
data = []
default_style = {"background": "lightgray"} # Default color for cells without style value
alternate_style = {"background": "white"} # Alternate color for cells with no style value
for i in range(py_data.getRowCount()):
row_dict = {}
for j, header in enumerate(py_data.getColumnNames()):
cell_value = py_data.getValueAt(i, j)
cell_style = py_config.getValueAt(i, j)
# Check if the style is empty and toggle between the default color and the alternate color.
if not cell_style:
if i % 2 == 0:
cell_style = default_style
else:
cell_style = alternate_style
cell_obj = {"value": cell_value, "style": cell_style}
row_dict[header] = cell_obj
data.append(row_dict)
return data
I think the final version of this is going to use list comprehension, zip, and CSS styles...
In the meantime, you don't need to getColumnNames for each row - do that once outside the outer loop.
You are using pyDataSets so I think you can address cell_value as py_data[i,j]
And probably a few more things to make it pythonic.
This seems contradictory. What does 'stops working' mean (for example, what stops working, and how do you know what error you're getting if it stops working?)
Exact error details, including the stacktrace, are infinitely valuable if you've got it.
You are right, the error I get is this " [ java.lang.ArrayIndexOutOfBoundsException] ", but I get it when I reduce the columns I am evaluating, when I work with the full size dataset it just closes the designer.
Agreed. This code assumes tableDataFilt and tableConfigFilt are datasets of the exact same dimensions. As you're using the expected indices of tableConfigFilt, it's highly likely that tableConfigFilt either has fewer rows, or fewer columns.
