Find loop and modify record

Business Central/NAV Developers Forum
1

Hello! Is there is a problem when I set filters, loop in filtered range and modify field included in filter? I run this code and get unpredicable results. There are not all filtered records processed. May be i need to use another record variable to modify record? lrec_CD.SETCURRENTKEY(“Item No.”,“Posted Date”); lrec_CD.SETRANGE(“Item No.”,“No.”); lrec_CD.SETFILTER(Quantity ,’>0’); ldec_Qty := “Qty. to Invoice (Base)”-“Qty. in CD”; IF (lrec_CD.FIND(’-’)) AND (ldec_Qty>0) THEN REPEAT MESSAGE(‘find in CD’); IF lrec_CD.“Qty. In”-lrec_CD.“Qty. Out” >= ldec_Qty THEN BEGIN ///… lrec_CD.VALIDATE(“Qty. Out”,lrec_CD.“Qty. Out”+ldec_Qty); // Quantity changed lrec_CD.MODIFY(TRUE); ldec_Qty:=0; END ELSE BEGIN ldec_Qty:=ldec_Qty-lrec_CD.“Qty. In”-lrec_CD.“Qty. Out”; lrec_CD.VALIDATE(“Qty. Out”,lrec_CD.“Qty. In”); // Quantity :=0 lrec_CD.MODIFY(TRUE); END; UNTIL ((ldec_Qty <= 0) OR (lrec_CD.NEXT = 0)) ;

2

quote:

May be i need to use another record variable to modify record?
Originally posted by se21 - 2005 Jan 11 : 13:03:03

You answered your own question! [:D] Yes, you do need to use a different variable to modify the record. If you modify the same variable you are using to perform the loop, you are changing the sorting and therefore affecting the position of the record inside the recordset. When you then try to move forward using the Rec.NEXT command, your record variable will point to the next record based on it’s already modified key.

3

As Nelson said, you answered your own question!

quote:

lrec_CD.SETCURRENTKEY(“Item No.”,“Posted Date”); lrec_CD.SETRANGE(“Item No.”,“No.”); lrec_CD.SETFILTER(Quantity ,‘>0’); ldec_Qty := “Qty. to Invoice (Base)”-“Qty. in CD”; IF (lrec_CD.FIND(‘-’)) AND (ldec_Qty>0) THEN REPEAT MESSAGE(‘find in CD’); IF lrec_CD.“Qty. In”-lrec_CD.“Qty. Out” >= ldec_Qty THEN BEGIN ///… lrec_CD.VALIDATE(“Qty. Out”,lrec_CD.“Qty. Out”+ldec_Qty); // Quantity changed lrec_CD.MODIFY(TRUE); ldec_Qty:=0; END ELSE BEGIN ldec_Qty:=ldec_Qty-lrec_CD.“Qty. In”-lrec_CD.“Qty. Out”; lrec_CD.VALIDATE(“Qty. Out”,lrec_CD.“Qty. In”); // Quantity :=0 lrec_CD.MODIFY(TRUE); END; UNTIL ((ldec_Qty <= 0) OR (lrec_CD.NEXT = 0)) ;
Originally posted by se21 - 2005 Jan 11 : 13:03:03

In your specific code you could remove the filter on Quanity and add the line: IF Quantity > 0 THEN BEGIN right after the REPEAT and, obviousy an END before the UNTIL.

4

Hi All, I think, it is not good, to remove the filter on Quantity, because of performance. If your table contains a lot of records with quantity zero you loop through all records although you only need few of them. Better use a new variable of the same table to change the quantity as nelson says. Regards, Frank

5

Try using a while loop (not sure if it works, code written off the top of my head). lrec_CD.SETCURRENTKEY(“Item No.”,“Posted Date”); lrec_CD.SETRANGE(“Item No.”,“No.”); lrec_CD.SETFILTER(Quantity ,’>0’); WHILE (lrec_CD.FIND(’-’)) AND (ldec_Qty>0) DO BEGIN MESSAGE(‘find in CD’); IF lrec_CD.“Qty. In”-lrec_CD.“Qty. Out” >= ldec_Qty THEN BEGIN ///… lrec_CD.VALIDATE(“Qty. Out”,lrec_CD.“Qty. Out”+ldec_Qty); // Quantity changed lrec_CD.MODIFY(TRUE); ldec_Qty:=0; END ELSE BEGIN ldec_Qty:=ldec_Qty-lrec_CD.“Qty. In”-lrec_CD.“Qty. Out”; lrec_CD.VALIDATE(“Qty. Out”,lrec_CD.“Qty. In”); // Quantity :=0 lrec_CD.MODIFY(TRUE); END; END

6

quote:

Hi All, I think, it is not good, to remove the filter on Quantity, because of performance.
Originally posted by FPulsfort - 2005 Jan 12 : 14:14:26

If quantity is in the key, it’s absolutely true. But when it is not in the key, well … I don’t know [?] I did a codeunit to test it in table 32, and, on the same data, sometime is faster using filter, sometime using IF.