Create an import function

The following guide will describe how to import purchase orders from a CSV or Excel file to M3.

The following input data:

Giving this CSV-file:

BAOR,MSGN,FACI,WHLO,SUNO,DWDT,PUNO,PNLI,ITNO,ORQA
FCM,0000000013,A01,101,Y00001,20171101,,,Y1005-Y04-028,111
FCM,,A01,101,Y00001,20171101,,,Y1005-Y04-030,222
FCM,,A01,101,Y00001,20171101,,,Y1005-Y05-028,333
FCM,,A01,101,Y00001,20171101,2000094,,Y1005-Y05-030,444

Create new function with the VXL configurator:

Input function metadata and click next

Select the CSV file and input information on the file:

Field separator: The char that separates the fields. Typically , or ; (Note: when using an Excel file this is not needed)

Decimal separator: For double/decimal value. , or . (Note: when using an Excel file this is not needed)

Task name: The name of this import task

Header start row: Which row is the header located. This is usually 1 for CSV files

Data start row: The row the data start. Usually the row after the header row.

Start and end column: Start and end column for the data. Used for later reference. Auto-suggested

Message column: Last message from the M3 API will be written here.

Input sort columns if the CSV file is not already sorted.

Add Step: Usually an import consists of multiple steps. For this example we are planning to use PPS370MI, the API for purchase order batch entry. To import a purchase order one has perform these steps:

1. StartEntry - Creates an entry and returns a message number that will be used for this batch entry. Will be run once per batch origin (BAOR)
2. AddHead - Adds batch order head. Should be run once per combination of BAOR, FACI, WHLO, SUNO and DWDT
3. AddLine - Adds batch order lines. Will be run for each line
4. FinishEntry - Ends the batch order entry. Will be run once per StartEntry(BAOR)

The first thing to do is to add the StartEntry step. Click "Add Step"

Search for and select the transaction

Set the message column to an available column. I.e the column next to the main message column, which is L. Messages from the StartEntry transaction will be written to this column

Group columns tells VXL when to run the transaction. Setting it to A means StartEntry will run every time column A's value changes.

We'll fetch the BAOR input from column A

And the MSGN output will be written back to column B

We're finished with step 1. Save

The next step to add is AddHead. This step will need to run every time BAOR, FACI, WHLO, SUNO or DWDT changes. There is no need to run this transaction if StartEntry failed, so we'll add a constraint that StartEntry should run ok.

Most of the input fields will be fetched from the input file, but MSGN will be fetched from the StartEntry transaction.

The PUNO output field will be written back to the file.

It's time to add the AddLine step. This step will run for each row and no grouping is necessary. Note that this transaction requires AddHead to run successfully.

The input fields MSGN and PUNO are fetched from previous transactions. ITNO and ORQT are fetched from the file.

Output fields PUNO and PNLI are written to their respective file columns.

The final step to add is the FinishEntry transaction. This transaction requires StartEntry to run successfully and should be run each time the value of column A(BAOR) changes. FinishEntry has no output values.

When all steps are added, the flow should look like this:

Click next and select which roles should access the function and which environments it should be available in. Save the function.

To run the function, start the VXL client, log on and open the function.

Click the import button and select the CSV file. You might need to select the correct file type to be able to view the file.

Verify the resulting file looks ok:

And the purchase order is added to M3: