Show Current Step, based on complete, date

[Rachael_Castela]

Hey team!

I want to be able to show my “Current Step in Progress”, based on the information in my “Steps” table.

How it should look:
If there is an actual completion date, the formula should look at the next upcoming step, based on the date found in the projected completion date.

In this case, the “Current Step in Progress” for the Merchandising Acceleration Program should be S2, since S1 has an actual completion date.

Thanks in advance!

[Jeremy_Oglesby]

Hi @Rachael_Castelaz,

This one is going to take a little bit more effort because you’ll need to bounce data back and forth between the two tables. Here’s a step-by-step of how I think you can accomplish this:

1. In a previous post, I suggested you consolidate your Steps >> {Projected Completion Date} and {Revised Completion Date} fields into a single Formula field that shows the Revised date if there is one, and otherwise shows the Projected date. I’m going to assume you’ve done this, and I’m going to assume in the rest of this explanation that this field is called {Expected Completion Date}

2. In your Objectives table, I see you have a Rollup field created called {Upcoming Step Due Date}. Let’s adjust this Rollup to 1) filter to show only “non-completed” Steps (ie, where {Actual Completion Date} is empty), and then 2) use a formula that returns the MIN({Expected Completion Date}) – This should accurately reflect the “Upcoming Step Due Date”

3. Create a Lookup field in your Steps table (let’s call it {Upcoming Due Date Lookup}) that Lookups up the {Upcoming Step Due Date} Rollup field from the Linked Objective record – this will return the MIN({Expected Completion Date}) for any “non-completed” Steps, and it will display on all the Steps records so we can use it for comparison.

4. Create a Formula field in your Steps table (let’s call it {Is Current Step?}) that compares the Date in the {Upcoming Due Date Lookup} field to the Date in the {Expected Completion Date} field, and if they match, this formula will return true (1); if they don’t match, it will remain blank. That formula can look something like this:

IF(
   IS_SAME(
      {Upcoming Due Date Lookup},
      {Expected Completion Date},
      'day'
   ),
   TRUE()
)

5. You have a {Current Step In Progress} field in your Objectives table – let’s turn that into a Lookup field that looks up the {Steps to Achieve} field from the linked Steps – but let’s apply a condition, again, so that it only returns records where {Is Current Step?} = 1. This means you should only get back the title of the Step that is currently in progress.

If I’ve understood what you are asking for properly, I think this will do it.

[Rachael_Castela]

Again, I really appreciate your help!

A few things - we’ve decided to do away with the “Revised Completion Date” in the steps table, so now I can just point to the Projected completion date.

Based on that, I’ve made a slight adjustment to your suggestion above, but I’m getting a little lost at step 2.

For my rollup field in Objectives, I assume I’m pointing at the Steps table, and am I rolling up Projected completion date, filtered when actual is empty, and using this formula? MIN({Projected completion date})

[Jeremy_Oglesby]

Yes, it should have a condition to show only records where {Actual Completion Date}.

Your Aggregation formula needs be:

MIN(values)

Rollup aggregation formula always have to use the values variable.

[Rachael_Castela]

You are a gem, this works like a charm. Thank you!!!