Newbie question - Import Sales Lines and Sales Headers

Ilona · 2007-01-17

Hi all, I need to import Sales Lines with their headers. It is 1 line per customer.
I used 2 dataports, 1 for the header and one for the lines.
Works fine, only the most important thing not...The document number is, altough the same, somehow not connected to the header.
I searched the forum for a couple of hours, but didn't found anything that could help me.
All the data come's from the csv file, if nessesary also the document number and type.

But how do you connect them so you can also see them as an invoice for the customer?

couberpu · 2007-01-17

Ilona,

I did something like that before. But I used one dataport instead. I first format the data with same number of fields and use type = 1 for sales header, type = 2 for sales line. Create a global variable SalesNo to pass the sales header document number to sales lines.

It is something like this :

CASE OType OF
'1':
BEGIN
"Sales Header"."Document Type" := "Sales Header"."Document Type"::Order;
SalesNo := '';
NoSeriesMgt.InitSeries("Sales & Rec Set Up"."Order Nos.",'S-ORD',"Sales Header"."Order Date",SalesNo,
"Sales Header"."No. Series");
"Sales Header"."No." := SalesNo;
<Codes>
"Sales Header".INSERT(TRUE);

END;

'2':
BEGIN
"Sales Line".INIT;
"Sales Line"."Document Type" := 1;
"Sales Line"."Document No." := SalesNo;
"Sales Line".VALIDATE("Sales Line"."Document No.");

"Sales Line"."Line No." := LineNo;
"Sales Line"."Sell-to Customer No." := SalesHead."Sell-to Customer No.";
<Codes>
END;
Hope this would help.

CouberPu

Ilona · 2007-01-17

wow, I guess this helps for sure.
I only don't get the type 1 and type 2 thing. Do you create the dataport for table 36 or 37?

Savatage · 2007-01-17

Yes - you need to define a document type in the sales line else the system doesn't know if it's a credit, quote,invoice or sale.

Ilona · 2007-01-17

:oops: I thought you meant Type 1 is for the sales line and type 2 is for the header or the other way around!
Ok, all the data that has to be imported is Invoice so that will be type 3?

Ilona · 2007-01-17

Stupid me!

Indeed type 1 is the header and type 2 are the lines.

couberpu · 2007-01-17

For both!
In dataport DataItemTable = 36, but set table 37 as a global variable.

and the input file looks like this:

50076 11/17/06 283676/7004 PICKUP WALLACE HARDWARE 5050 S.
CROCKETT PKWY XXXXXXXXXX TN 37813 53917 2 DA
0 0 0 0 1 Item
50076 11/17/06 283676/7004 PICKUP WALLACE HARDWARE 5050 S.
CROCKETT PKWY XXXXXXXXXX TN 37813 53917 2 DA
10000 FS4 100 100 .00000 2 Item
50076 11/17/06 283676/7004 PICKUP WALLACE HARDWARE 5050 S.
CROCKETT PKWY XXXXXXXXXX TN 37813 53917 2 DA
20000 130 400 400 .00000 2 Item

As you can see type 1 and type 2 has same number of fields so I can define dataport fields, using variables, and assign them to the proper table fieldname based on type.

Ilona · 2007-01-17

But, just to be sure, you run the dataport twice then?

Feeling a kind of stupid :?

Savatage · 2007-01-17

note you can use one dataport for sales header & lines

the data file just needs a space

First part of your text file will be the headers

Sales invoice Header.....
Sales invoice Header.....
Sales invoice Header.....
<space> it's actually changeable using the DataItemSeparator property but it makes it look cleaner
Sales invoice lines.....
Sales invoice lines.....
Sales invoice lines.....

You can see the one we use here :
http://savatage99.googlepages.com/Sales ... Import.doc

Ilona · 2007-01-17

:shock:
NoSeriesMgt.InitSeries doesn't work. Is that because of not having the sp1 version?

It says that initseries is an unknown variable.

Savatage · 2007-01-17

When you post a sales order or invoice the infomations is transfered to the sales invoice header table and sales invoice line table.

So you don't or probably can't import anything into these tables!

Savatage · 2007-01-17

to simplify....
what you need to get a header is

Doc Type & Invoice No & Customer Sell-to No
"Invoice","ABC123","1111"

Then for the sales lines you need

Doc Type & Invoice No & Type & Line No & Item No & Qty
"Invoice","ABC123","Item","10000","Item1","10"
"Invoice","ABC123","Item","20000","Item2","10"
"Invoice","ABC123","Item","30000","Item3","10"

