Creating a 'Current Week Calendar' Event Board with WeekDays as columns

Get Help
1

I tried the following:
An Event has a calculated field ToDays(Date - Today()) which is the difference from Today as number field.
However, in a board view, the formula field cannot be set as columns.

Then I created a database ‘WeekDay’ with the 7 entities named Monday to Sunday, each with a calculated field ‘WeekDayDate’ that displays a Date relative to Today.

In the Event database I created a relation field to the WeekDay database, and an automation that triggers daily at 00:00 to set the Event its WeekDay relation to to the WeekDay which has a WeekDayDate that corresponds with the Event Date.

Can you see a more efficient way to accomplish this?

2

I found a potentially more efficient way:

  1. YearDays Database Creation:
  • Establish a ‘YearDays’ database.
  • Populate it with 365 entities, each representing one day of the year.
  1. Initial Population Script:
  • Develop and run a one-time script to fill the ‘YearDays’ database with date entities for an entire year.
  1. Event Database Update:
  • In the ‘Event’ database, add a relationship field (e.g., ‘YearDate’) linked to ‘YearDays’.
  • Implement a script or automation that updates the ‘YearDate’ field whenever an ‘Event’ date is modified, aligning it with the corresponding date in ‘YearDays’.
  1. Board View with Range Filters:
  • Utilize the ‘YearDate’ field in the ‘Event’ database as columns in a board view.
  • Apply range filters in the board view to display events within specific time frames, like a week or a few days.
3

Create a db with the 7 days of the week, and a formula as follows:

Today() -
  Days(
    If(WeekDayName(Today()) = "Monday", 0,
      If(WeekDayName(Today()) = "Tuesday", 1,
        If(WeekDayName(Today()) = "Wednesday", 2,
          If(WeekDayName(Today()) = "Thursday", 3,
            If(WeekDayName(Today()) = "Friday", 4,
              If(WeekDayName(Today()) = "Saturday", 5, 6)
            )
          )
        )
      )
    )
  ) +
  Days(
    If(Name = "Monday", 0,
      If(Name = "Tuesday", 1,
        If(Name = "Wednesday", 2,
          If(Name = "Thursday", 3,
            If(Name = "Friday", 4,
              If(Name = "Saturday", 5, 6))
          )
        )
      )
    )
  )

Make sure to save it as the Date data type (not Date time).

image

Now you can create an auto-relation to your Event db, with the matching rule using the Date field in the Event db and the Date field in the Day this Week database:

image

No automations necessary :slight_smile:

1 Like
4

Your solution is very well thought out, and very efficient, thank you!

In my case, the Event Date field is of type Date Time. :cry:
I can still use your solution, but need to add an extra new Event Date field type Date, that gets its value through an automation from the Event DateTime field, using the formula:

Date(
  Year([Step 1 Event].DateTime),
  Month([Step 1 Event].DateTime),
  Day([Step 1 Event].DateTime)
)

Drag-drop functionality in board

However, because of the automatic relation between the DayThisWeek database and the Event, the events cannot be dragged dropped in the board view! :smiling_face_with_tear: :smiling_face_with_tear:

So I guess for this scenario we have to resort to the my previous option 2 of a YearDays database.