Uploadia by MJR - User Manual

This article explains how to use Uploadia, to upload information from Excel into Infor ERP XA. Uploadia uses the public API of Infor ERP XA that is System-Link to create transactions in XA. Uploadia supports only IDF transactions i.e. any transaction/action (Create, Update, Delete) that a user can process in PowerLink or NetLink. All of these transactions can be done via Excel using Uploadia. This includes both standard Infor XA Objects with or without extensions and user created objects in Enterprise Integrator.

 

Uploadia Pre-requisites

Server: Java version 8

IDF System-Link

Client Web Browsers: Google Chrome or Firefox

Excel file extensions supported: xlsx

Excel formatting requirements:

- Numeric values must be formatted as number without thousand separator

- dates must be in ISO format with no separator i.e. YYYYMMDD

- The first row of the Excel Sheet must be the column heading

 

To use Uploadia, start up Google Chrome or Firefox and type in the Uploadia URL provided by your IT staff. The typical URL is http://<Uploadia_Server_Name>:10080/mjraber-cloud-upload

You will be presented with the login page.

Login Page

On the Login Page

Use your IBMi or Infor XA User

Use your IBMi or Infor XA Password

Select the IBMi or XA server

Select the XA environment and Language

Click the Login Button

 

Uploadia uses SystemLink to create an Infor XA session. All authentication and authorisation for Uploadia is managed by the usual IBMi, Infor XA Cross Application Support and Enterprise Integrator security.

Entering an invalid user id or password will result in a security exception.

Click on the arrow to the left of the message modal to see details of the exception.

 

Upon successful login, the user is presented with the landing page.

Uploadia’s landing page consists of 6 sections.

 

Section 1: Contains the Excel File drop zone. It also has the cloud button that can be used to select a file using File Explorer.

Section 2: Enables a user to choose the IDF transaction that needs to be used. The transactions supported by Uploadia are Create/Update and Delete.

Section 3: Provides the list of business objects to choose from. There is a full text search option to look for business objects by name, object class, domain class, etc.

In order to display session details and logout from Uploadia, click on the user button in this section.

 

Section 4: The section at the bottom of the page enables a user to create one or more processes. Each uploaded sheet is linked to a process.

Section 5: Shows the mappings between the Excel columns and Business object attributes.

Section 6: This is the mapping canvas that is used to map Excel columns with Business object attributes.

 

Use Cases

This article will cover 3 scenarios to illustrate the use of Uploadia.

Case 1: Item Revision Base Price Upload – Simple IDF Transaction in a single process

Case 2: Supplier Quotation Upload – Complex IDF Transaction (Quote header and Quote Price Breaks) in a single process

Case 3: Price Book Upload – Complex IDF Transaction split across multiple processes

 

Case 1

Item Revision Base Price Upload – Simple IDF Transaction in a single process

Step 1: Prepare Excel Sheet

The Excel Sheet contains the following columns.

Excel Columns:

Site Id

Item Number

Item Revision

Effective Date

Price

PricingUm

1

000001

 

20180317

100.50

ST

1

000002

 

20180317

110.50

ST

1

000003

 

20180317

120.50

EA

Note: all dates must be in YYYYMMDD format i.e. ISO format with no separators

In XA there are currently no Item revision Base prices for effective date 20180317.

Step 2: Create the Mapping between Excel and Item Revision Base Price Business Object

Drag and Drop the Excel File into the Excel Drop Zone (Section 1)

 

 

Select the Item Revision Base Prices Business Object from Section 3

 

Drag and Drop the Excel Sheet from Section 1 on the left into the Mapping Canvas in the middle (Section 6). This will display all the columns in the Excel Sheet.

 

Drag and Drop the Item Revision Business Object from the right into the Mapping Canvas. This displays the attributes or properties of the selected business object. The Key attributes of the business object are highlighted in orange.

Do the mapping by dragging the Excel Column and dropping it onto the relevant business object attribute. For example, Column A (Site Id) is mapped to attribute Site.

