formula error

[oshlonger]

I've created a formula that determines "Business Hours" for a report. I've used this formula in other reports successfully but when I try and run it now, I get this error: "A loop was evaluated more than the maximum # of times allowed"

I haven't changed anything and don't understand why I'm getting this error. Has anyone seen this before? Do you need to see the entire formula?

 

[dgillz]

Yes, please post the formula and an explanation of what you are attempting to accomplish with it. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[Naith]

There is a ceiling to the amount of times a loop can evaluate to prevent the report from looping umpteen times and causing a hanging-like state.

The maximum number of times a loop condition can evaluate per evaluation per formula is 30k. The field you're looping on has more than thirty thousand occurences, hence the error message you're getting.

You want to break your loop into two or more parts, or employ a 30,000 loop error handler. If you're unsure how to go about this, post your formula, and one of the folk here will hook you up.

Naith

 

[oshlonger]

thanks for the explanation. here is my formula. i've tried to add enough commments so you know what i'm trying to do.


//forumla for "time to first contact" business hours only
dateTimeVar Start:= {@date/time adj for GMT case entered};
dateTimeVar End:= {@date/time adj for GMT first contact};


timeVar defaultstarttime:= time(08,30,00);
timeVar defaultendtime:= time(18,00,00);

stringVar workingdays:= "-23456-";


//------------------------------------------------------------------------------

dateVar startdate:= date(Start);
timeVar starttime:= time(Start);

dateVar enddate:= Date(End);
timeVar endtime:= Time(End);

numberVar HrsInAWorkDay:= Hour (defaultendtime) - Hour (defaultstarttime);

//------------------------------------------------------------------------------

While wdaysClass1wdIsWorkDay (startdate, workingdays) =
FALSE do
(startdate:=startdate +1;
starttime:=defaultstarttime);

//------------------------------------------------------------------------------

//if startdate falls on a non working day, make startdate the next working day

While wdaysClass1wdIsWorkDay (enddate, workingdays) = FALSE do
(enddate:= enddate +1; endtime:=defaultstarttime);

//------------------------------------------------------------------------------

//if enddate falls on a non working day, make enddate the next working day

While wdaysClass1wdIsWorkDay (enddate,workingdays) = FALSE do
(enddate:= enddate +1; endtime:=defaultstarttime);

//------------------------------------------------------------------------------

//if endtime or starttime are not within business hours, assign it to the defaulttime

if endtime < defaultstarttime then
endtime:= defaultstarttime;

if endtime > defaultendtime then
endtime:= defaultendtime;

if starttime < defaultstarttime then
starttime:= defaultstarttime;

if starttime > defaultendtime then
starttime:= defaultendtime;

//------------------------------------------------------------------------------

//calculate totalworkdays

numbervar totaldays:=wdaysclass1wdnumworkdays(startdate,enddate, workingdays);

//------------------------------------------------------------------------------

// calculate hours in full days (subtract 1st and last day * hours in a workday)

numbervar hours_fulldays:= (totaldays - 2) * HrsInAWorkDay;

//------------------------------------------------------------------------------

//calculate hours in first day

numbervar hours_firstday:= hour(defaultendtime) - ((hour(starttime)*60) + minute(starttime))/60;

//------------------------------------------------------------------------------

//calculate hours in last day

numbervar hours_lastday:= (hour(endtime) + (minute(endtime)/60)) - hour(defaultstarttime);

//------------------------------------------------------------------------------

//business hour = fulldays + firstday + lastday

hours_fulldays + hours_firstday + hours_lastday;

 

[synapsevampire]

Consider creating a Periods table.

This has multiple uses, one of which would cover this need codelessly.

The table design would be based on the granularity required, which in this case appears to be daily, with a column for the number of work hours for each day.

To summarize it, you have a date for every day, with some attributes for each day (working, holiday, in your case the number of work hours, etc.).

Now you can join any table that has a date to this table, and have a wealth of data available, inclusive of reporting on days which your data doesn't provide.

This is a standard Data Warehousing technique.

Beats coding this sort of thing over and over.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[reportgirl]

How would I go about employing a 30,000 loop error handler, or breaking my loop into two or more parts?

 

[Naith]

You use an incrementing variable to keep count of the amount of loops, together with the Exit While statement to jump out of the loop when it's about to fall over.

e.g.

Local NumberVar KeepCount := 1;
While wdaysClass1wdIsWorkDay (enddate, workingdays) = FALSE 
do
(
If KeepCount = 29999
Then Exit While;
enddate:= enddate +1; endtime:=defaultstarttime;
KeepCount := KeepCount + 1;
)

If you're using Crystal9, you can set your own loop ceiling with Option Loop. (i.e. Option Loop 50000)

Naith