Hi !! When doing a Repeat until like this VKProvKopfRec.SETRANGE(Auftragsnummer,AFT); IF VKProvKopfRec.FIND(’-’) THEN BEGIN REPEAT VKProvKopfRec.ANYFIELD := ANY VALUE; VKProvKopfRec.MODIFY(TRUE); //for example UNTIL VKProvKopfRec.NEXT = 0; END; Does the REPEAT UNTIL wil work under this situation with an (MODIFY/INSERT/DELETE) and REPEAT UNTIL on the same REC ??? Or will it lose it’s Pointer ??? Ufuk Altinkaynak yours sincerly Ufuk Altinkaynak buchhaltung@wibo-werk.com
As long as You don’t change a field which is part of the current key (or the primary key which is appended after a secondary key), it’s no problem modifying the same record as the one Your’e looping through. //Lars
Hi Ufuk! Of course you can MODIFY the same record, which is the “Loop-Criteria-Record”, but I would not recommend to change fields, where a RANGE or FILTER is set, that could cause some trouble … In your example, it could be a problem - maybe -, if you change
VKProvKopfRec.Auftragsnummer
Well, of course you can not INSERT the same record (because it exists), but you can INSERT records into the same table … (Similar to DELETE) Regards, Jörg Joerg A. Stryk Apollo-Optik, IT/ERP
You can always use another variabel against the same record and make changes to that record. VKProvKopfRec.SETRANGE(Auftragsnummer,AFT); IF VKProvKopfRec.FIND(’-’) THEN BEGIN REPEAT CopyOfVKProvKopfRec.GET(VKProvKopfRec.“Primary Key”); CopyOfVKProvKopfRec.ANYFIELD := ANY VALUE; CopyOfVKProvKopfRec.MODIFY(TRUE); //for example UNTIL VKProvKopfRec.NEXT = 0; END; Regards Magnus
A few points: 1. Use ISEMPTY() instead of FIND() in this case, if your version of the product supports it. 2. For SQL, always do LOCKTABLE() before looping though a set that you will be modifying (i.e. before the FIND()). 3. The NEXT takes the values from the record variable and determines the next record in the table using these values. Functionally, it does not matter where these values come from or if there is a record in the table with them; i.e. it does not matter if you have just deleted the record; it does not matter if you have modified the record, even the current key field values - you will just obtain the NEXT record given the values you now have in the record variable, for the relevant fields. Changing fields that are part of the filter (such that the record no longer qualifies in the set) does not matter because you will not read that record again in subsequent operations. Navision Server does not have a current position (just: input values → search → output result), so there is no issue, and the SQL version detects when cursors and current buffer positions, which it does use, are not valid for a particular request because of changed key values etc. You can tries these senarios out to test the results. Performance-wise, there can be very important issues with the SQL version, even though funcionally you will get the correct results. If you can avoid it, do not INSERT a record into the same table when in such a loop (a new cursor must be built, for every NEXT following an INSERT). Do not modify current key field values (a new cursor must be built for every NEXT) - however from Attain onwards, this pattern is recognised and a single row cursor is used if you continue to do it, which means fetches can be made instead of re-executions.
HI!! First of all thank you for answers.!! Well from my point the situation is a little bit clearer then before, but there are still some “grey zones”. We are using Navision 2.01b. When doing a Modify on a Record (with different values in the Primary Key fields)on a “filtered” record wich is looped, the system goes out off this loop. So this meets with some answers here. And we started to scan/change our programs for that kind of Loop’s. So far so good but what about the INSERT and DELETE. Will they also get in trouble in same scenarios like the modify ?? We are not realy sure on that point cause we had some strange problems with some of our applications, but they where issued on the zup file (wich was reported to navision and is bugfixed in v2.6). So the question is, is it still a zup file problem or not ?? yours sincerly Ufuk Altinkaynak ufuk.altinkaynak@wibo-werk.com
INSERT and DELETE work the same way. E.g. you have a set of records with key values A,B,C,D and E. If you run the following code: Rec.SETRANGE(‘A’,‘B’) if Rec.FIND(’-’) THEN REPEAT … … REC.Code := ‘C’; // Code is primary key REC.DELETE; // Deletes record with primary key = ‘C’ UNTIL REC.NEXT = 0; // Rec would be ‘D’, is out of range, so loop ends; You wil never get record ‘B’; Willy
quote:
When doing a Modify on a Record (with different values in the Primary Key fields)on a “filtered” record wich is looped, the system goes out off this loop.
Which is logical. Take as example the following Table of primary key values:
1000
1005
1012
1101
1200
2000
2100
2500
Assume you do a
setrange (code, '1000', '1999');
Now let’s start with a normal loop thru the records. If your Record-pointer stands on record 1101, which one will be the NEXT? Right 1200. That was easy. Now assume that your record-pointer points to 1101 and you rename it to 2101. Which will be the NEXT in this case ?? It would be 2500 but as this record is out of our SETRANGE, the loop will break. One method to solve this problem is to use a temporary variable which does the modify on the keyfield after the next loop-record has been read. Such as:
If LoopRec.Find('-') then repeat
if (xLoopRec.Code <> '') then begin
xLoopRec ... do changes
xLoopRec.Modify;
end;
xLoopRec := LoopRec;
until LoopRec.Next = 0;
if (xLoopRec.Code <> '') then begin
xLoopRec ... do changes
xLoopRec.Modify;
end;
------- With best regards from Switzerland Marcus Fabian
You can do a very instructive demonstration of this behaviour interactively: Open a table in the Object designer. Filter one field for a value (i.e. 1000). You might get 10 rows that comply with the filter. Make a Replace 1000 → 2000, and then remove the filter. Half of the records have been modified, the other half are left untuched! Flow: Modify first record; record is outside the filtered range, so next record gets the focus; NF moves to next row and modifies, leaving one row unmodified. The Navision solution when modifying a field that is part of your sort order (key) or filter would be to a) iterate through the records and Mark them b) clear the filter c) set Marked Only d) modify records. Best regards Pelle