Conditional Formatting

[gennaroalpha7]

Hi -

I have a field, CallLog.CallStatus, and it's 'not equal to closed', but I want the tickets that are 'closed' from the (today) current date to only show on the report. How can that be accomplished without bringing up the closed tickets from days past?

Here is my code so far...

{CallLog.CallStatus} <> "Closed" and
{CallLog.Tracker} in ["ANTONJ83", "bermun01", "CAMCACX1", "CHAVEV60", "COBBS53", "DONESC72", "FLOREV02", "HEALEP20", "Heldet00", "HERREC86", "jauree01", "LIMM01", "mirelj91", "Perezm58", "phant27", "Poste54", "QUEZAM01", "RAVANR37", "RIGORAD1", "salesc01", "VAZQUD01"] and
Date ({CallLog.RecvdDate}) >= CurrentDate -547 and
not ({CallLog.KeyWord} in ["FIS", "TP Admin Defect", "TP Capture Defect", "TP Sales Defect", "TP Service Defect", "TP Teller Defect", "TPSS Defect"])

Conditional Formatting:

I also have a 'Count of CallLog.Tracker' field. I am trying to get that field to highlight in 'yellow' if it's over 40 and then to highlight in green if it's less then 40. Here is the code i have for that field and I can't get it to work.

if ({CallLog.Tracker}) >= "40" then crYellow
else crGreen

Thanks for your help.

Alpha7

 

[xxyyzz49]

For the first formula have you tried to strip it down to the essentials? Status = 'Closed' and received date equal to the currant date? Why the subtraction of 547 from current date?

For the second formula what does not working mean? Is the field yellow or is the field is green or something else is happening? What values are you seeing for CallLog.Tracker?

Pete

 

[Charliy]

For your second one try:
if val({CallLog.Tracker}) >= 40 then crYellow
else crGreen

 

[gennaroalpha7]

Hi -

The first part of the code subtracts the 547 days to get (tickets) records from roughly 1.5 years in the past, from the current date. But, what is excluded is tickets that are 'Closed'. I would like the report to somehow only show what tickets were 'Closed' on the currentdate, not in the past, as this part of the code shows...{CallLog.CallStatus} <> "Closed"

The second part of my call for help is:

using this code...if ({CallLog.Tracker}) >= "40" then crYellow
else crGreen

it doesn't work...everything is yellow. Am I using a wrong field to use this code on? The field I am using is 'Count of CallLog.Tracker'. It is a count field in the report footer area, not the detail area.

Thanks for your patience and help.

Alpha7

 

[pmax9999]

