Does anyone know how to edit the Date table or any other way of improving date search speeds. I need to use it to split a week period to the appropriate working days for a production forecast, but I’ve discovered that it slows the whole process down considerably (about 10X slower since adding this code). Here’s what my code looks like=>> // Get Start Date EVALUATE(lvStartYear, FORMAT(fpWeekPeriod[1]) + FORMAT(fpWeekPeriod[2])); lvStartYear := lvStartYear + 2000; EVALUATE(lvStartWeek, FORMAT(fpWeekPeriod[3]) + FORMAT(fpWeekPeriod[4])); lvtblDate.RESET; lvtblDate.SETCURRENTKEY(lvtblDate.“Period Type”,lvtblDate.“Period Start”); lvtblDate.SETRANGE(lvtblDate.“Period Type”, lvtblDate.“Period Type”::Week); lvtblDate.SETFILTER(lvtblDate.“Period Start”, ‘%1…%2’, DMY2DATE(31, 12, lvStartYear - 1), DMY2DATE(31, 12, lvStartYear)); lvtblDate.SETRANGE(lvtblDate.“Period No.”, lvStartWeek); lvtblDate.FIND(’+’); lvStartDate := lvtblDate.“Period Start”; // Get End Date EVALUATE(lvEndYear, FORMAT(fpWeekPeriod[5]) + FORMAT(fpWeekPeriod[6])); lvEndYear := lvEndYear + 2000; EVALUATE(lvEndWeek, FORMAT(fpWeekPeriod[7]) + FORMAT(fpWeekPeriod[8])); lvtblDate.RESET; lvtblDate.SETCURRENTKEY(lvtblDate.“Period Type”); lvtblDate.SETRANGE(lvtblDate.“Period Type”, lvtblDate.“Period Type”::Week); lvtblDate.SETRANGE(lvtblDate.“Period No.”, lvEndWeek); lvtblDate.SETFILTER(lvtblDate.“Period End”, ‘%1…%2’, DMY2DATE(31, 12, lvEndYear - 1), CALCDATE(’+7D’,DMY2DATE(31, 12, lvEndYear))); lvtblDate.FIND(’+’); lvEndDate := lvtblDate.“Period End”; lvtblDate.RESET; lvtblDate.SETCURRENTKEY(lvtblDate.“Period Type”,lvtblDate.“Period Start”); lvtblDate.SETRANGE(lvtblDate.“Period Type”, lvtblDate.“Period Type”::Date); lvtblDate.SETFILTER(lvtblDate.“Period Start”, ‘%1…%2’, lvStartDate, lvEndDate); lvtblDate.SETFILTER(lvtblDate.“Period Name”, ‘<>%1&<>%2’, ‘Saturday’, ‘Sunday’); lvAllocQty := fptblPFE.“Forecast Quantity” DIV lvtblDate.COUNT; lvRemainderQty := fptblPFE.“Forecast Quantity” MOD lvtblDate.COUNT; lvDeductQty := ROUND(lvRemainderQty / lvtblDate.COUNT, 1, ‘>’); IF lvtblDate.FIND(’-’) THEN REPEAT lvtblToProdForecast.INIT; lvtblToProdForecast.TRANSFERFIELDS(fptblPFE); lvtblToProdForecast.“Entry No.” := lvtblToProdForecast.fGetNextEntryNo; lvtblToProdForecast.“From Entry No.” := gvTempEntryNo; lvtblToProdForecast.“Production Forecast Name” := fpPFName; IF (lvRemainderQty - lvDeductQty >= 0) THEN BEGIN lvtblToProdForecast.“Forecast Quantity” := lvAllocQty + lvDeductQty; lvtblToProdForecast.“Forecast Quantity (Base)” := lvAllocQty + lvDeductQty; lvtblToProdForecast.“Accumulative Production Value” := lvAllocQty + lvDeductQty; END // If (lvRemainderQty - lvDeductQty >= 0) ELSE BEGIN lvtblToProdForecast.“Forecast Quantity” := lvAllocQty; lvtblToProdForecast.“Forecast Quantity (Base)” := lvAllocQty; lvtblToProdForecast.“Accumulative Production Value” := lvAllocQty; END; // Else IF (lvRemainderQty > 0) THEN lvRemainderQty := lvRemainderQty - lvDeductQty; lvtblToProdForecast.“Forecast Date” := lvtblDate.“Period Start”; lvtblToProdForecast.“From Entry No.” := fptblPFE.“Entry No.”; lvtblToProdForecast.“EDI Date” := fptblPFE.“EDI Date”; lvtblToProdForecast.“Period Name” := lvtblDate.“Period Name”; fAddShippingLeadTime(lvtblToProdForecast); lvtblToProdForecast.INSERT(FALSE); UNTIL lvtblDate.NEXT = 0; (Sorry, didn’t paste so well) Anything to even slightly improve the speed would be great. BTW, thanks Nils for helping with my “Text Import Problem”, it works great now! [:D] Bradley
Bradley, haven’t looked into detail in your code, but you mention that you need to find out working days… wouldn’t the function DATE2DWY (Date) help to do the job without the Date table??? Just an idea… Saludos Nils p.s. “Text Import Problem”… you’re welcome [;)]
Your code is a little bit confusing. What exactly do you want to achieve ? The number of working days per year ? What does fpWeekPeriod contain ?
quote:
Your code is a little bit confusing.
… Thomas is right here, I guess the code can be improved, once knowing what exactly you want to achieve… E.g. the part “DMY2DATE(31, 12, lvStartYear - 1)” can rather be replaced by “CALCDATE(‘<-CY-1D>’,CurrentDate)” and you don’t have to do all the conversion with lvStartYear… Saludos Nils
And assign the “lvtblDate.COUNT” to a local integer variable once instead of referring to the original in three lines. It might also help to run the client Monitor in order to find out the time consuming operations [;)]
DWY2DATE looks as though it may be useful, but it still doesn’t help me to keep out Saturdays & Sundays. I did refine my code a bit so that I need not search for the end date of the week. Here is the refined version =>> // Get Start Date EVALUATE(lvStartYear, FORMAT(fpWeekPeriod[1]) + FORMAT(fpWeekPeriod[2])); lvStartYear := lvStartYear + 2000; EVALUATE(lvStartWeek, FORMAT(fpWeekPeriod[3]) + FORMAT(fpWeekPeriod[4])); lvtblDate.RESET; lvtblDate.SETCURRENTKEY(lvtblDate.“Period Type”,lvtblDate.“Period Start”); lvtblDate.SETRANGE(lvtblDate.“Period Type”, lvtblDate.“Period Type”::Week); lvtblDate.SETFILTER(lvtblDate.“Period Start”, ‘%1…%2’, DMY2DATE(31, 12, lvStartYear - 1), DMY2DATE(31, 12, lvStartYear)); lvtblDate.SETRANGE(lvtblDate.“Period No.”, lvStartWeek); lvtblDate.FIND(’+’); lvStartDate := lvtblDate.“Period Start”; lvtblDate.RESET; lvtblDate.SETCURRENTKEY(lvtblDate.“Period Type”,lvtblDate.“Period Start”); lvtblDate.SETRANGE(lvtblDate.“Period Type”, lvtblDate.“Period Type”::Date); lvtblDate.SETFILTER(lvtblDate.“Period Start”, ‘%1…%2’, lvStartDate, CALCDATE(’+1W-1D’,lvStartDate)); lvtblDate.SETFILTER(lvtblDate.“Period Name”, ‘<>%1&<>%2’, ‘Saturday’, ‘Sunday’); lvAllocQty := fptblPFE.“Forecast Quantity” DIV lvtblDate.COUNT; lvRemainderQty := fptblPFE.“Forecast Quantity” MOD lvtblDate.COUNT; lvDeductQty := ROUND(lvRemainderQty / lvtblDate.COUNT, 1, ‘>’); IF lvtblDate.FIND(’-’) THEN REPEAT lvtblToProdForecast.INIT; lvtblToProdForecast.TRANSFERFIELDS(fptblPFE); lvtblToProdForecast.“Entry No.” := lvtblToProdForecast.fGetNextEntryNo; lvtblToProdForecast.“From Entry No.” := gvTempEntryNo; lvtblToProdForecast.“Production Forecast Name” := fpPFName; IF (lvRemainderQty - lvDeductQty >= 0) THEN BEGIN lvtblToProdForecast.“Forecast Quantity” := lvAllocQty + lvDeductQty; lvtblToProdForecast.“Forecast Quantity (Base)” := lvAllocQty + lvDeductQty; lvtblToProdForecast.“Accumulative Production Value” := lvAllocQty + lvDeductQty; END // If (lvRemainderQty - lvDeductQty >= 0) ELSE BEGIN lvtblToProdForecast.“Forecast Quantity” := lvAllocQty; lvtblToProdForecast.“Forecast Quantity (Base)” := lvAllocQty; lvtblToProdForecast.“Accumulative Production Value” := lvAllocQty; END; // Else IF (lvRemainderQty > 0) THEN lvRemainderQty := lvRemainderQty - lvDeductQty; lvtblToProdForecast.“Forecast Date” := lvtblDate.“Period Start”; lvtblToProdForecast.“From Entry No.” := fptblPFE.“Entry No.”; lvtblToProdForecast.“EDI Date” := fptblPFE.“EDI Date”; lvtblToProdForecast.“Period Name” := lvtblDate.“Period Name”; fAddShippingLeadTime(lvtblToProdForecast); lvtblToProdForecast.INSERT(FALSE); UNTIL lvtblDate.NEXT = 0; fpWeekPeriod contains a week period expressed as YYWWYYWW (but for the purposes of this code, you can assume that it’s for just one week). I need to determine the actual working days that fall in that week and then spread the required quantity for that week over each working day. I hope that clears it up. As a side note can anyone tell me how to put my code in a “Text Memo” type thing as I’ve seen in other posts? [?] Thanks Bradley
Again: * use the Client Monitor to identify the bottleneck. * use a variable for your counting. Try the following filter which will work better for you [;)] DateFilter := ‘MON’+COPYSTR(fpWeekPeriod,3,2)+COPYSTR(fpWeekPeriod,1,2) + ‘…SUN’+COPYSTR(fpWeekPeriod,7,2)+COPYSTR(fpWeekPeriod,5,2); Then set the filter different lvtblDate.RESET; lvtblDate.SETCURRENTKEY(lvtblDate.“Period Type”,lvtblDate.“Period Start”); lvtblDate.SETRANGE(lvtblDate.“Period Type”, lvtblDate.“Period Type”::Date); lvtblDate.SETFILTER(lvtblDate.“Period Start”, DateFilter); lvtblDate.SETFILTER(lvtblDate.“Period No.”, ‘%1…%2’,1,5); and kill the code for determining the start and end date as you do not need them anymore. YYWWYYWW
Sorry, I wasn’t really thinking when I responded to the DWY2DATE idea(of course I can keep out Saturdays & Sundays!). I’m still not sure if it will work though, I need to think about it a bit more. Thanks Nils
Anyway, there is a different option to make your problem easier [;)] Just create a new table for Nonworking Days (I know there are some already). And use the days in there to do a minus on the total number of days in your filter. For that purpose you would not even touch the date table at all: EVALUATE(StartDate,‘MON’+COPYSTR(fpWeekPeriod,3,2)+COPYSTR(fpWeekPeriod,1,2)); EVALUATE(EndDate,‘SUN’+COPYSTR(fpWeekPeriod,7,2)+COPYSTR(fpWeekPeriod,5,2)); TotalNoOfDays := EndDate - StartDate + 1; NonWorkingDays.SETRANGE(Date,StartDate,EndDate); NoOfWorkingDays := TotalNoOfDays - NonWorkingDays.Count;
In this case you can also cater for company holiday, or public holiday, planned maintenance and so on.
Thanks Thomas, I’ll try that and see what I can turn up. Just so that we’re clear, please feel free to turn my code into useless mush and call me an idiot, so long as I can improve my skills. 
Thanks Brad
Maybe you can check the form “Base Calendar Card” and get some ideas from there [:D]
Thanks guys, I no longer need to touch the Date table for weekperiods of just one week(which makes our client very happy [^] )! I went for a combo calendar and DWY2DATE solution. Here it is if you’re interested… EVALUATE(lvStartYear, COPYSTR(fpWeekPeriod,1,2)); lvStartYear := lvStartYear + 2000; EVALUATE(lvStartWeek, COPYSTR(fpWeekPeriod,3,2)); EVALUATE(lvEndYear, COPYSTR(fpWeekPeriod,5,2)); lvEndYear := lvEndYear + 2000; EVALUATE(lvEndWeek, COPYSTR(fpWeekPeriod,7,2)); IF (lvStartYear = lvEndYear) AND (lvStartWeek = lvEndWeek) THEN BEGIN FOR X := 1 TO 5 DO lvDay[X] := DWY2DATE(X,lvStartWeek,lvStartYear); lvtblCalenderChange.RESET; lvtblCalenderChange.SETCURRENTKEY(“Base Calendar Code”,“Recurring System”,Date,Day); lvtblCalenderChange.SETRANGE(“Base Calendar Code”,‘SACALENDAR’); lvtblCalenderChange.SETRANGE(“Recurring System”,lvtblCalenderChange.“Recurring System”::“Annual Recurring”); lvtblCalenderChange.SETRANGE(Date,lvDay[1],lvDay[5]); lvtblCalenderChange.SETRANGE(Nonworking,TRUE); lvtblCalenderChange.FIND(’-’); REPEAT FOR X := 1 TO 5 DO IF lvtblCalenderChange.Date = lvDay[X] THEN BEGIN lvDay[X] := 0D; END; UNTIL lvtblCalenderChange.NEXT = 0; lvWorkDays := 5 - lvtblCalenderChange.COUNT; IF lvWorkDays <= 0 THEN ERROR(‘There are no working days defined for week %1 in year %2!’,lvStartWeek,lvStartYear); lvAllocQty := fptblPFE.“Forecast Quantity” DIV lvWorkDays; lvRemainderQty := fptblPFE.“Forecast Quantity” MOD lvWorkDays; lvDeductQty := ROUND(lvRemainderQty / lvWorkDays, 1, ‘>’); FOR X := 1 TO 5 DO BEGIN IF lvDay[X] <> 0D THEN BEGIN lvtblToProdForecast.INIT; lvtblToProdForecast.TRANSFERFIELDS(fptblPFE); lvtblToProdForecast.“Entry No.” := lvtblToProdForecast.fGetNextEntryNo; lvtblToProdForecast.“From Entry No.” := gvTempEntryNo; lvtblToProdForecast.“Production Forecast Name” := fpPFName; IF (lvRemainderQty - lvDeductQty >= 0) THEN BEGIN lvtblToProdForecast.“Forecast Quantity” := lvAllocQty + lvDeductQty; lvtblToProdForecast.“Forecast Quantity (Base)” := lvAllocQty + lvDeductQty; lvtblToProdForecast.“Accumulative Production Value” := lvAllocQty + lvDeductQty; END // If (lvRemainderQty - lvDeductQty >= 0) ELSE BEGIN lvtblToProdForecast.“Forecast Quantity” := lvAllocQty; lvtblToProdForecast.“Forecast Quantity (Base)” := lvAllocQty; lvtblToProdForecast.“Accumulative Production Value” := lvAllocQty; END; // Else IF (lvRemainderQty > 0) THEN lvRemainderQty := lvRemainderQty - lvDeductQty; lvtblToProdForecast.“Forecast Date” := lvDay[X]; lvtblToProdForecast.“From Entry No.” := fptblPFE.“Entry No.”; lvtblToProdForecast.“EDI Date” := fptblPFE.“EDI Date”; fAddShippingLeadTime(lvtblToProdForecast); lvtblToProdForecast.INSERT(FALSE); END; END; END // IF (lvStartYear = lvEndYear) AND (lvStartWeek = lvEndWeek) ELSE
LOL, so that’s how it works… Next time I’ll put it in the right place. Thanks Brad
Readable code =>> EVALUATE(lvStartYear, COPYSTR(fpWeekPeriod,1,2)); lvStartYear := lvStartYear + 2000; EVALUATE(lvStartWeek, COPYSTR(fpWeekPeriod,3,2)); EVALUATE(lvEndYear, COPYSTR(fpWeekPeriod,5,2)); lvEndYear := lvEndYear + 2000; EVALUATE(lvEndWeek, COPYSTR(fpWeekPeriod,7,2)); IF (lvStartYear = lvEndYear) AND (lvStartWeek = lvEndWeek) THEN BEGIN FOR X := 1 TO 5 DO lvDay[X] := DWY2DATE(X,lvStartWeek,lvStartYear); lvtblCalenderChange.RESET; lvtblCalenderChange.SETCURRENTKEY("Base Calendar Code","Recurring System",Date,Day); lvtblCalenderChange.SETRANGE("Base Calendar Code",'SACALENDAR'); lvtblCalenderChange.SETRANGE("Recurring System",lvtblCalenderChange."Recurring System"::"Annual Recurring"); lvtblCalenderChange.SETRANGE(Date,lvDay[1],lvDay[5]); lvtblCalenderChange.SETRANGE(Nonworking,TRUE); lvtblCalenderChange.FIND('-'); REPEAT FOR X := 1 TO 5 DO IF lvtblCalenderChange.Date = lvDay[X] THEN BEGIN lvDay[X] := 0D; END; UNTIL lvtblCalenderChange.NEXT = 0; lvWorkDays := 5 - lvtblCalenderChange.COUNT; IF lvWorkDays <= 0 THEN ERROR('There are no working days defined for week %1 in year %2!',lvStartWeek,lvStartYear); lvAllocQty := fptblPFE."Forecast Quantity" DIV lvWorkDays; lvRemainderQty := fptblPFE."Forecast Quantity" MOD lvWorkDays; lvDeductQty := ROUND(lvRemainderQty / lvWorkDays, 1, '>'); FOR X := 1 TO 5 DO BEGIN IF lvDay[X] <> 0D THEN BEGIN lvtblToProdForecast.INIT; lvtblToProdForecast.TRANSFERFIELDS(fptblPFE); lvtblToProdForecast."Entry No." := lvtblToProdForecast.fGetNextEntryNo; lvtblToProdForecast."From Entry No." := gvTempEntryNo; lvtblToProdForecast."Production Forecast Name" := fpPFName; IF (lvRemainderQty - lvDeductQty >= 0) THEN BEGIN lvtblToProdForecast."Forecast Quantity" := lvAllocQty + lvDeductQty; lvtblToProdForecast."Forecast Quantity (Base)" := lvAllocQty + lvDeductQty; lvtblToProdForecast."Accumulative Production Value" := lvAllocQty + lvDeductQty; END // If (lvRemainderQty - lvDeductQty >= 0) ELSE BEGIN lvtblToProdForecast."Forecast Quantity" := lvAllocQty; lvtblToProdForecast."Forecast Quantity (Base)" := lvAllocQty; lvtblToProdForecast."Accumulative Production Value" := lvAllocQty; END; // Else IF (lvRemainderQty > 0) THEN lvRemainderQty := lvRemainderQty - lvDeductQty; lvtblToProdForecast."Forecast Date" := lvDay[X]; lvtblToProdForecast."From Entry No." := fptblPFE."Entry No."; lvtblToProdForecast."EDI Date" := fptblPFE."EDI Date"; fAddShippingLeadTime(lvtblToProdForecast); lvtblToProdForecast.INSERT(FALSE); END; END; END // IF (lvStartYear = lvEndYear) AND (lvStartWeek = lvEndWeek) ELSE
You can use the EVALUATE command to evaluate a date expression much easier than you do in your code: EVALUATE(lvStartDate,‘MON1705’) will evaluate to the Monday of week 17 in the year 2005. EVALUATE(lvEndDate,‘SUN1805’) will evaluate to the Sunday of week 18 in the year 2005. The total number of days between the two dates + 1 is the number of days in the period lvWorkDays := (ABS(lvEndDate-lvStartDate) + 1)/7*5; This excludes the weekends already for you. (As your date range always includes full weeks) With the ABS in the calculation you can even go and enter 06180418 in your datefilterstring. And I would say that you are making it too easy with the nonworking days. You just care about the annual recurring nonworking days. What about “Non-Recurring ?” lvtblCalenderChange.RESET; lvtblCalenderChange.SETCURRENTKEY("Base Calendar Code","Recurring System",Date,Day); lvtblCalenderChange.SETRANGE("Base Calendar Code",'SACALENDAR'); lvtblCalenderChange.SETRANGE("Recurring System",lvtblCalenderChange."Recurring System"::" "); lvtblCalenderChange.SETRANGE(Date,STartDate,EndDate); lvtblCalenderChange.SETRANGE(Nonworking,TRUE); lvWorkDays -= lvtblCalenderChange.COUNT;
And one more: The line lvtblCalenderChange.SETCURRENTKEY(“Base Calendar Code”,“Recurring System”,Date,Day); is useless as that is the primary (and only) key in the Base Calendar Change Table which already selected after calling the RESET
Thanks Thomas, you make some good points. The reason that I’m only looking at Annual Recurring entries is that that’s all that the customer currently cares about and we’re on a tight schedule to go live by the end of Feb. I’ll certainly make changes to my code to accomodate your suggestions (as I care about the quality of my code), however I’ll need to do so in my own time, not during working hours (it’s 15:38 here now). Thanks for your help so far and I’ll get back to you with the changes I make as soon as I have time to do so. Regards Brad