That's pretty much it.

You have a new invoice # abc123 for cust #1111 & it now has 3 items

Note: NoSeriesMgt. should be type Codeunit # 396

Ilona · 2007-01-18

Savatage, it seems to take a while for the dataports to work like a breeze....

Nothing happens while importing......

This is what I made of it:

Sales Header - OnBeforeImportRecord()

INIT;
OType := SalesHead."Bill-to Name 2";
CASE OType OF 
'1': 
BEGIN 
"Sales Header"."Document Type" := "Sales Header"."Document Type"::Invoice;
SalesNo := ''; 
NoSeriesMgt.InitSeries("Sales & Rec Set Up"."Invoice Nos.",'V-FACA',"Sales Header"."Order Date",SalesNo,
"Sales Header"."No. Series"); 
"Sales Header"."No." := SalesNo;
"Sales Header"."Sell-to Customer No." := CustNo;
"Sales Header".INSERT(TRUE); 
END;

'2': 
BEGIN 
SalesLine.INIT;
SalesLine."Document Type" := 3;
SalesLine."Document No." := SalesNo;
SalesLine.VALIDATE(SalesLine."Document No.");
SalesLine."Line No." := LineNo;
SalesLine."Sell-to Customer No." := SalesHead."Sell-to Customer No.";
SalesLine."Shortcut Dimension 1 Code" := Kostenplaats;
SalesLine.Description := Omschrijving;
SalesLine.Amount := Bedrag;
//<Codes>
SalesLine.INSERT(TRUE);
END;
END;

Sales Header - OnAfterImportRecord()
CLEAR(SalesHead."Bill-to Name 2")

Please tell me I'm just doing something wrong.....

Savatage · 2007-01-18

where to begin :-k

If you always want to make just Invoices
first you have to get a "No."
"Document Type" = 'Invoice'; //Sets the type to invoice

OType := SalesHead."Bill-to Name 2"; //not sure why this equals a number

this looks good
"Sales Header"."No." := SalesNo;
"Sales Header"."Document Type" = 'Invoice'; //Sets the type to invoice
"Sales Header"."Sell-to Customer No." := CustNo;
VALIDATE("Sales Header"."Sell-to Customer No." );

Where are you specifiing the item #, the item type & the qty?

you should make them variables to and add them to your code.

did you see the sample dataport I posted or the simple sample?
It's always easier to start small (test it) & if everything os going acording to plan then you can expand it by adding other stuff.

Savatage · 2007-01-18

here is a small sample dataport that you can use as a guide

it's # is 50013 Just import the fob file
http://savatage99.googlepages.com/Sales ... Import.fob

& Sample Datafile
http://savatage99.googlepages.com/test.txt

just edit the test.txt file with some of your own item#'s
just replace 31035,31040,31050 with your #'s
& change 1111 to a customer # of your own

