How to auto-relate notes to journal entries with a formula

My Journal entries database is ‘Y Journals’
My Notes database is ‘Y Notes’, which has a many to one relation to an Y Journal.

My goal is upon creation to automatically relate Y Notes to an existing Y Journal that has the date of Today.

I created an automation in the Y Notes database:
When Y Note created,
Then Updatea Y Journal
Fields Y Journal is a formula:

[Y Journals]
  .Filter([Creation Date] = Today())
  .Sort([Creation Date])
  .First()

However, that formula does not work. Any ideas?

The reason that it does not work is that Creation Date and Today() do not have the same data types.
Creation Date is a datetime, but Today() is a date.
When comparing them, the value of Today() will be typecast to datetime, using midnight as the time (so dd/mm/yyyy will become dd/mm/yyyy 00:00)

In general, you can use
DateTime >= Today() and DateTime < (Today() + Days(1))

but actually in your specific case, [Creation Date] >= Today() will suffice, since there can’t be any items with a creation date after midnight tomorrow(!)

2 Likes