Friday, July 13, 2012

Sales Order Import, including Multiple Orders.

Hi Everyone.

What to do when we need quickly and effectively import numerous data into ERP. We can use Integration Services, embedded into our product. Lets go through 5 minute process of importing.
Where we are going to import. Sales order screen in Acumatica ERP.

From where we are going to import our orders? From Excel file.

What need to be done on Acumatica to achieve it?
First we should create Data Provider and attach our file to it. In theory we can use different means of data provision. From Salesforce web site, from comma separated file, from SQL directly etc. So we should tell the system where are data coming from.

Then we should attach our file to it and indicate in the File Name value column internal or external link to it

I used internal link to the file sitting inside Acumatica DB:


Last step for provider is to fill up the schema which is done by pressing these two buttons subsequently on the Schema tab:

Next step will be to map columns inside the file and our screen. This is done inside Import Scenario screen.
I am going to show resulting screen. Basically on the left are fields from the screen and on the right are values from our import file:


Now we can run the import:


And here is the resulting Sales Order:


Well, this is good to import one single order, but what if we have multiple orders in a file?
And what if it includes the same customer in couple of orders?

Solution is very siple, lets amend column [Number] in our source file or simply amend data, prepared for the import to split 5 lines into 3 different Sales Orders.

Here is what I changed in a data representation inside Acumatica:

Please note that I am using column Number as sequential number for my orders, not as actual Sales Order number, that will be issued by the system automatically.

Based on what was amended, it should end up with 3 sales orders. SO #1 and SO #3 for customer ABARTENDE and SO #2 for customer ABCSTUDIO.

Lets run the import for it and here is the result:



Works like a charm.

Have a great day,
Sergey.

9 comments:

  1. Hi Sergey,
    What is your Acumatica build version? I worked on build 3.1.0305 but it import all lines in only 1 sales order!

    ReplyDelete
    Replies
    1. Hi Nguyen Khang
      I was using version 3.00 latest build. Will try 3.1 but pretty sure problem is somewhere in your file or scenario. please copy my example.

      Delete
  2. Hi Sergey,
    I updated my import scenario with provider schema setup. I marked 3 fields SO Type, Number and CustomerID as key, it worked good for me now.
    Thanks so much!

    ReplyDelete
  3. Hi Sergey,
    How can I delete import file from my import folder (I've enabled File Storage Provider to Local Folder) when my files imported successfully?
    Khang Nguyen

    ReplyDelete
  4. Hi Khang Nguyen,
    You can click on the screen where you added the file, then click Attachments, then click Edit. You can remove the file from there.
    You can upload the latest file version right from the Process Import Scenarios screen.

    ReplyDelete
  5. Hi Sergey,
    Please explain how we can attach a file data type to the attach button on the forms.
    For eg. How can I attach a form through import process in the Journal Transactions line item or the header itself. We can attach the files when we manually enter details. But we have this scenario where we have lot of files in Locations to be attached to each batch / line items. Please advise.

    ReplyDelete
    Replies
    1. Hi Chandra, you can do that via web services. Sorry you were not at our training. Once we have documentation I will share.

      Delete
    2. Hi Sergey,
      Thanks for the reply and awaiting for the Documentation on this.regards, Chandra.

      Delete