validate itemno in sales line,another field will be emptied?

Savatage · 2012-02-08

So you can't insert it and you can't modify it after insert?
Are you sure it's getting to that part of your code?
Did you try adding a message line to see if it's executing where you think it should? Message('executing');

Perhaps that section of code only runs for the last line instead of running for each line. Add a message('%1',"line no.");
If its just the last line then your imported=true is in the wrong place

mohana_cse06 · 2012-02-08

Can you show the file which you are importing?

julkifli33 · 2012-02-08

this is my txt file sample

Sale;Invoice;YVO001;TWC-SINV001;EXTDOCNO-001;11/12/2011;COD;30/09/2011;DOMESTIC;AUD;DANNY;19/09/2011;CASH;TRIPLEPACK-SCA;MENLO;PCS;1;20;20;21.4;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;Company A
Sale;Invoice;YVO001;TWC-SINV001;EXTDOCNO-001;11/12/2011;COD;30/09/2011;DOMESTIC;AUD;DANNY;19/09/2011;CASH;CCHINBBQQY;BAYS;BTL;1;30;30;32.1;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;Company A
Sale;Invoice;YVO001;TWC-SINV001;EXTDOCNO-001;11/12/2011;COD;30/09/2011;DOMESTIC;AUD;DANNY;19/09/2011;CASH;CATER-FOOD;MENLO;PCS;1;40;40;42.8;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;Company A
Sale;Credit Memo;BER001;TWC-SCR002;EXTDOCNO-002;12/12/2011;90D;30/09/2011;DOMESTIC;USD;DANNY;19/09/2011;CASH;CCHINBBQQY;BAYS;PCS;1;20;20;21.4;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;Company A
Purchase;Invoice;ANG001;TWC-PINV001;TEXTCAPSLOCK;26/12/2011;COD;30/09/2011;FOREIGN;AUD;BELINDA;20/09/2011;VISA;CATER-FOOD;MENLO;PCS;1;30;30;32.1;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;Company A
Sale;Invoice;ASA001;TWC-SINV002;EXTDOCNO-002;23/12/2011;COD;30/09/2011;DOMESTIC;USD;BELINDA;19/09/2011;CASH;BULBHOLDER;BAYS;PCS;1;20;20;21.4;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;TEXTCAPSLOCK;Company A

Savatage · 2012-02-08

Savatage wrote:

So you can't insert it and you can't modify it after insert?
Are you sure it's getting to that part of your code?
Did you try adding a message line to see if it's executing where you think it should? Message('executing');

Perhaps that section of code only runs for the last line instead of running for each line.
Add a message('%1 %2 %3',"Document Type","Document No.","line no.");
If its just the last line then your imported=true is in the wrong place

:-k

julkifli33 · 2012-02-09

kapamarou wrote:

So strange... Could your field be a flowfield? :-k

i dont use flowfield
i use boolean as normal type

julkifli33 · 2012-02-09

Savatage wrote:

So you can't insert it and you can't modify it after insert?
Are you sure it's getting to that part of your code?
Did you try adding a message line to see if it's executing where you think it should? Message('executing');

Perhaps that section of code only runs for the last line instead of running for each line. Add a message('%1',"line no.");
If its just the last line then your imported=true is in the wrong place

after sales line inserted... i tried previously catch using message
and it's all correct.... imported is true, and description is Test
but after that... the result is wrong

dans · 2012-02-09

Try this

