How to get data from different docs within a folder (even the new not yet created ones)?

Ask the Community
#1

I am given a task to make simple project management for a publishing team.
They have about 30 books and add publishes a book or two every month.
They need a management base which from it each of their team can see what task he/she should do and it is related to which book (and have filters to see urgent tasks, near deadline tasks, etc).
Each book has about 9 Phases from determining if it should be published, to marketing after published. Each phase have different group of tasks.
‌
‌
I made this system:

  • A new doc for every book. This doc will have all phases and related to tasks. For every new book, a template that has saved will be copied and used.
  • Each doc will have different pages, Within each page, there will be standards tasks in groups (tables) which have fields like “Task Name”,“Responsible Person”,“Deadline”, “Status” and etc.
  • There is Summary page in each doc that shows the “Urgent Tasks” and info of book.
  • 1 doc for reference info such as translators, editors and such that are used in book docs.
    ‌
    ‌

My problem is:
I need to make a doc that gathers all undone tasks, urgent tasks and related info from the book docs.
I can do this by manually linking tables, but I need a system that for every new book, it automatically gathers tasks.

Something like “Crawl all docs in in this folder > show all rows (tasks) that are undone & urgent”.

Is it possible?

#2

Dear @saeed_ramazany, welcome to the community :handshake:

Thanks for the detailed description, where I have some additional questions

  • What is the idea behind that you, do you need to create a new doc for every book?
  • As soon as a book is published, is there a need to keep the history of all published books in this doc? It could be that only the last years books should remain at hand for reference or statisitcal info. Of course you can always remain a non active copy for reference available.

When all is “housed” in one doc, it will be much more easy to filter different views that meet a certain purpose

1 Like
#3

Hey @saeed_ramazany, welcome to the community!

It is possible with the use of Coda API and a script that you will run either manually or using some scheduler.

  • listDocs endpoint allows to get the list of all docs copied from the same source doc.
  • listRows endpoint allows you to get all rows (with values) from a specified view — e.g. a view of your table that’s filtered to only show urgent and undone tasks.

When you copy the doc, table/view IDs and column IDs stay the same for the same objects in the docs. So with these two methods you should be able to collect all rows from all docs. Then you can insert that into your target doc. You’ll also need some logic to somehow hide the rows that are not in the collected list anymore, and to prevent inserting duplicates.

You can use Google Apps Scripts for this, or any other scripting environment of your choice. It’s not doable with Zapier (actually doable but not viable.) It might be doable with Integromat because the latter supports iteration over collections IIRC, but I’m not sure because I never used it.

As @Jean_Pierre_Traets said, it would’ve been easier if this all was tracked in a single doc. But I assume you need to track your work in separate docs so that you could share those with different people individually?

1 Like
#4

Each book has 9 phases in it’s lifetime and each phase consists of different group tasks.
Book as a Doc, Phase as Page and Group of tasks as tables seemed a right structure.

Remember that we should be able to copy all the structure for a new book, while be able to work with tasks in good user experience for not tech-savvy publishing people.

What you propose for the strcuture?
Even a new page for each book will make a very long pages that needs scrolling to get sth you need. As some of these tasks can be done simultaneously or one should be able to skip some.

#5

I think with this structure I will face problem with api request limit.
For example in a scenario which I want to list all urgent tasks of a specific employee:

  1. A request to list all docs (presume 10 books)
  2. A request to list all pages within this 10 books (presume 9 phases for each book)
  3. A request to list all tables (presume 4 group of tasks for each phase)
  4. A request to get all rows with specific filter I need

This will be about 360 requests just to show urgent tasks for a specific employee. And it is a lot it seems to me. I mean either my structure is problematic or the API is not the way to my answer.

#6

Oh, I think you’re just misunderstanding the concepts of working with data in Coda.

You don’t have to create a new Tasks table for each phase. You don’t have to create a separate page for each phase. You don’t have to create a separate doc for each book, unless you want to share those docs with different lists of people and absolutely cannot have e.g. Book 1’s publisher accessing the data of Book 2. Instead you unify as much as you can.