Note: Uploadia provides a full text search to look for Business object attributes and Excel columns in order to facilitate the mapping process.

The Excel column - Attribute mapping is then displayed in section 5.

If an excel column is mapped incorrectly, simply click on the mapping in section 5 and it will be removed. You can then map the Excel Column again as needed.

 

Step 3: Save the mapping

Save the mapping so you can reuse it the next time you want to upload information. In Section 2 click on the Save button.

 

Give the mapping a name. Check ‘Save as Readonly Mapping’ if you don’t want the mapping to be altered by other users.

The saved mapping is private to the user who created it. The mapping is saved as a json file in the Mapping folder on the webserver on which Uploadia is deployed.

To make the mapping available to other users, simply copy the json file into the users IBMi/XA User Name Folder.

The available mappings are displayed in the drop down as shown below.

 

Step 4: Start the Upload

The Upload process will list the rows in the Excel Sheet and start the update into XA using SystemLink. Rows that are created successfully are highlighted in green. Rows that failed are highlighted in red.

Green = Success, Red = Failed

The exceptions for a failed transaction can be displayed by clicking on the failed row.

Row 4 failed because the pricing unit of measure is invalid

 

A filter in section 2 can be used to toggle between all rows and error rows

Toggle between All rows and Error only rows

 

To fix and reupload the failed rows, you can correct the data in the Excel Spreadsheet and reload the failed rows or you can fix the data directly in the application. To fix the pricing unit of measure, double click the column and change the value. Commit the changes by clicking the “tick” button.

Re-process failed rows

Once the data errors have been fixed, reprocess the failed rows by selecting them (Use Shift or Control Key to select multiple rows). Press the resend selection button.

 

All Item Revision Base Prices have now been successfully created in XA.

Since Uploadia uses System-Link, normal Maintenance history is available as an audit trail.

 

Case 2

Supplier Quotation Upload – Complex IDF Transaction in a single process

The creation of Supplier Quotations with price breaks involves 2 business objects Quote (Header) and Price Breaks (Detail). The quote number is normally system assigned. The system assigned quote number must be referenced when creating the price breaks.

 

Step 1: Prepare Excel Sheet

Excel Columns:

 

In the excel sheet the supplier quotes are grouped by vendor item warehouse. Each row represents a quantity price break. A Quote Number column is required to map the quote number as it is a key attribute in the Quote Business object. If the Quote Number is not user provided, do not enter any details other than the column heading. This is true for all attributes that are system assigned.

 

Step 2: Create the mapping

The mapping for this scenario will have 2 transactions. After logging into Uploadia, select the Quote object from section 3.

 

Quote Header for first Create Transaction

 

Upload the Spreadsheet in Section 1 (Drop Zone). Drag and drop the sheet into the mapping canvas (Section 6). Also drag and drop the Quote business object into the mapping canvas.

 

Canvas with Excel Columns and Quote object ready for mappings

 

Except for column G (Quantity) and H (Price), all other columns are mapped to the Quote object. The columns are mapped by dragging and dropping the column over the business object attribute or vice versa.

 

Mapped Columns and Attributes for Quote Header

 

The next step is to map the quantity price breaks. In section 2 (Transactions) click on the New button. This will add a new ‘Create’ transaction and display an empty mapping canvas.

  

New Transaction in the same Process

 

Attributes Mapped in a Previous Transaction are displayed in Blue in the new Transaction.

 

In order to map the quantity price breaks we create a new transaction. When multiple transactions are used in the same process, previously mapped attributes become available to the new transaction as Value References. This feature is required to map a system assigned value like Quote Number, to create quantity prices breaks for the same quote.

 

Select the Quantity Price Breaks business object. Drag and Drop it into the mapping canvas.

 

Matching Column Headings in Excel and Business Object Attributes are auto-mapped

 

Map the attribute Item to Column B, attribute Vendor to Column A. The attribute Quote from the Quantity Price Breaks object will be mapped to the attribute Quote listed in blue. The value assigned to the Quote during Quote Header creation can be used when creating the quantity price breaks.

 

