Related values vs "looked up" values

I thought I understood this a few days ago, but now I’m totally confused. Hoping somebody can straighten me out.

I come to Coda with very long experience with more conventional database design – where I define the primary and foreign keys in tables, build the relationships based on those keys etc, and where also I have the ability to script the entry of values. In the world I’m used to, there’s a difference between a true related value and a “looked up” value. (I put “looked up” in quotation marks because I am not sure that this is how Coda uses the term “lookup”.) So in my experience:

  • A true related value is dynamically linked to a child table from the related parent table. If the value in the parent table changes, the value displayed in the child table will be updated automatically. For example, say Jane Doe in the PERSONS table is linked to Acme Inc in the COMPANIES table. If Acme changes its name to Acme International Ltd, Jane Doe’s person record will automatically show her employer as Acme International Ltd (the new value).
  • A “looked up” value on the other hand is not dynamic. It’s a static, one-time data grab. Used for example to get the prices of items being sold, when the prices of those items might change later. So if on January 1 the price of a size 6 widget in the PRODUCTS table = $1.25, and I create a sales invoice for the purchase of some size 6 widgets, the item cost will be looked up as $1.25. But if the prices are changed in February, any new invoices for size 6 widgets will reflect the new price – while the old $1.25 price in all the old line-item records will be not be changed.

I cannot figure out how to do this second thing. Please help!

William

Hey William, here’s a very short intro to Coda for those with SQL experience.

Differences between SQL and Coda:

  1. You can have multiple values in a cell. Lists of values. Lists of lists of values. And so on. Essentially it’s dynamically typed.
  2. You can put anything into any cell, but it’s always better to set up proper column type. Think of SQLite and its treatment of column types: data is whatever, but depending on the “type affinity” it’s interpreted differently.
  3. You don’t have to (and actually shouldn’t) build relationships based on scalar values (i.e. numeric row IDs, name matches etc). You do that on the row references themselves. You set up single- or multi-select Lookup columns, and when you choose values there, you’re choosing row references, not just strings. You can spot row references by having blue bubbles in the cells / being hyperlinks elsewhere, and when you move your mouse over them you see the popup with the row data.
  4. Linking data is done at design time, not SELECT+JOIN time. You select related row(s) from another/same table into a cell, then in another column you write formulas to pull data from those related row(s). It’s somewhat like OLAP: e.g. in Companies table you can do e.g. thisRow.Tasks.Filter(Status = "In progress").Users.ListCombine().Unique() to get all users from all in-progress tasks from this company — and relevant query results will be be pulled into each Company and updated live whenever anything changes in any linked data. Unlike SELECT + JOIN, this is all specified at design time, resolved live, and recalculated regardless if you need it for this view or not.
  5. The only way to capture a value and make it independent of any linked data is to put it into an input (non-formula) column. You can make a button and/or set up an automation that would copy the formula-calculated value into a non-formula cell upon some condition when you want a value to be captured.

I wrote this because “Intro to Coda for people with SQL background” is one of the article ideas I have for my upcoming blog codatricks.com. This was a good opportunity to write a rough draft.

For your need with historic pricing, here’s the thread with multiple approaches to it:

And here’s the “superthread” that has links to all the discussions about this requirement under its first post:


As you can see, this is a rather common thing to stumble upon, so you’re not alone in this :slight_smile:

1 Like

Paul,

Better and more thoughtful reply than I deserve. Many many thanks. I have bookmarked and will study later.

Will

Is there a long help article somewhere that explains the syntax of formulas like the one Paul shows above?

William

Don’t think so — you just sorta know what you’re getting after each link in this chained call and what you can build atop. It’s all basically just a few simple principles. Whoever’s coming from software engineering background might have encountered already under the name fluent API. Or at least see a similarity with Builder pattern. See https://en.wikipedia.org/wiki/Method_chaining for the general idea.

Also usually you’d have long expressions like this one split up into separate columns to calculate intermediary steps. Not this one though, this one is pretty valid, as there’s not much to extract.

There’s this basic lesson with a video though. May be useful if you’re used to the excel style of writing formulas like Unique(ListCombine(Filter(thisRow.Tasks, Status = "In progress").Users)):