Data Hierarchy and Aggregration

[Krunal_Sheth]

Hello,

While working with one of the customer, I realized that getting a multi-level deep data to aggregate is not straightforward - for example, when you have projects with sub-projects (and they have their own sub-projects) and then projects have tasks and if you want a view where you can see all the tasks rolling up to root project

Projects Table

• First create Projects Table
• Create a Lookup column named Parent Project pointing to same table i.e Projects table

Tasks Table

• Create another table named Tasks.
• Create a Lookup column named Project and have it point to Project table.

and then create a another formulaic column Direct Tasks on project table with formula like =thisDocument.Tasks.filter(Project=thisRow)

This should give you basic Project <-> Tasks relationship and two tables.

Now lets say, if you want to create a column that gives you All tasks (i.e direct tasks + tasks of sub-projects) here is what you need to do.

• Create a new column Path on Project Table - and set it to IF([Parent Project], [Parent Project].Path + '/' + thisRow.RowId(), thisRow.RowId())
  What this does is for every project - it creates a path from root project to current project.

• Create another column name Children Projects on Project and set it to Projects.Filter(Path.StartsWith(thisRow.Path)) - this brings all the child projects where current project is in Path (i.e current project is one of the parent project)

• Create another column name All Tasks and set it to [Children Projects].[Direct Tasks].FormulaMap(CurrentValue).ListCombine().Unique().BulletedList()
  this goes through each of the children project and takes their Direct Tasks (removes duplicates - as task on leaf project could appear in all the parent task list) - this should give you exactly what you are looking for as seen in below image
  

if you want to play with document, here’s a link.

Thank you.

[Joseph_B]

This is great, thank you.

Are there plans to make this more straightforward in the future?

[John_Beaudoin_Jack]

This IS great and could be very useful for my work. Since I haven’t had my coffee yet and can’t bear to read Coda formula this early, does this approach permit multiple root projects on the same table? I assume so, but…

[Krunal_Sheth]

yes, John, multiple root projects are fine. I updated the coda doc I linked above with data that has multiple different projects as root.

[Riah_Allen]

This table is wonderful!! I plan to use it in quite a few of my projects, but I was wondering if it’s possible to apply the same tasks to multiple people while also allowing it to be recorded that they’ve completed those tasks without applying it to every single person?

Taking your table example - each of your detailed views on your Project View would be an actual person and they all have to complete the exact same projects and tasks, but in different orders so the task view table would always change based on person and whatever day the table is from. So instead of the tasks filtering to show all the direct tasks based for a project - it would filter to show the specific tasks that person must complete that shift. And then apply only to that row/person they’ve filtered into.

So, each ‘project’ would be our training ‘block’ and under that project we have 25 tasks that need to be completed. Adding a button and checkbox column to the tasks table along with adjusting the filtering to only pull the tasks marked true - get me to the correctly displayed tasks table within the detailed view. And the buttons work great applying their action to another table marking that task as completed…but I can’t seem to figure out how I can get it to apply to that specific row/person without the whole column activating…Do you know if it’s possible? (I’m hoping it’s just a really simple thing that’s I’m completely oblivious to!)

I’d appreciate any help or pointers you could throw my way on how it might work with your table, because it’s so close to what I’m needing!!

Thank you,
Riah

[Krunal_Sheth]

Could you please tell me more about each person has to complete exact same projects and Tasks?0

