What do you want to do with the answer? Do you want to store it, display it, use it in reporting, or do you need it for another calculation?

I just want to display the answer in a field on screen

I would use a SQL Job that runs once a day, or a couple of times during the day which runs a SQL Script along the lings of (Possibly could be run as an Escalation Rule depending on the table being updated) 

UPDATE <Table>

SET <Month Field> = DateDiff(Month,StartDate,EndDate) 

WHERE <Whatever parameter is met) 

Do this all the time for things like Equipment and I need to track the age of it. 

thanks for the reply Matthew - ideally i need it to run on change of entering the end date so that i can then try to add a formula which works out the cost based on the no of months

Method 1.

You don't need to do this. You don't need to store the value if it is for display purposes only. You can just create a text field of size 1. It isn't going to actually store anything at all. It is a placeholder on the screen as a target for a javascript. You add that field to the screen. In the create script you make it readonly. Then in a custom content script, you just have a calculation using easily findable javascripts from the internet that can do a datefdiff between 2 dates... BUT, you will hit problems though because it will depend on how your user displays their date in their preferences and date formats. You also have to consider what happens if one or both fields are blank. You need to consider what if date A is earlier or later than date B. Your answer could be a negative number. Do you display a negative or do you just say the difference is an absolute value?

Method 2.

If you want to store the value, then your approach can be different. Create the field as an integer field. Create a table script that runs ONLY when these 2 date fields have a value. If they do have a value (CRM has already validated them), then you can run a simple SQL command via the tablescript that does a simple datediff and sets the value in the field.

You don't need a SQL job. You shouldn't need a trigger. I'd still have the field a readonly field on the screen because you don't want your user manually changing it. I'd go with the second method.

Hi, SIQ!

I've read through what you're trying to accomplish, and I'll see if I can provide some insight into what you're trying to accomplish.

I understand there are three fields:

• Start Date (oppo_date1)
• End Date (oppo_date2)
• Calculated number of months (oppo_numberOfMonths)

Utilizing an OnChange script attached to the date fields, you'd like to have the third field populate a value when there are dates present in the Start/End dates respectively.

So long as all fields are editable and NOT read only, the below code has been tested and verified to work. Please ensure you replace the field names in the below code with the system's actual field names :

var startDate = new Date(document.getElementById('oppo_date1').value);
var endDate = new Date(document.getElementById('oppo_date2').value);
if(startDate > 0 && endDate > 0){
	var diffTime = Math.abs(endDate - startDate);
	var diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24)); 
	var diffMonths = Math.floor(diffDays / 30)
	oppo_numberOfMonths.value = diffMonths;
	}
	else{
		console.log("ERROR: Missing parameters. Unable to calculate date. Check your date fields and ensure a value is present in both oppo_date1 and oppo_date2.");
	}

If either of the date fields are ReadOnly, you would need to change the first two lines to read...

// if field is not ReadOnly
var startDate = new Date(document.getElementById('oppo_date1').value);

// if field is ReadOnly
var startDate = new Date(document.getElementById('_Dataoppo_date1').innerText);

I hope that helps! Let me know if you have any questions regarding the above code snippets/examples.

Best Regards,
Basil Malik, CRM Practice Leader
Blytheco
[email protected]
#transformingCompanies

Hi Basil

thanks for the reply and code - really appreciate it

ive added the below code to the onchange script boxes for both my date fields but the months field is not calculating:

var startDate = new Date(document.getElementById('oppo_mcstartdate').value);
var endDate = new Date(document.getElementById('oppo_renewaldate').value);
if(startDate > 0 && endDate > 0){
var diffTime = Math.abs(endDate - startDate);
var diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
var diffMonths = Math.floor(diffDays / 30)
oppo_months.value = diffMonths;
}
else{
console.log("ERROR: Missing parameters. Unable to calculate date. Check your date fields and ensure a value is present in both oppo_date1 and oppo_date2.");
}

