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?
Populate it with 365 entities, each representing one day of the year.
Initial Population Script:
Develop and run a one-time script to fill the ‘YearDays’ database with date entities for an entire year.
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’.
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.
Make sure to save it as the Date data type (not Date time).
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:
Your solution is very well thought out, and very efficient, thank you!
In my case, the Event Date field is of type Date Time.
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:
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.
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.
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.
I’m trying to build a week based board: Mon–Sun columns, tasks stacked by priority, drag between days to update the date, drag up/down to reprioritise. No time element.
Calendars fundamentally seem to be built around time and boards around states. I can’t actually find a piece of software that properly covers the middle.
Your solution Chr1sG covers 90% of what I’m looking for. While missing the ability to drag and drop I’ve worked around this by showing the date field and clicking that to change columns, and the inability to change week views. In my case I really only need +1/-1 weeks so that won’t be too hard to build views for.
Periods technically cover it, but feels like a different feature and generate 400+ daily/hourly syncs.
Alternatively: could Calendar simply have a date-only week and day view with no time grid
I would use Periods for something like this. You can set the sync frequency to daily, then it just syncs once a day.
If you don’t include the “Relative” field, and set it up with no semantic naming, it not use many entity syncs. If this doesn’t work, please share your set up and what’s happening
I’m not a fan of needing 400 entities × 28 days = 11,200 monthly syncs just to use the Periods feature. Even if only half of them sync each day, that still exceeds the free-tier limit for what is effectively a workaround.
I tried the template you linked. The drag-and-drop scheduling is useful, but the automation rules created another issue. For example, if a task is moved two weeks into the future (outside the next-7 days view) the “Set Date from Relation” and “Set Date Relation from Date” automations can fight each other and the result is both fields becoming empty.
That is probably a separate issue, but ultimately the template solves the drag-and-drop problem by introducing other limitations.
You should be able to set it up in way where it only uses 1-5 syncs per day. If you turn off semantic dates and the “relative” field it will only sync the new day every day, not all entities in the db (since they dont need to be changed, entity syncs only count if they edit/create any entities in the database) . Then you can filter in the view itself for “Date within next 2 weeks”.
(Sidenote, @Chr1sG it might be nice to include a “Current?” checkbox which respects the selected timezone in the periods integration for the sake of reducing entity syncs, Tarn has a point, and as time progresses, I can see how the “Relative” field will trigger lots of unnecessary syncs and start eating into people’s usage. This is less relevant here, since the “Is Today” in the view filters respects the user’s timezone. This is more relevant for formulas or automations.)
Good catch, just edited the template, should be fixed now. (Added filters to only run when the fields are not empty)
I’m not sure what you mean, but the integration does intelligently optimise to reduce syncs.
For example, it works out, based on the selected timezone, whether any of the items ‘semantics’ or ‘relative’ need to change, and if not, no records are updated (= no sync).
In such a case, it would only update if you have not selected a static start year (meaning start = current year) when it would update early on 1st January, to sync another year’s worth of data.
If you have static start and range, and don’t use ‘semantics’ or ‘relative’ then it should actually never need to sync again after the first one (and you even could disconnect).
Even if you have them all turned on, syncing every hour will just mean that records get updated once a day (the first sync after midnight).
So … if you can make do with UTC, then you can set up a reliable system with no syncs (after the first one) just by making use of formulas (comparing the dates in the Periods to Today()).
Alternatively, if local time is needed, you could set up a scheduled automation that runs at midnight local time, and does effectively the same thing.
Currently the only way to know if something is Today (in the selected timezone) is to use the “Relative” field. The problem with the relative field is that it updates all entities in the database, so if you have 1000 synced periods, every day it will use 1000 sync credits.
If instead you use a checkbox, it will only sync to uncheck yesterday and check today, instead of updating all entities “Relative” field by 1.
Unless I understand it wrong, in which case please let me know.
Ah, so you mean a checkbox which is only ticked for a single Day Period (and I suppose a single Week, Month, Quarter, Year, etc.)
And so a sync will only affect a pair of entities (one being ticked and another being unticked) in each case.
Not a bad idea.
But for the time being, I would say that it’s basically not much different to running a scheduled automation (set to run at midnight in local time) to tick/untick a checkbox.
And with automations, users can choose to set up any rules to match their needs, e.g. ‘this working week’
Anyway, if there’s a lot of demand for this specific need, I might consider adding it.
I think this would be quite complex to be set up reliably. Wouldn’t you’d need to convert the Today() check to a different timezone by hand? So add or subtract the timezone difference in the today check, then also with Daylight Savings it would also break, no? The tricky thing is know which checkbox to tick and untick across all period types. If you can share a template on this that would be great.
I don’t think this is a need people will ask for specifically, it’s mainly a solution if people complain about the high entity sync credits the periods integrations uses. Maybe even a setting in the integration to update “Relative” from -7 to +7, and the rest is empty. This way it doesn’t update everything, and is backwards compatible with any views people have set up. Although still less intuitive to understand than a “Current” checkbox. Thinking out loud.
Nope. If the automation is scheduled to run at a specific time, nothing complicated needed I hope.
If you choose the right time of day, then yes, the value of the checkbox might be wrong for ±30 mins but hopefully no-one’s working at those times of day to notice.
The easy option is an automation with two filters: one to identify the entity which needs ticking, and one to identify any currently ticked.
First action: untick them both
Second action: tick the one which is ‘current’
Sorry. I haven’t got time this week to experiment, but I think you have the skills, no?
If we hear complaints, we’ll think about it, and gather exact use cases people have.
I wouldn’t want to implement a solution which is too narrow/specific
