Cost & Price Maintenance & Listings

Import (Vendor) Pricing

Last Revised: 01/22/14

This application is used to automatically and quickly import price information into Dynamo using a file—as opposed to manually entering price changes—using either Item Maintenance or Batch Pricing Entry.  This application only adds pricing records for existing items; it does not add new items to Dynamo.  This application is very flexible and can be used with most data files provided by vendors or by creating a compatible file using a spreadsheet application.

Import Pricing will also schedule price labels to print when an item's selling price changes provided that the Item Price Labels option is set to Y. You can enable this setting by typing CWP at a Dynamo prompt to reach Company Wide Parameters and then follow Dynamo Parameter Maintenance/Dynamo Options/Enable Item Price Labels. When the item's price changes, Dynamo will schedule new price labels to print for each item, at each warehouse. The number of labels printed is based on the quantity of the item in inventory.

Outline of steps

1. Obtain a data file from vendor.  The ideal data file is a native spreadsheet file such as a file in Microsoft Excel format, or a tab or comma delimited file.  Any file that can be opened by your spreadsheet application can be used.  Spreadsheet programs such as Microsoft Excel, Google Sheet, OpenOffice Calc, LibreOffice Calc, and others can be used.
2. Open the data file using your spreadsheet application.  The best files have a single row with column headings, followed by additional rows for each item.  Delete any other heading rows and all rows that do not contain item information such as notes or comments.
3. The Dynamo Import Pricing application looks up the Dynamo Item# using either the Vendor Part Number, the UPC Bar Code Number, or a manufacturer's number.  The file must have a column for one or more of these three numbers.  In some cases, the Vendor Part Number on the file may differ slightly from the Vendor Part# stored in Dynamo.  For example, there could be a prefix or suffix or hyphens or other characters contained on one but not the other.  The best practice would be to first modify the Dynamo Vendor Part# to match exactly the Vendor Part# field in the import file.
4. If the spreadsheet file has a column with the UPC bar code number, then the UPC column should either contain all 12 digits in the bar code number, or the first 11 digits, leaving off the check digit.  If the UPC bar code number does not include the first 6 digits which represent the manufacturer, then create a new column appending the manufacturers digits to the bar code so that it contains 11 or 12 digits.
5. In some cases there is no pricing or costing unit field.  A Pricing and Costing field is not required unless the prices or costs are per hundred or per thousand.  If all costs are per hundred or per thousand, and there is no costing unit column, then add a column and copy a "C" or "M" to each row in the spreadsheet.
6. Remove all formatting for the cost and list price columns so that there is no comma or dollar sign.
7. Although the file from the vendor will never contain the Dynamo Formula Code, you may add a column to the spreadsheet if you would like to import a Formula Code.  Note that you do have the option to retain any existing Formula Code, so you only need to add the column when you want to set or change any existing Formula Code for the item. 
8. Next, identify the column letters from the spreadsheet, used to store the various fields that can be used.  Dynamo Import Pricing can use the following fields, although all fields are optional, except at least one of the first 3 fields are required in order to lookup the Dynamo Item#.

   UPC	UPC bar code number
   Vendor Part Number	Can be the same or different from the Dynamo Item#
   Manufacturer's Part Number	Only needed when different from vendor's part#
   List/Sell Price	The list/sell price for the item.
   Pricing Unit	C=per hundred, M=per thousand, etc.
   Cost	PO Cost
   Costing Unit	C=per hundred, M=per thousand, etc.  Can differ from Pricing Unit
   Formula Code	Code that calculates the selling price of the item.

9. Once the spreadsheet file has been edited (if necessary), then save the spreadsheet file in tab delimited format on the Dynamo server.  Use the Save As spreadsheet option, then select the Tab Delimited (txt) format, and save the file in the folder on the server where the pricing files are stored.  If your Dynamo server name is simply 'server', and your company code is DS, and the pricing is for Dynamo vendor# 123456, then the pricing file would be saved as \\server\DS-shared\pricing\123456.txt.  Note that the Dynamo Import Pricing application will display this file name for your convenience, once you have entered the Dynamo Vendor#.
10. Next start the Dynamo Import Pricing application, and enter the Vendor#.
11. Indicate the column letters to tell Dynamo where each field can be located.  Touch Enter or SpaceBar to skip any field not contained in the file.
    
12. Enter the Pricing Start Date as the date that these prices will take effect.  You may enter a past, current, or future date.  Dynamo pricing is date-specific, so the price will be determined based on the order date.  Note that specifying the same Pricing Start Date as existing pricing records will cause the existing record to be overwritten during the import process.
13. The Promo Pricing Date should only be entered if the prices are temporary as for a sale or promotion, after which the previous price should go back into effect.
14. There are three options with regard to the Formula Code.

    Code	Description	Notes
    R	Retain any existing Formula Code for each item	The Formula Code in the most recent pricing record will be used for the imported record.
    S	Set Formula Code on each item to value below	The Formula Code is specified once, and will be used for all items imported.
    B	Set Formula Code to blank on each item	The new price records will not have a Formula Code, even if the previous pricing record for the item did have a Formula Code.
    M	Map Formula Code from import file	The Formula Code is in a column on the spreadsheet.

15. The Import File Name is the name of the file from the Windows perspective.
16. The File Date & Time displayed is the date and time the import file was last saved.
17. The Columns (Fields) number represents the number of columns Dynamo could locate in the import file.
18. The Rows (Records) number represents the number of item records located in the import file.
    
19. The Matched by UPC number indicates the number of items in the import file that were matched to the Dynamo Item# using the UPC bar code number.
20. The Matched by Vendor Part# shows how many items were matched by Vendor Part#.
21. The Matched by Mfg# shows how many items were matched by Manufacturer#.
22. The Unmatched Items indicate the number of Dynamo Items for this vendor that were not found in the spreadsheet.  Note that the Dynamo Import Pricing will only import pricing for items where the Vendor# specified in Dynamo Item Maintenance matches the Vendor being imported.
23. Unmatched Import Records indicates the number of rows in the spreadsheet that were not identified using the UPC, Vendor Part#, nor Manufacturer#.  Review the Unmatched Item Listing to see which items were not matched.  Perhaps there is no Vendor Part# on file in Dynamo, or there are hyphens or other characters in the Vendor Part# not included in Dynamo.
    

Import Pricing Options
Maintain import parameters	Used to specify the Field Mapping, Pricing Dates, and Formula Code option.
Open Unmatched Import Record Spreadsheet	This option can be used to create a spreadsheet that contains the Unmatched items.  It can be saved so that after some research into determining why the items were not matched to a Dynamo Item#, it could be imported without delaying the import of the matching items.
Print Unmatched Item Listing	Lists items not matched
Print Matched Item Listing	It is a best practice to review this report carefully before importing.  Look for zero costs and prices and review the gross margin before for both the old and new price.  Do not proceed with the import if this report looks incorrect.
Import Pricing	This option creates the pricing record.  You can confirm that the pricing record were added using Item Maintenance, Pricing View, as well as using Item Inquiry.  Use the Price Lookup option and change the Pricing Date to display prices and costs in effect for that date.