Related values vs "looked up" values

[William_Porter]

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

[Paul_Danyliuk]

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:

Freeze past row values

Hello everybody, would ask your suggestion for a simple problem: You see in the following example an activities tracker; the activity cost is calculated on the user hourly rate basis. If I update the hourly rate of a user I would like to see the updated calculated cost only on new rows and keep past rows cost freezed at past rates. Is it possible in some way? Do you have some strategies to achive that? thank you very much!

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

Capture `History` of changes / Create Log of changes to important value of record

Hi, At times, I feel like I want to preserve history of changes to important column in a record (while coda has built-in change tracking and you can see that inside Row Activities - it is hard to query that changes or create a log of all the changes. Here’s what I use in some of my documents and has worked out really well. Example use case here is: I have Projects and I want to make sure all changes to Project Cost for each project is captured as history. The steps are: create Projects Ta…

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

[William_Porter]

Paul,

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

Will

[William_Porter]

@Paul_Danyliuk

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.

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

William

[Paul_Danyliuk]

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)):

Coda | A new doc for teams.