I would think it could be the same task done by different people , but you would have a separate record for each people ((like Complete Homework is a task that every student would do, but you would have a different record to keep track of each student in the class with a some other field like Category which all these records would have common)?

or is it that same tasks kind of goes through different people at different time - and at any given day assigned to a single person - like a workflow?

Thank you,
Krunal.

[Riah_Allen]

It’s more of the same tasks going through different people at different times, but there’s no real ‘this before that’ type of hierarchy. So, we’re a small restaurant chain and each new hire has to go through a 3 month training period to completely learn everything about working in the restaurant. It’s broken down into 7 blocks with numerous tasks under each block. Everyone has to complete all of the same tasks and blocks, but depending on when they were hired and what shifts they work - it’ll be at different times then everyone else. And, like I mentioned, they don’t have to follow a certain order under each block. All tasks just have to be completed.

The way we would like it to flow is this: each week our Director of Training sits down and creates a training schedule for each restaurant based on the schedule of employees working. He keeps track of what new hires have already completed and what they still need. So if they’re working a morning prep shift and they have to learn how to chop meat then he selects that employee and activates the button for the Chop Meat task. He copies his completed shift schedule and moves it to a separate section in the coda doc. There’s a link in our manager’s shift email draft that they’d click and simply press any and all the boxes for the tasks employees completed during the shift. This then would record which of the tasks those employees finished and record them for our Director. (This is currently all done on paper so he’s physically having to travel to each store to check the employees folders to see what they’ve completed before he can make any new schedule).

From there I’ve set up tables to flow so when all the tasks under a block are completed it sends him an email letting him know that they’re ready to take that blocks’ test, etc.

So, I was struggling with how to get each button to apply to each person without having them all personalized for that specific employee (because there are about a 150 tasks - and that’s a massive amount of buttons if you multiply them by the 200ish employees we have)! I’ve added a link to a scaled down template of my original idea - but then I also have a section listed as “NEW” and it’s basically my butchered attempt at doing just what I’m asking…and I think I might be on the right path, but I’m still pretty new to formulas so…there might be roadblocks in my future!

Thank you for your time,
Riah

[Sam_Giligich]

It would be huge if we could have subtasks as a field for a table row. As this is what is useful in JIRA, PivotalTracker etc. Much easier to manage for a large team with lots of tasks.

Additionally, in JIRA you can easily assign the ‘subtask’ of a task to someone else and have them clearly see that as a task to do in addition to their ‘tasks’. Coda it would be kind of separate.

[BenLee]

Hi @Tucker_Webb,

Thank you for trying out Coda and for joining the community here.

I saw a couple of your other posts and it’s more than obvious that you would like to have nested folders and nested tasks. This is a request that we have gotten before and it’s not the easiest one to fulfill. It’s also not always the best answer things. There are quite a few ways to “nest” items and it’s difficult to say which one is the best way to go in the long run. For that reason, it’s not the first feature added.

Things like nested folders make sense from a human standpoint because it’s we can’t actually compile that much data at once, so we break things up into smaller and smaller bits of information that we can handle until we get down to a point where we can make sense of things. This isn’t how software necessarily works though, so there are different bounds to consider when organizing items in the cloud.

There is actually quite a bit of complexity to it, considering sharing rules and other factors.

This is logged in our tracker and something that we are regularly researching, so there isn’t a need to post over the work that other community members have contributed.

If there are other suggestions you have for the product, please use the Suggestion Box category.

Coda moves quickly and is always adding new features. That said, things do take time and a post one day doesn’t mean that the feature is added the very next day. Right now we don’t have nested folders. We will continue to work on the product though.

[ABp]

I’d like to echo the sentiment here amongst the commenters for continued development by Coda of more user-friendly ways to work with hierarchies within rows and data.

In particular my pain points have been:

• Successfully using lookups to create intra-table structures like subtasks within the same table, ones talked about here for example:
  Subtasks in a task

I have trouble with the sophistication of the formulas Paul is talking about here, and Krunal’s solution in this thread is simply too hard for me to understand, and likewise I couldn’t implement in my doc as I would break it!

• More sophisticated builds between related tables: Grouping related data, pulling in descendant/ancestral root data between linked data to display in columns, use of associated filters, etc.

I know you guys are working on these solutions but I just wanted to flag up again that my need for this stuff remains big!

Thanks and Cheers!

[Jack_DePew]

@ABp Same boat. Trying to figure this out. My understanding is you can use referenced lookups (detailed here: https://blog.coda.io/launched-simpler-table-setup-8f6a202f3af2) to create two-way (lookup/rollup) relationships b/n a table and a sub-table, such as a Tasks table and a Projects table, or perhaps your Expenses table and your P&L.

I don’t see why you can’t use this technique to daisy chain more than two tables together. Am I missing something?

[Jack_DePew]

^nvm, I just scratched the tip of the iceberg.

[ABp]

@Jack_DePew hey,

Yes, I have actually been using quite a bit the new “blue-dot” lookup hint to quickly get a reference back to the originating table. However, when I do this, this puts in a formula in the destination table, so I can’t add any new records to the mix.

Case in point:

I have a Feature that belongs to a Product

When I connect the Feature to my Product table, I can then go back to the Product table, create a new column with a lookup, and I’m prompted via the blue dot to create a lookup back to the Feature table.

However, when I try to add a new Feature to the Product in this column, I can’t as it’s controlled by a formula and blocked. Ideally I’d like to be able to have this work two ways and able to add new entries both in the “Features” column in the Product table, and in the “Product” column in the “Features” table.

I’d be glad if anybody could shed some light on this actually and whether this is a limitation of this method, in which case we really don’t have true reciprocal functionality here.

I have been using Join Tables, talked about here:

Display each item in each row

Dear @Chain_Reaction Please have a look at this basic example:

with some success, although it complicates my set up as I have to deal with three tables. However, there is a good deal of data I can get that I’m after, as with this Join Table set up I can create a “subtable” view when viewing in Layout. So for example I can have a contact at a company, when I open that card, I get a subtable showing a list of communications (calls, Emails) and their details, and then link these to the various projects I have ongoing for the Client, getting detail here as well via subtables.

I think that may be not a great explanation so apologies for that, in a bit of a hurry.

Hope that’s at least a bit helpful!