Two dates in one metadata key?

I store films in Obsidian: one note for each film.

I query the films using Dataview, with a default sort based on a DateWatched key:

**DateWatched**:: [[2022-12-09-Friday]]

It works perfectly. However, if I watch the film again and add a second date:

**DateWatched**:: [[2022-12-26-Monday]], [[2022-12-09-Friday]]

… the film disappears from the Dataview query completely.

I assume this is because Dataview can only query one date in a date field and that I just need to restructure the metadata used.

Or is there a way to have two or more dates in the metadata key and to get Dataview to query only one (the most recent) of the dates or to list all of the dates?

But you don’t show us any query!
We can’t guess the used commands/functions.

You can use multiple values in fields, but you need to use the right functions in the query. For example, in type = "value 1" equal means equal, not part of… So, if in type you use multiple values, then you need to use another way, because you have a list of values and need to check if it contains one of the values: for example WHERE contains(type, "value 1").

Another question is related with dates! In your example you have links, not dates!

1 Like

Thank you!

It’s a date that I use to link to my journal entry.

The query is:

```dataview
TABLE WITHOUT ID
	("![|100](" + FilmPoster + ")") as Film,
	FilmTitle AS Title,
	IMDBlink AS IMDb,
	DateWatched As When
FROM 
	"films"
WHERE 
	Status != "NYW"
SORT 
	DateWatched 
	DESC
LIMIT 
	50
```

Even after reading the help pages I am still at a loss to know what to do.

Can you send me one or two example notes to test more (because it works for me)?

You don’t use any filter to DateWatched. So, if a list, it should shows a list:

About showing the most recent date… well, your “date” links exist, i.e., are they real files? If yes, it’s more easy…

1 Like

Thank you for the tips. I have had a look at the files and the query, and I think I understand what is happening but not how to fix it.

With a few files, it all works fine in terms of being able to see both dates in the query—exactly as in your screenshot—and all the files listed.

I said in the first post that with two dates the film disappears from the query. I removed the LIMIT 50 and when the query loaded all 898 films, I saw the one with the two DateWatched values listed at the end of the query. I think this means that the DateWatched for that file isn’t being sorted with the films that have only one DateWatched value. If I add a second date to a DateWatched value for any other film, the film immediately drops to the bottom of the query table.

And, yes, all the DateWatched values point to files that exist in my journal. I watch a film, write it down in my journal, and then create a new file for the film.

You can see in the sample files attached that the DateWatched value isn’t calculated for ‘Glass Onion’ with its two values.

Thanks in advance for any tips.

_inbox.zip (6.6 KB)

It seems likely that sorting on dates vs an array of dates will get you into some issues. You might resolve some of them by doing a min(DateWatched) or max(DateWatched) (untested, just a theory presentation) to select which of the dates you want for your sorting.

1 Like

Thank you, BOTH, so very much. This works:

SORT 
	max(DateWatched) 
	DESC

I must go back to the support documents and learn more.

:clap: :clapper: :clap:

To start, related to the code itself, you don’t need
choice(typeof(DateWatched) = "array", DateWatched, list(DateWatched)) AS When
just
DateWatched AS When.
But I guess you use that expression for “style” purposes (to see all values as a list… I’m using Minimal, I don’t have that problem, because there’s no “-” in dataview tables).

About the sorting thing, @holroy is right about the max() or min(). (We don’t know if, when multiple dates, you want to prioritize the last or the first.)

But the point is: you can’t do that directly with the links! Links aren’t dates:

  • If you ask to max() or min() of links, you get an answer related with alphabetic order;
  • if you ask to sort a link, you get a sorting related with alphabetic order of the file path (link is a path)

So, you need to play with dates. To do that, if the “date” files exist, then you need to go through the link and target an implicit date value in that link. Using, for example, file.day.

max(DateWatched.file.day)

not max(DateWatched).

1 Like

By the way, if you’d like to see in chronological correct order, you could also flatten the DateWatched so that those movies with multiple dates are shown multiple places in your table.

You would then do something like:

flatten DateWatched as When
sort When desc

(I’m a bit lazy today, so play around with where to place the flatten:slight_smile: But in theory this should also work, and then you wouldn’t need the max())

1 Like

What that guy says! HeHe… :smile:

2 Likes

Takker og bukker, holroy, and muito obrigado, mnvwvnm.

I read the help pages for max and min and for file.day and now understand what is happening with the query, although I would never have managed to put the jigsaw parts together without the kindness of you two sharing your time and knowledge. Very grateful.

Yes, the choice(typeof(DateWatched) line is used so that bullet points are used consistently. I run Obsidian with the default theme, just to keep things as simple as possible for me.

Thanks for the neat tip about the option to use Flatten—that will come in very useful if I do want to see the dates separately.

Feliz Ano Novo.

Godt Nytt År.

2 Likes

Happy new year (if this is your native language) :slight_smile:

1 Like

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