i have a report which uses the following code. as can be seen its applying setrange multiple times and hence the report is very slow. ILE2.SETRANGE(“Return Reason Code”,’’); IF ILE2.FIND(’-’) THEN REPEAT ILE2.CALCFIELDS(Amount); NAmount += ILE2.Amount * -1 ; NQty += ILE2.Quantity /ItemUOM.“Qty. per Unit of Measure” * -1 ; UNTIL ILE2.NEXT = 0; ILE2.SETRANGE(“Return Reason Code”,‘GOOD’); IF ILE2.FIND(’-’) THEN REPEAT ILE2.CALCFIELDS(Amount); NAmount += ILE2.Amount * -1 ; NQty += ILE2.Quantity /ItemUOM.“Qty. per Unit of Measure” * -1 ; UNTIL ILE2.NEXT = 0; can the below code be alternative to the above. when i try it i am getting value 0 which is not correct. IF ILE2.FIND(’-’) THEN REPEAT CASE ILE2. “Return Reason Code” OF ‘’ : BEGIN ILE2.CALCFIELDS(Amount); NAmount += ILE2.Amount * -1 ; NQty += ILE2.Quantity /ItemUOM.“Qty. per Unit of Measure” * -1 ; END; ‘GOOD’ : BEGIN ILE2.CALCFIELDS(Amount); NAmount += ILE2.Amount * -1 ; NQty += ILE2.Quantity /ItemUOM.“Qty. per Unit of Measure” * -1 ; END; END; UNTIL ILE2.NEXT=0; please give ur suggestions on the most efficient method
One suggestion would be to put line feeds between statements and indent your code. That makes it much easier to read.
While I was decyphering the first part of the code I was thinking about suggesting a CASE statement, so that part is good. Debug your code to see if it even goes into the case statement. Then if you’re on 4.0 SP2 or higher you might want to consider using FINDSET.
Is this inside another dataitem, or is ILE2 a dataitem in itself? If it’s its own dataitem why are you looping through it in code? It’s very costly to loop through large tables like that.
sorry for the junk. below is two alternatives.
IF ILE2.FIND(’-’) THEN REPEAT
CASE ILE2. “Return Reason Code” OF
‘’ : BEGIN
ILE2.CALCFIELDS(Amount);
NAmount += ILE2.Amount * -1 ;
NQty += ILE2.Quantity /ItemUOM.“Qty. per Unit of Measure” * -1 ;
END;
‘GOOD’ : BEGIN
ILE2.CALCFIELDS(Amount);
NAmount += ILE2.Amount * -1 ;
NQty += ILE2.Quantity /ItemUOM.“Qty. per Unit of Measure” * -1 ;
END;
END;
UNTIL ILE2.NEXT=0;
the first alternative is not working for me. below one is expensive but correct
ILE2.SETRANGE(“Return Reason Code”,’’);
IF ILE2.FIND(’-’) THEN REPEAT
ILE2.CALCFIELDS(Amount);
NAmount += ILE2.Amount * -1 ;
NQty += ILE2.Quantity /ItemUOM.“Qty. per Unit of Measure” * -1 ;
UNTIL ILE2.NEXT = 0;
ILE2.SETRANGE(“Return Reason Code”,‘GOOD’);
IF ILE2.FIND(’-’) THEN REPEAT
ILE2.CALCFIELDS(Amount);
NAmount += ILE2.Amount * -1 ;
NQty += ILE2.Quantity /ItemUOM.“Qty. per Unit of Measure” * -1 ;
UNTIL ILE2.NEXT = 0;
I think doing a filter on large tables like that without having a proper key will always result in a slow report.
I don’t know why the first alternative is not working but you could do it like this :
IF ILE2.FIND(’-’) THEN
REPEAT
IF (ILE2.“Return Reason Code” = ‘’) OR (ILE2.“Return Reason Code” = ‘GOOD’) THEN
BEGIN
ILE2.CALCFIELDS(Amount);
NAmount -= ILE2.Quantity /ItemUOM.“Qty. per Unit of Measure”;
NQty -= ILE2.Quantity;
END;
UNTIL ILE2.NEXT = 0;
Well I don’t know why your first code doesn’t work. It looks ok to me. Personally I would write the code this way:
ILE2.SETFILTER(“Return Reason Code”,‘%1|*2’,‘’,‘GOOD’);
IF ILE2.FINDSET(FALSE,FALSE) THEN REPEAT
ILE2.CALCFIELDS(Amount);
NAmount += ILE2.Amount * -1;
NQty += ILE2.Quantity /ItemUOM.“Qty. per Unit of Measure” * -1;
UNTIL ILE2.NEXT = 0;
And yes I would also insert at ILE2.SETCURRENTKEY(“Return Reason Code”), but since I don’t know if you have other filters before your REPEAT UNTIL loop, then I haven’t listed it.