Using note boolean properties as a data view column

Help
1

First, search the help docs and this forum. Maybe your question has been answered! The debugging steps can help, too. Still stuck? Delete this line and proceed.

What I’m trying to do

I have a dataview query that pulls a list of notes based on a tag.
Each of those notes have two boolean properties, “started” and “completed”.

Notes whose tags are both not started and not completed should show a value of “pending”
Notes whose tags are started and not completed should show “in progress”
Notes whose tags are started and completed should show “complete”
Notes whose tags are not started but completed should show “invalid”

In SQL this could be accomplished via a case statement.

Things I have tried

Searched the documentation, but could not find anything explicitly similar.
While writing this It occurred to me to google “dataview equivalent to SQL CASE”

and I got this…

Which appears to be what I want but I am not following the syntax.
Can someone help?
Thanks.
Chris.

2

Hi @Aang,

Yes, you can nest choice() functions to operate much like a CASE statement in DQL. It’s a pretty simple function that returns its second or third parameter based on the value of the first.

Here’s an example query that uses choice() to describe the time since a note was modified:

```dataview
TABLE file.mday AS "Modified", Age
FROM "Notes"
FLATTEN choice(file.mday = date(today), "Today", 
        choice(file.mday > date(today) - dur(1 week), "A week", 
        choice(file.mday > date(today) - dur(1 month), "A month",
        choice(file.mday > date(today) - dur(1 year), "A year", "Older"))))
    AS Age
WHERE contains(file.name, "DQL")
SORT file.mday DESC
LIMIT 10
```

Here’s what the output of the query looks like on my vault:

image
image738×359 50.1 KB

Hope this helps. Good luck!

Craig

3

Your response made it more clear.
I will try doing this and let you know.
Thanks.
Chris.

P.S Those notes look really interesting, do you have them published somewhere?

1 Like
4

How would I check the status of a boolean?

completed = true?

5

Yes, you can use completed = true, but it’s simpler to just use completed. For example:

---
completed: True
---

`= choice(this.completed, "Yep", "Nope")`

results in:

image

1 Like
6

Perfect.
Thank you.

1 Like
7

Hi,

I tested the non nested code and it doesn’t appear to work.

table without id
file.link as Filename, file.mtime as modified,file.ctime as created , choice(this.Completed, "complete","active") as status
from #Project and "Storage/Notes"
SORT file.mtime DESC

All of the notes are showing as Active even though I know some have the completed property set to true and some don’t

8

Never mind I removed

I replaced

 choice(this.Completed, "complete","active") as status

With

choice(Completed, "complete","active") as status

And that fixed it.

Thanks again.

1 Like