Hi
i have 2 date fields and want to be able to calculate the no months between the 2 dates - does any one have any sample code of ideas of how to achieve this?
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:
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
Hi Basil
tried that but still cant get it to work, checked in F12 and cant see any errors
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.
*Community Hub is the new name for Sage City