How Do They Do That?! Using Custom Calculated Fields to Work Smarter
Custom calculated fields are one of the most powerful tools that you can have at your disposal when working with complex data sets. I think of them like a Swiss Army Knife, you can use them to dynamically organize, filter, and manage your data. As I mentioned in a previous article, without them, you’re left trying to fit square pegs into round holes and bend yourself backwards trying to make sense of the data.
Please remember that we don’t always have a database field or column where we can enter every single particular detail of our Work Order, Asset, or Location. Since I like to keep my systems and processes as close to out-of-the-box as possible, I use this powerful tool to make the database work for me and make my life easy.
I can probably say that 99% of Prometheus Routine Maintenance implementations that I have been involved with take advantage in one way, shape, or form of custom calculated fields. Let’s go over a few use cases that come to mind that have “impacted me” from recent projects.
We’ve been working with our friends at Cornell University doing a Prometheus Routine Maintenance implementation and roll-out. One of our latest projects was to upgrade their deployed version of Prometheus to the latest release of the application.
Pending work and assignments: custom calculated fields for Cornell
While I was on-site working at Cornell, I also took the opportunity to ask how we could make things better? How could we help them work smarter, not harder? And two requests from their Maintenance Planners in Facilities and Campus Services, Shayne Miller and Samantha Strickland, stand out. Why? Because they can be accomplished with custom calculated fields:
- Display the Crafts that still have pending work on a Work Order. (REQCRAFT)
- Display backlogged Work Orders that have pending assignments. (WAITASGN)
To the untrained eye, including myself, these two requests might seem unremarkable. But to experts like them, this serves a crucial purpose and provides infinite amount of detail of what they need to do on specific records.
Samantha describes how Cornell is optimizing its processes:
“In the past, on long-term projects, it was difficult to get a grasp of the work orders that still needed to be assigned and completed. For instance, a maintenance technician may be assigned to a Work Order for six months, but really only have two hours of work to do. Assigning someone when we have to wait for materials and other requirements for these long-term projects created unrealistic craft requirements. So, this wasn’t the best solution, but we also didn’t want work to get lost in the shuffle.”
“Both of these fields work together to give us the visibility of the work per crew, section or group and overall with multi-trade work,” explains Shayne. “We found we were missing that transitional piece, that visibility, to see all the final work that needed to be done on Work Orders where say, the majority of work needed to be carried out by an electrician, but there was also still painting, carpentry, and other crafts required to finish the job.”
Samantha explains how the idea for the Required Crafts field came about: “The idea for this field stemmed from wanting to have more focused, relevant communication with different forepersons. We wanted a way to pull up only work that was required for a specific craft. So, for instance, any work orders that have non-completed painting assignments. This way, I’m just talking to the painting foreperson about the paint work that needs to be done, not other requirements that aren’t relevant to him or her.”
As part of a new team that is dedicated to helping Cornell reach new heights with improved Planning and Scheduling, both Samantha and Shayne are constantly on the lookout for ways to improve processes, increase communication, streamline work, and ultimately provide even better service to Cornell’s partners, customers, and community members.
“Their satisfaction is what drives us all,” says Shayne. “When nothing is forgotten or left behind, and a project is completed from start to finish with nothing outstanding, the reaction of our partners and customers is what makes it all worth it.”
Simplifying custom calculated fields with Prometheus Routine Maintenance
But, of course, custom calculated fields do have room for improvement. Namely, custom calculated fields are “hard-coded”. It is based on a process that you define and that is it. Why is this a problem? Well, let me tell you a little story: two months ago, I performed an upgrade for a site that I used to work at with my colleagues Shannon Smith and Janna Kerr.
At this site, we had created several calculated fields to be able to group Work Orders “correctly”. So, Work Orders were being grouped based on their hard-coded process that we developed some years ago and today, that process was not true 100% of the time, creating exceptions to our calculations. The question was: how do we handle these exceptions?
A unique feature of Prometheus is that there are ways to “edit” the result of the custom calculated field data. This empowers users to modify their groupings… with a few tricks of the trade. For example, you could modify your data to move a Work Order from a maintenance item to a warranty item.
By the end of the upgrade, we were able to empower the client to reorganize their work taking into account the exceptions, and now we were meeting 100% of their requirements. Not to mention that this upgrade was a great time because I had the chance to work with old colleagues and got to revisit all of the creative requirements that we were able to meet using calculated fields and clever configurations.
Below are some of my favorite custom calculated fields and how to get them. (Remember, some of the data mentioned below could be itself another calculated field that is being fed into Prometheus/IBM Maximo from a third system, e.g. PeopleSoft.):
- Days Old = Current Date – Reported Date
- Days to Next PM = Current Date – Next Due Dates
- Vacation Days Remaining = Vacation Days Allotted – Vacation Days Taken
- Vacation Days = Vacation Days Allotted + Exceptions
- WO Estimated Total Cost = Estimated Labor Cost + Estimated Service Cost + Estimated Material Cost + Estimated Tool Cost
- How Many Times a WO Has Been Rescheduled
- Custom Date Constraints, for example PM Start No Later Than = PM Due Date – Estimated Duration
- Age of the Asset = Current Year – Year Asset was Installed
Like I said, custom calculated fields are the Swiss Army Knife for your data. By asking yourself the right questions, you can get them to do just about anything you need. It doesn’t matter if it’s Oracle, SQL, or Excel – they work.