I recently needed to track available support minutes where the minutes went into a deficit. The only problem in MS Dynamics CRM is that duration fields cannot be negative. Some people would say to just use a regular number field. That was not entirely something I could do since the values being subtracted from the available support minutes were going to come from other duration fields. To add to the conundrum, I needed to take that negative value and apply it to an order detail line on an order as a quantity. Generally speaking, orders do not use negative quantities.
Here is the solution I developed to the negative duration problem. Maybe someone has a better idea or maybe someone else has the same situation and needs a solution.
First you’ll need 2 duration fields in the entity you’re going to store this information:
- Available Minutes (duration)
- Negative Minutes (duration)
There are a couple of different situations that you would need to use the Negative Minutes duration field. You may need to keep track of minutes from a purchased time block or estimated time for a project. In both scenarios, the available minutes could go negative to represent an overage. The problem is that duration fields do not maintain negative time. Sure, you could use a whole number field but that makes the fields harder to use by employees. A duration field gets a nice dropdown with time increments whereas a whole number field is a typed value representing an arbitrary number.
If Available Minutes = 0
This is the obvious check. If you have no minutes in your duration column but want to keep track of how many minutes are a deficit you’ll need to add the Minutes to Subtract to your Negative Minutes field. In your workflow or code decrement your Negative Minutes by Minutes to Subtract.
If Available Minutes < Minutes To Subtract
First you have to determine if you have enough available minutes to cover the value that you are subtracting from the available minutes. This is an obvious check. However, the conundrum is to find out the difference between the available minutes.
Since you cannot subtract those minutes from the Available Minutes because a duration field cannot go negative, you have to supply a field for Negative Minutes. So, if your available minutes are less than the minutes you need to subtract, you’ll add the minutes into the Negative Minutes field. Afterwards, you’ll subtract your Available Minutes from the Negative Minutes field. What is left is the minutes over the Available Minutes.
You can use this Negative Minutes to determine how far off an time estimate was or perhaps bill for extra time that was over a previously paid time block.