Assuming that the {CallLog.CallStatus} can be either "Open" or "Closed", it is not necessary to use the ({CallLog.RecvdDate}, and doing so is only going to omit cases that are still open but received more than 547 days ago.

If you what you are trying to do is track all cases that are either "Open", or that were "Closed" today, amend the Record Selection to:

(
	{CallLog.CallStatus} <> "Closed" or
	(
		{CallLog.CallStatus} = "Closed" and
		Date({CallLog.ClosedDate}) = CurrentDate
	)				
and
{CallLog.Tracker} in ["ANTONJ83", "bermun01", "CAMCACX1", "CHAVEV60", "COBBS53", "DONESC72", "FLOREV02", "HEALEP20", "Heldet00", "HERREC86", "jauree01", "LIMM01", "mirelj91", "Perezm58", "phant27", "Poste54", "QUEZAM01", "RAVANR37", "RIGORAD1", "salesc01", "VAZQUD01"] and
NOT({CallLog.KeyWord} in ["FIS", "TP Admin Defect", "TP Capture Defect", "TP Sales Defect", "TP Service Defect", "TP Teller Defect", "TPSS Defect"])

NOTE:, this requires a "Closed Date" from your database - I have assumed it is in the CallLog table, is called ClosedDate, and like your RecvdDate is a String rather than a Date field.

If this is not what you are trying to do, you will need to explain in more clear terms what you want.

As for the formatting, your most recent post suggests you want the colour based on the "... Count of {CallLog.Tracker} ..." which is very different to setting the colour based on the Value, which is what your code is doing. To implement this, create the Summary field (Count of {CallLog.Tracker}, right click on the summary field, select "Format Field", click on the "Font" tab, and enter the following code by clicking on X2 button beside "Color":

If      CurrentFieldValue >= 40 
Then    crYellow
Else    CrGreen

Again, if this is not what you are trying to do, please explain your requirements (and report structure) as clearly as possible.

Hope this helps.

Cheers
Pete

 

[gennaroalpha7]

Hi Pete -

As you recommended the code now looks like this, but when I check the code it says 'No errors found'. When I run the report the 'Date({CallLog.ClosedDate})' is highlighted in yellow and says 'Bad Date Format String'

(
	{CallLog.CallStatus} <> "Closed" or
	(
		{CallLog.CallStatus} = "Closed" and
		[highlight #FCE94F]Date({CallLog.ClosedDate})[/highlight] = CurrentDate
	))				






 and
not ({CallLog.KeyWord} in ["FIS", "TP Admin Defect", "TP Capture Defect", "TP Sales Defect", "TP Service Defect", "TP Teller Defect", "TPSS Defect"]) and
{CallLog.Tracker} in ["ANTONJ83", "bermun01", "CAMCACX1", "CHAVEV60", "COBBS53", "DONESC72", "FLOREV02", "HEALEP20", "Heldet00", "HERREC86", "jauree01", "LIMM01", "mirelj91", "Perezm58", "phant27", "Poste54", "QUEZAM01", "RAVANR37", "RIGORAD1", "salesc01", "VAZQUD01"]

The CallLog.RecvdDate and CallLog.ClosedDate are sting fields but produce a date when ran such as 2013-10-24


Thanks.

Alpha7

 

[pmax9999]

If the formula is accepted, and only fails when the report is run, I am almost certain this is because there is something other than a Date in the {CallLog.ClosedDate} column. This stems from poor database design that stores dates as strings.

Try the following code to check that the {CallLog.ClosedDate} can actually be interpreted as a date:


Try:

(
	{CallLog.CallStatus} <> "Closed" or
	(
		{CallLog.CallStatus} = "Closed" and
		IsDate({CallLog.ClosedDate}) and
		Date({CallLog.ClosedDate}) = CurrentDate
	)
)				
and
{CallLog.Tracker} in ["ANTONJ83", "bermun01", "CAMCACX1", "CHAVEV60", "COBBS53", "DONESC72", "FLOREV02", "HEALEP20", "Heldet00", "HERREC86", "jauree01", "LIMM01", "mirelj91", "Perezm58", "phant27", "Poste54", "QUEZAM01", "RAVANR37", "RIGORAD1", "salesc01", "VAZQUD01"] and
NOT({CallLog.KeyWord} in ["FIS", "TP Admin Defect", "TP Capture Defect", "TP Sales Defect", "TP Service Defect", "TP Teller Defect", "TPSS Defect"])

Cheers
Pete

 

[gennaroalpha7]

Hi Pete -

The code worked as smooth as a babies butt. I owe you a steak dinner and drink! Your a life saver Pete...thanks for your knowledge and patience.

Just one more question. There is a 'CallLog.Tracker' field that lists all the (trackers) owners of the tickets. Is it possible to bucket the trackers into two groups such as Level 1 and Level 2?

Sincerely

Alpa7

 

[pmax9999]

Thanks Apha7 - glad it helped.

In regard to your last question, is there anything in your database that identifies whether the trackers are level 1 or Level 2? If not, you would need to hardcode that detail in a formula, but every time a new tracker was added or they changed from a Level 1 or Level 2 that formula would need to be changed.



Cheers
Pete

 

[gennaroalpha7]

No, unfortunatly they don't differentiate level 1 or level 2 in the db.

As you indicate that would need to be hardcoded. Could you have two CallLog.Tracker columns, one for each level...is that possible in CR?

Would the formula be SQL or in Crystal Syntax? Would the formula be a command?

Thanks.

Alpha7

 

[pmax9999]

There are a few different ways to do it. I don't see any need to do it via a Command - a SQL Expression could be used but I don't see there is any real need to push the calculation back to the DB.

You could do something like this, that could be used to group the Tracker into Level 1/2:

[Code {@Level}]
If {CallLog.Tracker} in ["Bill", "Steve", "John"]
Then "Level 1"
Else "Level 2"
[/Code]

Or, if you wanted to list the Trackers under separate columns on the report, you could create two formulas:

[Code {Level1}]
If {CallLog.Tracker} in ["Bill", "Steve", "John"]
Then {CallLog.Tracker}
Else ""
[/Code]

[Code {Level2}]
If {CallLog.Tracker} in ["Mike", "Greg", "Frank"]
Then {CallLog.Tracker}
Else ""
[/Code]

Place both formulas on the report and the Tracker name would appear only in the column relevant to the Level.

Does this help?

Cheers
Pete

 

[gennaroalpha7]

Pete -

In following your direction for the second option above. Would I need to remove the existing 'CallLog.Tracker' column that lumps all trackers into one column such as the code above?

Thanks.

Alphaseven

 

[pmax9999]

Alphaseven

Whether you leave the {CallLog.Tracker} column on the report is up to you. Based on what I understand you are trying to do, but it makes sense to remove it.

Cheers
Pete

 

[gennaroalpha7]

Hi Pete -

Thanks for all your help. Have a good one and a relaxed evening.

Cheers.

Alpha7