[!] Learn in X — Outline

Explore

Learn in X — Outline

Note to self

Keep the guide simple. This is V1. Don’t shove too much into it upfront. (yet plan it thoroughly so that you won’t have to waste time pondering when you sit to write)

⁠

Fundamentals

⁠

1. Intro

Why formulas are important

Answers: some we get from organized data, some we need to calculate (❓)

“If you’re using Coda as a simple journal, you’ll probably won’t need formulas. But then, you’ll probably won’t need Coda.”

Where formulas are used

Table columns

Calculated properties

Conditionally matched references

Pages (to summarize data from tables)

Some use page formulas extensively. 🟦 Tip: data belongs in tables, so do formulas; use page formulas only for output

Filters

Column settings

Conditional format expressions

Actions (buttons & automations)

❗ anywhere you see an f button

How formulas are created

Typed out with text (letters, numbers and symbols)

The text follows some rules. Not hard to learn but must be observed (not Siri or ChatGPT)

Mention expressions

(! lower?) The Formula editor (overview, no details for now)

When speaking of references and functions: depending on the context, different items may be available. E.g. row formulas have thisRow but page formulas don’t; actions too.

⁠

2. Fundamentals

2.1. Imagine a bakery

Let’s use a powerful analogy

Conveyor belt

Composition (smaller stations working on mixing, shaping etc)

Variability (today we make this, tomorrow we make that)

Reusage (orders of different items)

2.2. Everything is an expression

Previously I said formulas are written with special expressions. So what are expressions?

3 examples: just the value, some simple math, and a very complex one

returns (calculates, gives) one result

it can be a tuple, a list, an object etc, but it’s still one result, akin to saying let result = MyLongExpression()

very short overview on data types; will go deeper into details later

comparison to programming

this dictates the mindset: that conveyor belt that:

💡 takes your input(s) and brings them closer to the sought answer

What goes into an expression

Inputs

Literals

References

Context-specific variables (thisRow, Step 1 Result etc)

Values from another expression (named formula etc)

Operators and Functions

do something with those inputs

2.3. Value types

Simple & complex types

“Umbrella” typing → what can be done with a value of such type

What about Coda data types? (Currency etc)

Plain & Formatted types

Lists: ordered items of any type

incl. nested lists

you should only use mixed-type lists when you know what you’re doing

A special case: List of rows

Objects, aka maps, aka dictionaries: set of named properties (key-value pairs)

Typed and Untyped

Typed (comes with a definition): access properties with a dot operator

Untyped (no way to determine its structure): no direct way to work in Coda (only with hidden formulas). Hence rare. ParseJSON.

Most common type of a typed object: a table row!

Typed objects from packs

User() returns a typed object

Every table is a new type (or two, actually; a row and a list of such rows)

2.4. Core principles

Principles overview

Composition (and decomposition)

Variability (same expression may calculate different results for different inputs)

Reusability

Idempotency — in 2.5

Composition

Combine expressions

Basic combination

Pros & cons → perf will be added later

WithName

Separate expressions

Tables: in separate columns

Pros & cons → perf will be added later

Pages: into named formulas

Chaining

💡 When nesting / using long args, indent your code. There’s auto-format but better to do this manually

Conveyor belt thinking

Reusability

A common (same) formula is used to answer the same question. Different inputs can result in a different answer but the nature of the inputs and the nature of the answer is the same (e.g. calculating age based on the date of birth)

TBD: Fix this age formula

29

⁠

Variability

Tip: for input → output mapping, consider using an external table (hard-coding variability vs externalizing it in a table)

2.5. Recalculation

Keep it for the pro level guide

Dependency graph (same frame)

Digest cycles (deferred multiple recalcs)

nocalc=true

Idempotency

2.6. Volatility

What’s peculiar about volatile inputs & formulas:

Modification time based (also row-based for random) — to make it deterministic across clients

Not saved to snapshot; calculated on the client instead

Viral: formulas with volatile inputs become volatile themselves

Pro level: exploit volatility to reduce doc size at the cost of recalc perf

Quick overview

2.7. Actions: Same but different

Deferred execution, once (not recalculated)

Sequential/parallel execution overview

Modify state

Pack actions

CFL Reference

⁠

3. Coda Formulas

3.1. The Formula Editor

Getting around

Chips & colors

Type hints

