Help! How to share filtered views involving multiple tables via cross-doc

1

Hey Folks,

So we have a doc that combines the following tables to create compensation insights for staff:

  • Levels
  • Functions
  • Comp benchmarks
  • Comp Ranges
  • Employee Data

Now that we have created the appropriate lookups and formulas, we are trying to create filtered views for various leaders.

We pulled it all into the employee table, created a filtered view, and then tried pulling it into another doc via a cross-doc.

But that doesn’t work :frowning:

Even if we pull in the respective look-up tables, because the lookups are to the orginal doc and not the cross-doc one, it doesn’t pull in the lookup columns or any formulas using the lookup columns.

Please help!! Someones has got to have figured out a way around this,
-A

2

Hey @Astha_Parmar!

Quick answer: use my pack instead of Cross-doc. I made it exactly because of the pains that you’re encountering there:

It’s a bit more setup upfront (please watch the videos as there’s no other documentation yet) but it will solve your use case:

  • No need to create separate views for various leaders – you can setup a single view and row-specific tokens to control shareable portions

  • Lookups will properly resolve out of the box for tables that come from the same doc, and you can manually fix the mapping if these tables are coming from different docs.

1 Like
3

@Paul_Danyliuk – you are a life saver!!! Will try this and circle back if I have questions. I really cannot thank you enough. We did all this work and I was starting to panic that it would all amount to nothing.

1 Like
4

Hi @Paul_Danyliuk, Thank you once again for creating this amazing resource!

I am making my way through the videos and have a question. Unlike in the example you have shown, all my master tables are within the same document (employees, benchmarks, titles, ranges etc.)

So say I pull a view from a table (employee) that includes lookups from tables (e.g., title, which in turn is pulling in data from benchmarks relevant to that title).

Will this mean that I need to pull in each of the tables used for lookups in the employee table? And, should each of those lookups tables have their own ‘secret token’?

I hope that made sense…I am still figuring it out. Once again, thank you SO MUCH!!

5

The lookup tables — yes, for the references to resolve, you’ll need to import those tables as well. This means, for each of those tables you also create an OUT view.

If it’s not a problem that all rows would be imported for each client doc, no need to assign tokens there — you can just add the [read=*] suffix to the view’s title. Otherwise yes, the same token-based system will apply. That said, if some row from an imported employees table has a reference to a row that’s not imported, it’ll be gray and there won’t be a popup, but the reference with its label will still be visible (i.e. if you import an employee Paul who has a reference Company to a row Coda but you don’t allow Coda to get synced to that doc, the reference chip would still say Coda even though you won’t get access to the underlying row data behind Coda)

6

Thank you, that is super helpful.

I have have one more questions: Will the changes people make to a table I pull in sync back to the source table?

So, for example, I have an employee table with a column called level. I pull it into a doc filtered for each country. If someone updates the value of level in this synced doc, will it update in the source/base table in the source doc?

I hope that made sense.
-A

7

There’s no two way edits in Packs yet. The sync table is always basically a “read only” table: what those synced columns are — they are all formulas basically reading corresponding fields from a Row object.

Coda is exploring the possibility of those 2-way syncs though, but no hard commitment on that and the timeline is unclear (i.e. there’s not even a beta yet). You can hack together an “edit from the child doc” interface that would submit changes back to the master doc either through Cross-doc Actions or better, a webhook automation. Cross-doc Actions are a bit clunky and also not secure, since you have to expose a read-write connection to the private doc there, and I haven’t yet added my own Cross-doc Actions implementation to my pack yet, although it’s certainly planned. Webhooks need a bit more setup but is a better mechanism overall.

8

Hi Paul, I really appreciate your help in working through this. I’m gradually redoing my data architecture to work with the pack (had made a ton of work arounds that I hopefully won’t need now :slight_smile: ).

One more question: is there a way to use the pack to merge tables?

Thank you, thank you!
Astha

9

It’s a planned feature but it’s not there yet. And I have a few projects I need to complete before I get back to this pack.

As a temporary measure I can privately share with you a version of the pack that can do merging (at no extra cost). Alternatively you can use a separate pack, Merge Table by another maker.

10

It would be great to have access to the merge feature. When the official pack gets updated, will it impact the setup I do now? I think you know so much more than I do, that I would just trust your recommendation on which route to go with.
-A

11

I try to not break things but keep them working retroactively :slight_smile: But I’ve completed a project with this pack and already saw certain areas where I could make it easier (mostly in the area of re-linking copies of docs with other copies of docs and restoring the lookups there)

The reason why I haven’t finished the merge feature was because I implemented it in a bit geeky way with SQL-like expressions, which is not user-friendly. And I haven’t had a chance to redesign it from the ground up yet:

I checked out the other pack and it’s $5/maker/mo — that’s in addition to the $3/maker/mo my pack is. But hey, it’s already available. So see depending on your budget. I don’t think I’ll be able to implement merge tables in my core pack until sometime April-May, at which point you might choose to migrate or not to migrate :slight_smile:

12

I would love to give the query table a try – this looks awesome!

I just purchased pro pack subscription. Please let me know how to get access to the version that has query tables in it.

You are amazing :raised_hands:t4:

13