GLSetup.GET;
SFile.TEXTMODE(TRUE);
SFile.WRITEMODE(FALSE);
SFile.OPEN('D:\Data\Test.txt');
  REPEAT
    SFile.READ(vString);
    //Transaction Type
    PosCode:=STRPOS(vString,';');
    vStringTransaction := FORMAT(COPYSTR(vString,1,PosCode-1));
    vString:=DELSTR(vString,1,PosCode);

    IF vStringTransaction = 'Sale' THEN
      BEGIN
     //********* SALES HEADER ************
        //SH Document Type
        PosCode:=STRPOS(vString,';');
        IF FORMAT(COPYSTR(vString,1,PosCode-1)) = 'Order' THEN
        SH."Document Type" := SH."Document Type"::Order;
        IF FORMAT(COPYSTR(vString,1,PosCode-1)) = 'Invoice' THEN
        SH."Document Type" := SH."Document Type"::Invoice;
        IF FORMAT(COPYSTR(vString,1,PosCode-1)) = 'Return Order' THEN
        SH."Document Type" := SH."Document Type"::"Return Order";
        IF FORMAT(COPYSTR(vString,1,PosCode-1)) = 'Credit Memo' THEN
        SH."Document Type" := SH."Document Type"::"Credit Memo";
        vString:=DELSTR(vString,1,PosCode);

        DocType := SH."Document Type";

        //SH No. & SH Sell-to Customer No
        PosCode:=STRPOS(vString,';');
        SH."Sell-to Customer No." := FORMAT(COPYSTR(vString,1,PosCode-1));
        vString:=DELSTR(vString,1,PosCode);

        PosCode:=STRPOS(vString,';');
        SH."No." := FORMAT(COPYSTR(vString,1,PosCode-1));

        IF (SH."Document Type" <> TempDocType) AND (SH."No." <> TempDocNo) THEN
        BEGIN

          vString:=DELSTR(vString,1,PosCode);
          SH.VALIDATE(SH."No.");
          SH.InitRecord;
          SH.SetHideValidationDialog(TRUE);

          DocNo := SH."No.";
          //External Doc No.
          PosCode:=STRPOS(vString,';');
          SH."External Document No." := FORMAT(COPYSTR(vString,1,PosCode-1));
          vString:=DELSTR(vString,1,PosCode);

          //Posting Date
          PosCode:=STRPOS(vString,';');
          PostingDateText := FORMAT(COPYSTR(vString,1,PosCode-1));
          SH."Posting Date" := ConvertPostingDate(PostingDateText);
          vString:=DELSTR(vString,1,PosCode);

          //Payment Terms Code   
          PosCode:=STRPOS(vString,';');
          SH."Payment Terms Code" := FORMAT(COPYSTR(vString,1,PosCode-1));
          vString:=DELSTR(vString,1,PosCode);

          //Due Date   
          PosCode:=STRPOS(vString,';');
          SalesDueDateText := FORMAT(COPYSTR(vString,1,PosCode-1));
          SH."Due Date" := ConvertSalesDueDate(SalesDueDateText);
          vString:=DELSTR(vString,1,PosCode);

          //Vendor / Customer Posting Group   
          PosCode:=STRPOS(vString,';');
          SH."Customer Posting Group" := FORMAT(COPYSTR(vString,1,PosCode-1));
          vString:=DELSTR(vString,1,PosCode);

          //Currency Code   
          PosCode:=STRPOS(vString,';');
          SH."Currency Code" := FORMAT(COPYSTR(vString,1,PosCode-1));
          SH.VALIDATE(SH."Currency Code");
          vString:=DELSTR(vString,1,PosCode);

          //Purchaser / Salesperson Code
          PosCode:=STRPOS(vString,';');
          SH."Salesperson Code" := FORMAT(COPYSTR(vString,1,PosCode-1));
          vString:=DELSTR(vString,1,PosCode);

          //   Document Date   
          PosCode:=STRPOS(vString,';');
          SalesDocumentDateText := FORMAT(COPYSTR(vString,1,PosCode-1));
          SH."Document Date" := ConvertSalesDocumentDate(SalesDocumentDateText);
          vString:=DELSTR(vString,1,PosCode);

          //Payment Method
          PosCode:=STRPOS(vString,';');
          SH."Payment Method Code" := FORMAT(COPYSTR(vString,1,PosCode-1));
          vString:=DELSTR(vString,1,PosCode);

          SH.Imported := TRUE;

          SH.INSERT(TRUE);
        END
        ELSE
        BEGIN
          vString:=DELSTR(vString,1,PosCode);
          DocNo := SH."No.";

          //External Doc No.
          PosCode:=STRPOS(vString,';');
          vString:=DELSTR(vString,1,PosCode);

          //Posting Date
          PosCode:=STRPOS(vString,';');
          vString:=DELSTR(vString,1,PosCode);

          //Payment Terms Code   
          PosCode:=STRPOS(vString,';');
          vString:=DELSTR(vString,1,PosCode);

          //Due Date   
          PosCode:=STRPOS(vString,';');
          vString:=DELSTR(vString,1,PosCode);

          //Vendor / Customer Posting Group   
          PosCode:=STRPOS(vString,';');
          vString:=DELSTR(vString,1,PosCode);

          //Currency Code   
          PosCode:=STRPOS(vString,';');
          vString:=DELSTR(vString,1,PosCode);

          //Purchaser / Salesperson Code
          PosCode:=STRPOS(vString,';');
          vString:=DELSTR(vString,1,PosCode);

          //   Document Date   
          PosCode:=STRPOS(vString,';');
          vString:=DELSTR(vString,1,PosCode);

          //Payment Method
          PosCode:=STRPOS(vString,';');
          vString:=DELSTR(vString,1,PosCode);
        END;
        TempDocType := SH."Document Type";
        TempDocNo := SH."No.";

     //*********** SALES LINE ************
      SL.RESET;
      SL.SETRANGE(SL."Document Type",DocType);
      SL.SETRANGE(SL."Document No.",DocNo);
      IF SL.FIND('+') THEN
        LineNo := SL."Line No." + 10000
      ELSE
        LineNo := 10000;

      SL.RESET;         
      SL.INIT;
      SL."Document Type" := DocType;
      SL."Document No." := DocNo;
      SL."Line No." := LineNo;
      SL.VALIDATE(Type,SL.Type::Item);
      //Item No.   
      PosCode:=STRPOS(vString,';');
      SL.VALIDATE(SL."No.",FORMAT(COPYSTR(vString,1,PosCode-1)));
      vString:=DELSTR(vString,1,PosCode);
      SL.Description := 'Test';
      SL.Imported := TRUE;
      SL.INSERT(TRUE);

      END;
  UNTIL
