Table already exist Identification fields and values.. even though the record is unique

Business Central/NAV Developers Forum
1

I having an error when running this script:


TransSales.DELETEALL;

Store.RESET;
Store.SETFILTER("No.",'<>%1','');
IF Store.FINDFIRST THEN BEGIN
    REPEAT
        TransSalesTransform.RESET;
        TransSalesTransform.SETCURRENTKEY("Store No.","POS Terminal No.","Transaction No.","Line No.");
        TransSalesTransform.SETRANGE("Trans. Date",CALCDATE('-1D',RunDate),RunDate);
        TransSalesTransform.SETRANGE("Store No.",Store."No.");
        IF TransSalesTransform.FINDSET THEN BEGIN
           REPEAT
              TransSales.INIT;
              TransSales."Store No." := TransSalesTransform."Store No.";
              TransSales."POS Terminal No." := TransSalesTransform."POS Terminal No.";
              TransSales."Transaction No." := TransSalesTransform."Transaction No.";
              TransSales."Line No." := TransSalesTransform."Line No.";
              TransSales."Active Location Code" := Store."Location Code";
              TransSales."Trans. Date" := TransSalesTransform."Trans. Date";
              TransSales."Item No." := TransSalesTransform."Item No.";
              TransSales.Quantity := TransSalesTransform.Quantity;
              TransSales."Trans. Time" := TransSalesTransform."Trans. Time";
              TransSales.INSERT;
           UNTIL TransSalesTransform.NEXT = 0;
       END;
    UNTIL Store.NEXT = 0;
END;

The Primary Keys of TransSales and TransSalesTransform is: Store No.,POS Terminal No.,Transaction No.,Line No.

It runs properly until a certain record saying that it is already exist when inserting even though it is not, also the record on TransSales is being deleted first before inserting data. I’m not sure if the FINDSET is repeating the same record on the source table (TransSalesTransform).

Thank you in advance!

Here is the image error: Picture 2.JPG

It says already exist but that table has no record.

2

Several comments:

  • I am pretty sure that the two primary sortingkeys are not the same for the TransSales and TransSalesTransformTable…

  • Dont do a Findfirst with a Repeat - Do at least a FIND(’-’) or a FINDSET

  • the line TransSalesTransform.SETCURRENTKEY(“Store No.”,“POS Terminal No.”,“Transaction No.”,“Line No.”); is not needed as you on the line above did a RESET (Reset removes all filters and switches to to primary key).

You also dont need the BEGIN on the line IF TransSalesTransform.FINDSET THEN BEGIN (and you also have to remove the corresponding END line.

But first and foremost what are the FIRST line of the two tables in (Design, Table, Find table, Choose Design, and then View, Keys) ? (if you are running NAV 2018 or later).

3

Here is the Primary Keys of the 2 tables:

1.) This is the PK of the TransSales

2.) The is the PK of the TransSalesEntries

I follow the advise

TransSales.DELETEALL;

Store.RESET;
Store.SETFILTER(“No.”,’<>%1’,’’);
IF Store.FIND(’-’) THEN BEGIN
REPEAT
TransSalesTransform.RESET;
TransSalesTransform.SETRANGE(“Trans. Date”,CALCDATE(’-1D’,RunDate),RunDate);
TransSalesTransform.SETRANGE(“Store No.”,Store.“No.”);
IF TransSalesTransform.FINDSET THEN
REPEAT
TransSales.INIT;
TransSales.“Store No.” := TransSalesTransform.“Store No.”;
TransSales.“POS Terminal No.” := TransSalesTransform.“POS Terminal No.”;
TransSales.“Transaction No.” := TransSalesTransform.“Transaction No.”;
TransSales.“Line No.” := TransSalesTransform.“Line No.”;
TransSales.“Active Location Code” := Store.“Location Code”;
TransSales.“Trans. Date” := TransSalesTransform.“Trans. Date”;
TransSales.“Item No.” := TransSalesTransform.“Item No.”;
TransSales.Quantity := TransSalesTransform.Quantity;
TransSales.“Trans. Time” := TransSalesTransform.“Trans. Time”;
TransSales.INSERT;
UNTIL TransSalesTransform.NEXT = 0;
UNTIL Store.NEXT = 0;
END;

but still showing the error. I’m using NAV 2013

4

Do the Following

Step 1 - Declare a variable TempLineNo type Integer

Step 2 - Declare another variable NewTranssales record Transales record

Step 3 -

NewTranssales.reset;

newtransales.setrange(Store no,transsalesfrom.store no)

newtransales.setrange(pos terminal no., transsalesfrom.pos terminal no.)

newtransales.setrange(transaction no,transsalesfrom.transaction no.);

if newtranssales.find(’+’) then

TempLineNo := newtransales.“Line no.” + 10000

else

templineno := 10000

TransSales.INIT;
TransSales.“Store No.” := TransSalesTransform.“Store No.”;
TransSales.“POS Terminal No.” := TransSalesTransform.“POS Terminal No.”;
TransSales.“Transaction No.” := TransSalesTransform.“Transaction No.”;
TransSales.“Line No.” := TEMPLINENO;

TransSales.“Active Location Code” := Store.“Location Code”;
TransSales.“Trans. Date” := TransSalesTransform.“Trans. Date”;
TransSales.“Item No.” := TransSalesTransform.“Item No.”;
TransSales.Quantity := TransSalesTransform.Quantity;
TransSales.“Trans. Time” := TransSalesTransform.“Trans. Time”;
TransSales.INSERT;-

This will definitely work.

Thanks

RJ.

5

You have to put this code inside the REPEAT loop.

6

Yes this will ofcourse work if it is only a matter of getting the records from one table to another. Weather it is ok or not depends of the usage of the data.

The main problem is that TransSales.INSERT cannot fail as the code is copying data from another table with the exact same primary key… I guess that it is not actually the line TransSales.INSERT; that is failing but another line in the code (we do not see all code )

You could try to just say

IF not TransSales.INSERT then;

to see if the error goes away (NOTICE this is NOT the right solution).

7

Do me a favour and please put this line before the init.

Clear(TransSales);

Sounds stupid but please try.

8

You are close but actually the very first line of the code that we see must be a

TransSales.RESET;

TransSales.DELETEALL;

…

.

.

9

Hi All,

This is now ok. What I did is, I delete the TransSales Table and start new table by saving as the TransSalesTransform (the existing or the source) and named as new table (the TransSales) then remove the unnecessary fields and add the “Active Location Code” which is not present on the source. The codes are still the same.

I don’t know how it happen, though.

Anyway, Thank you for your feedback! it is highly appreciated.

10

Please share your code as I am getting same issue.