Conditional Formatting - Date Field - Why '=' works but '<' doesn't?

[soadyc]

Hi,

I don't understand why this would work in a conditional style in a Web Grid:

Completiondate = date()

Bud this doesn't:

Completiondate < date()


Please help...
Craig

[agillbb]

Re: Conditional Formatting - Date Field - Why '=' works but '&lt;' doesn't?

prob because your field holds a string representation of the date

[soadyc]

Re: Conditional Formatting - Date Field - Why '=' works but '&lt;' doesn't?

Thanks,

No matter what I try I can't get '<' to work...

The data stored is in a short date field, the value stored in the field is in the same format as the date() function returns, plus = date() works fine.

Can anyone help?

Cheers
Craig

[2ninerniner2]

Re: Conditional Formatting - Date Field - Why '=' works but '&lt;' doesn't?

Craig -

Just tried this on a sample MySQL db that I use for this sort of thing and it works just fine

My field is named hire_date and is of the DATE type.

Using your example yields the desired results:

hire_date < date()

My "condition" is to have the date in red text; sure enough, any "Hire Date" that is less than today is in red :)

I added a second condition where:

hire_date = date()

... and had it display white text on a green background; change the Hire Date to Today, and yep, there it is.

Must be something about how your date type is set.

Cheers!
Lyle

[soadyc]

Re: Conditional Formatting - Date Field - Why '=' works but '&lt;' doesn't?

Thanks Lyle,

Since you got this working would you mind reading the following to see if you can see where I'm going wrong..thanks.

Maybe it's just my lack of understanding on where I should be putting this condition..

Completiondate = '12/08/2012' works if I put it in the Client Side Properties => Conditional Style; but doesn't if I put in the Display Settings => Conditional Style
Completiondate < '12/08/2012' works if I put it in the Client Side Properties => Conditional Style; but doesn't if I put in the Display Settings => Conditional Style

Completiondate = date() works if I put it in the Display Settings => Conditional Style; but doesn't if I put in the Client Side Properties => Conditional Style

If I try Completiondate < date() in the Display Settings => Conditional Style I get the following error:

Error executing conditional formatting code. Check to see that all fields referenced in conditional expressions are included in the Grid and that all conditional expressions are valid. Error reported was: rtc.vtbl.data("Completiondate")

The date() function doesn't work in the Client Side Properties => Conditional Style...

I've looked for other topics relating to the date format, the following thread suggests that I should try some Java; but I can't get this working..
http://msgboard.alphasoftware.com/al...nditional+date

BTW, I'm using an Alpha DB.
Cheers
Craig

[soadyc]

Re: Conditional Formatting - Date Field - Why '=' works but '&lt;' doesn't?

Also..

My field type is Date.

My display format in my grid is time("{grid.clientSideDateFormat}",<value>)

CS

[2ninerniner2]

Re: Conditional Formatting - Date Field - Why '=' works but '&lt;' doesn't?

What database are you using? I am using MySQL and the field is the "plain, vanilla" DATE type.

I am using the Display Settings > Conditional style

date1.JPGdate2.JPG

[soadyc]

Re: Conditional Formatting - Date Field - Why '=' works but '&lt;' doesn't?

I'm using an Alpha DB (.adb)

I have set my display format to be the same as yours but this hasn't helped.

[agillbb]

Re: Conditional Formatting - Date Field - Why '=' works but '&lt;' doesn't?

I've found that the easiest way to deal with conditional styles and dates is to use a javascript function.

For example, set up your conditional style as per the image (using the function call), and define a javascript function as per below:

function isTodayorFuture(DeadlineDate) {
  var TodayDate = new Date();
  var TestDate = new Date(DeadlineDate);
  if (TodayDate <= TestDate )  {
    return true;
  } else {
    return false;
  }
}

cb4.jpg

[soadyc]

Re: Conditional Formatting - Date Field - Why '=' works but '&lt;' doesn't?

Maybe a silly question, but do I add the code, etc as per you post, or do I need to change anything to be specific to my fields/grid?

[agillbb]

Re: Conditional Formatting - Date Field - Why '=' works but '&lt;' doesn't?

Paste the code as-is into the grid's javascript section.
You will need only to change the field "DeadlineDate" as shown in the image to your own fieldname.

[soadyc]

Re: Conditional Formatting - Date Field - Why '=' works but '&lt;' doesn't?

I believe I have added as per above (see screenshots), though I'm getting some odd results:

I'm in Aust so I'm using dd/mm/yyyy.

08/06/12 = Blue
09/06/12 = Red
20/12/11 = Blue
21/12/11 = Red
07/08/12 = Blue
11/08/12 = Red
12/08/12 = Red
13/08/12 = Red

[agillbb]

Re: Conditional Formatting - Date Field - Why '=' works but '&lt;' doesn't?

Seems to do with how the date is being parsed into the new Date object. I haven't tested, but you may need to re-organize your string:

function isTodayorFuture(InputDateString) {
  var TodayDate = new Date();

  var strDate = InputDateString; //dd/mm/yyyy format
  var arrDate = myDateString.value.split("/");
  var TestDate = new Date(arrDate[2], arrDate[1], arrDate[0]);  //create date object using Date(yyyy,m,d) arguments

  if (TodayDate <= TestDate )  {
    return true;
  } else {
    return false;
  }
}

[soadyc]

Re: Conditional Formatting - Date Field - Why '=' works but '&lt;' doesn't?

Thanks Andy,

Given my little knowledge of java I assume I need to change the 'InputDateString' with my field name 'CompletionDate'...


function isTodayorFuture(CompletionDate) {
var TodayDate = new Date();

var strDate = CompletionDate; //dd/mm/yyyy format
var arrDate = myDateString.value.split("/");
var TestDate = new Date(arrDate[2], arrDate[1], arrDate[0]); //create date object using Date(yyyy,m,d) arguments

if (TodayDate <= TestDate ) {
return true;
} else {
return false;
}
}


I've tried the above and get a script error...

It's 12:30am here so I'm heading to bed! I'll get back on tomorrow so please don't give up on me... I sooooo very appreciate your help!

[agillbb]

Re: Conditional Formatting - Date Field - Why '=' works but '&lt;' doesn't?

No, you don't need to change this variable name within the function - it is an arbitrary name (alias) which gets passed your variable.
But what you tried would still have work as I suggested - which obviously is not right for your system.

Try some alerts to see what is going on....

function isTodayorFuture(CompletionDate) {
var TodayDate = new Date();

alert(CompletionDate); //this will show you the value of the string, then you can parse it properly below

var strDate = CompletionDate; // check to see what format it is in - may not be dd/mm/yyyy format
var arrDate = myDateString.value.split("/"); // make an array out of the numbers that lie between the delimeter
var TestDate = new Date(arrDate[2], arrDate[1], arrDate[0]); //create date object using Date(yyyy,m,d) arguments

//the date comparison below will compare the number of milliseconds of each date since 1 Jan 1970

if (TodayDate <= TestDate ) {
return true;
} else {
return false;
}
}