Calendar Setup options, possibilites, frustrations and questions

[Brendan_Woithe]

Hey all,
I’m trying to figure out a way to show projects and upcoming bookings in a clear way for our small business - potentially using the calendar.
Unfortunately, Gantt chart displays of our projects are not working well since we cannot zoom into just this week…
Either, we see a zoomed out view (some projects go for months) or if I make a filter to zoom in, it excludes all the longer project from the view.
So - this is what it looks like without filtering - its not great, especially for the short turn around projects.

I then tried mucking around with calendars again, and the multi-day display. Which on week view works great. Except, you are left with the empty calendar underneath. Even setting the work hours to the minimum possible (which is 1 hour - from say 9am to 10am - its arbitary) means you loose screen real-estate and need to scroll down to see we have more info underneath…

Indeed - underneath we have a second calendar showing bookings.

As far as I know, there is no way of showing two tables on one calendar, although in this case that would be excellent. Projects at the top as all day events and bookings underneath.

All advice on the forum has been to change the data structure, which is not appropriate in this case given the one to many nature of bookings to projects, and then the other data structures around project deliverables, tasks, budget items, deliveries, invoices etc.

Is there perhaps a way to show JUST the all day events section of the calendar. That way I could just have another (differnet) table view underneath showing just the bookings. This would at least give me something semi-usable.

Has anyone got any other possible thoughts?

BTW : the bookings part of the calendar looks something like this :

You’ll probably notice the attempts of us to put more info into the cals with icons etc - but that too is a massive pain point right now for bookings! Especially when more than one studio is visable and the bookings get extremely thin in the display.

(Two other questions : 1. Is there any way to get the week scroll icons - the “<” and “>” to stay in the same position? At the moment they move which makes moving through a calendar extremely laborious! 2. Can we please have the DAY printed at the top of a weekly calendar - and along with that, the ability to start a week on a monday? I’ve searched threads and help, and I don’t think any of these are possible, but its always worth a try!)

Right now I’m thinking I’ll try use g-cal sync. But I don’t think this is fast enough - it needs to be real time sync, and I’ll need to sync from 2 tables to gcal, and then from gcal back to a single table for a single coda calendar. I can’t actually see it working, but you never know.

Any thoughts / ideas / other options for displaying the data muchly appreciated!

[Brendan_Woithe]

So - had one small win!
It doesn’t help with the calendar views, but it does help with the Gantt chart view.
I have created two new date columns - project start zoom and project end zoom.
Essentially what each of these do is check to see if the start date is this week, and if so, leaves it. If it is before this week, it just sets the start to the monday.

And the same for end date - if its during this week, it leaves it (adding one day just to allow for the quirks of the way gantt charts show calendar events), and if it finishes later, it sets the end day to saturday (so it shows up as being a project that is still in progress on friday!)

And it ends up looking like this

The final 4 columns of data looks like this (just a sample - but you can see the rounding taking place)

And the formulas for start date zoom

if(thisRow.[Project Start].DateTimeTruncate(“week”)=today().DateTimeTruncate(“week”),thisRow.[Project Start],today().DateTimeTruncate(“week”)+1)

and end date zoom

if(thisRow.[Project End].DateTimeTruncate(“week”)=today().DateTimeTruncate(“week”),thisRow.[Project End]+1,today().DateTimeTruncate(“week”)+6)

These formulas rely on the table already having been filtered to include only projects that occur THIS week. Otherwise, it ends up showing a bunch of very incorrect projects as being on this week. I’m comfortable with that, although I’m sure there’s many here who would rather see the formula include a check for this pre-filter.

Hope this helps others - I’ve seen other similar questions around the place.

Indeed - I think one could extend this so you have an interactive filter for dates on a gantt table, and using a formula, one could potentially get something working quite well. It feels a little complicated - as my formulas rely on a very specific function of datetimetruncate() - but I’m figuring there are other options… if I get some free time I might give it a go.

[Andrew_Milne]

I have similar issues, so following.