SFile.POS = SFile.LEN;
MESSAGE('Import Done');

BernardJ · 2012-02-11

Or this:

GLSetup.GET;
SFile.TEXTMODE(TRUE);
SFile.WRITEMODE(FALSE);
SFile.OPEN('D:\Data\Test.txt');
REPEAT 
  SFile.READ(vString);
  //Transaction Type
  PosCode:=STRPOS(vString,';');
  vStringTransaction := FORMAT(COPYSTR(vString,1,PosCode-1));
  vString:=DELSTR(vString,1,PosCode);

  IF vStringTransaction = 'Sale' THEN BEGIN
    //********* SALES HEADER ************
    //SH Document Type
    PosCode:=STRPOS(vString,';');
    CASE FORMAT(COPYSTR(vString,1,PosCode-1)) OF
      'Order'        : SH."Document Type" := SH."Document Type"::Order;
      'Invoice'      : SH."Document Type" := SH."Document Type"::Invoice;
      'Return order' : SH."Document Type" := SH."Document Type"::"Return order";
      'Credit memo'  : SH."Document Type" := SH."Document Type"::"Credit memo";
    END;
    vString:=DELSTR(vString,1,PosCode);

    DocType := SH."Document Type";

    //SH No. & SH Sell-to Customer No
    PosCode:=STRPOS(vString,';');
    CustomerNo := FORMAT(COPYSTR(vString,1,PosCode-1));
    vString:=DELSTR(vString,1,PosCode);

    PosCode:=STRPOS(vString,';');
    DocNo := FORMAT(COPYSTR(vString,1,PosCode-1));
    vString:=DELSTR(vString,1,PosCode);

    DoNewHeader := (SH."Document Type" <> DocType) OR (SH."No." <> DocNo);

    IF DoNewHeader THEN BEGIN
      SH.InitRecord;
      SH.SetHideValidationDialog(TRUE);
      SH.VALIDATE("No.", DocNo);
      SH.VALIDATE("Sell-to Customer No.", CustomerNo);
    END;

    //External Doc No.
    PosCode:=STRPOS(vString,';');
    SH."External Document No." := FORMAT(COPYSTR(vString,1,PosCode-1));
    vString:=DELSTR(vString,1,PosCode);

    //Posting Date
    PosCode:=STRPOS(vString,';');
    PostingDateText := FORMAT(COPYSTR(vString,1,PosCode-1));
    SH."Posting Date" := ConvertPostingDate(PostingDateText);
    vString:=DELSTR(vString,1,PosCode);

    //Payment Terms Code   
    PosCode:=STRPOS(vString,';');
    SH."Payment Terms Code" := FORMAT(COPYSTR(vString,1,PosCode-1));
    vString:=DELSTR(vString,1,PosCode);

    //Due Date   
    PosCode:=STRPOS(vString,';');
    SalesDueDateText := FORMAT(COPYSTR(vString,1,PosCode-1));
    SH."Due Date" := ConvertSalesDueDate(SalesDueDateText);
    vString:=DELSTR(vString,1,PosCode);

    //Vendor / Customer Posting Group   
    PosCode:=STRPOS(vString,';');
    SH."Customer Posting Group" := FORMAT(COPYSTR(vString,1,PosCode-1));
    vString:=DELSTR(vString,1,PosCode);

    //Currency Code   
    PosCode:=STRPOS(vString,';');
    IF DoNewHeader THEN
      SH.VALIDATE("Currency Code", FORMAT(COPYSTR(vString,1,PosCode-1)));
    vString:=DELSTR(vString,1,PosCode);

    //Purchaser / Salesperson Code
    PosCode:=STRPOS(vString,';');
    SH."Salesperson Code" := FORMAT(COPYSTR(vString,1,PosCode-1));
    vString:=DELSTR(vString,1,PosCode);

    //   Document Date   
    PosCode:=STRPOS(vString,';');
    SalesDocumentDateText := FORMAT(COPYSTR(vString,1,PosCode-1));
    SH."Document Date" := ConvertSalesDocumentDate(SalesDocumentDateText);
    vString:=DELSTR(vString,1,PosCode);

    //Payment Method
    PosCode:=STRPOS(vString,';');
    SH."Payment Method Code" := FORMAT(COPYSTR(vString,1,PosCode-1));
    vString:=DELSTR(vString,1,PosCode);

    SH.Imported := TRUE;

    IF DoNewHeader THEN
      SH.INSERT(TRUE);

    //*********** SALES LINE ************
    SL.RESET;
    SL.SETRANGE("Document Type",DocType);
    SL.SETRANGE("Document No.",DocNo);
    IF SL.FIND('+') THEN
      LineNo := SL."Line No." + 10000
    ELSE
      LineNo := 10000;

    SL.RESET;          
    SL."Document Type" := DocType;
    SL."Document No." := DocNo;
    SL."Line No." := LineNo;
    SL.Type := SL.Type::Item;
    //Item No.   
    PosCode:=STRPOS(vString,';');
    SL.VALIDATE("No.",FORMAT(COPYSTR(vString,1,PosCode-1)));
    vString:=DELSTR(vString,1,PosCode);
    SL.Description := 'Test';
    SL.Imported := TRUE;
    SL.INSERT(TRUE);

    END;
