I've been at it for a few days and I don't understand.. 
What should I do so the cell in the Status column become stylized based on their value.
Let's say your query returns a table that looks like this:
| name | value |
|---|---|
| foo | 1 |
| bar | 2 |
| pox | 3 |
| wuz | 4 |
First thing to do is change the return type to json instead of auto, since we're gonna work with json and not datasets. It should look like this:
data returned by the query
[
{
'name': "foo",
'value': 1
},
{
'name': "bar",
'value': 2
},
{
'name': "pox",
'value': 3
},
{
'name': "wuz",
'value': 4
}
]
We want to add a style key to every dict, with a color that corresponds to the value.
Now, how to do that exactly depends on what colors you want for what values, but we'll just use red for even values and blue for odds.
So, we want to get this:
what we want to have
[
{
'name': "foo",
'value': 1,
'style': {
'color': "blue"
}
},
{
'name': "bar",
'value': 2,
'style': {
'color': "red"
}
},
{
'name': "pox",
'value': 3,
'style': {
'color': "blue"
}
},
{
'name': "wuz",
'value': 4,
'style': {
'color': "red"
}
}
]
So, in code, this would look something like this:
output_data = []
for row in value:
d = dict(row)
if row['value'] % 2 == 0:
d['style'] = "red"
else:
d['style'] = "blue"
output_data.append(d)
return output_data
This is a bit long, but we can make it shorter:
return [dict(row, **{'style': {'color': "red" if row['value'] % 2 == 0 else "blue" }}) for row in value]
This is a bit hard to read ! And it gets messy if you want the color selection to be more complex.
You'll need to find the balance between expliciteness and complexity that works best for you, the important part is that you can read and understand what's going on.
You'll likely need to address a few things:
- why are we making a new list with new dict objects instead of injecting the new keys in the old dicts
because the original objects are actually not dicts, are they're immutable. (Though I may be confusing things here, and I can't test it) - Then why are we asking for json from the query if we have to rebuild a data structure ?
Phil would tell you not not, because we're adding a layer of processing, but let's make things simple before making them more efficient (if there's a need for optimization) - what determines the color ? if the logic is more complex than just one condition, if there are a lot of different possible colors, etc. you'll have to move that logic out.
- probably many things I can't thing of right now, because it's time for me to go to bed.
1 Like
I copy pasted your code and made some modifications to it and I get this error:
line 2, in transform TypeError:'..........................." Object is not iterable
return [dict(row, **{'style': {'color': "green" if row['value'] == "Old" else "red" }}) for row in value]
I really want this to work because I don't want to rely on hard code, here is something I tried, with the help of the great amazing community, that worked for me.
from copy import deepcopy
if self.getSibling("TextField_Asset").props.text is not None and len(self.getSibling("TextField_Asset").props.text) > 3:
txt = "%" + self.getSibling("TextField_Asset").props.text + "%" # CREATING LIKE PARAMETER %userinput%
self.custom.custpro = txt
query = "SELECT Val1 as Asset, Val2 as Sheet, Val3 as Title, Rev, Status, Path, Val6 as Vendor, Val7 as Category, FileNameModified, ID FROM ProductionData.dbo.DrawingInfo WHERE Val1 LIKE ? AND DocType = 2 ORDER BY Val2, rev DESC"
newData = [] # EMPTY LIST FOR DATASET
results = system.db.runPrepQuery(query, [txt]) # RUNNING QUERY
stylesDict = {'Old' : {'backgroundColor': '#00FF00', 'color': '#FF0000'}, 'Current' : {'backgroundColor': '#FFFFFF', 'color': '#00FF00'}}
colNames = results.columnNames
# Create a blank version of newRow.
blankRow = {col : {'value': None, 'style' : {}} for col in colNames}
for row in results:
newRow = deepcopy(blankRow)
for col in colNames:
newRow[col]['value'] = row[col]
if col == 'Status':
newRow[col]['style'] = stylesDict[row[col]]
newData.append(newRow)
self.getSibling("Table").props.data = newData
That was days in the making 
.
Here is what it looks like, and aditonally I can add more to the styledict right? I can define column widths, text alignment, etc?
You know, I actually had it as Json at first but it was giving me the non-itterable error. I tried it again and it was no longer there. However, now it's throwing a KeyError:value
this is not correct
you would want
{
'value':{
'name': "bar",
'value': 2
},
'style': {
'color': "red"
}
},
1 Like
he made this asuming the coljumn named "value" was a thing
Why would you do a query in here?
Yes, you could, but you should but styles for the whole column in the columns prop, not in the individual cell
I always get lost in the table's formats :X
It's been a journey trying to get a stylized table figured out.
There were two options available for me to stylize a table, using a data binding and transform- but I could not figure it out and still haven't until I try what you sent me 
or running a script with PrepQuery/NamedQuery- which is what is working so far, so I tried both variations.
this is an example that uses dataset
I appreciate all the help and I love the community help
I'll read over the link you sent me.
actually it was still not right, in the screenshot he only wanted to color one cell not the whole row
{
'value':{
'name': "bar",
'asset': 2,
'Status': {
'value': "OLD"
'style': {
'color': "green"
}
}
},
'style': {
'color': "red"
}
},
if do the binding with a json output,
and paste a couple of values here of the output,
then i can make that it work for you too:)
i prefer working with objects ("json") instead of datasets tbh (if there isnt to much typing going on)
That worked! YOU"RE THE BEST!
How do I make it so that just the cell is stylized and not the whole row?
Here is my code in the transform script:
def transform(self, value, quality, timestamp):
style_orange = {'backgroundColor': '#e97e7d', 'color': '#ffffff'}
style_green = {'backgroundColor': '#9dd55a', 'color': '#181F23'}
output_json = []
for row in range(value.getRowCount()):
row_object = {}
row_value = {}
row_style = {}
for col in range(value.getColumnCount()):
row_value[value.getColumnName(col)] = value.getValueAt(row, col)
row_object['value'] = row_value
if value.getColumnName(col) == 'Status':
if value.getValueAt(row, col) == 'Old':
row_style = style_orange
elif value.getValueAt(row, col) == 'Current':
row_style = style_green
row_object['style'] = row_style
output_json.append(row_object)
return output_json
Would you recomend the databinding transform, depicted above ^ or the way I've done it, depicted below:
from copy import deepcopy # WE NEED THIS FOR OUR deepcopy FUNCTION
if self.getSibling("TextField_Asset").props.text is not None and len(self.getSibling("TextField_Asset").props.text) > 3:
txt = "%" + self.getSibling("TextField_Asset").props.text + "%" # CREATING LIKE PARAMETER %userinput%
self.custom.custpro = txt # WE HAVE A CUSTOM PROPERTY ON THE BUTTON, IT'S A PARAMATER THAT'S BOUND TO THE TABLE
query = "SELECT Val1 as Asset, Val2 as Sheet, Val3 as Title, Rev, Status, Path, Val6 as Vendor, Val7 as Category, FileNameModified, ID FROM ProductionData.dbo.DrawingInfo WHERE Val1 LIKE ? AND DocType = 2 ORDER BY Val2, rev DESC"
newData = [] # EMPTY LIST FOR DATASET
results = system.db.runPrepQuery(query, [txt]) # RUNNING QUERY
# STYLE DICTIONARY THAT IS USED TO STYLIZE OUR CELLS BASED ON VALUE IN THE CELLS
stylesDict = {'Old' : {'backgroundColor': '#e97e7d', 'color': '#ffffff'}, 'Current' : {'backgroundColor': '#9dd55a', 'color': '#181F23'}}
colNames = results.columnNames
# Create a blank version of newRow.
blankRow = {col : {'value': None, 'style' : {}} for col in colNames}
# ITTERATE THROUGH THE ROUGHS AND COPY THE ROWS ALONG WITH THE NEW STYLIZED PARAMETERS
for row in results:
newRow = deepcopy(blankRow)
for col in colNames:
newRow[col]['value'] = row[col]
if col == 'Status':
newRow[col]['style'] = stylesDict[row[col]]
newData.append(newRow)
self.getSibling("Table").props.data = newData # Assign newData to the table
else:
# USING THESE VARS TO CREATE AN EMPTY TABLE WITH OUR HEADERS
columnNames = ["Status", "Asset", "Path", "Rev", "Category", "Title", "FileNameModified", "Vendor", "ID", "Sheet"]
emptyRow = [""] * len(columnNames)
emptyDataSet = system.dataset.toDataSet(columnNames, [emptyRow])
self.getSibling("Table").props.data = emptyDataSet
for the cell you need another value/style object at the specific value
like here. replace the container_type with your columns name "Status"
also note that lower in this topic i suggest using something different than this. read the whole topic should get you all the answers you need
You should definitly do the transform for something like this
That code doesn't look right.
Here's a refactor of the transform: mostly converting loops into comprehension, and taking things that shouldn't be in loops out of them:
style_orange = {'backgroundColor': '#e97e7d', 'color': '#ffffff'}
style_green = {'backgroundColor': '#9dd55a', 'color': '#181F23'}
return [
{
'value': {
value.getColumnName(col): value.getValueAt(row, col)
for col in xrange(value.getColumnCount())
},
'style': style_orange if value.getValueAt(row, 'Status') == "Old" else style_green
} for row in xrange(value.rowCount)
]
That's assuming you want the row red for Status == "old" and green for every other rows.
Now, if we want to apply the style to only one column, I'd set the return format to json and use this:
style_orange = {'backgroundColor': '#e97e7d', 'color': '#ffffff'}
style_green = {'backgroundColor': '#9dd55a', 'color': '#181F23'}
for row in value:
row['Status'] = {
'value': row['Status'],
'style': style_orange if row['Status'] == "Old" else style_green
}
return value
1 Like
When refactored code is minified while keeping its functionality.
We call that beautiful
Thanks a million!
I should point out that my goal is not to make it as small as possible, but clear and easy to follow.
1 Like