Problem with REPEAT loop

poppins · 2013-12-18

Hi everyone,
I am having an issue with the following code:

Prefixe:='FCM';
SalesInvoiceHeader.RESET;
SalesInvoiceHeader.SETRANGE("Posting Date",DateFilter,WORKDATE);

IF SalesInvoiceHeader.FINDSET THEN BEGIN
    REPEAT   
        SalesInvoiceHeader.RENAME(Prefixe+ COPYSTR(SalesInvoiceHeader."No.",3,STRLEN(SalesInvoiceHeader."No.")));     
    UNTIL (SalesInvoiceHeader.NEXT = 0);
END;

The problem is that the code doesn't loop, it modifies only one record.
What's missing in this code?
Thanks in advance

amazan · 2013-12-18

Hi poppins,

You're trying to rename "Sales Invoice Header" with a PK field ("No."). [-X
Try to change your current key to sort documents without PK or use a temporary table to do that (renaming).

poppins · 2013-12-18

amazan wrote:

Hi poppins,

You're trying to rename "Sales Invoice Header" with a PK field ("No."). [-X
Try to change your current key to sort documents without PK or use a temporary table to do that (renaming).

I am not sure I understand what you've just said...
Can you explain a little more?

radek.bb · 2013-12-18

Try changing the code to:
IF SalesInvoiceHeader.FINDSET(TRUE, TRUE) THEN BEGIN

Maybe you will go away without understanding what amazan has written before

bbrown · 2013-12-18

radek.bb wrote:

Try changing the code to:
IF SalesInvoiceHeader.FINDSET(TRUE, TRUE) THEN BEGIN

Maybe you will go away without understanding what amazan has written before

Yes, you should do that but it's not the whole solution. You need to use a different record variable for the rename than you are using for the looping. Accomplish this by usign 1 record variable for the looping. Then, as you retreived each record, copy it to a second variable and do the rename.

amazan · 2013-12-18

poppins wrote:

I am not sure I understand what you've just said...
Can you explain a little more?

Yes, I can.

You're trying to rename a bunch of invoices using Primary Key (field "No.") so when you're in renaming records, the position of the cursor changes depending of new value of your documents,.

For example:

First Loop
Invoice 'A' <- Cursor (Invoices 'A' becomes to 'FRZA')
Invoice 'B'
Invoice 'C'

After renaming
Invoice 'B'
Invoice 'C'
Invoice 'FRZA' <- Cursor

Next step it will be "end of loop" because the position of the cursor is the end of the selected records and can't find another record to do RENAME.

poppins · 2013-12-18

amazan wrote:

poppins wrote:

I am not sure I understand what you've just said...
Can you explain a little more?

Yes, I can.

You're trying to rename a bunch of invoices using Primary Key (field "No.") so when you're in renaming records, the position of the cursor changes depending of new value of your documents,.

For example:

First Loop
Invoice 'A' <- Cursor (Invoices 'A' becomes to 'FRZA')
Invoice 'B'
Invoice 'C'

After renaming
Invoice 'B'
Invoice 'C'
Invoice 'FRZA' <- Cursor

Next step it will be "end of loop" because the position of the cursor is the end of the selected records and can't find another record to do RENAME.

OK, I get it...
so, what shall I do to correct it?

mohana_cse06 · 2013-12-18

bbrown wrote:

You need to use a different record variable for the rename than you are using for the looping. Accomplish this by usign 1 record variable for the looping. Then, as you retreived each record, copy it to a second variable and do the rename.

Here is the solution already.

poppins · 2013-12-18

amazan wrote:

poppins wrote:

I am not sure I understand what you've just said...
Can you explain a little more?

Yes, I can.

You're trying to rename a bunch of invoices using Primary Key (field "No.") so when you're in renaming records, the position of the cursor changes depending of new value of your documents,.

For example:

First Loop
Invoice 'A' <- Cursor (Invoices 'A' becomes to 'FRZA')
Invoice 'B'
Invoice 'C'