UNTIL SFile.POS = SFile.LEN;
MESSAGE('Import Done');

julkifli33 · 2012-02-13

Hi Dans and Bernard
now it's work
thanks for your helps

can anyone share what happened previously?

dans · 2012-02-14

When you validate the Customer No. on the header, Nav recreates / refresh the Sales Line. That is why the previously inserted Sales Lines Imported Field is set back to default (FALSE).

julkifli33 · 2012-02-14

dans wrote:

When you validate the Customer No. on the header, Nav recreates / refresh the Sales Line. That is why the previously inserted Sales Lines Imported Field is set back to default (FALSE).

Hi Dan,
another problem if we import multiple documents
previously we tried for doc TWC-SINV0001
but if we using this one... failed
i changed now my data hava 2 documents : TWC-SINV0001 and TWC-SINV0002
it was said sales header document type : invoice and no TWC-SINV0002 does not exist

Sale;Invoice;CUST001;TWC-SINV001;EXTDOCNO-001;11/12/2011;COD;30/09/2011;TWCI;AUD;DENNIES;19/09/2011;CASH;TRIPLEPACK-SCA;BLUE;PCS;3;100;10;TEXTCAPSLOCK1-1;TEXTCAPSLOCK1-2;TEXTCAPSLOCK1-3;TEXTCAPSLOCK1-4;TEXTCAPSLOCK1-5;Company A Pte. Ltd.
Sale;Invoice;CUST001;TWC-SINV002;EXTDOCNO-011;11/12/2011;COD;30/09/2011;TWCI;AUD;DANNY;19/09/2011;CASH;CCHINBBQQY;BOXSAS;BTL;1;200;20;TEXTCAPSLOCK2-1;;TEXTCAPSLOCK2-3;TEXTCAPSLOCK2-4;TEXTCAPSLOCK2-5;Company A Pte. Ltd.

