Related values vs "looked up" values

[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