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

kaelumchaikaelumchai Member Posts: 6
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

  • kylehardinkylehardin Member Posts: 257
    What is the primary key for TransSales?
    Kyle Hardin - ArcherPoint
  • kaelumchaikaelumchai Member Posts: 6
    Hi Kylehardin,

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

    Store No.,POS Terminal No.,Transaction No.,Line No.
  • kylehardinkylehardin Member Posts: 257
    Paste the actual error message you are getting.
    Kyle Hardin - ArcherPoint
  • kaelumchaikaelumchai Member Posts: 6
    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

  • lubostlubost Member Posts: 593
    Error message says that duplicate record is in entry table. Same record probably has been posted before. Check this in entry table.
  • kaelumchaikaelumchai Member Posts: 6
    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?
  • kylehardinkylehardin Member Posts: 257
    Do you have any filters set on TransSales before you do DELETEALL?
    Kyle Hardin - ArcherPoint
  • kaelumchaikaelumchai Member Posts: 6
    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.
  • kylehardinkylehardin Member Posts: 257
    Put either MESSAGE(TransSales.GETFILTERS) or TransSales.RESET before the DELETEALL, just to make sure.
    Kyle Hardin - ArcherPoint
  • kaelumchaikaelumchai Member Posts: 6
    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.
Sign In or Register to comment.