BernardJ · 2012-02-14

Try this:

IF (SH."Document Type" <> TempDocType) AND OR (SH."No." <> TempDocNo) THEN

julkifli33 · 2012-02-14

BernardJ wrote:

Try this:

IF (SH."Document Type" <> TempDocType) AND OR (SH."No." <> TempDocNo) THEN

i tried but still the same
anyway i need these 2 conditions
i dont think i can use OR

julkifli33 · 2012-02-14

dans wrote:

When you validate the Customer No. on the header, Nav recreates / refresh the Sales Line. That is why the previously inserted Sales Lines Imported Field is set back to default (FALSE).

Hi dan, i just realized that if we do not validate the sales header for sell to cust no
then the sales header only get customer code... and not validated
i think this one is still wrong

dans · 2012-02-15

you just need to make sure you dont validate if the header is already existed.

julkifli33 · 2012-02-15

hi dan
i think it works
i put a code like this
thanks man ! =D>

.
.
.
.
        PosCode:=STRPOS(vString,';');
        SH."No." := FORMAT(COPYSTR(vString,1,PosCode-1));
        vString:=DELSTR(vString,1,PosCode);
        
        SH.VALIDATE(SH."No.");
        SH.InitRecord;
        SH.SetHideValidationDialog(TRUE);
        DocNo := SH."No.";
 
        CheckSalesHeader.RESET;
        CheckSalesHeader.SETRANGE(CheckSalesHeader."Document Type",SH."Document Type");
        CheckSalesHeader.SETRANGE(CheckSalesHeader."No.",SH."No.");
        IF NOT CheckSalesHeader.FIND('-') THEN
          SH.VALIDATE(SH."Sell-to Customer No.")
        ELSE
          SH.GET(SH."Document Type",SH."No.");
.
.
.
.

dans · 2012-02-15

glad I could help.

actually there is a few things that you could do to improve the coding, such as the variable naming, use GET instead of SETRANGE, etc.

IF NOT CheckSalesHeader.GET(SH."Document Type",SH."No.") THEN
SH.VALIDATE(SH."Sell-to Customer No.")
ELSE
SH.GET(SH."Document Type",SH."No.");

I will let you decide it for yourself though.

julkifli33 · 2012-02-15

dans wrote:

glad I could help.

actually there is a few things that you could do to improve the coding, such as the variable naming, use GET instead of SETRANGE, etc.

IF NOT CheckSalesHeader.GET(SH."Document Type",SH."No.") THEN
SH.VALIDATE(SH."Sell-to Customer No.")
ELSE
SH.GET(SH."Document Type",SH."No.");

I will let you decide it for yourself though.

ok noted
thanks a lot Dan

validate itemno in sales line,another field will be emptied?

Comments

Categories