Display open tasks in a Dataview-Table

Hi, I have been trying to solve this on and off for a couple of months.

I want to display all open tasks on my Project management landing page, but I just can’t get it to work.

I would like if it looked like this:


The file structure of a project looks something like this:


In each Project note I am able to display the open tasks from all the daily notes that link to it


I’d like to display the open Tasks, that are “collected” in each Project-Note in the table on the landing page. I don’t want them displayed as task, but as a list like indicated in the first picture.

My Ideas

  • I have mainly been trying things with file.tasks.text, but I think that this query doesn’t recognize the tasks that are displayed in another dataview block. I tested it with tasks written directly in the Project-Note and it worked flawlessly.

  • An alternate way for a solution is that I manage to collect the open tasks on the Project-Note in another way, which doesn’t involve dataview but I have no clue how this would even be possible. I need the tasks to automatically update, when I add them or complete them. The only reason why I don’t add the tasks on my Project-Note directly is because it’s way less clean then if it only shows my open tasks.

  • The final idea that I had was to not try to get the tasks from the Project-Page, but from all the notes that link to it. But yet again I have no Idea how to do this. I assume that this way might necessitate coding, at which I suck.

Any solution or Idea is more then welcome but I can’t change the structure of my workflow. It works great for me and I managed to automate almost everything.

