Help creating a master calendar for a base?

[Madeleine_Ghill]

Hi there. I’m trying to build a base that summarizes my company’s social media, email, blog, and event activity and puts it all in a shared calendar. Google calendar on steroids, if you will.

I currently have sheets for social, email, blog, and events. Each of them have their own calendar views to organize things by date. What’s the best way to link up all of these calendars so that you can see everything at once? I’d like to get this to the point where users can filter to see any combination of the four categories.

I don’t want to put everything in the same sheet, because each category is so complex and has a bunch of workflows within its tab. Any suggestions on how to make a big, aggregate calendar?

Thank you!

[HammerOz]

Hmm… what about one table for calendar. Calendsr items might be linked to a content table which is in turn linked to multiple items in a workflow table.

So for example you add a new item to your to your calendar with a link to blog content type. You would click the blog linked field to open the related blog record which in turn shows all the workflow entries linked to blog content.

This is just an example . There is a lot I dont know about your project.

At least with this option you get a nice combined calendar. You could use the workflow table and group it by content type or calendar item for more detailed viewing. Calendars are a bit overated anyway. Try grouping by due date .

[R_Felciano_QIAG]

Any updates or responses on this? This sounds like a key use case for AirTable:

1. Create tables for each type of marketing effort (events, webinars, blog posts, etc), including specialized columns based on the workflow for each effort.
2. Be able to view these as individual calendars (e.g. show me a calendar of our events, show me a calendar of our blog posts, etc)
3. Be able to view these on a single integrated calendars (e.g. show me a calendar of all our marketing activities, with events in Blue, blog posts in Red, etc)

…but it doesn’t appear to be possible to do that last step?

In theory you could do the last step outside of AirTable using iCal and showing/hiding the type-specific iCal calendars in Google Calendar, but those don’t seem to be being updated correctly (see iCal is not updated in Google Calendar).

Thanks,

Ramon

[W_Vann_Hall]

Take a look at this demo base, as I think it satisfies your needs. (Actually, I didn’t bother creating your Item 2 calendars, since if Item 3 is supported, achieving Item 2 is trivial.)

Airtable

📆 Calendar test - Airtable

Explore the "📆 Calendar test" base on Airtable.

There are two caveats here: First, each marketing record must be explicitly linked to a record in the [Marketing Calendar] table. To do so is extremely simple (well, at least when working from a Grid view): The two rightmost columns in every effort record, regardless the type, are a Formula field, {Key}, and a Linked Records field, {Link to Calendar}. When the user has created a new effort record, after completing whatever data entry is required, he or she

1. selects the {Key} field
2. presses Ctrl-C to copy the key value
3. right-arrows into or selects the {Link to Calendar} field
   and
4. presses Ctrl-V to paste the key value into the linked record field.

Airtable automatically creates a new record within [Marketing Calendar] and populates the appropriate Date fields. Yes, it’s an extra manual step — but it seemed to me the additional 4 (or was it 3?) keystrokes required was more than repaid by the additional functionality it offers.

Besides, it doesn’t even have to be an additional manual step: the process can easily be implemented as a two-step Zapier Zap, which means it can operate (with some limitations) from a free Zapier account. (For those interested in doing so, the [Document] table of my Wardrobe Manager base contains something called the Wardrobe Manager Zapier Guide that offers step-by-step instructions for defining a nearly identical Zap.)

The other caveat is that some of the functionality I’m using — including multi-day spans and color coding — are Pro features.

Let me know if I’ve missed something, and I’ll see if I can shoe-horn it in, somehow. :winking_face:

[R_Felciano_QIAG]

Thanks @W_Vann_Hall this looks promising. If I understand the design it is basically:

• Create and maintain events in their own type-specific tables, adopting a design pattern of the key + link columns
• Create a single central calendar for the integrated calendar, using formula fields to dynamically bring in the data fields from the “master” tables
• Enforce some minimal user behavior (or Zapier) to maintain the links

Is that right?

A few additional questions:

