Data Hierarchy and Aggregration

#1

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.

15 Likes

Need help with Nested Lookups
Nested Projects - Sort Order
#2

This is great, thank you.

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

0 Likes

#3

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…

0 Likes

#4

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

2 Likes

#5

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

0 Likes

#6

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.

0 Likes

#7

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

0 Likes

#8

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.

1 Like