Dataview Rollup for Project Management

Help
1

I’m trying to implement a light and flexible project management system in Obsidian, the key missing piece is rollup. I must be missing something because I couldn’t derive any applicable takeaway from the other posts in the forum.

My projects are made of goals (aka milestones) and activities carried out to complete those goals. Goals and activities are separated into different folders within the overarching Project folder. For clarity Project / 10 goals / 20 activities etc. 10 and 20 are on the same level, as shown here.

The result I’d like to get is roughly something like this but expanded. I’d like to see how all goals and the corresponding activities branch off the original project. I’d like to group the activities by the corresponding goal and to show some additional activities properties like status etc. I was able to achieve this when querying the activities by themselves, but not in the consolidated view

I assume the issue can depend either on (1) the folder structure, (2) on somehow the lack of bidirectional links between or (3) my inability to apply group.by to this use case - pretty sure this is not working.

For example, regarding the folder structure, am I querying the right folder?
As fas as point 2, I have tried to brute force my way through as you can see in each individual page, here, here and here. not sure this is the most elegant way to go about it and I’m definitely open to suggestions and improvement. If this approach is right them I’m doing something wrong with the group by.

There are so many moving part that I believe is not worth to share what I’ve been trying to do - a lot of things. I’m open to receive any suggestions about how to improve my rough attempt. Understand this is a heavy weight, with too many moving parts for me to handle, I hope someone with a good heart and knowledge of dataview is willing to help me out :smiley:

2

Hi.

Well, this have a complexity level difficult to solve without a careful “step by step” reflection about the ideal structure to achieve the goal.

I don’t think the main question is in a folder structure. That doesn’t have much relevance for the query. It’s important for your organization, not for query itself.

On the contrary, the relation between “levels” can be a relevant thing.

