Connections via Lookup & Filter lost using cross-doc

1

Hello dear community users,

Who can clarify a doubt for me? Just to make sure I’m not going to spend hours and hours on something turning out to be obsolete… I read many topics searching for a solution, but could not find any. In case this question has been asked already: sorry! :innocent:

When syncing views of tables using cross-doc from DocA into DocB (DocA containing both the ‘base’ tables and corresponding filtered views, importing only the filtered views into DocB via cross-doc in order to prevent certain elements from DocA being displayed in DocB) all connections (lookups/filters) between those tables get lost in DocB, due to Coda requiring to sync the original tables, right?

I’ve tried to manipulate the lookup formulas by changing them into filters using the views rather then the ‘main’ tables, but unfortunately Coda keeps on detecting its ‘just’ a view, referring to its corresponding ‘base’ table in the resulting rows (confirmed by expanding the row within any cell of the formula column and checking the ‘Row from’-indication on the upper left corner) hence the connection gets lost again after syncing in DocB stating ‘Table not found’…

As my goal is not having to sync ‘base’ tables with sensitive data, is it true to say I will need 3 docs at the end? DocA containing the original data, DocB with filtered data only and ‘re-establishing’ the connections between tables by means of new lookup/filter columns, and last but not least a DocC whereto the results can be synced once again, but this time with all lookups/filters working?

Edit: Just discovered this doesn’t do the trick either as DocC now states ‘Row not found’ on the columns using Lookups/Filters, despite having synced the entire tables from DocB to DocC (unlike views only in DocB synced from DocA). DocB contains the extra columns to ‘fix’ the broken connections from DocA, referring to tables from within its own doc only. What am I missing here? Or am I asking something from the cross-doc functionality it doesn’t support (yet)?

It looks like the only way to achieve this is to copy the result of the lookup/filter column using ToText into another column and use that column in the other doc to re-connect it to the corresponding values again, am I right?

Edit2: I’ve created 3 docs to demonstrate the case:

Thanks for taking time in reading this and thank you even more if you’re able to give an answer! Hopefully I expressed myself clear enough and hopefully there’s a better way of achieving this… Please take into account I’m a single person, just a Pro user, so no Team plan for me! :slightly_smiling_face:

3 Likes
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
3

Wow thank you very much for taking time in giving such a detailed reply! As soon as I get home I’ll dive into it and thank you further:)

Edit: Hi @Paul_Danyliuk,

After reading each paragraph carefully I believe I fully understand what you explained. I’ve learned new things for which I want to thank you deeply! :pray:

I’ll have to rethink the design of my docs and your answer will help me big time. If not, I now know who to bother again… hehe just kidding. For sure I’ll run into trouble again by trying to take full advantage of Coda’s expanding possibilities, but that’s part of the fun, right? Until the moment frustrations start to overwhelm of course, but thanks to people like you sharing explanations and solutions we all will have more and more resources to become wiser as Codans and so are able to continue puzzling ourselves before having to use a helpline… I know your contributions helped many already and will continue to help in the future. Speaking of which:

Signed-up a while ago already, can’t wait for it to launch… I am sure it’s gonna be a fantastic site complimenting Coda’s help pages and this amazing community! Just started to wonder: are you gonna publish it as a Coda doc? :nerd_face:

1 Like
4

Btw,

Already doing what you recommend, for example via so-called ‘sTables’:

Screenshot
Screenshot1711×965 155 KB

Hopefully codatricks.com will contain many best-practices as it’s very time consuming to find them within the Coda community!

3 Likes
5

No, mostly because I don’t want all the content to be loaded eagerly. It’s gonna be a more traditional website. I just feel too much pressure to get it out in a polished state, I cannot get to actually doing it. However just this week I decided (with a bit of nudge from @BenLee) to screw it and collect everything into a Coda doc as a temporary solution:

It’s work in progress but hopefully by the end of this week it’s in a more presentable state. I’ll send out an email to all codatricks subscribers

Regarding the “sTables, tTables” etc., I’m not a big fan of Hungarian notation, although I’m myself using prefixes like DB (master tables), IN (imported sync tables), OUT (views to be imported by other docs), HLP (helper tables mostly used for user flows: user-specific inputs, actions and variables for more complex operations etc):

image
image411×811 9.9 KB

image
image1835×779 66.1 KB

But that’s just my naming convention. You use whatever’s clear for your team, because the purpose ultimately is to help doc builders be able to quickly and predictably find and work with the data they need. I don’t use name abbreviations btw — on the contrary, I name tables/views/columns as verbosely as I can. It doesn’t matter for Coda (all objects are referenced by their IDs anyway, like $$[row:grid-QuAjty5EvW::i-aN8R1JXpHX:false:false:Display value]), but matters a lot for usability.

Lastly, with documenting it’s important to not get carried away. Best come up with clear as day conventions rather than have to explain everything.

Unrelated: I wish I had a PPL one day and my little own Cessna…

2 Likes
6

At first sight it’s looking veeeery promising Paul. Meanwhile your perfectionism (if I may be this direct) will give future viewers a more presentable state as you call it, I’m pretty sure current Codans are more than happy to hear BenLee was able to give you the final push and don’t really mind the way you present it that much, as long as your collected data is available in some form! You are amazing for disinterestedly sharing all the precious info with us like this… :pray:

Got it, thanks again for your insight and screenshots, much appreciated!

Reply to your unrelated wish: I know it’s not the same, but MS tries really hard to get close to the real thing and impresses with their newest simulator… :wink:

7

Thanks, but it will be fair to say it’s not 100% disinterestedly :slight_smile: This is my business after all, I consult on Coda and build docs for hire. So keeping a personal brand as a top Coda expert is also part of motivation. Furthermore, I’d really love my future clients to come to me with already well-built foundations so that I could channel my efforts (and their budgets) into making improvements and not rebuilding their mess of a doc from scratch :slight_smile:

That said, I truly enjoy sharing what I’ve learnt for everyone to benefit, and I was doing this before I started making any money out of it. So it’s not 100% interested motives either. As I once wrote,

It’s a win-win, that’s for sure.

1 Like
8

Hi @Paul_Danyliuk . Is there any significant reason you’ve ran into to use a generated id (e.g. UID) instead of RowID? I guess one nice thing with a generated id is complete control over it, however that can also be a bad thing since it is mutable.

And thank you so much for this - your write ups are very well written, easy to understand, yet very thorough.

1 Like