# Use Retool's Table as a Heatmap

## tl;dr

Find the column's Min and Max values for use in a calculation in `hsl()`.

i.e. Use a Query, Transformer, or Variable called `minMaxValues` :

`hsl({{((item - minMaxValues.value.result_a_min) / (minMaxValues.value.result_a_max - minMaxValues.value.result_a_min)) * 120}} 100% 65%)`

## How it works

Retool Table cells can be formatted with the Table's Column’s "Background color" attribute.

Retool Table gives access to `{{ item }}` and/or `{{ currentSourceRow }}` in order to make calculations in the Background color attribute for a row of a column of data.

There are two main ways to set the color of a cell:

1. Chain ternaries to get color steps (i.e. Red, Yellow, Green)
2. Normalize the color, i.e. calculate a color based on a range i.e. using HTML's `hsl()` function to go from `hsl(0 100% 65%)` through `hsl(120 100% 65%)`. All that's needed is a percentage value to calculate a color within the 0 to 120 range i.e. `hsl(0.5*120 100% 65%)`

Here's steps to set the Background color on a Retool Table:

``````Select your Table on the App Canvas ->
View the Inspector ->
Select a column ->
Update the Background color field.
``````

To find the "Background" attribute

## Color the quick way: Color steps with the ternary operator

If you know the boundaries for stepping to a different color, it's easiest to use a ternary.

Screenshot of Ternary color table:

### Column Background

1. Reminder how Retool works: The notation `{{ }}` with curly brackets is used in Retool to escape from inputting text and start writing javascript that has access to the current state.

2. Reminder about how ternary works:`{{ isCorrect ? 'green' : 'red' }}`

3. Example where `{{ item }}` is a reference to the current Row, and `{{ item.days_since_last_save }}` is a number of days since some update:
`{{ item.days_since_last_save.len === 0 ? 'default' : item.days_since_last_save < 30 ? '#b6d7a8' : currentRow.days_since_last_save < 90 ? '#d9ead3' : currentRow.days_since_last_save >= 365 ? '#fff2cc' : 'default'}}`

4. Pseudocode for that example:

``````if there's no data
‘default’
else if <30 days = ‘#b6d7a8’
else if <90 days = ‘#d9ead3’
else if <365 days = ‘#fff2cc’
else ‘default’
``````

## 2.1 Heatmap colors attempt 1: row-based approach to finding min and max

Apps can find the minimum and maximum for a range of data. Once the min and max are known, we can use those values to normalize the cell color, or figure out the percentage position along a range. This is useful in visual data analysis across many columns at the same time.

– sorted →

### V1 - Starting from a Table of data

As an un-optimized example first, because let's say you start by wanting to iterate a Table to find the mins and maxes.

This code could be placed in a Transformer called `minMaxValues` and it uses a Row-based approach to finding the Min/Max. Below is a faster column-based approach.

``````let result_a_max = -Infinity;
let result_a_min = Infinity;
let result_b_max = -Infinity;
let result_b_min = Infinity;

{{table1.data}}.forEach(row => {
// Calculate the Max and Min for every range needed

// Result_A
result_a_max = Math.max(result_a_max, row.result_a);
result_a_min = Math.min(result_a_min, row.result_a);

// Result_B
result_b_max = Math.max(result_b_max, row.result_b);
result_b_min = Math.min(result_b_min, row.result_b);
})

return {
result_a_max: result_a_max,
result_a_min: result_a_min,
result_b_max: result_b_max,
result_b_min: result_b_min
};
``````

Note: I started with this because sometimes you'll start from a Table and be thinking in Rows. There's a helper function to convert columnar data into row-based data with

## 2.2 Heatmap calculation optimized for Columns of data

Often data is already given as column data in `query.data` and finding the min and max of an array in Javascript is `Math.min({{getdata.data.array}})`

Screenshot: hovered over `getData.data` to find that we already have columns available.
`formatDataAsArray()` to keep using row-based approaches.

### V2 - Final version. Don't build your own columns.

The `minMaxValues` code (Transformer or JS Query) is just this for my two columns:

``````return {
result_a_max: Math.max({{getData.data.result_a}}),
result_a_min: Math.min({{getData.data.result_a}}),

result_b_max: Math.max({{getData.data.result_b}}),
result_b_min: Math.min({{getData.data.result_b}})
};
``````

Reference the value `{{ minMaxValues.result_a_max }}` and `{{ minMaxValues.result_a_max }}` and `{{item}}` or `{{currentSourceRow}}` on Retool Table's configuration to calculate each cell value's position in the min/max range.

### Pick a color

For the column's Background attribute: `hsl({{((item - minMaxValues.value.result_a_min) / (minMaxValues.value.result_a_max - minMaxValues.value.result_a_min)) * 120}} 100% 65%)`

Hue is computed in HSL(Hue Saturation Lightness) where Hue is `((value - min) / (max - min)) * upperThreshold`, or "{{item}}'s value's percentage within the range of colors from 0 to upperThreshold"

W3 Colors HSL: Colors HSL and HSLA

### Sidenote: Mins and Maxes via SQL

The minimum and maximum values of table data can be found with SQL`Select` in a Database Query i.e. `SELECT MIN(result_a), MAX(result_a) FROM your_table;`

## Use Retool Table

If you don't have access to `{{item}}` or `{{current_row}}` inside the Column's settings then upgrade your instance and use the new components!

### Quick how-to

To set Cell colors, click on your table and then select the Column you want to use

Screenshot: Here's my "Result a" column selected on a Table:

## Visible values

Tables can have a Fuzzy Match Search that changes what’s "visible" in the table.

Screenshot: Fuzzy search with a text input:

A Javascript Query i.e. `currentVisibleTableData` can run this code to retrieve the live Table data: `return table1.getDisplayedData()` and Javascript Queries must be triggered by some event in Retool.

Transformers then reference the output of the Javascript Query `currentVisibleTableData.`

When the Fuzzy Filter's value changes the event handler can trigger the JS Query `currentVisibleTableData` to run the `getDisplayedData` function and update its state.

### Fast UI

Depending on your call-chain the Transformer may run faster than `table1.getDisplayedData()` and cause a UI mismatch. Might be resolved in the future.

i.e.

Add a debounce on the change event to reduce mis-coloring the rows with old data while typing.

Screenshot: Change event on the Text Input that's connected as a Fuzzy Match filter on the Table:

9 Likes