function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
FGNYFGNY 

How to compare Date fields with Today's date in an S-Control and calculate the difference in days.

I want to create an S-Control which retrieves project milestones (dates) from an asset and compares these dates with today's date or "Today - x days". But the following code does not work.
Code:
var ident= sforce.connection.query("SELECT Id, PurchaseDate , Delivery_Date__c, InstallDate, Acceptance_Date__c, Warranty_End_Date__c, ProjectState FROM Asset WHERE Type__c IN ('SIS' , 'WIS') AND Asset_Autostatus__c NOT IN ('Out of Operation' , 'Out of Warranty' )");
var irecords = ident.getArray("records");
int i=0;
while (i<300)
{
if (irecords[i].Warranty_End_Date__c > {!Today})
 {irecords[i].ProjectState = "Out of warranty";}
if (Acceptance_Date__c != null AND InstallDate < {!Today}-60)
 {irecords[i].ProjectState = "Factory Acceptance overdue";}
i++;}
The first problem is the script fails at {IToday} and the second is that Today will be returned in european dd.mm.yyyy format while project milestones are returned in yyyy-mm-dd format.



Message Edited by FGNY on 11-26-2007 01:57 AM

SteveBowerSteveBower
Hi.  Several thoughts just from eyeballing it.

Your Javascript is failing because of syntax errors once the value for {Today} is substituted. 

A. Firstly, I haven't checked, but I'm not sure there *is* a substitution for {!Today}???   I know there is one
for {!$System.OriginDateTime}, but I think the Today() functions is for Functions in Salesforce Formulas and not for S-Control substitution... I may be wrong.

B. Assuming you were correct for A, all Salesforce does when creating the page that your browser runs to run the s-control is to blithly substitute in the string you've requested *wherever* you requested it.  Regardless of syntax.  So, if I
were to write  if ("Steve" == {!Contact.Firstname})  this would translate to:
if ("Steve" == Steve) which would be a syntax problem. 

C. I don't know your dataset, but rather than query all the Assets and going through them yourself, might you just change the query to salesforce to include the criteria  ... and ((Warranty_End_Date__c > TODAY) or (Acceptance_Date_C != null and InstallDate < LAST_N_DAYS:60))

Then you can cycle through the results, knowing that they all need an update, you just don't know which one.  Just saves on the number of records retrieved.  Since the ProjectState is mutually exclusive your code can then read:
while(...){
if (irecords[i].Warranty_End_Date__c > "{!$System.OriginDateTime}") {
    ....projectState = "out of warranty";
} else {
    ...projectState = "Factory....";
}

Instead of using {!$System...} you could build your own string in whatever format you want with Javascript Date().

D. I won't even go into the question of using a workflow or an Apex trigger.  ;-)

Best, Hope my late night ramblings help, Steve.
FGNYFGNY
So how to retrieve the date in YYYY-MM-DD format then? Will javascript Date() return the date in this format?
I also can't use the query to filter those assets out since there are additional conditions to be checked dependent on the previous condition:
The whole idea behind this S-Control is to check if project milestones were not updated by the Project Manager (i.e. Install date should be within 2 Months after delivery date, and acceptance not longer then 2 months after install) and then create a task for this Project Manager to remind him to check the milestones.
Here is the whole code:
Code:
<html>
<head>
<title>Salesforce - Enterprise Edition</title>
<script src="http://www.salesforce.com/services/lib/ajax/beta3.3/sforceclient.js" type="text/javascript"></script>
<script src="/soap/ajax/10.0/connection.js"></script>
<script type="text/javascript">
<!--
var stat = sforce.connection.query("SELECT Count() FROM Asset WHERE Type__c IN ('SIS' , 'WIS') AND Asset_Autostatus__c NOT IN ('Accepted' , 'Out of Operation' , 'Out of Warranty')");
var ident= sforce.connection.query("SELECT Id, Name, PurchaseDate , Delivery_Date__c, InstallDate, Acceptance_Date__c, Project_Manager__c FROM Asset WHERE Type__c IN ('SIS' , 'WIS') AND Asset_Autostatus__c NOT IN ('Accepted' , 'Out of Operation' , 'Out of Warranty')");
var rec = ident.getArray("records");
var i =0;
while (i<stat.size)
{
if (rec[i].Acceptance_Date__c == null AND InstallDate < {!Today}-60)
 {createTask(rec[i].Project_Manager__c, rec[i].Id, rec[i].Name);}
else {
 if (rec[i].InstallDate == null AND Delivery_Date__c < {!Today}-60)
  {createTask(rec[i].Project_Manager__c, rec[i].Id, rec[i].Name);}
 else
 {
  if (rec[i].Delivery_Date__c == null AND PurchaseDate < {!Today}-60)
   {createTask(rec[i].Project_Manager__c, rec[i].Id, rec[i].Name);}
 }
 }
i++;
}

function createTask(pmid, assetid, assetname)
{
    var task = new Sforce.Dynabean("Task");

    task.set("ActivityDate", "{!Today}");
    task.set("Description", "Dear Project Manager. The status your project" + "assetname" + "is supected to be expired. Please update the milestone dates of this asset");
    task.set("OwnerId", "pmid");
    task.set("Priority", "Normal");
    task.set("Status", "Not Started");
    task.set("Subject", "Other");
    task.set("WhatId", "assetid");
 
    sforceClient.create([task]);
}
//-->
</script>
</head>
<body>
</body>
</html>
However there is also an additional problem with the script in the createTask function. Apparently only a small mistake but I don't see it. Can somebody help
 

Message Edited by FGNY on 11-27-2007 09:54 AM
Ron WildRon Wild
Just so other readers don't go off to use OriginDateTime as a substitute for the current time ...

OriginDateTime - A global merge field that represents the literal value of 1900-01-01 00:00:00. Use this global variable when performing date/time offset calculations or to assign a literal value to a date/time field.

This will only lead to headache if you attempt to use it to determine if your warranties are out of date.

Cheers,
SteveBowerSteveBower
Thanks!

See, that's what happens when it's late and you write things without looking them up to be sure! 

Mea Culpa,  -Steve
FGNYFGNY
Well, the only solution was to write a function which transforms the get.date() return string to salesforce-compatible YYYY-MM-DD.  It also returns SF-compatible time.
For those who have the same problem, here's the code:
Code:
function time(num)
{ 
var zero="0";
var time= new Date();
var Day = time.getUTCDate();
if (Day<10)
{Day= zero.concat(Day);}
var Month = time.getUTCMonth() + 1;
if (Month<10)
{Month = zero.concat(Month);}
var Year = time.getUTCFullYear();
var Stunden = time.getUTCHours();
if (Hours<10)
{Hours= zero.concat(Hours);}
var Minutes= time.getUTCMinutes();
if (Minutes<10)
{Minutes= zer.concat(Minutes);}
var date= Year + "-" + Month+ "-" + Day;
var clock= date + "T" + Hours + ":" + Minutes + ":00.000Z"

switch (num) {
case 1:
return date;
break;
case 2:
return clock;
break; 
}
}
SteveBowerSteveBower

In the file: soap/ajax/9.0/connection.js  which you're including already, Salesforce provides a function:
sforce.internal.dateTimeToString = function(theDate) {

as well as some other date related conversions.

Best, Steve.