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.
Server: Java version 8
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.
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.
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
Item Revision Base Price Upload – Simple IDF Transaction in a single process
Step 1: Prepare Excel Sheet
The Excel Sheet contains the following columns.
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.
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
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.
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 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.