Quote Attribute in Quantity Price Break is mapped to Quote ValueRef from Quote Header

 

When the mapping is complete, save your mapping and click the upload button to start the Upload

 

Mapping completed and saved.

 

Start the upload process, by clicking the Upload button in red. This will display the excel rows and when the upload the process is completed (COG wheel stops spinning), the results are displayed in green (success) or red (failed)

  

All quotes have been created successfully.

  

Case 3

Create Price Book, Price Book Version and Price Book Page 4 Price

This scenario demonstrates the ability to use multiple processes when creating multiple related business object instances. This approach is preferred to using multiple transactions in a single process, if there are no system assigned keys.

 

Step 1: Create Excel Sheets

Price Book

 

 Price Book Version

 

 Price Book Page4

 

The excel workbook has 3 sheets as shown above.

 A new process will be used for each sheet and the mapping will be created for each process. After logging into Uploadia, upload the Excel File. It will display all 3 sheets in the file.

 Three Excel Sheets from the Excel file

 

Drag and drop the Price Book Sheet into the mapping canvas. From section 3, search for the Price Books Business Object and drag and drop it into the mapping canvas.

Mapping canvas showing Price Book sheet columns and Price Books Business Object attributes

 

Map the relevant Excel Columns and Business Object attributes.

Mapped attributes Price Book, Price Book description and Discount Markup Type.

 

Save the mapping and start the upload by clicking on the Upload button.

 

Price Book Created Successfully

 

To Create the Price Book Version for the above Price Book, click on the “+” button in Section 4 (Processes) of the landing page.

 

Create new process for Price Book Version

 

A new process with an empty mapping canvas is displayed.

 

Drag and Drop the sheet Price Book Version and Business Object Price Book versions into the mapping canvas. Map the relevant attributes and Save the Mapping.

 

Price Book version Mapping

 

Click the Upload Button to start the Creation of Price Book Version

Price Book version created sucessfully

Now create a new process to upload the Page 4 pricing (3rd sheet in the Excel File)

 

The Mapped Canvas is shown below.

  

Upload of Page 4 (Customer Item Discounts)

Only one of the Page 4 prices was created successfully. Row 3 in the excel sheet failed to create because of an invalid item price class. The data error can be fixed directly in Uploadia by double clicking the item price class cell. This changes the cell to edit mode. Incorrect Price Class OBC is changed to OBS and the selected row resubmitted by clicking the RESEND SELECTION button.

Correct Item Price Class by editing the cell.

 

Customer Item Discounts (Page 4) Prices created successfully.

  

Deleting uploaded sheets and processes

If the data in the Excel sheet is wrong or the data needs to be reloaded into Uploadia, you can delete an uploaded sheet and a complete process.

To delete a sheet, simply click the x that is displayed when you hover over the sheet. You will be prompted with a confirmation. Click yes to delete the sheet or cancel your action.

 

Delete an uploaded sheet

 

To delete a process, click the x that is displayed when you hover over the process. You will be prompted with a confirmation. Click yes to delete or cancel your action.

Delete a Process

  

Reuse Saved Mappings

To reuse a mapping upload the Excel workbook. The Excel Sheets will be displayed in section 1 of the landing page.

Select the mapping from the list of saved mappings. This will load the mapping. A confirmation is displayed when the mapping has loaded successfully.

 

Confirmation that mapping has been loaded.

Saved mappings use the Excel sheet name. If a user changed the Excel sheet name and then selects a mapping, they will be prompted to drag and drop the sheet into the mapping canvas.

Excel Sheet Name changed from Supplier Quote to Quote.

 

Delete Saved Mappings

Mappings are private to users and can be deleted by them. To delete a mapping click the “Trash Can” icon next to the mapping.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Login

Cookies erleichtern die Bereitstellung unserer Dienste. Mit der Nutzung unserer Dienste erklären Sie sich damit einverstanden, dass wir Cookies verwenden.
Ok