Please ping me at paul@codatricks.com and give me your Coda email if it’s different from the one you’re writing from, and I’ll share that version of the pack with you

14

Just sent it. Thank you!!!

15

Hey @Paul_Danyliuk – I have a question about the merged tables. Theoretically, could one add formula columns to a merged table? And what would happen to those columns when the table refreshes?
-A

16

You mean add a column to a sync table that’s a result of a merge? or a table that’s a source?

Generally sync tables (Cross-doc, my pack etc) work like this:

  • Sync table refreshes at a cadence (hourly, dayly) or manually.

  • By sync, I mean at that moment of time it fetches the data from the source table(s) as instructed.

  • The data is always the resulting value in the cells. It doesn’t carry over formulas, table metadata (conditional formatting etc) but only the resulting values that you have in original cells.

  • What sync table does is that for each source row it creates a row in the resulting table, and inserts all the row data into an object called Row. What you then see in your synced columns are really all formulas reading different properties from that big chunk object, e.g. thisRow.Row.Name, thisRow.Row.AssignedTo etc. So all these things are already formulas, but the formulas aren’t like the original formulas.

  • In your source tables, of course, you can have the values recalculated more often. E.g. you reassign a task and it now appears as a reference in a different row. However, you won’t see this change in your sync table unless you refresh it. Then the sync table will read the data in the cells of the source table again, load the new Row objects, and only then you’ll see the changes propagate to your resulting table.

  • If you add new formula columns to your sync table, that’s okay — they will recalculate whenever there’s a change in anything they depend on. If they only depend on that table’s columns — the formula only will recalculate when the table is re-synced.

Hope this makes sense. A while ago I recorded a video about how this works:

17

Super clear, thank you as always!!!

1 Like
18

Hey @Paul_Danyliuk – I have one more question :slight_smile:

So…for the savvy user who makes their way to the pack settings, would they be able to see tables from docs that they are not shared on?

For example:
Doc Asia: Asia Comp Benchmarks
Has OUT table Asia Comps with read:*
Asia HR Rep has edit access to this Doc

Doc Africa: Africa Comp Benchmarks
Has OUT table Africa Comps with read:*
Africa HR Rep has edit access to this Doc

Doc Global: pulls in all those OUT tables into a merge table.
Neither the Africa or Asia Rep has access to this doc.

Would the Africa HR Rep be able to see the OUT table for Asia via the pack interface?

19

Short answer: yes; Africa HR Rep can technically open up the STP pack panel and locate the OUT table for Asia there. If the doc is sufficiently unlockable, they can drag the table into the doc and if the rules there are read:*, they’ll see the data. You have to use the secret tokens to reliably separate who can load what through the common STP setup made under your account.

I’ll explain some more about how the packs authentication system works and hopefully it makes this clearer.

When you installed Sync Tables Pro into a new doc, it asked you to authenticate, in my pack’s case it was authenticate with Coda itself. It’s basically a one-click setup and it uses your account (the one you were using when you made that action). So in the end, Coda created a connection under your account (i.e. as if it were you), and this is going to be the connection under which everyone who’s using the doc will use Sync Tables Pro. Don’t worry, nobody can do anything under your account because the connection can only work with a pack it’s created for — i.e. it’s just the pack that will work (tables loaded, buttons pressed etc) as if it were you pressing them and making changes.

The most important thing that this means is: the pack has access to the docs that you have access to. This way you don’t share Doc Asia or Doc Africa with anyone, but you can make a public doc e.g. Summaries where you import some data from Doc Asia and Doc Africa. The data is imported because it uses your connection, but people cannot access those full docs directly.

Then, connections can be limited to readonly, and to specific tables/views/docs — that’s what cross-doc does. And that’s exactly where cross-doc lacks: its security model totally depends on those limited connections. That’s why you basically cannot swap them, or replace tables, or introduce more flexible access rules.

That said, since you would’ve authenticated Cross-doc with your account, it would still let anyone in that doc import tables/views that you would’ve been able to import. (unless something changed recently and I’m not aware of it)

My pack does it a bit differently. It uses the full access token but programmatically (in the pack’s code) limits what rows get to go through depending on that secret token parameter, security rules and other things. So, again, Cross-doc allows everything through that the connection rules allow (not evaluating any data), and my pack has that extra evaluation step to it. But underneath it’s still that connection that’s like “act like Astha Parmar”. That’s why, if the data is sensitive, you have to make use of read rules to actually secure the data from getting “let through” the filter inside the pack.

Hope this makes it clearer

1 Like
20

Hey @Paul_Danyliuk – I think the row level security works amazingly well for distributing central data.

So for the use case where I have data on all staff and I want to distribute it by country or leader or level, then the token in the master can have all three and the receiving table can use parts of that token. Which is brialliant, thank you for this!!

For merge tables, the assumption I am making is that the all the distributed tables must have the same _out_rules to then be aggregates. This creates a risk because then anyone with that originating _out_rules token can see the other tables being aggregated across the docs.

I am wondering if there is a way to set up the _out_rules as a public-private token combo in the query. So each merging table has a 3 digit _out_rules token which must be combined with a central 3 digit token in order to pull that data into a merged table.

I really hope this makes sense! My skills at all of this are fairly rudimentary,
-A