CRM: Comparing Time Worked with Time Billed on Resolved Cases

This article applies to CRM 4.0

We’re a technical service company and we manage the work effort needed to resolve issues by using cases and tasks in CRM. At the end of a case, however, the person resolving the case determines how much of the time worked was billable.

On the one hand, we want to be fair and balanced to our clients, but on the other hand we put bread on the table by selling our knowledge and experience by the hour. There’s not an easy way to report on the discrepancies between how much work was done and how much was billed.

Thanks to David Jennaway, I’ve got a handy-dandy T-SQL script that I used to come up with an Excel spreadsheet that pulls the data from a SQL view. I’ve modified his original script somewhat to bring back the Account Name rather than the Incident ID.

select
    i.accountidname,
    i.title,
    ir.actualend,
    max(timespent) as BillableTime,
    isnull(sum(a.actualdurationminutes), 0) as TotalActivityTime

from filteredactivitypointer a
    join filteredincident i on a.regardingobjectid = i.incidentid
    left outer join filteredincidentresolution ir on i.incidentid = ir.incidentid and ir.statecode = 1
    and ir.actualend = (select max(actualend) from filteredincidentresolution ir2 where ir2.incidentid = ir.incidentid and ir2.statecode = 1)
where i.statecode = 1

I actually saved this as a view, then called it from an Excel spreadsheet. That lets you pull the data into a pivot table, etc.

You can also then build on this and figure out your top accounts using a script like this -

SELECT
    Accountidname as AccountName,
    COUNT(accountidname) AS Cases,
    SUM(BillableTime) AS TimeBilled,
    SUM(TotalActivityTime) as TimeWorked,
    SUM(TotalActivityTime) - SUM(BillableTime) as Variance
from acc_ResolvedCaseTimeComparison
GROUP BY ACCOUNTIDNAME
ORDER BY TimeBilled DESC

Where myresolvedcaseview is the name that you gave your SQL view. This query brings back each account, the number of resolved cases, the amount of time worked, time billed, and the variance.

Note, you’re running directly against the SQL data, so it depends on specific security rights to the database to see the data.

Resolving Contract Line Errors

Steps to correct contract line allotments

Dynamics CRM 4 does not provide a way to revise case resolution allotments after the case has been resolved. Reactivating the case and reclosing it will double-dip allotments from the contract lines, leaving them incorrect.
This must be resolved behind the scenes, using SQL scripts.
NOTE: This is not a supported resolution as it directly modifies data rather than relying on the CRM interface. Always make sure and backup a SQL database before modifying data directly.
This article applies specifically to Dynamics CRM v4.0

Get the Case Resolution Activity ID

Use this procedure to get the activity id related to the closed case.
Open the case. Click the History menu item on the left. The top activity should be the case resolution activity. Open it, then click CTRL+N to open the activity in a new browser window.
The Activity ID is the long string between the {} characters

http://server:5555/company/cs/cases/closecase.aspx?id={68C018E8-5D08-E011-B563-001A4B4D64BE}&_CreateFromType=112&_CreateFromId=%7b616C59EA-2DE8-DF11-BA3B-001A4B4D64BE%7d

Get the Contract Line ID

Click through the case to the contract line and use the same procedure as above to get the contract line id.

Double-Check Your Work

Before you start updating all willy-nilly, make sure you have the correct records.
Using SQL Management Studio, connect to the CRM Company database. Use the following scripts to return the records you’re looking to update. Replace the <ID placeholders> tags in the script below with the correct ID’s you retrieved above.

SELECT *
FROM ContractDetailBase
WHERE ContractDetailId = '<CONTRACT LINE ID>'

SELECT *
FROM IncidentResolutionBase
Where ActivityId = '<RESOLUTION ID>'

Sample:

SELECT *
FROM IncidentResolutionBase
Where ActivityId = '81A0A2C9-DE1F-DF11-9063-001A4B4D64BE'

Verify that you’re looking at the correct record and note the current values. In the ContractDetailBase table, we’re specifically looking at the AllotmentsUsed and AllotmentsRemaining fields. For the IncidentResolutionBase table, we’re changing the TimeSpent field.

Update Your Values

