Hi,
There are some scenarios in which it would be useful to be able to have Documents refer to data from other Documents. For example:
- Have a “master data” document with a Client List, and have department specific Documents that import that data as Read Only.
- Have a GDPR Requests/complains Document, and other documents just lookup “does X have any open GDPR requests?”
- Have Separation of Responsibilities across departments / documents
On a very basic case we would be talking about something similar to Google Sheet’s importRange() or a database VIEW: something seen in the Document as a Read Only table. More advanced would be to specify what columns to bring in or a filter clause. Most advanced would be to have the lookup formulas work across documents. Though I would be against that last case, could be a maintenance nightmare.
Here, december 2021 @alexmojaki said:
You can’t reference across documents. What you really need is to combine your documents into one and use Access Rules to hide sensitive data from the appropriate clients.
Has this changed? I do not see anything in the Roadmap regarding either references across documents, or a importRange()/ read-only views behaviour.
So… like… importRange() in sheets
I may be completely wrong here Telenieko, but IF there was a function to refer to data from other documents, it would kill Grist’s business model, and they need to get money to pay their bills.
One would simply divide data across multiple documents and avoid the limit of 5000 rows for the entire document on the free version. Which is probably the main reason to migrate to the paid plan, I guess.
(…) if there was a function to refer to data from other documents (…) one would simply divide data across multiple documents and avoid the limit of 5000 rows for the entire document on the free version. Which is probably the main reason to migrate to the paid plan
(…) it would kill Grist’s business model, and they need to get money to pay their bills.
Doing this is incredibly complicated, even with standard DBMS sharding/partitioning is hard; Imagine (in this example) that you split your Invoices table (or Items which would get to 5000 much faster) by year to avoid the 5K limit: now you’d need to keep track of all the partition references so you could query them from the “central” document, find a way to handle updates to the sub-documents, etc.
If you can manage that you will soon realise that the maintenance cost of such solution is way more than upgrading plans or, alternatively, selfhosting.
In any case, the importRange() or VIEW proposal would not care, you could/would just count those rows as part of the document (importRange() may work like a materialized database view); that is one of the reasons I find the importRange proposal to be the easiest to implement “pinging” dependant Documents to refresh the “view” is probably the hardest thing to do; though I’ve not yet looked at the codebase!
There is not a way to refer to data from other documents, I’m sorry to say. This is due to technical reasons, not business ones. Basically, it’s a hard feature to implement. This is partly due to what @telenieko mentions (refreshing the view), and partly because Grist currently lacks any kind of function that can create and populate a set of rows (as opposed to populating a single cell).
What people have done is to copy data from a table in one Grist doc into a table in another. As long as the copying is one-directional, and there is a simple rule for when to do it (e.g. daily), it’s not very difficult to automate, and can be done with Zapier, or some other integrator services, or a custom script that uses the API.
1 Like
Thank you for the reply @dmitry-grist! Would it be possible to have the feature request on the Roadmap?
Basically to know if “this cannot be done and will never be possible” or “this has to be carefully thought out before going on either direction”; I have yet to explore the API route 
The Feature Request to consider would mainly be for the “copy data from document to document” part; the importRange() like capabilities (aka, Materialized Views; table replica). First on a timer with data within the workspace, later on real-time and lastly, the most difficult, across workspaces (more difficult because of permissions, etc.). – you can guess I abuse importRange() on Google Sheets!
I guess there’s at least one pre-requirement: read-only tables to prevent accidental writes to the views which would result in data-loss upon sync.