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.

5

Have you been able to set a relative filter to display year to date?
I’ve been tinkering on this lately in hopes that I can avoid having filter to a specific day so I can use views/reports over multiple years without manually adjusting.

6

I dropped the effort, but I still have the databases so I can help figure it out, if you give a bit more context of what you want, your use case.

1 Like
7

I am translating dashboards/reporting views over to Fibery. For KPIs or reporting I often have a year to date view filled with rows of data for just this year. (Ex: Transactions for this year. Invoices for this year).
I know that I can insert an exact date, but that would mean that I need to either A) duplicate the view and create a new one each year (which would break URLs linked in other apps/bookmarks) or B) manually adjust each view at the start of a new year. If there is a way to set the view filters to a dynamic value that only shows the current year, I haven’t found it yet.

8

Not currently, and the workaround is to add a formula (e.g. a checkbox called This Year) and use this in the filter, but I can imagine that’s a ballache if you wanna do it for multiple dbs and/or multiple filter settings.

Formulas in filters will happen some day though…

1 Like
9

This year?

It’s the #1 feature request for me, I’d vote for it twice if I could. :stuck_out_tongue: