Avatar of Joseph LaFlex
Joseph LaFlex
Flag for United States of America asked on

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
Crystal Reports

Avatar of undefined
Last Comment
Joseph LaFlex

8/22/2022 - Mon
Mike McCracken

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
James0628

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
Mike McCracken

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Joseph LaFlex

ASKER
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.
James0628

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.

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
Joseph LaFlex

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
James0628

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
Joseph LaFlex

ASKER
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?
James0628

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Joseph LaFlex

ASKER
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
James0628

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
ASKER CERTIFIED SOLUTION
Joseph LaFlex

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.