1. Your key-construction logic introduces a brief tag prefix (e.g. W18, B18, etc). Does that serve a function other than visually identifying keys by year and marketing event type?
2. Is there a particular reason why you maintained independent date fields in the Calendar table (Event Start, Event End, Webinar Date, Blog Date)? I understand why these are named differently in the source/master tables, but once aggregated shouldn’t these just all get collected into a single Event Date column (and Event End Date column where appropriate)? Or is there a functional reason to keep them segregated in the Calendar table?
3. Similarly I notice that the Calendar table has distinct link columns for Blog, Webinar, etc. Could this not simply be a single “related record” table with a general Link type? Or must Link-valued columns be associated with only a single table type?

Thanks again for a very nice example showcasing AirTable’s capabilities.

Ramon

[W_Vann_Hall]

(I should say this is simply one way to implement the system; you may find other, better ways. This one is simply the one I know. :slightly_smiling_face: )

Your three bullets do a better job of encapsulating my base than I did. I may go with mandatory links more often than I should, but typically they seem awfully low-impact to create in comparison with the functionality they enable. And given how Airtable so obligingly creates a new linked record when goosed by that copy/paste into a link field, it’s hard not to make use of them.

As far as your other questions go…

1. The prefix is actually a failed attempt; I probably should have embedded a datestring in there as well. The reason is that the [Marketing Calendar] table really needs unique ‘keys’ (that is, the primary field, which isn’t truly a key); otherwise, when you copy/paste a duplicate {Key} value into the link field, you’ll end up linking both effort records to the same calendar record, which will screw up your Calendar view. (I’d meant to highlight this in my original reply.) Feel free to use whatever mechanism you prefer that will help guarantee unique primary fields.

2. (And 3, as well.) The distinct link and date fields that bubble up through the [Marketing Calendar] table are required by Airtable. A link field links to one and only one type of record (and, accordingly, one table); while that would seem to imply multiple tables could link to a single ‘landing site’ in the target table, Airtable linked records fields are bidirectional, so the return loop could not originate from a single field but travel to multiple destinations.

   You also ask why the multiple date types aren’t collected into a single date field — which they are. I pull all start dates into {Start} and the end date for the user group meeting into {End}; for events with only a start date, that date is duplicated into the {End} field, as well. This allows me simply to use {Start} and {End} to define the ends of my date range when configuring the Calendar view — and, even better, the system doesn’t choke if both dates are the same.

Edit: Just noticed I didn’t consolidate blogs, webinars, and events into a single {Efforts} field in [Marketing Calendar], the way I did in folding the various start and end dates into {Start} and {End}. That certainly could be done…

Glad to be of assistance. I essentially use the Support forum as an Airtable study guide, so I’m always glad to have an excuse to learn something new…

[Tracy_Dendy]

This calendar solution is exactly what I was looking for, however I’m not able to pull in the consolidated {Start} dates into my calendar view because it only wants to accept actual date fields, not formulas. Your sample table is not having this issue - is it something about the way the date field is formatted?

[Matt_Bush]

Calendar view is designed to work with date formulas. If your date formula isn’t showing up when setting up a calendar, you may have applied formatting to your formula that makes it output text rather than a date.

For instance, using DATETIME_FORMAT() as the outermost formula function will output text and makes your formula field ineligible for use in a calendar view. As an alternative, remove DATETIME_FORMAT() and instead use the Formatting tab of the formula field configuration to get the desired formatting.

[Tracy_Dendy]

Thanks Matt!
I have not added any formatting functions to my formula, but I am getting a strangely-formatted number which looks like this:
2018-02-02T17:22:00.000Z
Because of this string, the formatting options in the formula field configuration are not available.
My formula is using lookup fields that are pulling in normal dates with a M/D/YYYY format. I tried turning off time fields on all my records, but that didn’t seem to change anything.

Here’s the formula I’m using if that helps:
IF(NOT({Programming Start}=BLANK()),{Programming Start},
IF(NOT({Campaign Start}=BLANK()),{Campaign Start},
IF(NOT({Homepage}=BLANK()),{Homepage})))

[Matt_Bush]

In that case, it’s possible that one or more of the 3 fields you’re referencing from your formula (Programming Start, Campaign Start, Homepage) is not correctly formatted as a date.