A somewhat of a documentation of hidden formulas [Added mechanics of nested buttons]

I was asked to move the list of hidden formulas into a separate post for better readability, so here we go.
If you have any tips for using hidden formulas, don’t hesitate to post them below.

Disclaimer
While hidden formulas are very powerful, they are also experimental.
That means that they can crash your document and may be deleted at any point in time without any warning.

Index

List of hidden formulas
Created by @Paul_Danyliuk

Formulas are grouped by how likely they are to create problems with the document.
However, this is only my speculation based on the source code of coda.io.

  • 99.9% safe
  • Pretty safe for use
    • _RichText() — seems to be useless, basically does the same as Concatenate()
    • _Color()
    • All()
    • Any()
    • Identity — returns input value (so it is basically useless)
    • _MERGE — “Deeply merges objects”
    • _DELAY — “Returns a value after some delay”
    • _CREATE_ERROR — “Create an error”
    • _NoOp() [action] — no operation to use in IF statements for an action
    • Activate() [action] — brings referenced element into focus
  • Unstable or untested, you should implement fail-safe procedures before using those
    • _MergeConditionalFormats — “Deeply merges objects”
    • _PROJECT_COLUMN_FOR_ROWIDS — “Filters rows in a table to the set of rows with ids matching the values”
    • _HASH_MATCH — “Filters on column equivalence on some or all matching columns”
    • _TRUE — not sure why it exists along with just True().
    • _RandomImpl — “Generate a random number”
    • _FAKE_ARRAY — “Returns an array with a schema”
    • _FAKE_OBJECT — “Returns an object with a schema”
    • _WRAP_INVALID_FORMULA — “Used to wrap an invalid user formula so the invalid input does not break AST parsing.”
    • _DEREF_OBJECT — “Dereferences the given path”
    • _DEREF — “Dereferences the given input with the given reference”
    • _DEREF_OPT — “Dereferences the given input with the given reference”
    • _ROW_KEY — “Returns the reference key for the given row”
    • _SORTED_SEEK — “Filters on column equivalence on some or all matching columns”
    • _RANDOMIZE_CURRENTLOCATION — “Random travel”
    • FormatCurrency()
    • FormatDateTime()
    • FormatDuration()
    • FormatNumber()
    • FormatPercent()
    • FormatTime()
    • ATTACHMENTCONTROL
    • BUTTON
    • CHECKBOX
    • DATEPICKER
    • DATERANGEPICKER
    • FORMULACONTROL
    • MULTISELECT
    • SELECT
    • SCALE
    • SLIDER
    • HTML()
    • BLOB — for system use only
    • STDEV and STDEVP
    • ToHtml
    • Project — “Projects one or more columns from the given result”
    • CurrentLocation
    • RegexExtract
    • Quartile() — “Outputs the item nearest to a specified quartile from a list of items”
    • Card() — “Renders a read-only card control”
    • DuplicateSection() [action]
    • Xhr() [action] — make an XHR request to an URL with arbitrary method and body
    • RefreshPack() [action]
    • StartSync() [action]
    • FinishSync() [action]
    • PieChart()
    • LineChart()
    • BarChart()
    • PieChartAggregate()
    • Histogram()
    • GoogleDirections()
    • GoogleMap()
    • LaTeX()
27 Likes

Diferent types of color arguments [Back to index]

Different hidden formulas use different format for specifying colors.
Here is a list of all different color argument types I found so far:

Hex
The most straightforward of them all. Just a hex color in the #rrggbb(aa) format.
This is used in the _Color() formula.

