Project Management: Using Calendar with multiple checkpoints per project

[Meghan_Doherty]

Hi Folks,
I’m a project manager with about 30 clients. At any time, each client has one live project, and then it gets archived and we log the next one. So I only have to handle 30 projects at any time.

So, I’m trying to use AirTable to export to Outlook as well as to track the projects in detail. I have a base setup with a Clients sheet / tab as the master, so that each Client can eventually accept multiple (sequential) Research Projects. I have that Clients sheet linked Research Projects sheet / tab with the key elements of a Start Date, Draft Date, and Delivery Date. (Reasoning behind this: Start to Draft = my research and writing stage; Draft to Delivery = my editing and checking stage). I am using those three dates in each Research Projects record to keep things simple. What’s my best way to set up a calendar or timeline view (or set up filters?) so that I can see the projects and track them at each stage? So I need to be able to see color code or other designation for Start → Draft, then another color for Draft → Delivery.

If you can solve this, then I’ll ask the bigger question, which is how to handle the survey tracker (4 linked sheets to track surveys across each stage (design, program, administration, and analysis). Each of those stages has Start, Draft, and Delivery. The surveys are just a specific type of Research Project above with 4 sub-projects, basically, so I’d love to figure out how to handle the date ranges in each of those stages as well. I haven’t done any automations here, but perhaps that’s how to handle it?

Looking forward to hearing from any of you. Thank you in advance.

[Adam_C]

Hi Meghan, I think I may have something simple for tracking the stages, where the current stage is calculated from the Start, Draft and Delivery dates, and then grouped after. You can find it set up here

The formula for that field is:

IF(
  AND(
    IS_AFTER(TODAY(), {Start Date}),
    IS_BEFORE(TODAY(), {Draft Date})
  ),
  "1. Research and Writing",
  IF(
    AND(
      IS_AFTER(TODAY(), {Draft Date}),
      IS_BEFORE(TODAY(), {Delivery Date})
    ),
    "2. Editing and Checking",
    "3. Pending"
  )
)

I thought it would be best if you used a Gantt chart as well, but your workflow involves having the Start, Draft and Delivery dates in the same record, whereas the Gantt view expects to have a single “Start” and “End” date

To solve that, I did the following:

1. Created a “Gantt” table
2. Created an automation that, on the creation of a record in the “Research Projects” table would create two new records in the “Gantt” table
3. Set up the fields in the Gantt table to generate the Start and End dates as needed, which resulted in the following:

For the setup details of step 2 and 3, do check out the example base linked above; you should be able to duplicate it and view the automation and the field formulas I used!

---

I’m finding it hard to wrap my head around the survey tracker bit though. Any chance you could create a base with an example set up so I could see what I can come up with?

[Meghan_Doherty]

Hi Adam,

Wow, thank you. That’s super useful and I managed to get all that implemented. I have to play with the Gantt features a little more, and one of the reasons I didn’t bring that up initially on this forum was because of exactly what you noted – that I use a single record with multiple milestone dates rather than multiple records with simpler start/end dates.

Ok, the survey. Here’s an example. Now, like I said, this is just one kind of project, so ideally, I’d like to be able to handle this through the Research Projects table in an integrated view or views, but I’m not sure I can. There’s a ton of detail that I’m not addressing here that I use the survey tables to track (like contacts, contract values, testing confirmations, checklist items, email, and so on), so that’s one of the challenges: the dates are just the thing I need to be able to track cleanly and export to Outlook.

So, an example: I get a request for an employer needs survey from a client. We scope the project and determine costs. Survey Scoping is currently a table with records that link to a Client table record. I create a record in Research Projects that links to the client, and then I enter it as a Survey (which I’m realizing maybe I should use as a single select (“Is Survey?”) and make that simpler. Once logged as a project, in the next phase, Survey Administration, we program, test, and administer the survey. So, that Survey Administration table also links back to the Scoping record, and it contains Programming Start and Programming Draft dates, Testing Start and Testing Draft, Administration Start and Administration Draft Dates, and Planned Launch and Planned Close dates. And a whole bunch of other stuff we’re not going to touch here for the moment. So, the employer needs survey goes through all these hoops, hopefully we get good data, so we go to analysis. Then, the final survey table is the Survey Analysis table, and that contains the linked record back to the Administration record, and then dates for Analysis Start, Analysis Draft, Analysis Internal Due Date, and Client Delivery Date. So, that’s a full description of what I’m trying to capture. It’s not so big (yet!) that I can’t go back and reformat tables differently if I’ve not gotten it right, but that’s the goal here: to track surveys as one particularly intricate type of project, and hopefully, to keep it all integrated in one or a small number of views. Thoughts? Again, so many thanks in advance.

[Adam_C]

I’m glad it was useful!

Okay, I’m going to give you my understanding of what your workflow is so that you know where my design is coming from; I do apologize if I’ve gotten something wrong

• You have clients, each of which you might have to do multiple projects with
• Each project has a “Start Date”, “Draft Date”, “Delivery Date”
  • One of these project types is a Survey
• The Survey project type has three phases
  • Scoping it out and determining costs
  • Adminstering the survey
  • Analyzing the survey
  • Each phase has multiple sub-phases, e.g. Analysis Start → Draft, Analysis Draft → Internal Due Date

I’m running with the assumption that when you say tracking you’re primarily referring to seeing project phases in a calendar view, to see what’s active, due soon, coming up soon, etc.

With that in mind, I think in your position I would do the following:

1. With the example base in mind, separate each phase into a single table (which is what you already have now, I believe), as it seems like there is a lot of information, besides date information, that needs to be organized for each phase
2. Using the same logic as the automation from the example base, create a record in the Gantt table for each sub-phase, allowing you to view everything via the Gantt table

I think this would work as, once the automation has created said records and they’re linked, you can update them easily and you can add logic to the Gantt table to customize it to what you want to see.

===
I feel like having multiple records with two date fields would be a cleaner solution in terms of data display / linking, but if business needs allowed you to, you would already be doing that, and I’m not sure what those business needs are that are preventing this

If you have the time, could you tell me about why you would prefer to have multiple date fields in one record, versus having multiple records with two date fields? I feel like once I understand that I may be able to provide a better solution for you