how to copy customer no. from sales header table

ManiNav · 2019-07-22

Hi Everyone,

I have one custom table, inside that some fields are: No.,Document Type,Sell-to Customer No.,Ship-to Name{Same data type as sales header table}; And inside my table field "No." has some values same as sales header No. field(ie order no). I want to update the remaining fields for corresponding order in my custom table.I tried but, i am not able to get. This is my code: its showing error[The CusSHm does not exist.]

CusShM.GET("No.","Document Type");
CusShM.SETRANGE("No.", Salesheader."No.");
CusShM.SETRANGE("Document Type", Salesheader."Document Type"::Order);
IF CusShM.FINDSET THEN REPEAT
CusShM.INIT;
CusShM."Sell-to Customer No." := Salesheader."Sell-to Customer No.";
CusShM."Ship-to Name" := Salesheader."Ship-to Name";
CusShM.INSERT(TRUE);
UNTIL CusShM.NEXT = 0;

Please guide me, how can i get this.

Thanks in advance,
Mani.

ShaiHulud · 2019-07-23

OK, so it seems that your starting point isn't Sales Header, but rather this custom table of yours. You may want to change that somehow, because Sales Header should be the starting point. Otherwise you depend on data first being inserted into your custom table, even though everything in it is based on Sales Header.

Anyway, then the code changes a little bit:

IF CusShM.FINDSET THEN BEGIN
  REPEAT
    CLEAR(Salesheader);
    IF Salesheader.GET(CusShM."Document Type", CusShM."No.") THEN BEGIN
      CusShM2 := CusShM; 
      //the copy of CusShM could be avoided with parameters on FINDSET, 
      //but personally I had bad experience with them, so using the workaround here
      CusShM2.VALIDATE("Sell-to Customer No.", Salesheader."Sell-to Customer No.");
      CusShM2.VALIDATE("Ship-to Name", Salesheader."Ship-to Name");
      CusShM2.MODIFY(TRUE);
    END;
  UNTIL CusShM.NEXT = 0;
END;

ManiNav · 2019-07-23

Hi Everyone,

Please guide me for the above requirement, this is my custom table:

I want to populate the remaining field of custom table based on order no that is in sales order(ie from Sales table).

Thanks,
Mani.

ShaiHulud · 2019-07-23

//assuming your CusShm."Document Type" has the same option values in the same order as Salesheader
//otherwise replace "Salesheader."Document Type" with CusShM."Document Type"::Order
IF CusShM.GET(Salesheader."No.",Salesheader."Document Type") THEN BEGIN 
  CusShM.VALIDATE("Sell-to Customer No.", Salesheader."Sell-to Customer No.");
  CusShM.VALIDATE("Ship-to Name", Salesheader."Ship-to Name");
  CusShM.MODIFY(TRUE);
END;

A quick explanation on what went wrong with your code:

First you try to "GET" a CusShM, with undefined "No." and "Document No." (this is where your code breaks)
Then you filter (correctly, but needlessly) your CusShM using the Salesheader that (I assume) you retrieved before). But you know that there can only be a single combination of No. + Document Type, so no need to try and loop it. Just GET and if successful, do things
No need for INIT, because it will clear all non primary key fields (will be a problem once you decide to add more fields to the table)
Good idea to VALIDATE your new fields, in case you need to do some more processing/calculations on them, like retrieve the entire Ship-to address, based on Ship-to Customer No.
INSERT would fail in your code, because you previously already found that the record exists, so you need to MODIFY it

ManiNav · 2019-07-23

Hi ShaiHulud,

Thanks for the reply,
but its not populating the remaining fields of my custom table.
Please, guide me regarding this. I am not able to get,where and what i am doing wrong.

Thanks,
Mani.

ShaiHulud · 2019-07-23

What other fields? You only showed that, besides the primary key, you only have 2 fields - "Sell-to Customer No." and "Ship-to Name".
If you have more, just keep adding them (VALIDATE) inside the code.

ManiNav · 2019-07-23

Hi ShaiHulud,

Yes, besides the primary key, only have 2 fields - "Sell-to Customer No." and "Ship-to Name".
but as I attached table,that multiple lines have to populate.Even its not populating a single line. where i am doing mistake. Please guide me.

Thanks,
Mani.

ShaiHulud · 2019-07-23

Ah, I think I got this - how are you getting your Salesheader records? Are you looping through something? Have any filters on it?
You need to write a logic that would loop through Sales Headers that you are interested in and then within that loop, put in the code I wrote. Otherwise, Salesheader will not have anything on it.

For inspiration:

CLEAR(Salesheader);
Salesheader.SETRANGE(somefield, somevalue);
Salesheader.SETFILTER(otherfield, 'condition1|condition2');
IF Salesheader.FINDSET THEN BEGIN
  REPEAT
    //<the code from before>
  UNTIL Salesheader.NEXT = 0;
END;

ManiNav · 2019-07-23

Hi ShaiHulud ,

I tried the below code, its not populating the custom table field.

CLEAR(Salesheader);
Salesheader.SETRANGE("No.",CusShM."No.");
Salesheader.SETFILTER("Document Type", 'Order');
IF Salesheader.FINDSET THEN BEGIN
REPEAT

IF CusShM.GET(Salesheader."No.",Salesheader."Document Type") THEN BEGIN
CusShM.VALIDATE("Sell-to Customer No.", Salesheader."Sell-to Customer No.");
CusShM.VALIDATE("Ship-to Name", Salesheader."Ship-to Name");
CusShM.MODIFY(TRUE);
END;

UNTIL Salesheader.NEXT = 0;
END;

Please check and guide.

Thanks,
Mani.

sorenhinrup · 2019-07-23

Try this:
if CusShM.findset then begin
repeat
IF Salesheader.get(CusShM."Document Type",CusShM."No.") then begin
CusShM.VALIDATE("Sell-to Customer No.", Salesheader."Sell-to Customer No.");
CusShM.VALIDATE("Ship-to Name", Salesheader."Ship-to Name");
CusShM.MODIFY(TRUE);
end;
until CusShM.next = 0;
end;

This will repeat your CusShM and find the correlating orders and update the fields.

ManiNav · 2019-07-23

Hi sorenhinrup,

Thank you for the reply,
I tried your code also, but its not working.Please guide or suggest me what is the mistake or any alternate way to do this.

Thanks,
Mani.

ShaiHulud · 2019-07-23

OK, so it seems that your starting point isn't Sales Header, but rather this custom table of yours. You may want to change that somehow, because Sales Header should be the starting point. Otherwise you depend on data first being inserted into your custom table, even though everything in it is based on Sales Header.

Anyway, then the code changes a little bit:

IF CusShM.FINDSET THEN BEGIN
  REPEAT
    CLEAR(Salesheader);
    IF Salesheader.GET(CusShM."Document Type", CusShM."No.") THEN BEGIN
      CusShM2 := CusShM; 
      //the copy of CusShM could be avoided with parameters on FINDSET, 
      //but personally I had bad experience with them, so using the workaround here
      CusShM2.VALIDATE("Sell-to Customer No.", Salesheader."Sell-to Customer No.");
      CusShM2.VALIDATE("Ship-to Name", Salesheader."Ship-to Name");
      CusShM2.MODIFY(TRUE);
    END;
  UNTIL CusShM.NEXT = 0;
END;

ManiNav · 2019-07-23

Hi ShaiHulud ,

thanks alot.
Its done.........

Thanks,
Mani.

how to copy customer no. from sales header table

Best Answer

Answers

Categories