After renaming
Invoice 'B'
Invoice 'C'
Invoice 'FRZA' <- Cursor

Next step it will be "end of loop" because the position of the cursor is the end of the selected records and can't find another record to do RENAME.

mohana_cse06 wrote:

bbrown wrote:

You need to use a different record variable for the rename than you are using for the looping. Accomplish this by usign 1 record variable for the looping. Then, as you retreived each record, copy it to a second variable and do the rename.

Here is the solution already.

I changed my code as following:

Prefixe:='FCM';
SalesInvoiceHeader.RESET;
SalesInvoiceHeader.SETRANGE("Posting Date",DateFilter,WORKDATE);


IF SalesInvoiceHeader.FINDSET THEN BEGIN
    REPEAT
        SalesInvoiceHeader1.RESET;
        IF SalesInvoiceHeader1.GET(SalesInvoiceHeader."No.") THEN BEGIN
            ModifEcrituresClientDetaillees(SalesInvoiceHeader1."No.",Prefixe);
            ModifEcrituresClient(SalesInvoiceHeader1."No.",Prefixe);
            ModifEcrituresComptables(SalesInvoiceHeader1."No.",Prefixe);
            ModifEcrituresTVA(SalesInvoiceHeader1."No.",Prefixe);
            SalesInvoiceHeader1.RENAME(Prefixe+ COPYSTR(SalesInvoiceHeader."No.",3,STRLEN(SalesInvoiceHeader."No.")));
        END; 
    UNTIL (SalesInvoiceHeader.NEXT = 0);
END;

but it is still wrong.....it keeps looping then gives me the following error message:

Overflow under type conversion of Text to Code.
Value: FCMMMMMMMMMMM+1303767

I think it keeps looping over the first invoice....
What shall I do?

bbrown · 2013-12-18

poppins wrote:

...I think it keeps looping over the first invoice....
What shall I do?

How about this?

mohana_cse06 wrote:

bbrown wrote:

You need to use a different record variable for the rename than you are using for the looping. Accomplish this by usign 1 record variable for the looping. Then, as you retreived each record, copy it to a second variable and do the rename.

Here is the solution already.

mohana_cse06 · 2013-12-18

Is it a one time job?

What about adding a filter on No.?

SalesInvoiceHeader.RESET;
SalesInvoiceHeader.SETRANGE("No.",<first record>,<last record>); //New line
SalesInvoiceHeader.SETRANGE("Posting Date",DateFilter,WORKDATE);
IF SalesInvoiceHeader.FINDSET THEN BEGIN
  REPEAT
    SalesInvoiceHeader1 := SalesInvoiceHeader;
    SalesInvoiceHeader1.RENAME('FCM' + SalesInvoiceHeader1."No.");
  UNTIL SalesInvoiceHeader.NEXT = 0;

Please test it in TEST Environment.

poppins · 2013-12-18

bbrown wrote:

You need to use a different record variable for the rename than you are using for the looping. Accomplish this by usign 1 record variable for the looping. Then, as you retreived each record, copy it to a second variable and do the rename.

That's what I did with my second code...
I used one variable for loop (SalesInvoiceHeader) and then inside the loop, I used a second variable (SalesInvoiceHeader1.GET(SalesInvoiceHeader."No.")) to do the rename....
Correct me if I am wrong :-k

vaprog · 2013-12-18

Take a look at amazan's example again. Now the cursor does not follow along with the renamed record anymore, but the renamed record still is placed at some position within the very set of records you are processing.

But, you are about to rename posted invoices and modify entries. It is quite likely that what you try to do is illegal, or at the least, not good practice.

aavio · 2013-12-19

poppins wrote:

bbrown wrote:

You need to use a different record variable for the rename than you are using for the looping. Accomplish this by usign 1 record variable for the looping. Then, as you retreived each record, copy it to a second variable and do the rename.

That's what I did with my second code...
I used one variable for loop (SalesInvoiceHeader) and then inside the loop, I used a second variable (SalesInvoiceHeader1.GET(SalesInvoiceHeader."No.")) to do the rename....
Correct me if I am wrong :-k

