Set a color in conditional format by formula

@Paul_Danyliuk, you’re right. For whatever reason I couldn’t get other hex colors to work for me the other day, but all values seem to be working now. :man_shrugging:

Since this is post already contains a list of hidden functions, I want to add some info about the non-hex colors.
(For example, to use in Button()).

List of available color (case-sensitive):

  • Red
  • Orange
  • Yellow
  • Green
  • Blue
  • Purple
  • Pink
  • Gray

Providing an incorrect color name will permanently crash your doc, even if you haven’t finished typing in the formula. (The doc can be recovered with version history).

That’s why it’s important to add the ending " after the color is fully typed in:

And not type like this:

P.S.
In a few cases, such as buttons, you can also change the font color independently from the main color by using the _Color function as suggested in this post.

Yeah, that one bit me as well.

Another interesting “property” of the BUTTON() (and probably some other artificial elements) - double clicking it can crash the document. (You can simply reload for it to work again, but it’s still pretty annoying.)
And this happens even if the button is disabled.

When does this happen?
So far I noticed this when an artificially created button is displayed inside a Text column. My guess is that the button clicking action somehow collides with the double-click action to edit formula.

Solution 1
This is the less pretty solution out of the two in this post, but I’m going to show it anyway in case the other one stops working.

Change column type
Using certain column types can prevent crashing - slider, scale, date and time.
This also works for list, lookup from table and people column, as long as they have “allow multiple selections” set to true.

Unfortunately, this solution works only for pure buttons, so they cannot be in a list or combined with anything else via Concatenate().
What’s more, the type mismatch causes error (red triangle and hover-over bubble) to be displayed in that column at all times.

Wrap everything with a button
The basic idea behind this is to move the formula to a label of dummy button. As it turns out, the buttons still work when they are a part of a label. But again, an error will be always visible.
It is also a good idea to disable the dummy button to prevent flashing whenever you click on it or any other sub-buttons.

Example formula:

And by setting background color to white in conditional formatting you can even hide the button in the background. (Unfortunately, any text will be lighter and even using _Color() won’t change it back to black).

Wrap text with a pseudo-select
There actually is a way to make the text darker again - if you wrap your text in a SELECT() it’s not affected by the disabled button.

An important thing to note about using SELECT() - it’s first argument (i.e. text that will be displayed) must be a column reference, using a string won’t work. However, referenced column doesn’t have to be a select column and can be of any type.

Here is an example formula:

And the result looks like this:

Solution 2
This solution is slightly simpler and doesn’t display an error in the rows.

Changing column to button
Create a standard button column and make it so the buttons are always disabled.
Then put the combined buttons and text into the button label.

And by applying the conditional formatting and wrapping text in pseudo-selects (as it was explained in the previous solution), you can make it completely seamless.

And a final overview of how this solution works:

3 Likes

Dear @Filmos,

Great input, I see you have a serious skill set and valuable inputs.:gem:
Would you mind to put this in a seperate post and mention (@) one of the Codans so they can evaluate your input?

Moved list of hidden formulas to this post, as we have slightly gone off-topic from the original question.

This just got a whole lot easier for you today!

5 Likes

I was wondering if there are any new enhancements to changing button properties through a formula… specifically changing the color of the button itself in a way that is safe?

Andrew, wondering if it might be possible to use a formula to drive row colors?

I.e. specifying the color for a cell formulaicly

Might look like adding a formula specifier in this area
image

And then the formula could be something like:
CurrentValue.[Type].[Color]

Where the Color attribute could be a hexadecimal color code.

This way I wouldn’t have to set up endless mappings from something’s type to its color like I’ve had to do here:

You CAN set the background (and font) colors of text using the LaTeX pack function LaTeX::Math()

In LaTeX you can create a ‘colorbox’ and also set the color of the text.
In the example below, I generate the LaTeX colorbox algorithmically based on the values in the row.


You can use HTML color names or, instead, use the ‘#RRGGBB’ hexadecimal format (ge #FFFF00 for yellow)
Ive shown the LaTeX code to illustrate what it looks like.

These colored items look great (there are far more vibrant colors available etc) BUT…
Unfortunately, these LaTeX columns are rendered as SVG images, so LOOKUPs just get a ton of SVG code and not the actual text.

@Paul_Danyliuk has also shown how to generate images using SVG and HTML - and these could also be used to set the background color

Respect
Max Xyzor

4 Likes

hi @Xyzor_Max , would you mind sharing a doc with this code. I have no experience with LaTeX , but would love to comprend the applied logic, certainly in combination with Hex and RGB.
thx, best, Christiaan

@Christiaan_Huizer , I have prepared a demonstration document.
LaTex is a huge and complex markup language, but I only use a few functions here

Max

2 Likes

I believe that @Paul_Danyliuk has worked out how to render colored text blocks using SVG.
Paul? Can you post a snippet here on this?

Max

I have worked out a better way (a bit simpler than LaTeX) to set the colors of a block of text (the text-color and the background-color) using SVG

Basically, I have a piece if SVG graphics code stored in the canvas formula called SVG_Template.
I use the Format() function to replace {1}…{4} with the text, background-color, text-color and the block-width.
The width is set to the number of characters in the text times nine - ie 9 pixels per character.

You can use any of the HTML color names or you can encode the red, green, blue values using the string
#RRGGBB
where RR is the red value, GG is the green value and BB is the blue value (all 2 digit hexidecimals)

This gives full algorithmic control over the colors use in text blocks.

Respect
Max Xyzor

2 Likes

Hey @Xyzor_Max I’d love to see, but the permissions on your doc don’t allow access/embed.

@Johg_Ananda

hopefully access permissions are fixed now

Max Xyzor

1 Like

I have updated my sample doc to provide a complete description of how this technique works.
Hopefully makers can now use this to provide a greater range of colors to their data representations.

I also hope that Coda will add an official way to encode the color of text that includes the BACKGROUND-COLOR as well as the text-color, as THAT has the biggest impact on the data presentation.

Respect
Xyzor Max

3 Likes

@Xyzor_Max this is brilliant thank you for sharing.

Do you know if Image(Format(SVG...)) is ‘expensive’ / slows down the doc?

@Johg_Ananda , I have run a simple test.

I created a table with 1000 rows, each with a single Image(Format(SVG,…)) with random colors .
I triggered the execution of just the 1000 Image(Format(Svg, …)) functions and the refresh was a small fraction of a second. So short I couldnt clock it on my stopwatch.

Not the most scientific test, but more than enough to stop me worrying about the performance hit from SVG.
Its way faster than loading 1000 small images, which was what I was doing before.

No doubt, scaling this to tens of thousands or rows might be problematic, but my clients dont display that much data on their dashboards due to the use of filters etc.

So I am happy to use SVG colored text for now, until Coda releases a proper function for setting the background color for text.

HOWEVER: take a look at my post on using the OFFICIALLY SUPPORTED conditional format COLOR RANGE feature to set background colors (from a limited palette) AUTOMATICALLY - it meets most of my needs.

Respect
Max Xyzor

5 Likes

Hi everyone :slight_smile:

I know this is an old topic, but just wanted to share in case anyone happened upon this thread. There is in deed a way to dynamically format table rows using a formula. I posted about this last year, including a demo doc with a few methods of implementation.

There’s a hidden formula called _GRADIENT() which allows you to specify HexCodes or utilize Coda’s internal color sets. It’s incredibly useful, and easy to implement. Take a look and feel free to use this in your own docs.

4 Likes