OK ... great news, and a lingering snag ...
Great news first:
Hammer, the speed increase is beyond belief - I went from a 2 min calc using my original code, down to 6 seconds using your code. Whatever flavor of cake you want, it's yours.
Lingering snag:
I tried to faithfully follow Hammer's suggested coding, but the code isn't getting, for each ItemNum, the correct QtyTotal and # of Recs (I don't completely yet understand the logic trail of the program, so I'm sure I've mis-stepped somewhere). I'm going to paste the code, and then show a snippet of the program output versus the actual numbers (in red):
First the code:
SUBROUTINE CalcAndEmit()
var vAvgInterval as int var vAvgQty as int var vPredictedDate as date
vAvgInterval = 0 vAvgQty = 0 vPredictedDate = vDate
If vRecs > 1 {
vAvgInterval = @DefinedRound((@ToNumber(vMaxSODate) - @ToNumber(vMinSODate)) / vRecs, 0, 1)
vPredictedDate = vMaxSODate + vAvgInterval vAvgQty = @DefinedRound(vQtyTotal / vRecs, 0, 1) If vAvgQty < 1 { vAvgQty = 1 } If (vPredictedDate > (vDate - 7)) and (vPredictedDate < (vDate + 7)) //If (1 = 1) // Forcing output since none of the sample dates are current { //WriteLn("Reorder Date for " + vAvgQty + " " + vItemUom + " of ItemNum #" + vItemNum + " / " + vItemDesc + @NewLine() + " should be " + vPredictedDate + @NewLine() + @NewLine()) Writeln("ItemNum # = " + vItemNum + "; vRecs = " + vRecs + ", vQtyTotal = " + vQtyTotal + ", vMaxSODate = " + vMaxSODate + ", vMinSODate = " + vMinSODate + "; vSONum = " + vSONum + @NewLine()) vNeedSoonAddTo = "" vNeedSoonAddTo = "Reorder Date for " + vAvgQty + " " + vItemUoM + " of ItemNum #" + vItemNum + " / " + vItemDesc + @NewLine() + " should be " + vPredictedDate + @NewLine() + @NewLine() vNeedSoonFYI = vNeedSoonFYI + vNeedSoonAddTo + @NewLine() } }
END SUBROUTINE
// Get list of unique ItemNums as a StringArray // We don't need them to be in order, so we're // not gonna waste time on sorting
vItemNumList = "" vRS = @XResultSetSearch(@FN, "Orders!SOLines", SEARCH_MODE_AND, SEARCH_SYNTAX_QA, "!ShipToClientID=" + vClientID, "!SODate=" + vDateRange, "!ItemCat=/" + "DESC;LBR;RC", "!SONum=/" + "..a;..b;..c;..d;..e;..x") If vRS > -1 { vCnt = @XResultSetTotal(vRS) For vLoop = 1 To vCnt XResultSetCurrentPosition(vRS, vLoop) vItemNum = @XResultSetValue(vRS, "ItemNum") vItemNumList = vItemNumList + vItemNum + ";" Next XResultSetClose(vRS) }
vItemNumList = @Repllas(vItemNumList, ";", "") vItemNumList = @SortStringArray(vItemNumList, 0) vItemNumList = @UniqueStringArray(vItemNumList)
writeln(vItemNumList)
// Get calc info for all the ItemNums at once instead of doing // one @XResultSetSearch for each ItemNum by using the list assembled // above as search criteria.
vRS = @XResultSetSearch(@FN, "Orders!SOLines", SEARCH_MODE_AND, SEARCH_SYNTAX_QA, "!ShipToClientID=" + vClientID, "!ItemNum=" + vItemNumList, "!SODate=" + vDateRange, "!ItemCat=/" + "DESC;LBR;RC", "!SONum=/" + "..a;..b;..c;..d;..e;..x") // , "!Company=/=") If vRS > -1 { // Now the sort order matters XResultSetSort(vRS, "ItemNum:-1;SODate:-1") vCnt = @XResultSetTotal(vRS)
writeln("vCnt - " + vCnt)
vUniqueItemNum = "This will never appear as an ItemNum" vQtyTotal = 0 vRecs = 0 vLoop = 1 // Calc each ItemNum For vLoop = 1 To vCnt XResultSetCurrentPosition(vRS, vLoop) vItemNum = @XResultSetValue(vRS, "ItemNum") vItemDesc = @XResultSetValue(vRS, "ItemDesc") vItemUoM = @XResultSetValue(vRS, "ItemUoM") //vSONum = @XResultSetValue(vRS, "SONum")
If vUniqueItemNum <> vItemNum { If vUniqueItemNum <> "This will never appear as an ItemNum" {
CalcAndEmit() // Emit result
} // Reset for next ItemNum vUniqueItemNum = vItemNum vMinSODate = @ToDate(@XResultSetValue(vRS, "SODate")) vMaxSODate = vMinSODate vQtyTotal = 0 vRecs = 0 } vMaxSODate = @XResultSetValue(vRS, "SODate") vItemQty = @XResultSetValue(vRS, "ItemQtyShadow") vQtyTotal = vQtyTotal + vItemQty vRecs = vRecs + 1 Next XResultSetClose(vRS) CalcAndEmit() // Emit last ItemNum
} XResultSetClose(vRS)
Now the resultant output from the code (with actual numbers inserted in red :
ItemNum # = 1-5513G; vRecs = 140, vQtyTotal = 383, vMaxSODate = 2012/01/27, vMinSODate = 2011/08/05 actual: ItemNum ItemQtyShadow 1-5513G 2.00 1-5513G 2.00 1-5513G 1.00 1-5513G 2.00 1-5513G 2.00 1-5513G 1.00 1-5513G 1.00 1-5513G 2.00 8 13.00
ItemNum # = 1-5517; vRecs = 39, vQtyTotal = 54, vMaxSODate = 2012/01/27, vMinSODate = 2011/08/05 actual:
ItemNum ItemQtyShadow 1-5517 0.60 1-5517 1.00 1-5517 1.00 1-5517 0.60 4 3.20
ItemNum # = 54-0281081001; vRecs = 26, vQtyTotal = 120, vMaxSODate = 2012/01/27, vMinSODate = 2011/08/05 actual:
ItemNum ItemQtyShadow 54-0281081001 1.00 54-0281081001 2.00 54-0281081001 (1.00) 54-0281081001 1.00 54-0281081001 1.00 54-0281081001 1.00 54-0281081001 1.00 7 6.00
ItemNum # = 54-0281136001; vRecs = 7, vQtyTotal = 6, vMaxSODate = 2012/01/12, vMinSODate = 2011/09/23 actual:
ItemNum ItemQtyShadow 54-0281136001 1.00 1 1.00
|