when i enter the start and end date the months field is just showing as blank -  could you please advise?

This is why I tend to look at SQL more the same above as a trigger on the opportunity table would be 

CREATE TRIGGER Opportunity_RecordUpdates
ON Opportunity 
AFTER Insert, Delete

AS
DECLARE @OppoID INT
SELECT @OppoID = oppo_opportunityid FROM Inserted

BEGIN
UPDATE Opportunity 
SET oppo_months = DateDiff(Month, oppo_mcstartdate, oppo_renewaldate)
WHERE oppo_opportunityid = @OppoID 
END

As the calculation is done after they have clicked on Save, you would have to make both date field mandatory in CRM as it's too late to stop the user by this time. (Though you could put something else in as a message if you wanted to allow users to continue) 

That is the advantage of TableScripts 

Hi, SIQ!

I see where I went wrong in my sample code - I left off a semicolon in one of the lines, which causes the code to error and not perform as expected. Please see your below, modified code.

var startDate = new Date(document.getElementById('oppo_mcstartdate').value);
var endDate = new Date(document.getElementById('oppo_renewaldate').value);
if(startDate > 0 && endDate > 0){
    var diffTime = Math.abs(endDate - startDate);
    var diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
    var diffMonths = Math.floor(diffDays / 30);
    oppo_months.value = diffMonths;
}
else{
    console.log("ERROR: Missing parameters. Unable to calculate date. Check your date fields and ensure a value is present in both oppo_date1 and oppo_date2.");
}

When testing the code, ensure that you can see errors reported by your browser. In Google Chrome, you can hit the F12 key to observe errors. Implement the above code on both fields, and test it with the ability to see errors.when they occur. You will also be able to see the error message from the "console.log()" response.

Let me know if that fixes it.

I agree with Matthew Shaw that the SQL trigger would be a great solution so long as you're not trying to perform dynamic calculations on the screen. The code provided would be great to calculate values from a database level. The code provided above allows the customization to function dynamically onscreen. Benefits to both methods depending on what you're looking to accomplish.

Best Regards,

Basil Malik, CRM Practice Leader
Blytheco
[email protected]
#transformingCompanies

Hi Basil

tried that but still cant get it to work, checked in F12 and cant see any errors 

Completely different approach. Add a meta data only field (derived field) and then use the base v[Enttity] to calculate the value on the fly by aliasing the caluatation to the meta data field. It falls down if there are any natural/direct table reads.

Example of derived field method (https://www.sagecity.com/sage-global-solutions/sage-crm/b/sage-crm-hints-tips-and-tricks/posts/adding-derived-fields-in-views-into-meta-data?CommentId=8476c5d9-de62-4174-bbc3-23bfa54afde3)

Hi SIQ!

You're right! While the code works in the console itself, it tends to spark an error when executed onscreen.

Since there's a bit too much to type to explain the "why" behind the code not working as expected, please refer to this video, hot off the presses. It illustrates how to take the above code, wrap it into a function, and utilize that concept to implement the customization.

The video has a shelf life of six months. Direct link: blytheco-my.sharepoint.com/.../Eb5cdLIf_VZDlRekdm3Ung4BFKM_T49_lCYUSKuJCscj_g

Best Regards,

Basil Malik, CRM Practice Leader
Blytheco
[email protected]
#transformingCompanies

Amazing! it works, the next issue i have is that it only works if i set my date time preferences to mm/dd/yyyy what would i need to do the script to get it to work with dd/mm/yyyy?

thank  you for your help - really appreciate it!

Excellent! :) I'm glad that worked as desired.

I would post the issue under a new topic. I'll be happy to weigh in on that, and how to resolve it. Sage CRM (Jeff Richards) has discussed this dilemma in previous posts, but I can't seem to find it.

Another good option, Darren! This just adds to the concept that there's more than one way to accomplish the goal.