How to view upcoming project phase gates for portfolio of projects

[Tse-Sung_Wu]

Hi,
I’m using Airtable to manage a portfolio of projects. Each project has a milestone or phase. We have a table of projects and the dates they’re expected to finish each milestone. So I have end of Phase 1, end of Phase 2, etc. as columns to each project, rows.

Question: how can I view or sort the data by date of ANY upcoming end of phase, and name that phase?

So for example, I’d have a list that would tell me:
Proj 1 Phase 1 due on 3/1/18
Proj 3 Phase 3 due on 4/1/18
Proj 2 Phase 4 due on 5/15/18

Any ideas?

Thanks in advance,

[W_Vann_Hall]

I don’t think I understand your question — or, wait: maybe I do.

If you are saying your {Project} records look something like this

{ProjectName} {Phase 01} {Phase 02} {Phase 03}
  Project_1    03/01/18   04/15/18   06/01/18
  Project_2    02/01/18   03/10/18   04/01/18

and you want to be able to display them like this

  Project_2    Phase 01 due 02/01/18
  Project_2    Phase 02 due 03/10/18
  Project_1    Phase 01 due 03/01/18
  Project_2    Phase 03 due 04/01/18
  Project_1    Phase 02 due 04/15/18   
  Project_1    Phase 03 due 06/01/18

you can’t — at least not without having to twist yourself into knots.

However, with a slight change to your base architecture, you can: Instead of {Phase 01}, {Phase 02}, and so forth being date fields, make them linked record fields. Have them link to, say, the [DueDate] table. Each [DueDate] record contains a date field, three linked record fields, and a couple of formula fields.

You know, it’s probably easier to show than describe. That’s a link to a read-only base built along the lines I just described; duplicate it so you can examine the field configurations. It adds a couple of mouse-clicks per date added, I fear: To set the due date of a phase, select the plus sign in the phase field, which opens a drill through to the [Due Dates] table. Once there, select ‘Add new record’. When the expanded view of the due date record opens, click in the {Due Date} field and set the date. Close the record, and you return to the [Projects] table.

To create the list you want, go to the [Due Dates] table. You’ll find two views defined there: One that generates the list you specify, sorted in date order; the other is grouped by project.

---

If this isn’t what you were asking about, run it by us again, and we’ll give it another try…

[Tse-Sung_Wu]

Wow. Quick reply here… let me think this through. One thing tho’ right off the bat is that I like the current architecture because we can run stats. off of it: How long did a phase take for a given project- how many days to execute Phase 2? What’s the average for our portfolio, e.g.?

We also have, which I didn’t show, baseline milestone dates, in addition to these forecast/actual dates (which denote dates in the future or the past, respectively). We use these to make comparisons between forecast or actual completion date to expected (baseline) completion date, for each phase. We use this as a leading indicator diagnostic: how many projects are forecasting within 30dd of their baseline milestone date? How many in excess of 30dd? How many projects hit their dates within 30dd? How many were late by >30dd?

Hence, the idea of capturing date values for as an attribute of a project. Thanks!

[W_Vann_Hall]

Tse-Sung_Wu:

Hence, the idea of capturing date values for as an attribute of a project.

Ah, but the milestone dates (and anything else you care to track) are still attributes of the project under my architecture (or, at least they are as long as your process makes certain each [Due Dates] record links to no more than one [Project]. Exactly how you generate stats will depend upon how you set your measurements, For instance, do you collect actual phase start and stop dates, or do you simply treat the start date for Phase[x+1] as being the end date of Phase? If you track the actual dates, you’re in luck: Simply add a few fields to the [Due Date] table, probably give it a different name, and calculate your actuals and performance accordingly.

If you’ve historically measured the interval between dates that would now be tracked in different records, things get a little more complicated — but still far from impossible. What you’ll have to do is create a field, most likely on a per-project basis, that holds what are essentially a number of key::value pairs, probably along the lines of this:

01|1PS:MMDDYYYY|1PE:MMDYYYY|2PS:MMDDYYYY

(That shows, for Project 1, the start date for Phase 1, the end date for Phase 1, and the Start Date for Phase 2.) To perform your calculations, you first extract and convert the correct date value from the string. You can find an implementation of the fundamental process you’d use in the solution to this post.

You might also want to look at how I track garments sent out for cleaning or repair in my Wardrobe Manager base. While I’ve yet to build the full suite of monitoring and tracking tools you currently use, as I recall there are similarities in the way I track goals and deadlines and some of the processing you need to do. (In my case a single garment could conceivably experience numerous referrals to a vendor and back over the course of its life. Using fixed date fields defined within the record wouldn’t work — or, rather, it would work, but at the cost of losing performance metrics for all but the current referral.) If you decide to check it out, the pertinent fields and routines are primarily within the [Out] table. There’s also a 60? 80? page users guide stored as a PDF attachment in the [Documentation] table. It’s been several months since I worked with that base, but I think I discuss the handling of referral dates somewhere in the guide.