Connections via Lookup & Filter lost using cross-doc

2

Hey @anon41554251,

Without reading into it too much, I think I know what issues you’re running into. Let me explain how cross-doc behaves.

  1. In Coda tables, references to rows are always stored as references to the master tables, not views. When you replace Table.Filter(...) with View.Filter(...), the only benefit you get is that the view can be filtered, and your formula will use that pre-filtered set instead of the whole set.

  2. That’s why when you import that table into another doc, this doc expects to resolve the link from an imported master table. It won’t find this row if you import not a master table but a view — that’s what you see in your Doc B. I believe there’s a valid reason why it works like that, but I’ll get back to this later.

  3. An imported table becomes a table of its own, with its own table ID and view IDs, its own column IDs and row IDs. The only differences of a sync table from a regular table are:

    • You can’t manually add/delete/reorder rows. Coda does this for you when it imports a list of row objects (adds rows for objects that weren’t in the table before and deletes rows for objects that are no longer in the new list), and “locks” you from modifying it.
    • There’s a column that contains an object (conventionally named Row) that contains an imported piece of data for each row. All other columns are just formula columns reading from that object, e.g. Row.Name.
      image
      image694×260 18.5 KB

      You can delete those columns, or you can replace those formulas with something else entirely and not read from the Row object. When you import a table and see all the columns added for you, that’s just done for convenience so that you don’t have to do it yourself, and that’s it.

    What’s important to know here is that when you import a view or a table, it becomes a new table on its own. Consider this scenario: you actually imported DocA.Colors (NOT View of Colors!) into DocB, and the link got resolved in DocB.Fruits properly. However, the row that link got resolved to is from DocB.Colors table now, which is a new table technically unrelated to DocA.Colors (except from Coda managing its size and its Row column based on imported records from DocA.Colors). So when you import DocB.Fruits and DocA.Colors into DocC, the link will be broken again, because the imported DocC.Fruits table will now expect an imported master table of DocB.Colors (from Doc B!), not the original table from Doc A.

Now that you know how it works, I guess it’s easier to understand why all the limitations. I believe the reason why Coda cannot resolve linked rows from imports of views and not master tables is that because:

  • you can have multiple views of the same table (e.g. Warm colors and Bright colors)
  • you can import each view separately (once, but it may result in the same rows being imported multiple times as a part of multiple views, e.g. Yellow in both Warm colors and Bright colors)
  • all those imported views become separate tables
  • ultimately, Coda simply wouldn’t know which of the imported views to resolve that linked row to.

So the only reasonable constraint there was to require an import of a master table. However given that it’s obvious how importing views is a much better practice than importing master tables, I hope that Coda supports this in the future, e.g. by letting us manually choose an imported view to resolve links from.

The best practices I came up with while working with very complicated cross-doc setups are these:

  1. Don’t cross-doc row links. Cross-doc row identifiers (RowID(), UID, or any arbitrary IDs like users’ unique badge numbers) instead. Then re-link on the receiving side, SQL style.
  2. Always import tables from their original source. Don’t bring in cross-docs of cross-docs.

E.g.:

  • There’s a doc with Teachers.
    • Export a view of Teachers that has a Teacher ID column.
  • There’s a doc with Students that imports Teachers and links each student to a Teacher.
    • Export a view of Students that has a Teacher ID column, not a link to a row in Teachers.
  • You need to import students with respective teachers into a Classes doc
    • Import the view of Students from the Students doc
    • Import the view of Teachers from the Teachers doc (the original data source), not Students doc.
    • Link teachers to students in the imported Students table by lookup: Teachers.Filter(CurrentValue.Teacher ID = thisRow.Teacher ID).First()
  1. And if you ever feel like you need to further cross-doc an already cross-doc’d table, e.g. because you add more data upon the import:
    • First think if you can get that added data back into the original doc instead (e.g. cross-doc the number of classes the student has ever had back into the Students doc, instead of cross-doc’ing the Students table from Classes doc further in the system)
    • If you can’t do it or it’s unfeasible, document the shit out of it. Because whoever’s going to maintain the system must always know they can find the data source where they expect it to be.

Wow this turned into a long read. A worthy candidate to be posted in codatricks.com eventually :wink:

7 Likes