A Couple JavaScript Date Tricks, and Some Limitations of Client-Side Scripting

Recently, Trick Bag reader Larry Bradley asked an interesting question:

…I have a custom entity called Projects. This is for custom vehicles we order for customers. There is a date field called new_shipmentforcast. I need to populate another field called new_daystillshipment with the number of days which is essentially Today – Shipment Forcast. I don’t know how to form the proper syntax to make this work.

JavaScript Date Arithmetic in a CRM Context

Ready Larry? I’ll describe how to do this using the Opportunity entity, mainly because I wanted to use something everybody’s familiar with, but it will work identically (apart from attribute names) for your custom entity.

1. Start by adding two custom attributes to opportunity (or your entity of choice):
   1. Ship Date (schema name new_shipdate, data type of datetime)
   2. Days to Ship Date (schema name new_daystoshipdate, data type of int)
2. Add them to the form. For my example, the opportunity form in the customization UI looks like this:

   

3. Next, add the following code to the OnLoad event of the form:

    

   var CRM_FORM_TYPE_CREATE = 1;
   

   var CRM_FORM_TYPE_UPDATE = 2;
   

   crmForm.all.new_daystoshipdate.Disabled = true;
   

    

   switch (crmForm.FormType)
   

   {
   

   case CRM_FORM_TYPE_CREATE:
   

   break;
   

   case CRM_FORM_TYPE_UPDATE:
   

   if (crmForm.all.new_shipdate.DataValue != null) {
   

   crmForm.all.new_shipdate.FireOnChange();
   

   }
   

   break;
   

   }
   

4. Add this code to the OnSave event:

    

   crmForm.all.new_daystoshipdate.ForceSubmit = true;
   

    

   The code crmForm.all.new_daystoshipdate.Disabled = true; in the OnLoad event and crmForm.all.new_daystoshipdate.ForceSubmit = true; in the OnSave event are a matched pair: disabling a field in the load event is appropriate for calculated fields, but then you have to use ForceSubmit in the save event to save it.

    

5. Finally, for the money shot, add this code to the onChange event of the Ship Date field:

    

   var currentdate = new Date();
   

   millisecondstoshipdate = crmForm.all.new_shipdate.DataValue – currentdate;
   

   crmForm.all.new_daystoshipdate.DataValue = Math.round(millisecondstoshipdate/(1000*60*60*24));

    

No wonder you had a question about the syntax, Larry – this is somewhat cryptic the first time you see it! Here’s how the code in the onChange event works:

The first line — var currentdate = new Date(); — puts the current date into the currentdate variable.

But dates in JavaScript are stored as numbers…specifically, the number of milliseconds since Jan 1, 1970. So when you subtract one from another, like my example does in the second line, you’ll get what looks like a Really Big Number, but that’s just because it’s in milliseconds. To keep things readable I put the difference between the Ship Date and the current date (remember, the whole object of this endeavor was to calculate how many days until we ship!) into the millisecondstoshipdate variable.

So finally, in the third line, divide the number of milliseconds by the number of milliseconds in 24 hours, (1000*60*60*24)…or by 86,400,000 if you prefer a less readable approach. (millisecondstoshipdate/(1000*60*60*24)) will have too many decimal places, so use the built-in JavaScript Math object, and round() is one of its methods that, well, rounds things. Math.round(millisecondstoshipdate/(1000*60*60*24)); gets stuffed into the daystoshipdate form field.

You could dispense with all the niceties and do it like the following, but I’d be like, “what the…?”, and I’m writing the article!

var currentdate = new Date();

crmForm.all.new_daystoshipdate.DataValue = Math.round((crmForm.all.new_shipdate.DataValue – currentdate)/(86400000));

Limitations of Form Scripting and CRM Online

The most obvious limitation of this example is that the “Days to Ship Date” field only gets updated when the form is loaded. I pointed this out in a general way in the Form Script #1 article, but the current example really drives the point home: there’s no true calculated field type in Dynamics CRM, so you’ll need to design around the fact that unless every form gets loaded every day, the current values in the database of the “Days to Ship Date” fields won’t always mean what you’d like them to mean.

What could throw our numbers off? Here are the two most obvious things:

1. Time passes, and currenttime keeps getting bigger. (Even now, something like 40,000,000 milliseconds have ticked off since I started writing this article!) Unless the form is loaded, Days to Ship Date stays the same.
2. The Ship Date field can change in other ways than by a user changing it on the form: a workflow can change it, or something like a Scribe integration with an external system can change it. In either case, the form doesn’t get loaded so the code doesn’t fire.

Currently this is more of a problem for Dynamics CRM Online than it is for on-premise. In on-premise, for example, you could write a custom RSS report and use an expression within the report writer to do the date subtraction, rather than rely on an always-updated value in the database. But in CRM Online, it’s not so obvious how you would do this. In CRM Online all you have is schema customizations, form script, and workflows. No plug-ins, no custom workflow actions, no reporting services or BI to speak of.

Anybody got any good ideas? It’s getting late – 1,276,575,545,132.00 to be exact – and the best I can come up with is to make an on demand workflow that subtracts the workflow execution time from the Ship Date field and updates the Days to Ship Date field.

That’s probably worth a separate article, on “batch jobs with workflows”, and while it will work in a pinch and may be our best solution in the current example, there are at least two problems that may crop up for you if need to do something like that with Dynamics CRM workflows, ca the 4.0 version:

1. On demand workflows can only be run on a maximum of 250 records at a time.
2. You can’t schedule workflows to run periodically, say at timed intervals.

A combination of fixes for those two limitations would be excellent additions for the 5.0 release…but that topic’s covered in a different article.