Small update, I got it working that it filters out the completed tasks that are directly written on the project page. (This took me longer than I’d like to admit. There should be a better explanation of how the filter function works imo.) (As far as I understand it, the function file.tasks.text gets assigned a variable (in this case (t)). The => is just to indicate the next step. The contains() function checks every file.tasks.text, via the t, and only keeps the ones without the :white_check_mark:, which then get listed.

```dataview
TABLE WITHOUT ID

file.link as Project, 
filter(file.tasks.text, (t) => contains(t, "✅") = false) as OpenTasks,
Urgency, 
DueDate, 
Responsible + "<br>" +  Department as "Responsibel and Department", 
embed(link(meta(CoverImg).path, "125")) as ""

FROM "Projects"
WHERE file != this.file
Where Completed = False and Limbo = False 
```

I also tried to replace the Task dataview on the project page with a ```tasks listing of the linked tasks, which didn’t work either, as I already suspected.

I have all my daily notes thrown into one Daily note folder and link to them via an inline field so my best option is probably to change the From to include “Daily notes”. Do filter it I can maybe do something like [Project:: [[project name]] = file.outlinks?

I managed to do something, somehow. I used the query file.inlinks.file.tasks.text to display the tasks which are in the Daily notes associated with each project. Now I need exclude the ones that are .checked. Again, no clue how to do this with this query. I always get an error when I try to use where. When I use the contains function it manages to separate the completed and non-completed tasks, but it only returns a Boolean, that is always false… No idea why.

```dataview
TABLE WITHOUT ID

file.link as Project, 
contains(file.inlinks.file.tasks.text, "- [ ]") as "Open Tasks",
Urgency, 
DueDate, 
Responsible + "<br>" +  Department as "Responsibel and Department", 
embed(link(meta(CoverImg).path, "125")) as ""

FROM "Projects"
WHERE file != this.file
Where Completed = False and Limbo = False 
```

I have also tried file.inlinks.file.tasks.text where contains(file.inlinks.file.tasks.text, "- [ ]") as "Open Tasks", but this gives me a parsing error.


Another weird thing that happened is that the task list now looks like this. :smiling_face_with_tear:

image

I changed the line to this and it works now. I just have to activate that dataview adds the emoji when I click on complete task.

filter(file.inlinks.file.tasks.text, (t) => contains(t, "✅") = false) as "Open Tasks",

This problem still persists though :confused:
image

I’ve been a little busy last week, so I’ve not gotten around to giving you some advice on your endeavours, but here is an untested version which I hope gets you close to what you want:

```dataview
TABLE WITHOUT ID
  file.link as Project, OpenTasks.text, Urgency, DueDate,
  Responsible + "<br>" +  Department as "Responsibel and Department", 
  embed(link(meta(CoverImg).path, "125")) as ""
FROM "Projects"
WHERE file != this.file
FLATTEN list(file.inlinks.file.tasks) as AllProjectTasks
FLATTEN list(filter(AllProjectTasks, (task) => !task.completed)) as OpenTasks
```

This utilises a few tricks which I’ve been thinking that you’ve been missing so far:

  1. Using FLATTEN ... as something stores the result of that expression into something ready for use later in the query. Normally though the FLATTEN would expand any list into its separate items, but then we can use the next trick…
  2. Using the combination of FLATTEN list(filter( .... )) as AllProjectTasks, especially with the combination of list(filter( ... )) stores the result of that operation into the variable at the end, which then is ready for user later on in the query
  3. Stuff you do in the TABLE ... line can’t be reused later on in the query. I like to think of queries be sequentially going down line by line, but the “first” line with the column definitions is actually the last lint interpreted. Does that make sense to you?
An example on how the first line is read last

If we’ve got the query below:

```dataview
TABLE a, b, a + b as c, d
FROM "Projects"
FLATTEN a + b as d
WHERE d = 5 OR c = 5
```

I would read it in this order

  • FROM "Projects" - Read only files from “Projects” folder
  • FLATTEN a + b as d - Add together the values a and b found in each file, and store the result in d
  • WHERE d > 5 OR c < 5 - Check if d is larger than 5 (which can be done since we calculated its value in previous line), or if c is less than 5 (which would fail, since c hasn’t been defined yet, due to our correct reading order. It’ll be defined in the “next” line )
  • TABLE a, b, a+b as c, d – And finally, we present the result with one row per file, where we list the a and b values from the files. We add them together, and display them in a column named c, and the last column displays the d value which we calculated in the previous line

So with the exception of the deliberate error of OR c = 5, this is how I would interpret such a query. Line by line, but the “first” line with the TABLE or (LIST <something>) is read at the end.

1 Like

Thanks for your reply. You are under no obligation to respond, so everything is welcome!

I’ll have a look at it as soon as possible, which will be on monday.

I tried your code, and it sadly didn’t work. But, I’ll be trying stuff with flatten, and see where it takes me. I have avoided it so far, because I don’t quite understand it yet, but I’m getting there.

This was the Result of your query:

image

Edit:
I think the problem with the two list points is that the inline and tasks command both output a list.

Ok, I played around with your code, and got this result:

```dataview
TABLE WITHOUT ID
  file.link as Project, list(OpenTasks.text) as "Open Tasks", Urgency, DueDate,
  Responsible + "<br>" +  Department as "Responsibel and Department", 
  embed(link(meta(CoverImg).path, "125")) as ""
FROM "Projects"
WHERE file != this.file
Where Completed = False and Limbo = False 

FLATTEN file.inlinks.file.tasks as AllProjectTasks
FLATTEN filter(AllProjectTasks, (task) => !task.completed) as OpenTasks
```

It is pretty close, but if I have more then one open task in a project it displays the project multiple times. I presume this comes form the flatten.
Something like this:

Project 1| Task 1
Project 1| Task 2
Project 1| Task 3
Project 2| Task 1
Project 3| Task 1

I played around with group by, but don’t get it to work. This was my best attempt, (I think):

```dataview
TABLE WITHOUT ID
  file.link as Project, rows.list(OpenTasks.text) as "Open Tasks", Urgency, DueDate,
  Responsible + "<br>" +  Department as "Responsibel and Department", 
  embed(link(meta(CoverImg).path, "125")) as ""
FROM "Projects"
WHERE file != this.file
WHERE Completed = False and Limbo = False 

FLATTEN file.inlinks.file.tasks as AllProjectTasks
FLATTEN filter(AllProjectTasks, (task) => !task.completed) as OpenTasks
GROUP BY Project
```

I use rows. on the open Tasks to bundle them and Group by Project, which is a field in my Daily notes (Project:: xxxxxx). This seems the logical answer in Order for Group By to work…

I get the error: - Cannot call type ‘array’ as a function

Is the problem that I try to use the rows on a list?
(I also tried to add rows. to all other things, like Urgency, DueDate, etc. with the same result.

I played around with group by and ended up hat this point. Is there a way to use the flat function to get rid of all the duplicates?

```dataview
TABLE WITHOUT ID
rows.file.link as Project, 
rows.OpenTasks.text as "Open Tasks", 
rows.Urgency as Urgency, 
rows.DueDate as Date,
rows.Responsible + "<br>" + rows.Department as "Responsibel and Department"

FROM "Projects"
WHERE file != this.file
WHERE Completed = False and Limbo = False 

FLATTEN file.inlinks.file.tasks as AllProjectTasks
FLATTEN filter(AllProjectTasks, (task) => !task.completed) as OpenTasks

group by file.link
```

I also don’t get the embedded images to work, which I need.

Furthermore, I don’t understand why everything is displayed as a list now, except for “Responsible and Department”.

tl;dr: The tasks work now but everything else broke.

I found this post by you @holroy:

I tried the nonnull and it worked! No idea why, but I’ll take it. I guess it returns the array but only the value from on position 0?

Now I just need to get the embed to work.

```dataview
TABLE WITHOUT ID
nonnull(rows.file.link)[0] as Project, 
rows.OpenTasks.text as "Open Tasks", 
nonnull(rows.Urgency)[0] as Urgency, 
nonnull(rows.DueDate)[0] as Date,
nonnull(rows.Responsible)[0] + "<br>" + nonnull(rows.Department)[0] as "Responsibel and Department"

FROM "Projects"
WHERE file != this.file
WHERE Completed = False and Limbo = False 

FLATTEN file.inlinks.file.tasks as AllProjectTasks
FLATTEN filter(AllProjectTasks, (task) => !task.completed) as OpenTasks

group by file.link
```

Edit: I have noticed that the project disappears if there are no more open tasks, which should not happen (I think) because of my WHERE Completed = False and Limbo = False. As long as none of these are ticked in properties the project should be displayed in the dataview table. Am I wrong in this assumption?

It’s hard to tell what’s happening in your case, since it’s hard to test a query without a proper test set, so it could fail in a variety of ways. However given that each file is one project I don’t think that using GROUP BY is the correct way forward.

I’d rather experiment with the definition of OpenTasks to see if that isn’t more fruitful.

```dataview
TABLE  
OpenTasks.text as "Open Tasks", 
Urgency as Urgency, 
DueDate as Date,
Responsible + "<br>" + Department as "Responsible and Department"

FROM "Projects"

WHERE file != this.file
WHERE Completed = False and Limbo = False 

FLATTEN list(flat(file.inlinks.file.tasks)) as AllProjectTasks
FLATTEN list(filter(AllProjectTasks, (task) => !task.completed)) as OpenTasks
```

Hopefully this should work, and bring you back closer to your original query. Some explanations are in order.

The default function of FLATTEN is to split up any list values. This would mean that given two files, where each had three inlinks, and each of those inlinks had four tasks, after doing FLATTEN file.inlinks as inlink you’d end up with 23 rows in your table, and if you did FLATTEN file.inlinks.file.tasks you’d end up with 23*4 rows in the table.

To counter this behavior we can add list( ... ) around the expression to explicitly state that we want a list as output. However, when doing that on something which already is a list, it’ll end being a list of lists, which is not wanted. Confused? Understandable… The solution to this part is to use flat( ... ) to reduce the multi level list into a single level list. This will also (hopefully) deal with the issue of any given inlink is one level, and the tasks under that file is another level.

So therefore we use list(flat( ... )) to first remove any multi-level issues from the file.inlinks.file.tasks beast, and then we use list to store it as a list into AllProjectTasks. In the next FLATTEN we use list(filter(...)) to store the filtered lists of open tasks into OpenTasks. Finally for display purposes we use OpenTasks.text to extract just the text part of the compound object that a task is.

If the query above works as expected, then it should be relative easy to do the embed given proper link format for the image field. Hopefully…

You are the GOAT. A true dataview wizzard.

It works like a charm now. As far as I can see, you just added flat( ) for it to work. I had some fun experimenting and was happy how close I got.

Thank you so much!

1 Like

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