Get documents about "Adding Custom Fields to the Purchase Order - Excel
W
Description
Adding Custom Fields to the Purchase Order document sample
Document Sample
Sales Order Items Upload
Note: the data lines in the yellow line below are SAMPLE lines. Please do not erase them.
Item # Requested ship date Ordered qty Ordered qty unit Price Lot #
136283 12/15/07 99 ea 4.85
6CWFN21 12/15/07 215 cs 2.50
34PJ5 1/1/08 50 lb 0.78
20558a68-3da4-422a-8a5e-a2c2de5cbedd.xls Instructions
Mass Upload for your Sales Order Items
This tab includes instructions you should follow to successfully upload your data into SmartTurn. Uploading your sales order items data saves time because
you enter data once (up to 500 items at once) and then see it offered as choices in appropriate places throughout SmartTurn. For instance, after you make a
successful sales order items upload, you can make one click to ship the entire Sales Order. With one upload, you are communicating data across various
modules within SmartTurn.
Please read these instructions prior to upload so that you can gain an understanding of what is required during the upload process, namely the following key
concepts:
► Reference data - Please make sure you have set up your Warehouses, Locations, and Item Masters in SmartTurn before uploading sales order items
► Formatting constraints on the data spreadsheet
► Formatting constraints on the worksheet tab name
► Filtering interferes with the upload
► Blank Item # fields stop the upload
These instructions take you step by step through the process of copying your existing data in a way that SmartTurn can read it. They also provide the following
resources that you will need:
► A reference table for the data field definitions used in the worksheet
► Tips and suggestions for a successful upload
---------Basic Concepts to know before uploading your data to SmartTurn--------
SmartTurn wants to make it easy for you to transport and maintain your data between your accounting application and SmartTurn WMS. Before starting, there
are five basic concepts to keep in mind:
► REFERENCE DATA - Some data fields in SmartTurn rely Mass Upload Sequence:
on other data fields in SmartTurn in order to make sense. So *1st: Customers, Vendors, Warehouse(s)
the concept to remember is that when you're uploading data to 2nd: Locations
SmartTurn, you'll need to follow a certain sequence. (See Mass 3rd: Item Masters
Upload Sequence to the right).
*you can upload these first three in any order, but all three must be uploaded before you
can proceed to uploading Locations.
► SPREADSHEET FORMATTING CONSTRAINTS - Names of For example: "Quantity" would not work as a column header for the data field, "Ordered
Columns in the workheets directly correspond to data fields qty." There must be an exact match.
in SmartTurn. This means that column headers in SmartTurn's
data upload worksheets specify where to put the data you supply
within SmartTurn. If you inadvertently change the column
headers, the upload won't work because the column header no
longer corresponds to a specific data field in SmartTurn.
Similarly, if you add column headers that SmartTurn hasn't
specified, the data will not correspond to anything in SmartTurn,
and the upload will not be successful. (See example to the right.)
page 2 of 8
► SPREADSHEET FORMATTING CONSTRAINTS - Names of
Columns in the workheets directly correspond to data fields
20558a68-3da4-422a-8a5e-a2c2de5cbedd.xls Instructions
in SmartTurn. This means that column headers in SmartTurn's
data upload worksheets specify where to put the data you supply
within SmartTurn. If you inadvertently change the column
headers, the upload won't work because the column header no
longer corresponds to a specific data field in SmartTurn.
Similarly, if you add column headers that SmartTurn hasn't
specified, the data will not correspond to anything in SmartTurn,
and the upload will not be successful. (See example to the right.)
► SmartTurn looks for a specific worksheet name during For example: if you rename the SmartTurn Worksheet from 'SalesOrderItems' to
upload. In other words, the name of the worksheet within the file 'SOItems_071215' and try to upload it, the upload will fail. By contrast, it is perfectly fine
you upload is important; it must match the name of the to name your SmartTurn upload files according to the purchase order they are for:
worksheet SmartTurn is expecting to import. (See example to SmartTurn_SOItems_071215.xls .
the right.) Note that while you shouldn't change the worksheet
name within the SmartTurn upload template, you can name the
.xls file whatever you want.
► Filtering interferes with the SmartTurn upload process. For example: you may like to review your SO items data by filtering it for blank cells. If
Filtering may cause errors in the upload process, so filtering you left this filter on, the upload to SmartTurn would fail. To turn this off, click into the
must be turned off prior to upload. SmartTurn can import only cell in the worksheet containing the filter arrow. In the Excel Toolbar, select Data > Filter >
unfiltered worksheets. (See example to the right.) Autofilter. This will turn off the filtering.
► A blank "Item #" cell is the cutoff point for the data For example: you may have 82 PO items to upload. To have a successful upload, you
records. "Item #" is the first column in the spreadsheet, and would key in the item number in the first cell of each row containing your PO item record.
SmartTurn uses it to find out how to process the row. Once the However, if you inadvertently leave the first cell in the 3rd row blank, the upload would
processor sees a blank "Item #" cell, it stops looking for data stop after processing just your first two records.
records, and will complete the upload with the data it has already
read.
-------Steps to Complete the Sales Order Items Worksheet---------
What to Expect: Depending on the number of sales order items and condition of your existing item data, SmartTurn expects this process to take anywhere
from 10 minutes to an hour. The process involves extracting data from the system you are currently using to track sales order item lists, re-arranging a copy of
this data to conform to the SmartTurn upload system, and uploading.
1. Obtain an electronic version of your existing data. Make a copy of an existing spreadsheet of your item data for a particular sales order. Many
accounting packages have an export feature to facilitate this process.
If you do not have your data in electronic format, you may elect to use this spreadsheet for data entry.
page 3 of 8
20558a68-3da4-422a-8a5e-a2c2de5cbedd.xls Instructions
2. Open the import template. Look at the SalesOrderItems tab in this file. (It might also be helpful for you to print this out so you can clearly see the
names of the columns SmartTurn uses.)
Tip: The column headers contain information. Click column header cells in the
SalesOrderItems worksheet to see important information about values for the field,
whether the field is required or optional, etc.
3. Complete the SmartTurn Upload Spreadsheet. For each column header in the SmartTurn SalesOrderItems tab, match a column header in the
existing spreadsheet containing your items data. Notice that the column headers are different colors. The fields that correspond to the column headers are
either optional or required. Click into the cell to see more information in a pop-up:
►Required fields are denoted by Red column headers. These fields must be completed for a successful upload.
►Optional fields are denoted by Black column headers. You can still have a successful upload with these fields left blank.
Tip: Reconcile your column header names with SmartTurn. Make note of any
differences between the column header names in your data and the column header
names in the SmartTurn upload spreadsheet. Also, make note of any extra or missing
columns in your data set. Include all SmartTurn column headers in the upload, exactly
as in the SmartTurn spreadsheet.
page 4 of 8
20558a68-3da4-422a-8a5e-a2c2de5cbedd.xls Instructions
4. Upload the SmartTurn spreadsheet. When your spreadsheet is complete, log in to SmartTurn.
a. Go to Ordering > Sales Orders and select Add. Enter the Sales Order header information, including the SO Date, the Ship To customer, and any other
information required by your operation.
b. At the bottom of the screen under SO Items, select Import. Follow the prompts to browse for the file and complete the import.
page 5 of 8
20558a68-3da4-422a-8a5e-a2c2de5cbedd.xls Instructions
-------Sales Order Items Field Names and Definitions---------
Sales Order Items Fields Field Definition and Value Restrictions (if any)
Item # Required: The item master number your company uses to reference the item
description, materials and handling specifications, sales & fulfillment specifications, &
warehouse-specific information. This # must already be set up in SmartTurn for your
import to succeed.
Requested ship date The date the item must be shipped. Requires proper formatting (mm/dd/yyyy).
Ordered qty The quantity being ordered.
Ordered qty unit The unit of measurement (UoM) for the ordered qty. If supplied, must match a UoM
defined in the item master *for this item*. If left blank, this field defaults to the shipping
UoM specified in the item master.
Price The price of the item. If left blank, this field defaults to the sales price from the item
master. (This is the sales price for the shipping unit of measure.)
Note: Do not use the '$' symbol.
Lot # Enter the Lot #, including wildcards ('*' for one or more characters and '?' for exactly one
character).
All inventory items used to fulfill this sales order item must have a matching Lot #.
Serial # Enter the Serial #, including wildcards ('*' for one or more characters and '?' for exactly
one character).
All inventory items used to fulfill this sales order item must have a matching Serial #.
License # Enter the License #. Exact matches only (no wildcards). This must be a license number
that already exists in SmartTurn.
All inventory items used to fulfill this sales order item must have a matching License #.
Expire Date Enter the Expire Date, in format mm/dd/yyyy. Exact matches only (no wildcards).
All inventory items used to fulfill this sales order item must have a matching Expire Date.
Product ID Enter the Product ID, including wildcards ('*' for one or more characters and '?' for
exactly one character).
All inventory items used to fulfill this sales order item must have a matching Product ID.
page 6 of 8
20558a68-3da4-422a-8a5e-a2c2de5cbedd.xls Instructions
<name of your custom field> If you have defined custom fields for business process Inventory, add one column for
each custom field.
The column name must match exactly the name of the custom field you have already
defined in SmartTurn. For each sales order item, enter the value for the custom field.
The value must be a valid value for that custom field. For example, if your custom field is
a list type custom field, the value you enter must match exactly one of the list options
you defined when you created your custom field.
If you do not add a column for a custom field, its value on the sales order item is based
on the Business Process Details you defined when you created your custom field.
If you do add a column for a custom field, the value you enter overrides any default set
up in the Business Process Details.
If you are importing custom field information, you may need to "unprotect" the
ReceiptItemsData worksheet in order to add the custom field columns. To "unprotect"
the worksheet, select Tools>Protection>Unprotect Sheet (Excel 2003) or
Review>Changes>Unprotect Sheet (Excel 2007).
(Custom fields are available in the Edit pop-up on the Custom tab, or if you customize
the Create Sales Order page.)
page 7 of 8
20558a68-3da4-422a-8a5e-a2c2de5cbedd.xls Instructions
-------Tips for a Successful Sales Order Items Data Upload to SmartTurn---------
► Turn off filters: If you use filters in your spreadsheet to review data, please disable them in the SmartTurn SalesOrderItems worksheet before you upload
to SmartTurn.
► Deactivate Sorting: If you use sorting in your spreadsheet to review data, please disable it in the SmartTurn SalesOrderItems worksheet before you
upload to SmartTurn.
► Freeze Panes: When you review the data you paste into the SmartTurn SalesOrderItems worksheet, you can use the default Freeze Panes formatting to
examine your data. This tool will help you carefully review the data by showing each record in close proximity to the column headers and required record
information. If you are unfamiliar with Freeze Panes, see Microsoft Excel Help.
► Automate if possible: Consult your accounting software package to see if you can export data in the same format required by SmartTurn.
► Examine twice, Copy & Paste once: To borrow from an old carpenter's rule, make sure your data records are clean and correct before pasting them into
the SmartTurn SalesOrderItems spreadsheet. This is crucial for handling the data in a way that will prevent overwrites and losses.
► SmartTurn can import up to 500 records with this template: Please limit each upload to 500 records.
► SmartTurn prevents you from Adding or Deleting rows and columns in the template: SmartTurn has protected the upload template against adding or
deleting rows. Paste additional rows at the bottom of the data instead of adding rows. Instead of deleting rows with incorrect data, fix the data.
page 8 of 8
Related docs