Quick question: if the “bounce through GCal” solution to part of your problem (which I have thought about for my own issues) is a possibility, couldn’t you do a unified calendar table that does a look-up to your other tables? Doesn’t solve the formatting issues tho.

[Brendan_Woithe]

Regarding doing a unified calendar table that does a look-up to two other tables…
AFAIK thats not the way coda works. There’s no way for a table to pull in data from two different tables without user intervention or without using an automation that I know about. I have an idea for doing it with automation / buttons - but I’m not entirely sure the end result will be what I really want. Maybe. I can also see it taking more time than I have right now ;(

Any “lookup” column needs the user to select the row from the table that you want to bring in, or have it selected via an action.
OR you use a table view - which is the data from one table only.

I totally understand it from a database schema perspective. While some online systems have figured out ways around this, it really makes a tonne of sense from a data-management point of view.

As for the bounce through GCal - this is doable. But I can also foresee issues with things not being updated in realtime etc.

Thankfully, the view I have right now using a Gantt chart is pretty useful for us - and I can combine it with a calendar view underneath for details about each day for bookings.

[Brendan_Woithe]

This is how its ended up looking with my bookings underneath the projects in progress chart.

[Andrew_Milne]

Okay, this is pretty terrible, and to scale you would need to either (a) have a way to intelligently add the right number of rows or (b) have intelligent error handling and just have a unified table with way more rows that are needed. And yes, this should be easier!

edit: have added a button column to make sure that all the info shows up on the unified table, and changed formulas to make sure that adding/deleting rows doesn’t mess up the numbers. I think it is actually pretty bullet proof now, but someone will prove me wrong!

[BenLee]

Hi @Brendan_Woithe,

This is a great workaround for this problem. Thank you for posting it. With the added columns you can choose which ones to use on which views too. So maybe you only need them for the Gantt view and the calendar can still show the full items.

And yes, we do need to put some work into the Gantt chart options so these workarounds aren’t required. That’ll take time and we have been logging a lot of notes on this, so the team knows for sure. That’ll take time though. Til then, that’s a great solution here.

[Andrew_Milne]

@BenLee, is the “table unifying” example above a good idea? I am concerned that (a) I am making it harder than needed and (b) it is doing the kind of things that will slow things down a lot when scaled (lots of column formulas…).

[BenLee]

Can you change permissions on the doc. It says “Request Access” at the moment.

[Andrew_Milne]

@BenLee Is that right now? I thought I had followed the embedding instructions I got last time…

[BenLee]

I think a more simplistic approach is to add a button to the individual tables that would add a row to the “Unified” table. Then through a checkbox on the row or a filter comparison, you could disable the button after it’s added.

I haven’t seen a need to split up an already unified table because filtered views can be used. The most popular use-case here is usually taking several separate tables and wanting to add them to one big table.

You’re right that this adds some weight to a doc and to the data you’re having to juggle in general.

For me, I’ve used the approach of two button actions where I add a row to a main table, then delete that row from the individual table. This removes the duplicate values and makes sure that all edits from that point on are done in the main table.

You can add two actions to a single button using RunActions(), so this generally feels pretty lightweight.

RunActions(
  AddRow(),
  ModifyRow()
)

[Andrew_Milne]

@BenLee, this isn’t splitting up a unified table, it is unifying two split tables. Why are there two split tables? Because the original poster said their situation demanded it. I have situations myself, where you want to pull common columns from very different tables and stick them together in a single table for a particular purpose.

I agree that an “add row” for each original table is cleaner. But then you have to make sure users aren’t adding a row through other means (I know you can hide the add row +), and you have to make more changes in more places. My thought process was that you fix it at the place you want to view the unified table, but… meh. Just my thoughts.

[Quentin_Morel]

Hi mate !

Little suggestion. Wouldnt it be possible to set dynamic working hours for calendar display, in daily view ?

i’d like to restrain the display for example, to Now()- 1 hour, to avoid to see what’s in the past today For now the 2 parameters “Working hours” are frozen (I mean we cant modify it dynamically by formula)

Cheers

Quentin

[Natalie_Dang]

I second the second point made. It would be awesome to have a weekly calendar view without the time slots!