error should be from inside functions within the loop. Please check in debugger.

abhinav0408 · 2013-12-19

Use SalesInvoiceHeader.Ascending(False).

This will change all ur records.

vaprog · 2013-12-20

abhinav0408 wrote:

Use SalesInvoiceHeader.Ascending(False).

This will change all ur records.

Mind you, this will not change any records. This *might* lead to your code successfully doing what you want *for a particular data set* you're processing, but this is no general solution.

poppins · 2013-12-20

vaprog wrote:

abhinav0408 wrote:

Use SalesInvoiceHeader.Ascending(False).

This will change all ur records.

Mind you, this will not change any records. This *might* lead to your code successfully doing what you want *for a particular data set* you're processing, but this is no general solution.

I agree, I tested it and it didn't work,I am still facing the same issue ](*,)

bbrown · 2013-12-20

Test it with a FINDSET(TRUE, TRUE)

poppins · 2013-12-20

bbrown wrote:

Test it with a FINDSET(TRUE, TRUE)

I've already tested it, it doesn't work ](*,)

bbrown · 2013-12-20

poppins wrote:

bbrown wrote:

Test it with a FINDSET(TRUE, TRUE)

I've already tested it, it doesn't work ](*,)

You're right, it wouldn't. The issue is your renamed records fall with in the filter range. One approach would be to initially mark the records you need to change and then only process those marked records.

DenSter · 2013-12-20

1 - Copy all the records into a temporary record variable.
2 - loop through the temporary records.
3 - for each temporary record, GET the corresponding 'real' record and RENAME that record as necessary

william_marcelinus · 2013-12-26

Dear,

I think the problem is the "Posting Date" did not found the filter record you use with DateFilter variable and WORKDATE,
since "Posting Date" is Date type only.

search1110 · 2014-06-03

FINDSET and REPEAT does not make sense. Use FINDSET for modifyall. If you use REPEAT for looping start with the first FINDFIRST

DenSter · 2014-06-03

search1110 wrote:

FINDSET and REPEAT does not make sense. Use FINDSET for modifyall. If you use REPEAT for looping start with the first FINDFIRST

No that is wrong. FINDFIRST only retrieves one record. For looping you use FINDSET.

bbrown · 2014-06-04

DenSter wrote:

search1110 wrote:

FINDSET and REPEAT does not make sense. Use FINDSET for modifyall. If you use REPEAT for looping start with the first FINDFIRST

No that is wrong. FINDFIRST only retrieves one record. For looping you use FINDSET.

And MODIFYALL does not require any retrieval statement. No GET, FIND, FINDSET, etc is needed before a MODIFYALL or DELETEALL.

william_akihisa · 2014-06-05

Have you solved this problem? this is simple me thinks..
first, your filter not run correctly, test it with count function to check how many record you get.
There's something wrong with your DateFilter, except all the codes, everything looks fine..

vaprog · 2014-06-05

To all those of you, who still could not figure out the root cause of the problem here, and are giving all kind of useless suggestions:

When you want to modify records in a loop, you need to take care that you do not skip any records nor re-process any that you processed already. The problem is most prevalent with RENAME, but not restricted to it.

Skipping may occur, when you modify the data of the record variable on which you call NEXT (or FIND('>'). These same circumstances may also lead to reprocessing, depending on the nature of the change to the variable.

Reprocessing may additionally occur if the modified record is placed somewhere in the set of records to be processed at a position after the current one.

Different kind of methods can be applied in order to prevent the problems.

Probably the most robust is the one suggested by DenSter: create a temporary list of records to be processed which you do not modify during procession. Changing the sorting order or applying suitable filters might be others, depending on circumstances.

While FINDSET(TRUE,TRUE) appears to be a solution, it is not. The parameters are meant to optimize calls to the database server, not to solve the problem discussed here.

Howdy, Stranger!

Quick Links

Categories

Problem with REPEAT loop

Comments