Hidden formulas: no hints for those unless we summon

How we found them (occasionally; then in Coda’s code)

Code style

3.2. Inputs

What we can access:

Notion of namespace / context

Literals (and not quite)

"", undefined, true/false & True()/False()

Character() & LineBreak()

Pi()

Globally available references

Any tables!!!

Controls

Named formulas

thisDocument (synthetic)

Setting-based

DocumentTimezone

Weekday(!)

Volatile inputs & volatility

Now

Today

CurrentTimezone

User

IsSignedIn

Randomness:

Random

RandomInteger

RandomItem

RandomSample

Context-specific values (quick overview)

thisTable

thisRow

parentRow

CurrentValue

WithName-captured values

Step X Result

Only in actions:

Page references

Tables / columns serve as references (maybe note this later)

Pack formulas often serve as inputs too

3.3. Operators

Numeric

Tip: Don’t use + for concatenating

Comparison

Boolean

Tip: always use over functions

Misc

Order of operations; brackets

3.4. Branching

SwitchIf

Switch

Shorthand: IfBlank

Used in expressions

And / Or

Not

True / False

3.5. Iteration

Iterativity; CurrentValue & WithName

ForEach / FormulaMap

Filter (+Lookup)

Filter-like: CountIf, SumIf, AverageIf, MinBy, MaxBy; Any/All

WithName

TODO: the whole page about iteration

TODO: list other iterative formulas

Iterativity in:

Item settings

UI filters

3.6. Connecting data

Case-based study where iteration is most commonly seen Think this one through better

Filtering

Filter

+ Lookup

Filter expressions

→ Operators

IsBlank

IsNotBlank

Contains / In

ContainsText

StartsWith

EndsWith

RegexMatch

(mention the old ways with Find, Lower, Trim etc)

Matches

IsFromTable

3.7. Working with lists

⁠

⚠️ Coda coalesces list-of-one to one recently in many scenarios. Need to be wary of that.

Source (data)

Rows

Cells of a column

Multiple values of a cell

Multi-select controls

(!) Date range control

Objects with list properties

Create

List

Sequence

Split

ParseCSV

ParseJSON

RegexExtract (with “g” flag)

Flatten (lists[] → list)

ListCombine

Append

ListCombine

Splice

(+ how to combine in a list of lists)

Transform

Sort

ReverseList

ForEach / FormulaMap

RandomSample

Subset (list → list)

Filter

(+ Lookup)

If the only goal is to filter for aggregation, use shorthands CountIf, SumIf, AverageIf

Matches

Slice

RandomSample

Unique

Duplicates

Pick one (list → item)

First

Last

Nth

MinBy

MaxBy

RandomItem

Inclusion checks

Contains / In

(works as Contains Any / In Any)

Find

→ Filter(...).IsNotBlank()

ContainsOnly

ContainsAll

Validation of all items

IsBlank / IsNotBlank

(quirks!)

→ Filter(...).IsBlank()

Any

All

Aggregation

Any

💡 Gotcha: List(...).CountIf(true)

Count

CountUnique

Numeric

💡 Gotcha: include (0)

Sum

Average

Min

Max

Median

Mode

Product

Filter-shorthands:

CountIf

SumIf

AverageIf

Outcast function

SumProduct

Statistics

StandardDeviation

StandardDeviationPopulation

Percentile & Ranking

Rank

Percentile

PercentileRank

Combine as text

Concatenate

Join

BulletedList

NumberedList

3.8. Numeric functions

Creation

Literals

Functions that return numbers

Dates/Currency

⁠

ToNumber

ParseCSV

ParseJSON

Math

+ Math operators

AbsoluteValue

Sign

Remainder

+ %

Factorial

BinomialCoefficient

Power

Exponent (e^x)

SquareRoot

Log10

Log

+ Trig pack

Rounding

To factor:

RoundTo

Floor

Ceiling

To places:

Round

RoundDown

RoundUp

Up to nearest even/odd that’s larger (smaller when negative) than the input (?)

Even

Odd

Just cut the text

Truncate

Checks

IsNumber

IsEven

IsOdd

Output

ToText

ToHexadecimal

ToByteSize

Format

FormatNumber

FormatCurrency

FormatDateTime

Useless?

Quotient

3.9. Date/Time and Duration

💡 Are numeric: numeric operations apply

