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

kaelumchai
kaelumchai Member Posts: 7
edited 2020-08-05 in NAV/Navision Classic Client
Hi,

I having an error when running this script:

TransSales.DELETEALL;


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."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.Description := Item.Description;
    TransSales.Quantity := TransSalesTransform.Quantity;
    TransSales."Trans. Time" := TransSalesTransform."Trans. Time";
    TransSales.INSERT;
  UNTIL TransSalesTransform.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. I'm not sure if it has to do with the primary keys or my setrange or what. Thank you in advance!

Answers

  • kylehardin
    kylehardin Member Posts: 257
    What is the primary key for TransSales?
    Kyle Hardin - ArcherPoint
  • kaelumchai
    kaelumchai Member Posts: 7
    Hi Kylehardin,

    Sorry. Same with the TransSalesTransform, the primary key of TransSales is:

    Store No.,POS Terminal No.,Transaction No.,Line No.
  • kylehardin
    kylehardin Member Posts: 257
    Paste the actual error message you are getting.
    Kyle Hardin - ArcherPoint
  • kaelumchai
    kaelumchai Member Posts: 7
    edited 2020-08-05
    Here is the error:

    jcem7b3ytfit.jpg


    However upon checking it on the sql, the TransSalesTransform has a no duplicate record.

    y5158l132lk3.jpg

  • lubost
    lubost Member Posts: 633
    Error message says that duplicate record is in entry table. Same record probably has been posted before. Check this in entry table.
  • kaelumchai
    kaelumchai Member Posts: 7
    Yes. but the record on the TransSales are being deleted before inserting.
    So I'm not sure if the FINDSET looking that record twice or something?
  • kylehardin
    kylehardin Member Posts: 257
    Do you have any filters set on TransSales before you do DELETEALL?
    Kyle Hardin - ArcherPoint
  • kaelumchai
    kaelumchai Member Posts: 7
    kylehardin wrote: »
    Do you have any filters set on TransSales before you do DELETEALL?

    None. Inside the function, it will delete all data in TransSalesTransform before anything else.
  • kylehardin
    kylehardin Member Posts: 257
    Put either MESSAGE(TransSales.GETFILTERS) or TransSales.RESET before the DELETEALL, just to make sure.
    Kyle Hardin - ArcherPoint
  • kaelumchai
    kaelumchai Member Posts: 7
    edited 2020-08-06
    Hi,

    This is now ok. What I did is, I delete the TransSales Table and I save as the TransSalesTransform (which is 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.

Categories