Problem With HIghlighting Formula

[paulcook]

I am using CR 8.5 and I am attempting to higlight certain records in a report that meet certain criteria.

The report is composed of clients who fall into two catergories "Older" and "Younger". The younger clients need to have a goal set while the older clients do not. I have a formula field set up to show whether the client is "Older" or "Younger", @older_younger_youth, then I have a formula in the Section Expert>Details>Color formula area that goes like this...

If {youth_goal.dt_set} + 365 < CurrentDate
And IsNull({youth_goal.result_cd})
And {@older_younger_youth} = &quot;Younger&quot; 
Then Yellow
Else NoColor

Now without the

And {@older_younger_youth} = "Younger"

line the formula works as advertised but when I add that line in, the results are the same. If I am correct that should only highlight lines that have Younger in the the formula field named @older_younger_youth.

Where am I going wrong?

TIA,
Paul

 

[synapsevampire]

I assume that the results are the same means that you get no highlighting.


Seems pretty simple, that formula isn't returning what you think it is, but you didn't share the formula so I can't help.

Place the @older_younger_youth formula alongside this formula so hat you can see what's in there.

-k

 

[paulcook]

synapsevampire,

I apologize for not making my post clear enough.

Actually the highlighting formula works fine without the

And {@older_younger_youth} = "Younger"

line. That is all clients whose youth_goal.dt_set is over one year old and who youth_goal.result_cd is null are highlighted. When I add the

And {@older_younger_youth} = "Younger"

line the results are the same as without that line. My goal is that only the youth who are "Younger" should be highlighted.

The formula for the formula field @older_younger_youth is....

LeadingCaps (If {youth_goal.combd_wia_eligy_cd} like ["*F*", "*I*"] Then "Younger"
Else
If {youth_goal.combd_wia_eligy_cd} like ["*G*", "*J*"] Then "Older")

The highlighting formula is...

If {youth_goal.dt_set} + 365 < CurrentDate
And IsNull({youth_goal.result_cd})
And {@older_younger_youth} = &quot;Younger&quot; 
Then Yellow
Else NoColor

I set up a formula field to test the

@older_younger_youth

formula. I used this formula,

IF {@older_younger_youth} = "Younger"
Then "True"
Else
IF {@older_younger_youth} = "Older"
Then "False"

and the records that are "Younger" had the result of True and the records that are "Older" had a result of False. So I know that the

@older_younger_youth

formula is returning "Younger" and "Older".

Thanks,
Paul

 

[Turkbear]

Hi, Try another combo formula:

If ({youth_goal.dt_set} + 365 < CurrentDate)
And (IsNull({youth_goal.result_cd}))
And ({@older_younger_youth} = &quot;Younger&quot;) 
Then True
Else False

See what happens when all 3 are tested together.
Any True?

 

[paulcook]

Turkbear,

That's it! If they Older youth didn't have a goal set then the first IF statement is null also. The test formula that you suggested which was my highlighting formula changed a little and all older clients where null and younger clients with overdue goals where true and all others where false.

So I have to add another check in there to check for clients without any goals set.

Thanks to all that helped out.

Paul

 

[synapsevampire]

Glad that this was resolved.

BTW, when testing a formula, don't create another formula to test it, just drop it in the report...

-k

 

[paulcook]

I am about ready to give up on this report.

I included an IF statement to exclude those records with no goal set date

Here is the formula I am using..

If IsDate(ToText({youth_goal.dt_set}))
And {youth_goal.dt_set} + 365 < CurrentDate
And IsNull({youth_goal.result_cd})
And {@older_younger_youth} = &quot;Younger&quot;
Then Yellow
Else NoColor

This should highlight records where all of these conditions are true:
Record has a youth_goal.dt_set, no older youth have one.
Record has a youth_goal.dt_set more than 365 days ago.
Record has no youth_goal.result_cd.
Record has @older_younger_youth formula field with a result of "Younger".

It doesn't work I still get all "Older" youth who have no youth_goal.dt_set.

I set up a formula field with this formula...

If IsDate(ToText({youth_goal.dt_set}))
And {youth_goal.dt_set} + 365 < CurrentDate
And IsNull({youth_goal.result_cd})
And {@older_younger_youth} = &quot;Younger&quot;
Then &quot;True&quot;

And the only field that I get a result of True is the one youth that should be highlighted. As soon as I place this formula in Detail>Format Section>Color>Formula Editor with the change of

Then Yellow Else NoColor

, Like the one at the top of this post. I once again get the "Older" youth higlighted in addition to the one I expect.

Thanks for you patience and assistance.

Paul

 

[lbass]

You might try something like:

If (IsNull({youth_goal.result_cd}) or
trim({youth_goal.result_cd}) = "&quot and
(Not isnull({youth_goal.dt_set}) or
{youth_goal.dt_set} <> date(0,0,0))and
{youth_goal.dt_set} + 365 < CurrentDate and
{@older_younger_youth} = &quot;Younger&quot;
Then Yellow
Else NoColor

If {youth_goal.result_cd} is a number, not a string, eliminate the trim and replace the &quot;&quot; with 0.

-LB

 

[synapsevampire]

Have you bothered to place the {@older_younger_youth} formula alongside yet so that you see what it's returning?

-k

 

[paulcook]

synapsevampire,

That formula is in my report. It returns what I expect. I cannot figure out why I cannot exclude those records that return &quot;Older&quot; from being higlighted.

lbass,

I get the same results with your formula.

I just can't figure it out.

Paul

 

[paulcook]

To all that helped,

I got it to work. I don't understand why but when I changed the order of the IF statements it worked. I moved the

{@older_younger_youth} = "Younger"

line to the top and that solved the problem.

This is how it looked when it worked...

IF {@older_younger_youth} = "Younger"
And IsDate(ToText({youth_goal.dt_set}))
And {youth_goal.dt_set} + 365 < CurrentDate
And IsNull({youth_goal.result_cd})
Then Yellow
Else NoColor

It didn't work this way...

If IsDate(ToText({youth_goal.dt_set}))
And {youth_goal.dt_set} + 365 < CurrentDate
And IsNull({youth_goal.result_cd})
And {@older_younger_youth} = &quot;Younger&quot;
Then Yellow
Else NoColor

Why does the order of the IF statements effect?

Thanks,

Paul

 

[tektipdjango]

I think there's a problem with the {youth_goal.dt_set} field which sometimes isnull or not a date.

So the formulas
IsDate(ToText({youth_goal.dt_set}))
And
{youth_goal.dt_set} + 365 < CurrentDate

are misleading Crystal

You'd better write

if
isnull({youth_goal.dt_set})
then
noColor
else
(
if
isdate(ToText({youth_goal.dt_set}))
then
(
if
{youth_goal.dt_set} < CurrentDate - 365
And IsNull({youth_goal.result_cd})
And {@older_younger_youth} = "Younger"
Then
Yellow
else
noColor
)
Else
NoColor
)

PS : it's a good pratice for queries to put the fields alone and the operations on the other side (left).
so I modifed
{youth_goal.dt_set} + 365 < CurrentDate
to
{youth_goal.dt_set} < CurrentDate - 365

Here it's not important but in selection formulas it will be the difference for the SQL to run on the server a lot quicker if there's an index on the field