Change background color depending on cell value

[areeb]

I have a table with a column full of dates. I'd like to change the background color to red if the date is more than a month old, otherwise keep it white.

I know I can do inline stuff like

{{ moment() - new Date(self) > 30 * (24*60*60*1000) ? 'red' : 'white' }} 

but as you can tell this is messy and hard to debug.

So I tried to create a new JS query and reference {{query1.value}} in the column's background color property but it doesn't seem to be working.

The query is trying to reference the variable i to get the current row but I can't seem to figure out how to get the current cell of the current row.

Kind of at a loss here, any help would be appreciated. Thanks

[Chris-Thompson]

Hey @areeb!

Happy to help here! I don't believe you can call a query for each of these rows like this but you could write a preloaded javascript function (link to our docs on this here) and pass the value of each row using this same self value to clean this up a bit. Do you think this could work for your use case here?

[areeb]

Thanks chris. How would I pass self for each row if I can’t call the function from there?

[Chris-Thompson]

If you create a custom JS function in your app, you should be able to call it in the column mapper using something like:

myCustomHelper(self)

Hope this helps, let me know if you have any questions!

[didexpectedgot]

I got this to work (you can use self in any argument to hsl() and any scaling values to get the result you want):

hsl(0, 100%, {{self/1000}})

It also works with rgb() (tested this) but not oklch(). I haven't tested others.

[kbn]

Thanks for sharing, @didexpectedgot ! Also wanted to share some implementations that other community members have used to do this more recently. With the current version of the Table component released a few months back, you can use item to configure dynamic background color for a cell in the new Appearance > Background field of the Table component.

See these two related community posts where @AnsonHwang shared a suggested implementation!

Color code a column in a table based on its value

Hello, self in new table is point to table itself. I try item is ok. but still with error tooltip. pls help to fix this wrong tooltip, we can get item in the background option's scope. [image]

New Table - Column Dynamic Appearance

Hello, self in new table is point to table itself. I try item is ok. but still with error tooltip. pls help to fix this wrong tooltip, we can get item in the background option's scope. [image]

[Isaac_Black]

I'm having an issue with the suggested implementation. When I add a row to the table the background colors to not move with the cells, so the cells above the old color
ed cells are now colored. In the picture below the cells with a value of today's date are colored red and tomorrow's date are colored orange but when a row is added it gets messed up until I refresh the page.

[kbn]

@Isaac_Black sorry to hear you're running into some friction getting this implemented. Could you please share a bit more info/screenshots on what you've implemented? What is the exact logic you're using for the background color, and how are you adding a row? Those details will help me pinpoint next steps!

[Isaac_Black]

I managed to fix the problem by re-querying the database before refreshing the table. I'm not sure why that made a difference as the new row was being displayed somehow just by refreshing the table.

[kbn]

@Isaac_Black glad it’s working for now, if you have capacity to share details on how the background color is configured and how you're adding rows, we’d appreciate it! certainly seems like odd behavior But glad re-querying works\

[Isaac_Black]

Below is the code for the background color. Its 2 ternary functions that check if the date stored in Item is today tomorrow or neither.
{{ (moment(item).format("YYYY-M-D") === (new Date().getFullYear().toString() + "-" +
(new Date().getMonth() +1).toString() + "-" +
new Date().getDate().toString()) ? theme.automatic : ( moment(item).format("YYYY-M-D") === (new Date().getFullYear().toString() + "-" +
(new Date().getMonth() +1).toString() + "-" +
(new Date().getDate()+1).toString()) ? theme.highlight : theme.surfaceSecondary))}}

For adding rows there is a form that when submitted adds a record to the database table that the retool table is sourcing from. On Success of that database insert, the retool table is refreshed.

With this configuration I was getting the issue mentioned in my original post.

I created a function for refreshing the retool table that first queries the database table then refreshes the retool table and that solved the issue.

[kbn]

@Isaac_Black thanks for sharing more details! Refreshing the table should also query the db, so not sure why you found you needed to do both - still, good that you have something that works for what you need

[Isaac_Black]

It is working, but it is strange that it only works if the DB gets queried twice.

[kbn]

@Isaac_Black indeed - if we want to get further with understanding why it only works if the DB gets queried twice, sharing a sample app export and some sample data will be best if that's possible to share here.

Also curious if you could share more about what this means/how this is implemented:

On Success of that database insert, the retool table is refreshed.