If everything matches up and we’re sure we have the right records, use the scripts below to update the correct fields. Replace the <NEW VALUE> tag with the number you wish to use. Replace the <ID placeholders> tags in the script below with the correct ID’s you retrieved above.

UPDATE IncidentResolutionBase
SET TimeSpent = '<NEW VALUE>'
Where ActivityId = '<CONTRACT LINE ID>’

UPDATE ContractDetailBase
SET AllotmentsUsed = '<NEW VALUE>', AllotmentsRemaining = '<NEW VALUE>'
WHERE ContractDetailId = '<RESOLUTION ID>'

You’ll first adjust the Resolution Activity to the correct value. Make a note of how much it as changed and then adjust the Allotments Used on the Contract Line accordingly. Then, use the same number and adjust the Allotments Remaining as well.
For example, we closed a case and resolved it for 30 minutes, when actually it should have been resolved for 45 minutes. The Contract Line is for a total of 600 minutes (10 hours) and after we closed this case, 4 hours (240) had already been used up.
Old Values

Case Resolution: TimeSpent = 30
Contract Line: Allotments Used = 240
Contract Line: AllotmentsRemaining = 360

Corrected Values

Case Resolution: TimeSpent = 45
Contract Line: Allotments Used = 255
Contract Line: AllotmentsRemaining = 345

Updating ReadOnly and Disabled Fields

This is a repost + comments from an article by Ryan Farley. Many thanks to him and the information he puts out. The full article is located here >>. Relevant portions are copied below.

When you need to set fields disabled or readonly at runtime, you need to keep in mind the type of field you use. Setting a HTML input field is a simple line of Javascript, however, the type of CRM field you've chosen will have an impact on exact items you need to change at runtime. I personally like the look of disabled over readonly. A disabled control will show it's value greyed, indicating to the user that they cannot modify it's contents. A readonly field appears normal. The user only finds out that they cannot change it's value until they attempt to do so.

Use the following code to toggle the field status -

// set it as readonly crmForm.all.new_textfield.readOnly = true; // OR set it as disabled crmForm.all.new_textfield.disabled = true;

 

Here’s the tricky part. By default, read-only fields aren’t saved back to the database. Makes sense, right. However, what if you’re updating a field with some code and want that value saved even though you don’t want the user to be able to change it?

Use the ForceSubmit action.

crmForm.all.<field>.DataValue="some value";

crmForm.all.<field>.ForceSubmit = true;

Setting defaults for System Fields in CRM

In Dynamics CRM v4.0, the system fields don’t allow you to set a default value. Unfortunate, but there are ways around it.

Add the following code to the OnLoad event for the form. Add as many defaults as you need. The code checks to see if this is a new form (CRM_FORM_TYPE_CREATE) and then sets the values you provide.

//Set default values for system fields
var CRM_FORM_TYPE_CREATE = "1";

if (crmForm.FormType==CRM_FORM_TYPE_CREATE)
   {
   crmForm.all.fieldname.DataValue = 60;
   }

Requiring 1 of X fields in CRM

There may be times when you don’t want to require one specific field to contain data, but at least one in a group. An example would be deciding that we can’t enter a lead if we don’t have a way to contact the lead – phone number, email, mobile, etc.

What we have to do is make sure that at least one of the contact fields for a lead contain data. There’s no way to make sure this happens. We could set them all Business Recommended, but that tells the user that they should all be filled in if possible and that’s not what we need.

Gather the actual names of the fields you want in the required group.

Use the following code to require at least one of the fields to contain data in order to save. Note that this example is based on the Lead form. Place the code in the OnSave event for the form.

The first portion is a concatenated If statement that checks each of the fields for data. If the entire statement evaluates as false, then the second portion of the code pops a prompt to the user letting them know why the save failed and then stops the save process.

Also, the if statement is all on one line, even though it wraps below and

//Contact Check
//Check for at least one contact method
{
if (crmForm.all.telephone1.DataValue==null && crmForm.all.emailaddress1.DataValue==null && crmForm.all.mobilephone.DataValue==null && crmForm.all.telephone3.DataValue==null && crmForm.all.telephone2.DataValue==null && crmForm.all.websiteurl.DataValue==null)
{
alert("Provide at least one phone number or an email address");
event.returnValue=false;
return false;
}
}