Magento Connector - Sample Integration: Order Download
Overview
This sample job will download “Pending” sales orders from a Magento web store into an accounting system (In this sample, to Sage 300), and then update the order status in Magento.
Setup
There are a few setup steps which will need to be done before this sample job will work correctly. The sample job makes a few assumptions, and it is possible for these setup steps to be ignored if the job is modified accordingly. For example, the sample job will attempt to find an existing Sage Customer by email address, and if no matching Customers are found, an IMan “Counter” is used to create a new IDCUST value. In order to accomplish this, an IMan “Lookup” in addition to the Counter will need to be set up. If your integration uses a single Sage Customer for all incoming orders, then that value will need to be hardcoded, and the Lookup and Counter will not be needed.
Additionally, after import to Sage, the sample job will update Magento with a new order status of “Staging.” This value will need to be setup in the Magento admin backend as it is not a standard “Order Status Code.” If you prefer that the sample job updates the Magento order status to a standard value, then the setup step of creating a new Order Status Code is unnecessary.
Optional Fields
Please see our more details documentation about Optional Fields, but for this import you will need to create (and assign) the following Optional Fields:
| - MAGCUSTID (A/R Customer) | |
| - MAGORDERID (O/E Order) | |
| - MAGITEMID (O/E Order) | Optional. If you plan to handle Shipments |
| - MAGEXPORTED (O/E Order) | Optional. If you plan to handle Shipments |
Customer Count
This lookup returns the count of Sage Customers who have a certain values in the ARCUS.EMAIL2 field. This is needed to ensure we can use the Lookup below to find that Customer record.
The Database Connection / Connection String should point to your Sage 300 install.
Customer Number Lookup
This lookup will attempt to find a Sage Customer based on the value in the ARCUS.EMAIL2 field. It should only be used when there are 0 or 1 ARCUS records with that EMAIL2 value. If there are more than 1 records with that value, an IMan error will be thrown.
The Database Connection / Connection String should point to your Sage 300 install.
Customer Number Counter
This Counter will increment a value which starts with “MAG”, has 10 character-width, and increments by 1. It is prefixed with “MAG” to show that this customer came from Magento.
Create Magento Order Status
In the Magento admin section, go to the “System” dropdown and choose “Order Statuses”:
On the following screen, there should be an option in the top right to create a new Order Status:
Clicking “Create New Status” will take you to a page where you can enter details:
In Step 6 below, we set the Magento Order Status for orders which have been imported to Sage. In the sample job, it is hardcoded to “staging”. Because “staging” is not a default Order Status, it will have to be added to the system. If you choose a standard Order Status, you can skip this step.
Job Configuration - Palette View
Transforms:
- Magento Reader - Orders
- Map Transform #1
- Sage 300 Connector - A/R Customer
- Shipping Line Aggregate
- Sage 300 Connector - O/E Orders
- Map Transform #2
- Magento Connector - Order Update
1. Magento Reader - Orders
The first step in this integration is the configuration of the Magento Reader. The Magento Reader provides the ability to download orders from a Magento web store. After adding the Magento credentials to IMan’s System Setup, the “Select System” dropdown will be populated with all Magento systems for which the credentials have been entered.
By default, the Magento Reader will pull all orders with an Order Status of “Pending”. You can specify a different Order Status to search, and can limit the number records downloaded in a single run of the task.
The sample Order Download job is configured so all “Pending” orders will be downloaded.
Select System
Select which Magento install to connect to.
Export Entity
Select which Magento entity to export. In this case “Orders”.
Comma-Separated Order Status List
Define which Magento order status to filter on.
Number of Records to Pull
Define how many records should be pulled for each run of the job.
Starting Increment Id Number
Define which Magento order “increment_id” to start on. Any orders with an increment_id value greater than the one specified will be downloaded.
Comma-Separated Custom Header/Detail Field List
Define which additional header fields to pull. The connector has no knowledge of any custom fields added to the order header, so they must be specified here and the response from Magento will be queried for these fields.
Comma-Separated Store_ID List
Magento installations allow for multiple “Stores”. This option allows you to specify that you would only want to download entities from a particular store.
2. Map Transform #1
The first Map Transform will be used to add fields to the dataset as placeholders for all the fields needed to complete the import into the accounting system that aren’t part of the data retrieved from Magento. VB script is used to generate the value for these fields, such as simple defaults, or combining (Such as making a full name by combining the first and the last).
This Map Transform will contain new fields for both Customer and Order creation inside of Sage.
All field mappings are set on the “Field Mapping” tab.
New Fields - "Order" (header)
| CustomerGroup | Logic / value for new Customer’s Customer Group. Depends on Sage 300 values. Possible value: “RTL” |
| TaxGroup | Logic / value for new Customer’s Tax Group. Depends on Sage 300 values. Possible value: “USDTAX” |
| CustomerAccountSet | Logic / value for new Customer’s Account Set. Depends on Sage 300 values. Possible value: “USA” |
| SageOrderNumber | Placeholder for Sage Order Number [Blank] |
| ShipVia | Logic / value for setting Ship-Via Code. Depends on Sage 300 values. Possible value: “CCT” |
| CreatePrePayment | Logic / value for whether payment has already been made. [True / False] |
| - BankCode | Logic for determining which BANKCODE. Only used if CreatePrePayment is true. Depends on Sage 300 values. Possible value: “SEATAC” |
| - PaymentAmount | Amount of payment made on order. Only used if CreatePrePayment is true. Depends on integration. Possible value: %grand_total |
| Reference |
Logic / value for “Reference” field on Order. We use: “Magento: “ & %increment_id |
| WarningMessage | Placeholder for import warnings back from Sage 300 [Blank] |
| ErrorMessage | Placeholder for import errors back from Sage 300 [Blank] |
New Fields - OrderDetail (line items)
| Exported | Marks that item has not shipped “0” |
| MiscCharge | Placeholder for Shipping Line |
| ExtendedAmount | Placeholder for Shipping Line |
| LineType | Hardcoded to “1” for “Item”. Set to “2” for Shipping Line in Aggregate |
Sage 300 Connector - A/R Customer (Insert/Update)
Creating / Updating a Sage Customer record in an Order import is optional.
In the sample job, the Customer record is created / updated using the Billing Address from the Magento order. Other fields such as “Group Code” and “Tax Group” are required when creating a Sage Customer record, and the values for those fields were hardcoded in step 2. In most cases, these values will either be hard-coded, or derived using simple logic.
The “Warning Message” and “Error Message” mappings are optional, but allow you to capture any information returned from Sage 300 for the Customer import.
<
Customer - Mappings
| Billing_Address_City | City |
| Billing_Address_Country | Country |
| Billing_Address_State | State/Prov. |
| Billing_Address_Street1 | Address Line 1 |
| Billing_Address_Street2 | Address Line 2 |
| Billing_AddressZipCode | Zip/Postal Code |
| billing_name | Customer Name |
| customer_email | |
| Customer_id | Customer Number |
| CustomerGroup | Group Code |
| TaxGroup | Tax Group |
| AccountSet | Account Set |
| WarningMessage | Warning Message (Optional) |
| ErrorMessage | Error Message (Optional) |
4. Shipping Line Aggregate
Create new OrderDetail line: “Shipping”
| Field | Value |
| name | Shipping line text: “Shipping & Handling” |
| MiscCharge | Miscellaneous Charges Code for shipping. “Pack” / “LINE” / “SHIP” |
| ExtendedAmount | Shipping charge GetParent(“base_shipping_amount”, “Order”) |
| LineType | “2” for Miscellaneous code. |
5. Sage 300 Connector
The Sage 300 connector is used to push data into Magento. After adding the Sage 300 credentials to IMan’s System Setup, the “Select System” dropdown will be populated with all Sage 300 systems for which the credentials have been entered.
There are many “Import Types” supported by the Sage 300 connector. For an Order import, set the “Import Type” to O/E Order.
After setting the System drop down and the Import Type drop down, jump to the “Field Mappings” tab to set up each of the individual fields:
Order - Mappings
| Billing_Address_City | Bill-To City |
| Billing_Address_Country | Bill-To Country |
| Billing_Address_State | Bill-To State/Providence |
| Billing_Address_Street1 | Bill-To Address Line 1 |
| Billing_Address_Street2 | Bill-To Address Line 2 |
| Billing_Address_ZipCode | Bill-To Zip/Postal Code |
| Billing_name | Bill-To Name |
| customer_id | Customer Number |
| Shipping_Address_City | Ship-To City |
| Shipping_Address_Country | Ship-To Country |
| Shipping_Address_State | Ship-To State/Providence |
| Shipping_Address_Street1 | Ship-To Address Line 1 |
| Shipping_Address_Street2 | Ship-To Address Line 2 |
| Shipping_Address_ZipCode | Ship-To Zip/Postal Code |
| Shipping_name | Ship-To Name |
| SageOrderNumber | Order Number |
| ShipVia | Ship-Via Code |
| CreatePrePayment | Prepayment Made Against Order (Optional) |
| BankCode | Bank Code (Optional) |
| PaymentAmount | Receipt Amount (Optional) |
| Reference | Order Reference (Optional) |
| ErrorMessage | Error Message (Optional) |
| WarningMessage | Warning Message (Optional) |
Order Detail - Mappings
| item_id | Magento Order Item Id (MAGITEMID) |
| name | Description |
| original_price | Pricing Unit Price |
| qty_ordered | Quantity Ordered |
| sku | Item |
| weight | Unit Weight |
| Exported | Magento Exported Flag (MAGEXPORTED) |
| MiscCharge | Miscellaneous Charges Code |
| ExtendedAmount | ExtendedAmount |
| LineType | LineType |
