“Can you help me just get this process down to where I can click one button and it does what I need?”

There’s an anxiety and an excitement in these kinds of questions. Partially because it meets at the very thin intersection between outputs and outcomes. And at this boundary-place, there’s a navigation of expectations and enablement which can make or break an engagement. It’s in this space where the phrase (re)engineering complexity gets its legs.

For a recent client, this was question at the end of a complex scenario. The person wanted (rightly) to remove the complexity in an action happening across several documents and collaborators. They also only had a loose vision of what could be based on a larger workshop put on by Avanceé they attended. As such, there’s a route to almost all solutions, and in sitting with them, we ran through one of our frameworks for focusing on the addressable parts of the solution.

Goal: minimize the amount of work needed to update and validate a final report

Issues (a few of them):

  • final report was generated from a separate, static document
  • the final report would be generated weekly, and there was not yet a means to move data collection into a more malleable database
  • a significant validation step was in using a formula to extract information from a source document, then convert that product to generic text

Resources:

  • we had a timeline of past reports to draw from
  • within that timeline was also a series of behaviors, some of which were unique to the final report
  • there was a structured date-time reception to the information, giving a previously “head knowledge only” calculation a place to be used

There were a few more issues and resources, but this is sufficient to detail some of the complexity found in a seemingly simple “can we turn this into one button that solves everything” kind of ask.

After reviewing the behavior to generate the report, and discovering some additional quirks to validation (holidays shifting dates for example), we were able to create a path towards a solution - though probably not the initial desired one.

First, we were able to extract the date-time of each successive report from the file name of the static information. Based on the formatting and cadence, we created a field in a hidden area of the final report which would take the current date, then do a calculation for the date from that we desired. We then formatted that date-time to the same format of the source file.

Next, we took the product of that newly formatted date-time, and re-inserted the formula which had been previously swapped out for a text-only view. This gave us a view of the source document within the formula, and a route of simply using find-replace to update the formula as needed. And, in an effort to support the validation step, this find-replace action is kept as a manual step. The previous piece, getting the formatted date-time, happens when the report is loaded, so there’s no further calculation action needed.

We stepped through just doing these actions, and the result showed there only being a two step (instead of the previous 13) behavior - copy the new date-time, do a find-replace. The additional benefit of this is in the formula not changing, keeping the older state of data for review.

Now, we could turn the copy-paste then find-replace into a single action, but in our validating of the behaviors, we noticed some additional data points which could be a part of the report, and this would be an intermediate validation step before the find-replace. Still, we have a one-button solution if needed ready to go.

Does this answer the initial need? Yes. The solution discovered in minimizing the validation steps to make and create the new report eased the friction found in there being too many steps in the additional process. There’s a bit more which can come from this; but that would happen within other points in the process - likely in the source documents. And if I’m those points, am certain another technology would be used instead of static documents.

This is one of a few examples of what Avanceé does to coach and consult individuals and teams. If work like this would be of a benefit to you or your team, reach out and let’s discover a better path.