OBJECT Dataport 50013 Import Item Info-Sale Orders
{
  OBJECT-PROPERTIES
  {
    Date=01/17/07;
    Time=[ 7:56:22 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    OnPreDataport=BEGIN
                    OrderCount := 0;
                  END;

    OnPostDataport=BEGIN
                     CurrFile.CLOSE;
                     IF CONFIRM(text0001,TRUE,OrderCount) THEN
                     ERASE(CurrDataport.FILENAME);
                   END;

  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table36;
        OnAfterImportRecord=BEGIN
                              VALIDATE("Sales Header"."Sell-to Customer No.");
                            END;

      }
      FIELDS
      {
        {      ;     ;"Document Type"      }
        {      ;     ;"No."               ;CallFieldValidate=Yes }
        {      ;     ;"Sell-to Customer No.";
                                           CallFieldValidate=Yes }
      }
       }
    { PROPERTIES
      {
        DataItemTable=Table37;
        OnAfterImportRecord=BEGIN
                              "Sales Line".VALIDATE("No.");
                              "Sales Line"."Qty. Ordered" := Doc_Qty;
                              "Sales Line".Quantity := Doc_Qty;
                              "Sales Line".VALIDATE("Qty. Ordered");

                              OrderCount := OrderCount + 1;
                            END;

      }
      FIELDS
      {
        {      ;     ;"Document Type"      }
        {      ;     ;"Document No."       }
        {      ;     ;Type                 }
        {      ;     ;"Line No."           }
        {      ;     ;"No."                }
        {      ;     ;Doc_Qty              }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      Doc_Qty@1000000000 : Integer;
      text0001@1000000001 : TextConst 'ENU=%1 Items Imported / Would You Like To Delete The File?';
      text0002@1000000002 : TextConst 'ENU=Item %1 is Blocked';
      OrderCount@1000000003 : Integer;

    BEGIN
    END.
  }
}

couberpu · 2007-01-18

Ilona wrote:

:shock:
NoSeriesMgt.InitSeries doesn't work. Is that because of not having the sp1 version?

It says that initseries is an unknown variable.

Did you include code unit NoSeriesManagement as part of global variable?

Savatage · 2007-01-18

I just noticed I use ("Qty. Ordered");

this is a mod of ours you'll probably have to change it to just "Quantity"

Ilona · 2007-01-18

I abuse that field for importing type 1 and type 2.
After that I clear it.

You're right, I need quantity, but nu item no. etc.
What that customer is doing now manually is :

select customer, select GL Account with number 8030, type a discription, quantity =1, unit price excl VAT, and a not standard field called Kostenplaats.

I now did instead of a code validate the checkmarks. I guess there should be no difference.....
Getting an error message now.

I'll check some things first now. (like quantity etc)

Ilona · 2007-01-18

I think I found the problem.

There is a "Shortcut Dimension 1 Code" in tabel 36. The name of the field is KOSTENPLAATS. This field is being filled with numbers from the text file.
But KOSTENPLAATS is actually a Dimension Code in tabel 349. And the number from my text file are actually the corresponding codes from table 349. KOSTENPLAATS is also a code in table 348.

Because now I'm getting this error message:

There is no Dimension Value within the filter
Filters: Code: 0, Global Dimension No.:1

Do I have to set a filter? And then make a record variable?

couberpu · 2007-01-18

Savatage wrote:

here is a small sample dataport that you can use as a guide

it's # is 50013 Just import the fob file
http://savatage99.googlepages.com/Sales ... Import.fob

& Sample Datafile
http://savatage99.googlepages.com/test.txt

just edit the test.txt file with some of your own item#'s
just replace 31035,31040,31050 with your #'s
& change 1111 to a customer # of your own

This is cool! I did not know the proper way before, now I do. This Is so cool!!!

I learn something new everyday.
\:D/

Savatage · 2007-01-18

Ilona wrote:

Iselect customer, select GL Account with number 8030, type a discription, quantity =1, unit price excl VAT, and a not standard field called Kostenplaats.

in the sample datafile - you need to change the line file "Item" to "G/L Account" and change the item # to your g/l #

couberpu wrote:

This is cool! I did not know the proper way before, now I do. This Is so cool!!!
I learn something new everyday.
\:D/

I'm glad it helped it's not so hard after you play around with it a few times.
Note if you change "Invoice" to "Order" it's now a sales order.

Ilona · 2007-01-18

Hi Savatage,

I don't have ' item' somewhere, it's already G/L Account.
And I have Invoice everywhere instead of order.

Still, the dimension 1 error appears......

Savatage · 2007-01-18

are you talking about the sample dataport fob & sample datafile I posted?

It works try it with the instructions for slight changes you need to make.

You'll see that it creates a sales invoice. once you got that going we can make changes to fill you needs

Ilona · 2007-01-18

No, I'm talking about the mine.

The dimensions code 1 error is solved now. I removed the checkmark for calculate field.

But now I'm back at the beginning, nothing happes while importing. I'm not running in the designer.

couberpu · 2007-01-18

Ilona wrote:

No, I'm talking about the mine.

The dimensions code 1 error is solved now. I removed the checkmark for calculate field.

But now I'm back at the beginning, nothing happes while importing. I'm not running in the designer.

Please check if you have INIT; in your
Sales Header - OnBeforeImportRecord()

and also check the "Autosave","Autoupdate" and "Autoreplace" properties.

IF you see the code in my example, I have "Sales Line".INIT under type 2. You need to do this in BeforeImportData to init sales header when you using dataport to import sales header data.

You might want to change the "FieldStart Delimiter", "Fieldend Delimiter" and "Field Separator" if the dataport did not read the imported data correctly.

That is what I could see in your dataport.

Good Luck!

Savatage · 2007-01-18

perhaps you can show us 1 line of your sales header text file & 1 line of the sales line text file so we can see if all the necessary info is there to begin with.

Howdy, Stranger!

Quick Links

Categories

Newbie question - Import Sales Lines and Sales Headers

Comments