Let’s advance an example.

  1. A project folder (“Project A”) with a [[Project A]] note. Inside this folder, only for organize the other related notes, we have the “Goals” folder (with the goals notes) and the “Activities” folder (with the activities notes).

  2. Inside the [[Project A]] note we can create the tag #project and place our query.
    (the #project tag can be optional… only if you have other files in the same folder level of the [[Project A]] note, i.e., inside “Project A” folder)

  3. In each note on “Goals” folder we add this metadata:

project:: [[Project A]]
  1. In each note on “Activities” folder we add a metadata field with the relation between activity and related goal
goal:: [[goal A1]]

  1. Now the complicated part: the query. Isn’t easy to work in this direction because the relations are made from end to start, i.e, from activities to project. The more easy way is in the opposite direction, from project to activities, as you try to do in your examples. But this is not the more practical way, because we want to create the note and in the same note create a relation only with the superior level, not with the level above.

  2. There is also the problem of dataview limitations. I don’t have js knowledge, so I work only with dql queries. With this type of limitation, it’s only viable to your with one Group by. Or better, it’s possible to work with multiple Group by commands, but with this type of structure the result would be a mess with multiple duplications…

  3. With all limitations we can start with this query:

TABLE WITHOUT ID G.project AS Project, G AS Goals, rows.ACT AS Activities, rows.ACT.status AS Status
FROM "Projects/Project A" AND #project
FLATTEN filter(file.inlinks, (i) => filter(i.file.inlinks, (ii) => ii.goal)).file.inlinks AS ACT
FLATTEN ACT
WHERE ACT.goal
GROUP BY ACT.goal as G

(status as an example of a field inside activities notes)

filter(file.inlinks, (i) => filter(i.file.inlinks, (ii) => ii.goal)).file.inlinks is the main step to start relations between notes from the last level, i.e., from activities notes with links to goal notes. But to give the opportunity to have other type of inlinks to the goals notes, then it’s necessary to add some strange filters…

  1. If the query is placed in the [[Project A]] note, then is redundant the column related to the project (a column with repetitions of the project A link…), then we can remove that column:
TABLE WITHOUT ID G as Goals, rows.ACT as Activities, rows.ACT.status as Status
FROM "Projects/Project A" AND #project
FLATTEN filter(file.inlinks, (i) => filter(i.file.inlinks, (ii) => ii.goal)).file.inlinks AS ACT
FLATTEN ACT
WHERE ACT.goal
GROUP BY ACT.goal as G

This is an example. But, for sure, not the more simple and elegant. But with all my limitations, this is the best sample I can give you.

2 Likes
3

Thanks for taking the time, the explanation is very clear even for someone like me who has 0 experience with dataview JS. I also appreciate you could totally get my use-case and the solution is much more elegant than me trying to link everything with everything, hoping it would end-up working.

As they say, and I believe is especially true here, one picture is worth 1000 words, so I’d post a couple of screenshots about the folder structure and internal linking for anyone else that could benefit from this explanation.

I get the point about the redundancy of putting the query in the [[Project A]], but for the purpose of demonstration, didn’t care.

So the only improvement I’d make is to have Project A appear just once, and not being repeated for each goal. I guess this can be achieved with a group by, but being not fluent in dataview JS I leave the field to an expert like you

4

On a separate note, I don’t get why this type of linking is so difficult. I initially got inspired by this example - you can see my reply as well. I assumed that year could be assimilated to project, quarters to goals and activities to month… but evidently it didn’t work for me. What’s your take?

5

Well, about this, as you said, we can use another level of group by:

TABLE WITHOUT ID P as Project, rows.G as Goals, rows.rows.ACT as Activities, rows.rows.ACT.status as Status
FROM "Project A"
FLATTEN filter(file.inlinks, (i) => filter(i.file.inlinks, (ii) => ii.goal)).file.inlinks AS ACT
FLATTEN ACT
WHERE ACT.goal
GROUP BY ACT.goal as G
GROUP BY G.project as P

But everything is flatten in only one row (per project), what can be a mess if you want to see the relation of goals and activities.

That example works if you are working in a top-down way, as you tried before. Works well to a static structure of notes. But if you want more freedom in the lower level (activities notes), that system is very rigid, because each time you create a note you need to add metadata not in the note himself but in note one level above.

I never tested it, but maybe you want to check this plugin:

1 Like
6

Thanks for sharing the breadcrumbs plugin, it’s worth playing around.

When I add another level of group by, the query stop working, actually I was asking because I’m not sure it’s possible to group by multiple fields

7

About this, you don’t follow my query. When we add another group by level, we need to change all the column fields (in the cases without renaming, we need to add another prefix “rows”).

1 Like
8

thanks mate, you’re the dataview Guru

9

I tried to do the same thing when I started with Obsidian. I’d used it in Notion and it felt like quite an important part of my system. It took me a while but now like many things I think I’ve got it working better in Obsidian than it did before.

In my system, I have roles (e.g Home, Work); areas of focus within roles (e.g Family, Leisure, House within Home); “projects” which can link to one or more area of focus, and finally tasks/actions which are part of a project.

My main use case was to get a list of actions for my roles - e.g. Home or Work. I wanted to only enter information once, and wanted to retain as much flexibility as possible - e.g. actions can support more than one project, within different areas of focus.

I’ve done this using dataviewjs. In particular, using outlinks with some standard javascript. All of my pages in my project system have a link to a “type” and links to other pages.

So, to get all projects within a role, I can do something like:

let role = "Home" // Home is the name of a page with type role
//Get areas of focus
let focii = dv.pages('"Vault/Focus"')
	.where(f => f.file.outlinks.includes(dv.page(role).file.link))
	.map(value => value.file.link)

//Get projects which link to this area of focus
let projects = dv.pages('"Vault/Projects"')
	.where(p => p.file.outlinks.some(r=> focii.indexOf(r) >= 0))
	.map(value => value.file.name)

I’ve recently move to using inline tasks rather than a page per task. If a task is part of a project, it has a link to that project within the text. So to get all the tasks from all the relevant projects, I just loop through the projects, looking for tasks that have the link text (as tasks don’t use outlinks afaik). The code to get a list of tasks is:

var myTasks = dv.array([])
for (var i = 0; i < projects.length; i++) {
	var projectTasks = dv.pages('"Vault"').file.tasks
	.where (t => !t.completed && t.text.includes(projects[i]))
	myTasks = myTasks.concat(projectTasks)
	}

I’m not totally happy with this yet - it feels a little bit imprecise, but it does the job. And basically means I can group my tasks using roll ups.

10

Something unexpected occurred, when I move Project A to the Project folder, and adjust the query accordingly, it stops rendering results

```dataview 
TABLE WITHOUT ID P as Project, rows.G as Goals, rows.rows.ACT as Activities, rows.rows.ACT.status as Status FROM "10 Projects/Project A" FLATTEN filter(file.inlinks, (i) => filter(i.file.inlinks, (ii) => ii.goal)).file.inlinks AS ACT FLATTEN ACT WHERE ACT.goal GROUP BY ACT.goal as G GROUP BY G.project as P
11

Well, based in a simple move of the “Project” note from one folder to another, and changing the source (i.e., FROM "folder-path-to where-you-move-you-project-file), then everything should work normally.
Maybe a “cache” issue. To that, restart Obsidian.
If the issue remains, occurs me only two things:
1 - you placed the metadata fields in the frontmatter instead of inline fields;
2 - or in Settings > File & Links you disabled “Automatically update internal links”.
The structure was thought based in backlinks that automatically refresh if any change of the path (a move) or of the name.
Besides that, only if any issue with the Obsidian or Dataview version. What are your versions?

12

actually, it was a cache issue. Today I had some odd behaviours from Obsidian, like notes not saving their content. Luckily, everything is solved now

13

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.