Idioms:

Sequence

Addition/subtraction

Aggregation (min/max)

Gotchas:

Base 1900

Historic date/time offset issues with some TZs

Second (float) precision

⁠

Instant

Now

Today

+ Created/Modified

Creation

Date

💡 Tip: rollover days

Time

DateTime

Parsing

ToDate

ToTime

ToDateTime

Output

ToText

ToNumber

FormatDateTime

TimeValue

Epoch

EpochToDate

DateToEpoch

Check

IsDate

Get individual properties

❗ don’t confuse w/ ToXxx

Year

Month

Day

Hour

Minute

Second

Weekday / IsoWeekday

other:

WeekNumber / IsoWeekNumber

WeekdayName

MonthName

Date math

Obvious date math

Aggregation

DateTimeTruncate / ToDate comparison

Workday

NetWorkingDays

RelativeDate

EndOfMonth

Duration

💡 Are numeric: numeric operations apply

Sorta copy gotchas etc from Date/time

Creation

Duration

— or by math

Days

Hours

Minutes

Seconds

To numeric value

ToDays

ToHours

ToMinutes

ToSeconds

💡 Gotcha: don’t just multiply!

3.10. Working with text

Gotchas: on the difference between atrefs, inline values, and text / rich text (also hyperlinks)

Creation

Literals

ToText

+ "" idiom

DecodeFromBase64

ParseJSON

RegexExtract (no “g” flag)

Creation from list / vararg

Concatenate

Join

Format

+ templates

BulletedList

NumberedList

Checks

💡 Tip: "" as undefined

IsBlank / IsNotBlank

IsText / IsNotText

ContainsText (❗ not Contains!!)

RegexMatch

StartsWith / EndsWith

Find

Characters

Character

LineBreak

Encode

EncodeAsBase64

EncodeForUrl

_Merge() + ""

Info

Length

Transformation

Trim

Lower / Upper

Left / Right

Middle / Slice / Nth() etc

LeftPad / RightPad

Format

Repeat

Replace / Substitute

RegexReplace

Splice

Rich text formatting

BulletedList

NumberedList

IndentBy

Hyperlink

Format + templates

Plethora of hidden formulas: _Bold(), _Italicize(), _Color() etc

Inclusion of controls (buttons etc)

Image

Rectangle

Split

ParseCSV

RegexExtract (with “g” flag)

3.11. Rich objects

Hyperlink

Image

ClipCircle

+ ObjectLink

Rectangle

Embed

HyperlinkCard

+ some hidden properties

Hidden formulas for Buttons etc

Misc: (belongs here?)

Spatial

Distance

Location

3.12. Object (row) properties

Created

Modified

CreatedBy

ModifiedBy

ObjectLink

RowId

thisRow + ""

_Merge() + ""

3.13. Type checks & casts

Type checks

IsBlank

IsNotBlank

IsText

IsNotText

IsNumber

IsDate

IsLogical

IsFromTable

Type casts

ToText

ToNumber

Boolean checks (= true, != true, undefined?)

ToDate

ToTime

ToDateTime

FormatCurrency

_Deref

3.14. Encoding / decoding

DecodeFromBase64

ParseCSV

ParseJSON

EncodeAsBase64

EncodeForUrl

Object() + ""

_Merge() + ""

3.15. Misc functions

Spatial

Distance

Location

_Delay()

other hidden functions

⁠

4. Coda Actions

TBD decide on whether it’s a separate part or just 3.16 This will depend on whether I want to include any extra info here and not in the theory part (2.7)

Run multiple actions in sequence

RunActions

Gotchas: no RunActions for parallel execution

Edit table data

AddRow

ModifyRows

AddOrModifyRows

DuplicateRows

DeleteRows

Navigate

OpenRow (obsolete: Activate)

OpenWindow

Create pages

DuplicatePage

CopyPageToDoc

CopyDoc

Edit controls

SetControlValue

ResetControlValue

Refresh sync tables

(! not in automations)

RefreshTable

RefreshColumn

Misc

Notify

CopyToClipboard

SignUpForCoda

TODO: hidden ones

External actions (pack actions)

⁠

All of this — still only for $80

(or more. or less.)

⁠

Get the “In X Minutes” guide Instructions on how to purchase it in advance coda.io⁠

⁠

⁠

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.