- Crystal Reports
Highlight Formula for Empty Date Fields Older Than 5 Days Today
I must be missing something in my logic that I don't see and need help. I am writing a highlighting formula on a date field. There are two dates involved.
DateOne = in our system
DateTwo = left our system
The formula looks at DateTwo to see if there is a date there. If there is no date there, and it has been in our system for more than 5 days it should highlight red on the empty field.
I can get it to work if I check for the date field being empty only. However, if I check to see if the field is empty and it's been here for more than 5 days, nothing gets highlighted.
Here's my formula:
If isNull({DateTwo}) and ({DateOne} < DateAdd("d",-5, CurrentDate)) Then "X"
Can anyone see the logic I am missing?
Crystal Reports XI
Thanks
Joe
DateOne = in our system
DateTwo = left our system
The formula looks at DateTwo to see if there is a date there. If there is no date there, and it has been in our system for more than 5 days it should highlight red on the empty field.
I can get it to work if I check for the date field being empty only. However, if I check to see if the field is empty and it's been here for more than 5 days, nothing gets highlighted.
Here's my formula:
If isNull({DateTwo}) and ({DateOne} < DateAdd("d",-5, CurrentDate)) Then "X"
Can anyone see the logic I am missing?
Crystal Reports XI
Thanks
Joe
Are you using the HIGHLIGHTING EXPERT?
Create your formula
Name - HighlightOld
If isNull({DateTwo}) and ({DateOne} < DateAdd("d",-5, CurrentDate)) Then
'Y'
Else
'N'
Right click the field
Click HIGLIGHTING EXPERT
Click NEW
In the VALUE OF dropdown select the formula
Set the comparison to IS EQUAL TO
Set the value for comparison (bottom dropdown) to Y
Select the BACKGROUND COLOR
Click OK
You can do it in the FORMAT FIELD - BORDER TAB
Add a formula to the BACKGROUND
If isNull({DateTwo}) and ({DateOne} < DateAdd("d",-5, CurrentDate)) Then
crRed
mlmcc
Create your formula
Name - HighlightOld
If isNull({DateTwo}) and ({DateOne} < DateAdd("d",-5, CurrentDate)) Then
'Y'
Else
'N'
Right click the field
Click HIGLIGHTING EXPERT
Click NEW
In the VALUE OF dropdown select the formula
Set the comparison to IS EQUAL TO
Set the value for comparison (bottom dropdown) to Y
Select the BACKGROUND COLOR
Click OK
You can do it in the FORMAT FIELD - BORDER TAB
Add a formula to the BACKGROUND
If isNull({DateTwo}) and ({DateOne} < DateAdd("d",-5, CurrentDate)) Then
crRed
mlmcc
mlmcc's suggestions may work, but in case they don't, and just out of curiosity ...
When you said "highlight red on the empty field", what did you mean by "empty field"?
If you were trying to change the fg or bg color on the DateTwo field when it's null, that won't work. When a field is null, CR puts nothing (no spaces or anything like that) in that report field, so the color won't show.
If that's not what you were trying to do, what "empty field" were you trying to change, and how were you changing the color?
I don't see anything wrong with the logic in your formula, but you didn't say what you were doing with it. You should be able to use a formula like that to produce a value ("X", in your case), put that on the report, and set the color for that field (ie. what mlmcc suggested).
Also, just to make sure, are you absolutely sure that the report data included records that met your criteria (DateTwo is null and DateOne is more than 5 days ago)?
James
When you said "highlight red on the empty field", what did you mean by "empty field"?
If you were trying to change the fg or bg color on the DateTwo field when it's null, that won't work. When a field is null, CR puts nothing (no spaces or anything like that) in that report field, so the color won't show.
If that's not what you were trying to do, what "empty field" were you trying to change, and how were you changing the color?
I don't see anything wrong with the logic in your formula, but you didn't say what you were doing with it. You should be able to use a formula like that to produce a value ("X", in your case), put that on the report, and set the color for that field (ie. what mlmcc suggested).
Also, just to make sure, are you absolutely sure that the report data included records that met your criteria (DateTwo is null and DateOne is more than 5 days ago)?
James
Good point James. If the value is NULL you may not be able to highlight it.
I tested my formulas on a report but all fields had data.
mlmcc
I tested my formulas on a report but all fields had data.
mlmcc
Thanks both of you. I got called away on an family emergency and didn't have time to reply.
To help answer both questions. in my report I created a new formula and called it "NullDate" here is the actual formula but I edited the table/field names:
If isNull({DateTwo}) AND ({DateOne} < DateAdd("d", -5, CurrentDate)) Then "X"
Then I placed the "NullDate" field over the top of the "DateTwo" field that exists on the report.
Then on the "NullDate" field in the report I right clicked and selected the Highlighting Expert and set the "Value of:" selection in the Item editor area to :
this field --------> is equal to ----------> X
Below that I set the Font Color and background choices to both be Red.
Like I said the first part of the formula by itself works just fine. The second part of the formula by itself highlights everything red only if DateTwo has something in it (not Null) but it doesn't look at DateOne with the < -5 days from now.
I'm going to play some more on this as it must be something else that I'm missing.
Thank you both for confirming my logic is correct. I just need to see where else I'm missing.
To help answer both questions. in my report I created a new formula and called it "NullDate" here is the actual formula but I edited the table/field names:
If isNull({DateTwo}) AND ({DateOne} < DateAdd("d", -5, CurrentDate)) Then "X"
Then I placed the "NullDate" field over the top of the "DateTwo" field that exists on the report.
Then on the "NullDate" field in the report I right clicked and selected the Highlighting Expert and set the "Value of:" selection in the Item editor area to :
this field --------> is equal to ----------> X
Below that I set the Font Color and background choices to both be Red.
Like I said the first part of the formula by itself works just fine. The second part of the formula by itself highlights everything red only if DateTwo has something in it (not Null) but it doesn't look at DateOne with the < -5 days from now.
I'm going to play some more on this as it must be something else that I'm missing.
Thank you both for confirming my logic is correct. I just need to see where else I'm missing.
If you set the font and bg to both be red, then the "X" won't be visible. That seemed odd to me at first, but maybe that's what you wanted.
Putting that issue aside for the moment ...
You said that you put the NullDate formula over the DateTwo field. Are you sure that it's "in front" of the DateTwo field? If not, you can right-click on one of the fields and "move" it to the front or back (depending on the field).
It might be helpful to move the NullDate formula somewhere else (or copy that field and paste it somewhere else on the report), so that you can see that field without any possible "interference" from the DateTwo field.
James
The second part of the formula by itself highlights everything red only if DateTwo has something in it (not Null) ...That sounds wrong. As far as I can tell, you should only be getting red when DateTwo is null. I don't see how you could be getting red when DateTwo is not null.
Putting that issue aside for the moment ...
You said that you put the NullDate formula over the DateTwo field. Are you sure that it's "in front" of the DateTwo field? If not, you can right-click on one of the fields and "move" it to the front or back (depending on the field).
It might be helpful to move the NullDate formula somewhere else (or copy that field and paste it somewhere else on the report), so that you can see that field without any possible "interference" from the DateTwo field.
James
Hi James,
To answer you question; what I am trying to do is highlight the spot Red where the DateTwo field is displayed when an item hasn't left our inventory yet (DateTwo = date it went out, if empty it still here) and we have had it for more than 5 days.
The X is just being used as a marking for the formatting. (Hence if the date is still empty and more than 5 days, color the NullDate field all red)
Yes, I used the wrong terms, I meant in front of not top so the area now shows red.
I did move the NullDate field to the side of DateTwo so I can see both.
I'm thinking it some other part of my report that is causing it and I'm reviewing that now.
Thanks
Joe
To answer you question; what I am trying to do is highlight the spot Red where the DateTwo field is displayed when an item hasn't left our inventory yet (DateTwo = date it went out, if empty it still here) and we have had it for more than 5 days.
The X is just being used as a marking for the formatting. (Hence if the date is still empty and more than 5 days, color the NullDate field all red)
Yes, I used the wrong terms, I meant in front of not top so the area now shows red.
I did move the NullDate field to the side of DateTwo so I can see both.
I'm thinking it some other part of my report that is causing it and I'm reviewing that now.
Thanks
Joe
A simple diagnostic step would be to create two formulas to test your conditions separately.
If isNull({DateTwo}) Then "Y"
If ({DateOne} < DateAdd("d", -5, CurrentDate)) Then "Z"
Put those on the report to check your two conditions.
James
If isNull({DateTwo}) Then "Y"
If ({DateOne} < DateAdd("d", -5, CurrentDate)) Then "Z"
Put those on the report to check your two conditions.
James
So I've done some testing and both parts of the formula work correctly when they are separated. It's just when combined that they fail.
This report uses two different tables that are joined to produce the results. My formula with the isNull piece looks at the entries on one table, while the Date piece of the formula looks at entries on a different table.
Is my issue really the fact that I can't have the system look at two different tables entries in a single formula?
This report uses two different tables that are joined to produce the results. My formula with the isNull piece looks at the entries on one table, while the Date piece of the formula looks at entries on a different table.
Is my issue really the fact that I can't have the system look at two different tables entries in a single formula?
So, if you put all three of these formulas on the report, in the same section ...
If isNull({DateTwo}) Then "Y"
If ({DateOne} < DateAdd("d", -5, CurrentDate)) Then "Z"
If isNull({DateTwo}) AND ({DateOne} < DateAdd("d", -5, CurrentDate)) Then "X"
... you get some records where you get "Y" and "Z" from the first two formulas, but don't get "X" from the third?
I don't see how that's possible.
If that's what's happening, can you copy&paste your exact formulas here, to make sure that nothing is being lost in translation? And exactly where are you putting those formulas?
James
If isNull({DateTwo}) Then "Y"
If ({DateOne} < DateAdd("d", -5, CurrentDate)) Then "Z"
If isNull({DateTwo}) AND ({DateOne} < DateAdd("d", -5, CurrentDate)) Then "X"
... you get some records where you get "Y" and "Z" from the first two formulas, but don't get "X" from the third?
I don't see how that's possible.
If that's what's happening, can you copy&paste your exact formulas here, to make sure that nothing is being lost in translation? And exactly where are you putting those formulas?
James
Hi James,
Here's what I believe is going on.
This data I am trying to get to work is actually coming from a subreport inside this primary one. The primary report passes an item number through the subreport and the subreport then uses that item number to search the database to see if it was checked out of our system and returns the date that it was. If it wasn't checked out then no record is returned and the date is blank. I'm doing it this way because the table that tracks the dates and movements in our system has many entries for each item number and I only want to see when it was checked out of our system.
The isNull piece will work fine since there's no record and it can see that. However the date compare piece of the formula isn't going to work because it actually didn't return the record so it can see what the DateOne field was for a comparison.
I think I'm getting too bogged down in the weeds trying to highlight some dates and it isn't worth it on this report.
I'm going to abandon this and tell the requester that due to the table structure, it can't be done.
Thanks
Joe
Here's what I believe is going on.
This data I am trying to get to work is actually coming from a subreport inside this primary one. The primary report passes an item number through the subreport and the subreport then uses that item number to search the database to see if it was checked out of our system and returns the date that it was. If it wasn't checked out then no record is returned and the date is blank. I'm doing it this way because the table that tracks the dates and movements in our system has many entries for each item number and I only want to see when it was checked out of our system.
The isNull piece will work fine since there's no record and it can see that. However the date compare piece of the formula isn't going to work because it actually didn't return the record so it can see what the DateOne field was for a comparison.
I think I'm getting too bogged down in the weeds trying to highlight some dates and it isn't worth it on this report.
I'm going to abandon this and tell the requester that due to the table structure, it can't be done.
Thanks
Joe
I've been assuming that DateOne and DateTwo were two different fields (eg. an entry date field and a completion date field). But it sounds like maybe both dates are in the same field (in different records). If so, that would complicate things.
IAC, if you can't get both values (the "in system" date and the null "left system" date) at the same time, for whatever reason, then that would be a problem.
James
IAC, if you can't get both values (the "in system" date and the null "left system" date) at the same time, for whatever reason, then that would be a problem.
James