If I were building this doc, I’d make:

  • A table of Books where each row is a book record.
  • A table of Book Phases where each row is a phase of a particular book. (presume 90 rows for 9 phases x 10 books). A column Book would link each group of 9 rows to a particular book.
  • A table of Tasks, where each task is attributed to one of the Book Phases and one of those 4 groups you’re talking about.

So in total, three base tables.

Then I’d have one page to show the list of tasks (a view of the Tasks table). I’d have some user-controls in the top that would filter that view down to only one book, one phase, one type etc.

If I wanted, I could create separate pages for separate phases etc. — but still show the views of that one Tasks base table. But there’s little benefit to doing this really — I’d rather only create different pages where I actually need to present content differently, not as a means to select which data to look at.

Finally, I’d have a separate, “admin’s” page with a view for all urgent and unfinished tasks for all books and phases. In one place. And if you ever need to access that via API or Zapier, it’s going to be just one request from one table.

And if you decide to go with one doc per book approach, the same applies there as well. You won’t need the Books table, and your Phases table will only contain 9 rows in each of those 10 docs. But in the end, in each of the docs you’ll only have one place (one view of Tasks) to read from. 1 + 10 requests overall, not 360.

Pages are a purely presentational device. They are separate from data structure. The best practice is to first think of data design, then presentation.

TL;DR: Making separate tables (actual separate tables) like Phase 1 tasks, Phase 2 tasks etc are a bad practice in Coda. You only make separate tables when you mean totally different concepts in your system, different levels of data. You cannot combine data from multiple tables into a single table, e.g. collect all Urgent tasks from both Phase 1 Tasks and Phase 2 Tasks table, because as far as Coda is concerned, Phase 1 Tasks and Phase 2 Tasks are two different concepts, even if they have columns with the same names. But you can do the opposite — take a single unified Tasks table and make many views out of it, each filtered to show only that portion of data that you need — e.g. Tasks for Phase 1, All Urgent Tasks, All Tasks Assigned to Current User etc.

1 Like
#7

Thanks for your detailed guide.
The problem that I couldn’t figure out:
Every task of each book has it’s own state (Doing, Done, To do, Skipped).

In your “Table of tasks” I can’t figure how I can change the state for each task of each book.
You mean’t something like this?:

image
image865×218 9.95 KB

Because in this type of table, I wouldn’t be able to determine state (and responsible person and other info particular to each task of each book) individually.

#8

Not sure what you mean. If you want to change the state of many tasks at once, you can do that (and much more) with buttons.

Again, not sure what’s the problem? It’s the table where each row is particular to each task of each book — you can add columns to hold info like responsible person and other.

How about this? You explain here what you need in your doc, and I build that for you, for free. Just a few catches:

  • I record it as a tutorial video and post in the community for everyone to learn from. (I fill it with fake info, so no worries about your personal data)

  • I don’t take edit requests after that — i.e. I build you the foundation according to your needs and as I see best, but if you need additions afterwards, either you build it yourself or that’s discussed separately (I’ll try my best to do it so that you’ll be able to continue on your own though; I’m not in this for making you or anyone else dependent on me but for education)

Reason: I wanted to record this kind of tutorial for a while now. Here’s a perfect chance to showcase some best practices with a relatively simple setup.

1 Like
#9

@saeed_ramazany please write what you need in your doc (e.g. about those state changes and required columns), so that I could record this tomorrow. Otherwise I’ll just go about it as I see it.

2 Likes
#10

I felt very good when you proposed to built it.
Details like state changes and stuff like that are not the main issue and I think i’ll be able to add those details.

I’m waiting for your Doc and Tutorial. Many many thanks.

(Meanwhile I read your comment once more and got the idea for how I should make the Doc. I’ll ask question if there was more issues. thanks for your time and the energy you put).

#11

Sorry, I haven’t recorded this specific thing yet.

I have recorded a different tutorial though, which could already give you the idea of what I have in mind. Just assume that my Leads table is your Tasks table. You can implement it in a single page, with a single view filtered specifically for how each user looking at the page wants it to be filtered:

1 Like