Insert records in same table

jsshivalik

Hi

I have Main Form (Location) & SaleDeposit SubForm (Source table is SaleDepositLines) . I have 1 Button . On Push Trigger of Button

I want those lines which have ReceiptAmount > 0 should be inserted in same table SaleDepositLines. I am trying below code but not working

CDLine refers to the same table which Subform refers to


CDLine.RESET;
CDLine.SETRANGE("Location.","No.");
CDLine.SETFILTER("receipt Amount",'<>%1',0);
IF CDLine.FINDFIRST THEN BEGIN
REPEAT
CDLine.INIT;
EntryNo := EntryNo + 1;
CDLine.VALIDATE("Entry No.",EntryNo);
CDLine.VALIDATE("Location","No.");
CDLine.VALIDATE("Posted Amount", CDLine."Receipt Amount");
CDLine.INSERT;
UNTIL CDLine.NEXT = 0;
END;



Thanks

Slawek_Guzek

The answers have been already given:

Slawek_Guzek wrote: »

...make sure that primary keys are not duplicated and that newly inserted record will not be included in the loop - as this would create an infinite loop.

1. make sure that primary keys are not duplicated:

//find the last "Entry No." in the CDline table so new ones are inserted after the last existing one
IF CDLine2.FINDLAST THEN ;  
EntryNo := CDLine. "Entry No.";

You need to find the LAST entry in the table, not the last entry which have "Location" equals to "No." as you have coded.
Imagine scenario where you table data looks like this:

"Entry No.", "Location", rest of fields..
1, 'A', ...
2, 'B', ...
3, 'A', ...

If your code tries to duplicated lines having "Location" equal to 'B' it will fail - becasue it will find the last line with "Location" 'B' on it, the one having "Entry No." =2, and then it will try to add a new line with "Entry No." = 3 - which will cause PK violation as a line with "Entry No." = 3 already exists.

Then then there is a potential of an infinite loop because you have skipped this

//make sure that your loop will not include newly inserted records
CDLine.SETFILTER("Entry No.", '..%1', EntryNo);

The potential of an infinite loop exists bacause you are adding new records which are within the "Location"= "No." AND "Receipt Amount" > 0 filters. So basically each loop adds another record to the set which is suposed to be processes by that loop. I guess that VALIDATE("Posted Amount") or other bit of your code, not shown may clear somehow the "Receipt Amount" in newly inserted records, so your code works now - but it should be clearly addressed in the loop filter condition to not to go further than last existing Entry No.

Slawek_Guzek

What is the primary key in table CDLine ?

jsshivalik

EntryNo . CDLine refers to same Record DataType as SubForm SourceTable.
When form is Loaded there may be more records in SubForm but i want to insert only those records in which user Enters Receipt Amount

Slawek_Guzek

Your current design uses the same CDLine var for looping and for inserting new data, Since you are modifying the primary key in the var used for looping it is possible you are moving the "loop pointer" out of the scope of the initial filter, therefore the code goes through the loop only once.

Use 2 separate records of the same type, one of looping, one for inserting new data, make sure that primary keys are not duplicated and that newly inserted record will not be included in the loop - as this would create an infinite loop.

CLEAR(CDLine2);

//find the last "Entry No." in the CDline table so new ones are inserted after the last existing one
IF CDLine2.FINDLAST THEN ;  
EntryNo := CDLine2. "Entry No."; 

CDLine.RESET;

//make sure that your loop will not include newly inserted records
CDLine.SETFILTER("Entry No.", '..%1', EntryNo);

CDLine.SETRANGE("Location.","No.");
CDLine.SETFILTER("receipt Amount",'<>%1',0);
IF CDLine.FINDSET THEN  
  REPEAT
    EntryNo  += 1;
    CDLine2 := CDLine;
    CDLine2.VALIDATE("Entry No.", EntryNo);
    CDLine2.VALIDATE("Location", "No.");
    CDLine2.VALIDATE("Posted Amount",  CDLine."Receipt Amount");
    CDLine2.INSERT;
  UNTIL CDLine.NEXT = 0;

jsshivalik

Hi

I have change the code but it goes in infinite loop

CDNLine.RESET;
CDNLine.SETRANGE("Location","No.");
IF CDNLine.FINDLAST THEN
EntryNo := CDNLine."Entry No.";

CDNLine.RESET;
CDNLine.SETRANGE("Location","No.");
CDNLine.SETFILTER("Receipt Amount",'>%1',0);
IF CDNLine.FINDFIRST THEN BEGIN
REPEAT

EntryNo := EntryNo + 1;

CDNLine1 := CDNLine;
CDNLine1.VALIDATE("Entry No.", EntryNo);
CDNLine1.VALIDATE("Location", "No.");
CDNLine1.VALIDATE("Posted Amount", CDNLine."Receipt Amount");
CDNLine1.INSERT;
CDNLine.VALIDATE("Receipt Amount", 0);
CDNLine.MODIFY;
UNTIL CDNLine.NEXT = 0;
END;

Thanks

Slawek_Guzek

Read my previous post, please. The answer is there.

jsshivalik

Hi Slawek

I have written this line
CDNLine1 := CDNLine;

before below lines and it is working . Is it correct way.
CDNLine.RESET;
CDNLine.SETRANGE("Location","No.");
CDNLine.SETFILTER("Receipt Amount",'>%1',0);

Thanks

Slawek_Guzek

It may work but this is not correct way I'm afraid. You will find this at some point.

jsshivalik

Hi Slawek

Can u pls guide me where it can go wrong . My problem is that i have to use only 1 table.

Thanks

Slawek_Guzek

The answers have been already given:

Slawek_Guzek wrote: »

...make sure that primary keys are not duplicated and that newly inserted record will not be included in the loop - as this would create an infinite loop.

1. make sure that primary keys are not duplicated:

//find the last "Entry No." in the CDline table so new ones are inserted after the last existing one
IF CDLine2.FINDLAST THEN ;  
EntryNo := CDLine. "Entry No.";

You need to find the LAST entry in the table, not the last entry which have "Location" equals to "No." as you have coded.
Imagine scenario where you table data looks like this:

"Entry No.", "Location", rest of fields..
1, 'A', ...
2, 'B', ...
3, 'A', ...

If your code tries to duplicated lines having "Location" equal to 'B' it will fail - becasue it will find the last line with "Location" 'B' on it, the one having "Entry No." =2, and then it will try to add a new line with "Entry No." = 3 - which will cause PK violation as a line with "Entry No." = 3 already exists.

Then then there is a potential of an infinite loop because you have skipped this

//make sure that your loop will not include newly inserted records
CDLine.SETFILTER("Entry No.", '..%1', EntryNo);

The potential of an infinite loop exists bacause you are adding new records which are within the "Location"= "No." AND "Receipt Amount" > 0 filters. So basically each loop adds another record to the set which is suposed to be processes by that loop. I guess that VALIDATE("Posted Amount") or other bit of your code, not shown may clear somehow the "Receipt Amount" in newly inserted records, so your code works now - but it should be clearly addressed in the loop filter condition to not to go further than last existing Entry No.