Technical Call logs older than 3 working days 2

[Tabingz]

Hi to everyone. My name is Thomas

This is my first post and I am not a Crystal expert.

I work for a IT company and have to work the support help desk during the the week.

If a call goes on over 3 days it has be highlighted. I have written a Crystal report to display calls open older than 3 days. The trouble with this is it includes the weekends.

I need a way of displaying calls older than three working days.

I have searched these forums and the web but can't get my head around the method to achive this.

Please help.

 

[ShortyA]

You could use a formula to calculate 3 working days after the call is opened and then amend your record selection formula to only include those that are over 3.

If you go to

http://www.kenhamady.com/form17.html

Ken provides a formula to add x number of business days onto a date value.

Hope this addresses your problem.

 

[skries]

In the Font Format Formula editor for the date field on the report you can enter the following


if today - CurrentFieldValue > 3 and
WeekdayName (DayOfWeek ({your_referenced_date_field}))
in["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"] then crred else crblack

//Enter whatever your work days are in the array


Hope this helps.

 

[Tabingz]

Thank You for youe help but...

while added > add

is not working. "the remaining test does not appear to be part of this formula" error is occcuring on that line.

Thanks Thomas

 

[ShortyA]

Please can you post a copy of the exact syntax you have used.

 

[Tabingz]

WhileReadingRecords;
DateVar Array Holidays;
DateVar Target:={@StartDate}; //Put your field name in here
NumberVar Add:= -3; // Put in the number of days to subtract (a negative number)
NumberVar Added := 0;

WHILE Added > Add
Do (target := target -1;
if dayofweek (target) in 2 to 6 and not (target in holidays)
then Added:=Added-1
else Added:=Added);
Target

 

[skries]

Sounds like a minor syntax error you are receiving.

Display what you have done thus far.

 

[ShortyA]

I have copied your syntax and replaced {@StartDate} with a date field of my own and it works fine. Is {@StartDate} a date-type variable and does it have a value when the records are being read in (the formula above is processed whilst the records are being read so not sure how it would handle a print-time formula). Are you able to provide more info on what {@StartDate} looks like ?

 

[Tabingz]

Thanks for the replies guys,

I have replaced the field with {tblTechnicalCallLog.CallDate}. It is in the datetime format

 

[ShortyA]

Try amending the business days formula to have :

DateVar Target:=datevalue({tblTechnicalCallLog.CallDate});

 

[Tabingz]

I get the following error.

"A number, currency amount, boolean or string is expected here."

I copied the formula from Ken Hamady so I'm unsure why it is not working

 

[ShortyA]

I copied the formula too for consistency and the only problem occured when my value for Target wasn't a date value.

Where does it point to in the formula when the error message appears ? Also, you could try creating a test formula that contains:

datevalue({tblTechnicalCallLog.CallDate});

This would show you what the business days formula is seeing.

 

[Tabingz]

I got the message again..

"the remaining test does not appear to be part of this formula" error is occcuring on that line

 

[Tabingz]

sorry it point to just above

WHILE Added > Add


"the remaining text does not appear to be part of this formula" error is occcuring on that line"

the same occurs if i do a datevalue({tblTechnicalCallLog.CallDate});

 

[ShortyA]

Did the error message appear in the new test formula or in the original ? If in the original have you changed it as the previous msg referred to "A number, currency amount, boolan or string is expected here".


Does call date always have a value eg is it ever null or blank ? What does the test formula output when the report is run eg DD/MM/YY ?

 

[Tabingz]

The error occured in the new test formula too
The field {tblTechnicalCallLog.CallDate} always has data.

 

[ShortyA]

Amend the test formula as follows :

If isnull({tblTechnicalCallLog.CallDate}) then 'Null value'
else if {tblTechnicalCallLog.CallDate} ='' then 'Empty'
else 'Date'

Also, comment out the business days formula entirely (with // at the beginning of each line) so that you don't get any errors. Run the report and have a look at the Test formula's values in the reports. Look for any that are not 'Date'.

 

[Tabingz]

the test formula is asking for a date between ''

else if {tblTechnicalCallLog.CallDate} ='<asking for a date here>' then 'Empty'

i cant seem to win on this one

 

[ShortyA]

Of course...it needs a date value - my mistake.

If isnull({tblTechnicalCallLog.CallDate}) then 'Null value'
else if {tblTechnicalCallLog.CallDate} ='date(1901,01,01) then 'Low date'
else 'Date'

 

[Tabingz]

they are all DATE

Once again thanks for your help