Proposal Summary
Toolfetch would like to implement the integration between NetSuite and Do It Best Data Exchange for synchronize the items from Do It Best Data Exchange to NetSuite.
Requirement
The requirement is to synchronize the item details from “Do It Best Data Exchange” to NetSuite.
The following data need to be synced from Do It Best to NetSuite:
- Item Core Updates
- Item Attribute (Delta)
- Do it Best Stocked SKU Inventory by Date
- Item Cost Updates (requires member number)
- Stock Asset Deltas
Deliverables
Field Customizations
The Item records need to be customized, and we need to add a checkbox field and two fields for each record. These fields will display under a custom subtab called “Do It Best Sync”
- When a sync between NetSuite and “Do It Best Sync” occurs, we will check the check box in the corresponding item record in NetSuite.
- The unique ID (ItemID) of the Do It Best will be stored in the Text field in the corresponding item record in NetSuite.
- Error custom record will be stored under the “Do It Best Sync” custom subtab
Custom Record for API Information
We will create a custom record to store the API details in NetSuite. We will be storing the Subscription Key and Member Number in the custom record which will be helpful for the NetSuite user to edit the details such as update in authorization through a custom record in NetSuite.
Custom record for error handling
We will create a custom record to store the error message when the script failed to create or update item details from Do It Best in NetSuite. And this custom record will link to the item record.
- An email will send to specific employees if there is an error occur during item creation/update.
- Also, we will set the last sync date in custom date field in custom record. Along with that, stored in the under the “Do It Best Sync” custom subtab.
- This custom record will store the data we obtained from Do It Best as a JSON data
Item sync
The new items will be created, or the existing items will be updated in NetSuite on scheduled basis.
We will consider the SKU from Do It Best as unique id for item in NetSuite.
Warehouse Management in Item
A custom record will be created for storing the warehouse details, item cost and quantity on each warehouse.
The custom record will be added under new subtab “Warehouse details” as sublist.
Following Fields will be shown under “Warehouse details” subtab.
- Warehouse Id
- Warehouse Name
- Quantity In Stock
- Qty
- Price
We will store the following warehouses in NetSuite using custom records for handling Do It Best Warehouses.
- 01 = Sikeston
- 02 = Dixon
- 03 = Medina
- 04 = Waco
- 05 = Lexington
- 06 = Woodburn
- 07 = Montgomery
- 08 = Mesquite
Field mapping
| NetSuite Field | Do It Best API | Do It Best Field | Notes |
| Item Name/Number | Item Core Updates: | sku | |
| DISPLAY NAME/CODE | Item Core Updates: | productTitle | |
| PURCHASE DESCRIPTION | Item Core Updates: | shortDescription | |
| COSTING METHOD | Static value “FIFO” | ||
| DROP SHIP ITEM | Static value “Yes” | ||
| MANAGE VENDOR INVENTORY | Static value “Yes” | ||
| PREFERRED VENDOR | Static value “Do it Best Corp. (DIB)” | ||
| Purchase Price | Item Cost Updates | multValues.cost | Cost of Item |
| Vendor Quantity | Stocked SKU Inventory | Quantity | Sum total of all warehouses |
| MPN | Item Core Updates: | warehouseSpecifications. modelNumber | |
| UPC Code | Item Core Updates: | upc.productCode | |
| UPC Item | Item Core Updates: | upc.productCode | |
| Sales Description | Item Core Updates: | shortDescription | |
| Item Weight | Item Attribute (Real Time) | Weight+ 25% of weight to cover additional fees | |
| TAXABLE | Static value “Yes” | ||
| Shipping Length | Item Attribute (Real Time) | Length (In.) | |
| Shipping Height | Item Attribute (Real Time) | Height (In.) | |
| Shipping Width | Item Attribute (Real Time) | Width (In.) | |
| Color(custom field) | Item Attribute (Real Time) | Color | |
| Special Shipping Group | Standard for Non-Freight Items, Truck for Freight Items | ||
| Ship Freight | If the item ships freight this is Yes | ||
| Freight Class | If ships freight take freight class, if no freight class default to freight class 150 | ||
| Webstore Display Name | Item Core Updates: | productTitle | Display Name for Product on PDP |
| Image URL | Stock Asset Lookup | imageLocation | |
| Brand | Item Core Updates: | brand | |
| Detailed Description | Item Core Updates: | longDescription | |
| Specifications | Item specifications | The Item specification will contain following data: No: {MPN } Height: Width: Projection: Material: Pkg Qty: | |
| Website Ship Type | Standard for Non-Freight Items, Truck for Freight Items | ||
| Cost Estimate Type | Static value “Purchase Order Rate” | ||
| Display in Webstore | Static value “Yes” | ||
| Lead Time | Static Value “Ships Within 24-48 Hours” | ||
| Google Feed | Static value “Yes” | ||
| Image ID | SKU |
Image in Website
The image URL from Do It Best will be stored in a custom field in Item record. And using this url, the image will be added to website.
Brand:
The brand will be synced from Do It Best to NetSuite. If the brand is existed in NetSuite with same name of brand fromDo It best, the brand will set on the Item. Otherwise the new brand will be added in NetSuite.
The incorrect spelling of brand in Do it Best will not handled in NetSuite. If the brand’s name is not exist in NetSuite, new brand will be added in NetSuite.
Question to Do it best team:
- We haven’t found field to identify freight or non-freight items on Data from Do It Best using API. Could you please share the details of fields for the freight data using API?
Assumptions
- We will consider only inventory items for the syncing items from Do It Best to NetSuite. The Matrix, parent, and child items will not be created using this integration.
- We will not consider multiple weighing units and assume that it will be “lb” for all items.
- Multiple units of measure are not considered in this integration.
- The field warehouseSpecifications.modelNumber in the API will be same for all warehouses for one item.
- Since we are planning the one-way integration, there won’t be any sync from NetSuite to the Do It Best. Any changes in NetSuite will not be reflected in the Do It Best.
- We are not considering the deletion of items in Do It Best in this scope. We are considering only the creation and update of items.
- The migration of existing/historical data will be out of the scope of this development.
Risk
- The integration script will overwrite the vendor quantity updated by the vendor quantity automation script with the total quantity of all warehouses.
- The warehouse details in the item record will be managed using the custom records. And these details will not be available in the standard report, location-wise item fulfillment.
- The taxable field in the item record will always be yes by default.
- It is applicable for one instance.
- The default accounts will be selected in the newly created item records.