color cells based on value in SQLFORM

[LoveWeb2py]

Hello,

I have a spreadsheet of items and I want to track their inventory based on color (similar to an excel spreadsheet).

I have two columns for example: Item, Item_Status
The item could be a desk, chair, etc... and for status I have 1 2 or 3.

If the status is 1 I would like to make the cell color of the desk green, 2 yellow, 3 red.

so far I have

def represent_colored(value):
   if value == 1:
   return SPAN(value,_style="background-color:green',)
elif value == 2:
   return SPAN(value,_style="background-color:yellow',)
elif value == 3:
   return SPAN(value,_style="background-color:red',)
else:
   return SPAN(value,_style="background-color:orange',)

def inventory():
   db.inventory.items.represent = lambda value, row: represent_colored(value)
   grid=SQLFORM.grid(db.inventory, paginate=20)
   return dict(grid=grid)

This works so far, but it only changes the background color of the text in the status column and I want to change the column of the actual item. I also would like to change the entire cell <td> instead of just the background behind the text.
Also, I plan on branching the inventory out to multiple columns and I have two questions:

1) How could I change the entire cell color?
2) Is making multiple "status" columns the most efficient and pythonic way to accomplish this.

You guys are the best!


Wil
  

[Anthony]

Might be easiest to do this with Javascript in the browser. You can also manipulate the grid DOM on the server -- maybe something like this:

colors = {1: 'green', 2: 'yellow', 3: 'red'}
grid = SQLFORM.grid(...)
table = grid.element('.web2py_table')
if table:
    [td.update(_style='background: ' + colors.get(td[0], 'orange'))
     for td in table.elements('td')]

That may be a bit slow because it must traverse the whole table to find the TDs and then iterate over the TDs to update their attributes.

Anthony

[Michael Beller]

I learned this method from one of Massimo's lecture video's:

At the top of your model file:

# function to allow models to set status style
def show_status(status,row=None):
    return SPAN(status.f_name.replace(' ','_'),_class=status.f_name) if status else status

After your table definition (could be in your controller but I set the represent after my table definition in my model file so it is consistent across all locations in my app):

db.table_name.status_field_name.represent = show_status

Note that in my models db.table_name.status_field_name is a reference field to a status table (so you select from a drop down list to select a status) and the name of the status in the status table is f_name.

In your CSS (I have one CSS static file for my application for application specific styles that I include in layout.html):

/* Status styles */
.Not.Started {background-color: yellow; padding:5px;}
.In.Progress {background-color: green; color:white; padding:5px;}
.Completed {background-color: red; color:white; padding:5px;}
.Deferred {background-color: blue; color:white; padding:5px;}

I think this is nicer than embedding an if statement with hardcoded styles in your code.

On Friday, May 9, 2014 4:22:45 PM UTC-4, LoveWeb2py wrote:

[Anthony]

Not tested, but another option would be to keep something like your current code (though you can simplify by creating just a single version of the SPAN code and fill in the color via a lookup in a dictionary, as in my previous example), but instead of setting style attributes in your represent functions, just set CSS classes (e.g., SPAN(value, _class='green-cell')). Then use jQuery to set the background colors of the table cells:

$('.web2py_table td .green-cell').parent().css({background: "green"});

Anthony

[LoveWeb2py]

Thank you for your reply, Michael & Anthony.

Michael: I really like this idea. What would my model look like?

I setup db.define_table('status',
     Field('id','integer'),
     Field('normal','string'),
     Field('warning','string'),
     migrate=False)

Would I have to populate the fields or can we just use the field names... I want to make it to where the referenced table allows me to choose a color for the selected column of interest (or cell).
So if my column looked like this:
Item | status
server | 1

The server cell would be green.Could I do this?

[Michael Beller]

I wouldn't recommend using field names, that would not be very flexible.  Here's an option:

db.define.table('status',

    Field('name'),

    Field('highlight_color'))

db.define.table('inventory',

    Field('item'),

    Field('status', 'reference status'))

Then just populate the status table with your list of statuses and its associated highlight color.  Then in your controller and view, create a query for the status colors and a corresponding loop in your view to build the list of status styles.  Something like this may work ...

{{for x in status_list:}}
    {{=x.name.replace(' ','_')}} {background-color: {{=x.highlight_color}}; padding:5px;}
{{pass}}

to produce this ...

.Not.Started {background-color: yellow; padding:5px;}
.In.Progress {background-color: green; color:white; padding:5px;}
.Completed {background-color: red; color:white; padding:5px;}
.Deferred {background-color: blue; color:white; padding:5px;}

[Anthony]

Keep in mind, this method will execute a separate database select for every row in the table in order to retrieve the status highlight_color. Also, the CSS rule will not color the background of the entire cell, just the status text (plus padding).

Anthony

On Monday, May 12, 2014 7:33:06 PM UTC-4, Michael Beller wrote:

I wouldn't recommend using field names, that would not be very flexible.  Here's an option:

db.define.table('status',

    Field('name'),

    Field('highlight_color'))

db.define.table('inventory',

    Field('item'),

    Field('status', 'reference status'))

Then just populate the status table with your list of statuses and its associated highlight color.  Then in your controller and view, create a query for the status colors and a corresponding loop in your view to build the list of status styles.  Something like this may work ...

{{for x in status_list:}}
    {{=x.name.replace(' ','_')}} {background-color: {{=x.highlight_color}}; padding:5px;}
{{pass}}

to produce this ...

.Not.Started {background-color: yellow; padding:5px;}
.In.Progress {background-color: green; color:white; padding:5px;}
.Completed {background-color: red; color:white; padding:5px;}
.Deferred {background-color: blue; color:white; padding:5px;}

On Monday, May 12, 2014 1:44:29 PM UTC-4, LoveWeb2py wrote:

[villas]

Here is a Bootstrap way which displays well enough in the grid.  Easy but not so flexible ...

    qstatus_labels = dict(Draft='label label-info',

                          Open='label label-warning',

                          Closed='label label-danger',

                          Won='label label-success')

    db.csquote.qstatus.represent = lambda id,row: SPAN(

       row.qstatus, _class=qstatus_labels.get(row.qstatus,'label label-default')

       )