Color palette
This is actually a set of color and it can be any of the following values (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:

This format is used in the Button() formula.

Class based
This is basically a single entry from the color palette format above.
In addition to the color name you have to add -light, -medium or -dark.
This format is also case-sensitive, however all letters should be lowercase.

However, this color format is strictly dependent on the function you are using, as it also requires a prefix and a suffix.
This means that if you wanted to use it in the _Color() function, the format would be
_Color("kr-[color name]-[light/medium/dark]-fg","Text")

And here is a full color preview (as of September 2019):
image

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.

3 Likes

Preventing crash when double clicking a button [Back to index]
Deprecated: double clicking a button in a text field no longer causes document crash.

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:

EDIT:
Instead of wrapping only text elements, you should wrap the whole formula in a select element.
Otherwise the text will act weirdly when it doesn’t fit in the cell.

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:

4 Likes

How to prevent total destruction of your document [Back to index]

Hidden formulas can be extremely dangerous and destructive.
That’s why it is crucial to take proper precautions when using them in any document.

The issue
As it was said earlier, some hidden formulas can permanently crash your document if they have a syntax error (this happens, for example, if you use incorrect color name). Normally this isn’t a huge issue as you can revert to the previous version using version history.

But now imagine this - all your hidden formulas are working when you created them. However, after a few months there’s an update which changes their syntax, and now previously working formulas crash your document (this is a completely plausible situation since experimental formulas don’t have to be backwards compatible).

Since it’s not a change in your document that caused the crash, you can’t just revert to the previous version. You would have to revert to the last version which doesn’t use the hidden formulas that crashes, which would mean enormous amount of data loss.
You can’t edit the formula since the crash happens the very moment the document loads, and currently there is no way to access raw save file, which means you lost the whole document.

What now?
While you can’t recover the whole document you may be able to recover some of its data.
When you enter version history, none of the formulas are being executed. That means you can use this to manually copy data from visible cells.

How to ensure access to your document
There is a way to prevent this situation before it happens.
Formulas aren’t being calculated if they are not visible, which means that the document won’t crash if there aren’t any broken formulas in your current section.

The idea is to use this and create a fail-safe section which doesn’t use any hidden formulas.
In case your document crashes you can use direct link to this section to open the document without the crash.

You have to create this section while you document is still working. Otherwise, none of the following paragraphs will help you.

It is also important to keep a direct link to this section somewhere outside of the document.
Just typing “/section-name” won’t work, it needs to be followed by the section id.

Fixing the document
Now that you have access to your document you can start fixing it.
You can do that by removing all rows from the broken table. Since you can’t directly display the table, you will have do that using a button and delete rows action.

With a few additional buttons you can also copy all data from the broken table, as shown in the document below.

Easier way to fix the document
There is another precaution you can take to make fixing the document significantly easier.

By adding a checkbox control to the fail-safe section and then wrapping each formula that uses experimental features with if(checkbox,"",...), you can fix crashing with a single click of a button.
After that you can open the section with the broken table and fix the invalid formula.

It is also a good idea to turn this checkbox on whenever you create a backup of your document, just in case.

Additional precautions
It may be a good idea to also create another section called “raw data”.
It should contain a view for all tables that can contain data inputted by the user. If everything else fails you’ll still be able to manually copy user data from version control.

And under no circumstances should you put that table in the same section as the fail-safe. You would risk loosing the whole document.

Crash sandbox
Here is a document which allows you to play with the crashing mechanics of coda.io and has implemented the precautions explained above.
(You have to copy this document in order to fully see how those precautions work)

Additional notes
All of the above precautions are based of current (August 2019) coda mechanics.
That means that, even though it’s extremely unlikely, they may no longer be working after a few updates.

6 Likes

Deep modification of control objects [Back to index]
Disclaimer: deep modification is extremely unstable. Make sure you implemented all fail-safe procedures and created a backup of your document before using it.

Note that all information in this post has been gathered by analyzing source code, deduction and trial-and-error. This means it is highly probable that some parts are inaccurate or just simply wrong.

Format of control objects
Each control object is actually a standard object with a few special flags. However, whenever you reference a “button”, “select” or any other control column, the result isn’t the whole object, but only it’s value property.
Hidden formulas (such as Button()) allow you to insert the full object, so it renders properly in other columns.
By putting such an object inside of a _Merge() function, you can convert it from a control object into a normal object and view its structure.
image
And by adding +"" to the formula, it converts into pure json.


(Result has been formatted using json beautifier)

You can convert json string back into an object using ParseJSON(). However, this formula converts the output into a standard object, and not a control object. This means that instead of rendering the button, it will display its inner structure.
In order to display it properly you need to wrap it with any kind of property. This works because a preview of an object is its first property and control objects inside of a standard object aren’t flagged to display as structure.
The resulting formula looks like this:
image
And, in case of a button, creates a functioning control.
There are however some differences because this isn’t a control object, but rather a standard object containing a control object.
The most notable is the box that displays on hover.
image
This can be easily fixed by putting the control into a button label.
Edit:
Since nested buttons are no longer supported there is currently no way (that I know of) to hide this on-hover box.

Why would you need that?
Everything above results in a slightly worse version of a the same button.
However, in the process we gain access to the pure json string which we can then modify.
By adding a string as a second argument for the _Merge() function you can change different properties of the resulting control object.

Example usage: dynamic buttons
This can be used to change button formula without the need to create a button column for each button.
In addition to leaving less clutter and being less tedious, this allows to dynamically modify amount of buttons in any formula.

Button action is saved as an array of actions in properties --> actionValues.
Adding ParseJSON('{"properties": {"actionValues":"_7_"}}') as a second parameter in _Merge overwrites that value with a _7_. Now you can use Substitute to replace it with "[" + _Merge(Action) + "]". (Don’t forget about adding the [], your doc will crash without them)

You have to use substitute instead of putting your action directly into the _Merge function, for unknown reasons putting it directly won’t work.

The formula should look something like this:
image

You can even put it in a loop and make every button do something different.
Here you have an example how to implement that:

[Back to index]

7 Likes

What have I done… :joy:

JK, fantastic guides!

5 Likes

It might be interesting to separate out the formula in simple and advanced formula. The advanced formula can be separated off, but made more stable.
There’s a definite need for some of the more complex formula in Coda, but I can also see the reason as to why these formula are hidden.

1 Like

Dear @Filmos,

What a source of inspiration, “:large_blue_diamond: science fiction :large_blue_diamond:” in Coda :+1:

1 Like

Advanced usage of the Color() formula [Back to index]
Disclaimer: this functionality is very likely to change in the near future.

The Color() formula is implemented in a very interesting way, which allows this formula for more than just changing the font color.
You can use special keywords, combined with spaces, to change the text even more.
For example, you can create red text on a green background by using
_Color('kr-green-light-bg kr-red-dark-fg',"Text")

Unfortunately, this does not work with the hex colors.

List of keywords:

  • kr-[color class]-fg – changes font color
  • kr-[color class]-bg – highlights the text with given color
  • kr-highlight – highlights the text in yellow (#fff59d, color which isn’t achievable with a color class)
  • kr-[color class]-interactive-text – changes font color and makes it react to mouse events
  • kr-[color class]-interactive – highlights the text with given color and makes it react to mouse events
  • kr-bold – makes the text bold
  • kr-italic – makes the text italic
  • kr-underline – adds an underline to the text
  • kr-line-through – adds a strike-through to the text

Nesting
Unlike other text formatting functions, this one doesn’t allow for nesting.
The outer Color() formula will completely overwrite all other Color() formulas.

This means that while

_Bold(_Italicize("Text"))

creates text that is both bold and italic,

_Color('kr-bold',_Color('kr-italic',"Text"))

will create text which is only bold.

This also means that partially overlapping formats like

_Bold("Partially ",_Italicize("Italic"))

have to be split into

Concatenate(
  _Color("kr-bold", "Partially "),
  _Color("kr-bold kr-italic","Italic")
)

[Back to index]

1 Like

@Filmos hmm you post creates such opposing emotions. I really want to use _bold() … but don’t want to risk destroying my document … and much effort to failsafe it for a bold. Do you think adding _bold() to a formula risks the entire doc?

1 Like

@Johg_Ananda this is actually a really important matter that I failed to add here.

I’d say that the _Bold(), _Italicize(), _Strikethrough(), _Underline(), _Highlight() and _Code() formulas are 99.9% safe.
Since they don’t use any arguments other than text, if there were any drastic changes to them (which is pretty unlikely on its own) either their syntax would stay the same or they would require additional parameters, which would make it nearly impossible for such a formula to crash the document.

Formulas such as _Color(), _RichText(), Identity(), _NoOp() or Activate() are slightly more unstable, but it is still incredibly unlikely that they will cause any major problems with the document.

However, other formulas can be pretty dangerous. For example the Button() formula (which is the one I use the most) can already create a lot of problems with the document. And if you add to this coda updates things can get pretty serious.
Those kind of formulas were the main reason why I had to come up with all those fail-safe procedures.

EDIT
Inspired by this question I grouped hidden formulas based on how likely they are to cause problems.

2 Likes

Holy sh~ how did you find that out? Dug deeper into the JS?

1 Like

Yup, I’ve put it through a few beautifiers until I got a semi-readable code.
And each formula is defined as an object with a standard js function for getting the result.

And using the _Merge() formula to view the computed column results as json was the key to reading the obfuscated javascript.

1 Like

Fiddling with your research doc I found out about the $$ operator used to create references.

E.g. $$[cell:grid-ahmvaWqCgY:c-ni32haMaa_:i-pHC2juX1xr:false:false:Value]


Syntax goes [<type>:<tableId>:<columnId>:<rowId>:<bool>:<bool>:<displayName>]. Some params can be omitted depending on type. Not sure what the bools are for either.

Types are (click to see)
"canvas";
"cell";
"column";
"controlGrid";
"document";
"grid";
"row";
"rowColumn";
"variable";
"table";
"constraint";
"linkedColumnFormula";
"image";
"volatileFunction";
"fakeReference";
"documentObjectsReference";
"packReference";
"packConnectionReference"

You can also type $$[] anywhere in a formula to break down all references in it.

Still, haven’t found a practical use for it. Since it’s not a string and it needs to be typed, I guess a first step would be using your ParseJSON method to create the object.

My first idea was to programatically insert/get column values. If we just found a way to list columns (as the API does)… there’s a challenge for you @Filmos.

2 Likes

Dear @Filmos, @Dalmo_Mendonca, @Johg_Ananda and @Paul_Danyliuk

I felt so free to move your posts under “Developers Central” as your input is at a higher level what might confuse other users.
Keep up with your magical inputs :handshake:

2 Likes

@Filmos It might be an idea to have some documentation on these calls. Or have someone from Coda provide the missing details.

1 Like

Mechanics of nested buttons [Back to index]
Since 2019-09-12T22:00:00Z buttons can no longer be nested. There is a petition to bring this feature back, but for now all of the information below is deprecated.

With the Button() formula, you can put a button into a label of another button (which is in a label of another button and so on).
image
This kind of nested buttons have a few useful and interesting properties.

Action execution order
When you click on a nested button, it will firstly execute the action of the button you directly clicked.
Then it will execute the action of its parent, and then a parent of the parent, and so on until it reaches the root.

That means that if you were to click the blue button from the image above, action order would be
blue --> green --> yellow --> white, with white overwriting any colliding actions (like changing value of the same row and column).

There is just one big problem - those actions are asynchronous.
That means that they start executing in this order, but the changes may be applied in a completely different one, based on the complexity of the action.
This is fragile to such an extent that just wrapping a formula with RunActions() or adding an if statement (even if it is just If(true,"","")) will change this order.

That means that this order is only reliable when the actions are nearly identical (for example: ModifyRow() with static target, column and value).

Activating only the top button
Clicking only the top-most button can be useful in many cases and it can actually be achieved.
Because of the execution order, when a button action is executed you can check if any other button was clicked earlier. If it was, it means it was above this one.
To do that, you would need to wait (using _Delay()) until the previous action is executed and check if there were any changes using Modified().

While it is possible to create this for more than 2-deep buttons, the delay would stack up and make the buttons pretty slow and impractical. So I’m only going to explain how to achieve that with 2-deep buttons.

The problem is that we need to make sure the background button detects the top button being clicked, even if it has a slow formula.
To do that we can simply change the formula of the top button to

RunActions(
  ModifyRows(thisRow, [Button detection], "W"),
  <the actual formula>
)

This makes sure that the change will be detectable at the same time after the button is clicked, no matter what the complexity of the formula actually is.

Detecting the button change is also tricky.
Because of how the _Delay() formula works, you have to split the action into two buttons.
The first one (the one you actually click) should look like this:

_Delay([Actual action],300)

And the second one should look like this:

if(Now()-Modified(thisRow.[Button detection])<=Seconds(0.35),"",
<the actual action>)

You may be tempted to set the button detection column to Now() when clicking first button, but not only is that value rounded (and basically useless) it will also create a lot of other problems.

Note that you don’t need to use those exact delays, those are just the ones I found reliable.

One more issue
There is still one small issue with this solution - if you click the bottom button fast enough after clicking the top button, the bottom button won’t be activated.

This can be easily fixed by changing the formula of the bottom button to

if(IsNotBlank(thisRow.[Button detection]) && 
   Now()-Modified(thisRow.[Button detection]) <= Seconds(0.35),
 ModifyRows(thisRow,thisRow.[Button detection],""),
 <the actual action>
)

Sandbox

[Back to index]

2 Likes

Is there any chance any Codans could tell us the likelihood that FormatDateTime() (and it’s siblings) will change or be removed? Formatting dates/times on the canvas is ridiculously verbose right now, and these functions make it a lot simpler.

2 Likes

@Ian_Nicholson you would need to ask @mallika about that.

I’m also curious what’s the status of hidden formulas and if it possible that those that are more stable (_Bold(), _Italicize() etc.) will be fully implemented soon.

1 Like

Unfortunately due to recent coda update nested buttons are no longer supported, which significantly limits possibilities of hidden formulas.
This includes making deep modifications nearly useless, as you can no longer hide the on-hover box.

1 Like