Best way to do multiple instances of a table across different users, maintaining central record

Here is my use case: I build a rich table, multiple rows and columns. I want to give a distinct instance of this table to each of a set of users to use and fill in, but I don’t just want to give them all a distinct copy, because I still want the information centrally where it can be stored and be processed by row/column/user.

I guess what I really want to know is the best/easiest/most elegant way to do a 3D table in Coda - regular rows and columns, and a “z” dimension of user instance. I can think of hacks, but they all seem very… hack-y. So I am hoping someone has a great idea! Thanks in advance!

Hi @Andrew_Milne… A bit of clarity needed.

Given users Mary, Tom, and Sue, are you asking for a table where:

  • Mary can only see rows created by Mary?
  • Same with Tom and Sue?
  • But you can see all rows.

More specifics on what restrictions and privs you want to have for Mary, Tom, and Sue would be helpful.

1 Like

There is a table, there is a list of users. I wish to be able to push out to users so that each sees a table with ONLY their entries, but I see a table with all their entries.

In a way, there is an easy solution - just duplicate all the rows of the whole “original” table, as many times as there different users, with a new column for users to keep them separate. But the issue is the mechanism by which to do that, and a more efficient way to do it.

Use case is this: I am a teacher, and am testing Coda for educational uses. I need to be able to something like the following: Teacher plans using a tools like the Task Planning table, and the students all get a copy of the Task Management table to individually track their progress. If a new student is added to the class, they have a new copy of the table automatically.

The more I think through this… in the example above, there are really only three columns I need to keep track on individually - status, actual start, actual end. Everything else is fixed across instances. But… how do I set this up so I don’t have to duplicate a bunch of stuff that doesn’t change? And how do I handle the “push” element without needing user (teacher) intervention?

Ok… Thanks for the Doc.

Last question: Can the students UPDATE their entries or only VIEW them?

If they can only VIEW them, then this is easy:

  1. You’ll need a Team account, with You as the only “Doc Maker” and all the students as free Editors.
  2. You’ll add a “Assigned To” Person-type column to your “Task Management” table (or which ever Table you’ll use to assign entries to a student.)
  3. You’ll create a “Private Folder” in which you’ll store your master Tables and all your Pages that the students shouldn’t be able to see.
  4. You’ll create a “Students” folder (or whatever you want to call it) that the students will each be able to see the pages inside.
  5. On one of the Pages in the “Students” folder, you’ll create a Cross-Doc that will be a view-only of your master “Task Management” table.
  6. You’ll add a “filter” to that table where “Assigned To” is “Current User” (image attached).

Then, when any student views that page, they’ll only see entries Assigned To them.

You need a Team account to have access to the Private Folders and other privilege controls so students can’t change/remove the filter on that Table.

IF your students need to be able to add/delete/modify their own entries… You’ll still need a Team account but the setup is going to become more complicated/difficult.

The main thing is, you DO NOT want to start making copies of the Tables for each student. That’s not scalable or manageable.

2 Likes

Thanks!

Yeah, I assumed that a Team account would be necessary.

So the plan I am looking at, then, would be something like this: A “thin” table with however many lines of of status/start/end for each student, flitered to current user. Then a lookup for the other columns for the non-replicated details, maybe indexed by rowID?

I’m wondering if I would be better linearizing it - instead of having, say, 10 rows of three columns per student, I have one row of thirty columns. Don’t know enough to know whether there would be any load/latency differences, but it would make some other operations I will need be simple column lookups, rather than filters.

As a DBA (database architect) for 23+ years, I would consult that if you’re trying to decide between “more rows, fewer columns” and “fewer rows, more columns” that the data is not yet structured well.

In general, each Table should contain as few columns as necessary. Each Table should serve a particular and specific purpose. Then relate to other Tables which each server their particular and specific purpose.

For example (super simple/silly example) if for some reason someone created a single Table and in it placed both Organizations AND People, there is no efficient way to relate People to Organizations. The Table won’t scale or be easily managed (requiring too many Columns to manage the different type of data required by People and Organizations).
That would be an example of an incorrect “fewer rows, more columns” approach.

Instead, create a “People” Table and a “Organizations” Table and a “one-to-many(Organizations-to-People) relationship.
That would be an example of a correct “more rows, fewer columns” approach.

Hope that helps in perhaps re-thinking and re-structuring your Data. :slightly_smiling_face:

1 Like

Perhaps I’m late to the party again, but if I hear your request right, you want to have (in simplified form):

  1. A pro-forma table with Tasks (let’s call it a checklist).
  2. A way for each User to have their own copy of a checklist to fill out.

For that you’ll definitely need a Users-Tasks level table (i.e. a table where one row contains values for a specific task and a specific user)

From there it gets a bit more complicated. There are two approaches:

  1. Every time you add a user, you make as many new rows in the Users-Tasks table as there are items in the proforma Tasks table, thus making a checklist for this new user (i.e. adding rows so that the user has spaces to fill in.) Whenever you change anything in the proforma table (e.g. add or delete items), you have to remember to add/remove those items for each active user. Then you build your UI on this Users-Tasks table that’s filtered to only show entries for current user, or all entries if the current user is an admin.

  2. If you only need buttons, or if it’s okay to show a modal window for the user to enter the data rather than let them enter it directly in the table, then you could create such rows lazily only as soon as the user clicks a button. The button will then create a Users-Tasks row if it doesn’t exist yet, and optionally open it as a modal. In this approach you build your UI on the Tasks table and create missing Users-Tasks rows behind the scenes. No need to generate X rows upfront or bother about propagating checklist changes later.

The second one is cleaner but has this limitation that users cannot enter values directly in the table.

1 Like

@Paul_Danyliuk, I am currently using a button-based approach like your (2), but using a rather baroque “list” method. (There is a list, for instance, of task statuses, and you update the entry in a particular list position. It does have the advantage of being flexible if the number of tasks grows - the list just grows.) But after I finish the build I am trying right now, then I will do another build using the generative approach you are describing. I really like that idea!

Thanks @Shane_Robinson, I think you are completely right. I am still trying to get my head around the appropriate structure, and the appropriate structure that is realizable with the right kind of control and permissions, in Coda. There is a really easy and wasteful way to do what I want - just duplicate the entire table, stacked “vertically” as it were, for every user and filter each users’ view. And I am struggling with how best to implement something less huge and more elegant. I certainly need to fear rows less!

On the off chance that anyone cares, here is the first solution I have completed! On the student page, everything works through buttons, to make it easier to lock down the rest. All alterations made on this page only alter the User Tracker table, which is then read back into the display table. I need to add an undo. Right now, instead of Current User, I am using a stand-in check box in the tracker table.

The status and dates for each task are tracked with a list. Is that a good idea? I don’t know. But it works, and it is insensitive to the number of tasks so has some robustness.

2 Likes

:clap: :clap: :clap: Congratulations!

1 Like