Menu 5.20.1 - Auto Update Costs/Prices from a Spreadsheet  (Old Menu 5.9)

This program allows you to change or create pricing from a spreadsheet (file or program pricing). This program is VERY specific from the way the spreadsheet is created to where it is saved on the computer. Here are the specifics to use when creating this spreadsheet:

Use Excel to create the file, taking care that the following information is in the specified columns. Do NOT include column headings or extra notes.

• Column A = Program name (or type 'NONE' (without quotes) in cell A1 if not updating a program) OR if setting up customer specific 5.3 pricing you can enter the internal customer number in column A.  You can find the internal customer number by going to 12.2.2.2 screen 1 and looking at the upper right corner (INT CUST #)
• Column B = Product number (Johnstone Stock Number or vendor part number*)
• Column C = Program price (no $) (leave this column blank if not updating program price)
• Column D = Expiration date (Must be in the MM/DD/YY format. Leave this column blank if not updating a program.)
• Column E = Each price (no $) (Leave this column blank if not updating each price.)
• Column F = Retail (no $) (Leave this column blank if not updating retail price)
• Column G = Direct cost (no $) (Leave this column blank if not updating direct cost)
• Column H = Direct Quantity (If left blank it will not change any direct qtys already existing.)
• Column   I = Branch Cost (Leave this column blank if not updating branch cost.)
• Column J  = Program qty break price (Leave this column blank if not updating a program.)
• Column K = Program qty break( Leave this column blank if not updating a program.)
• Column L = Quantity Break 1 M-12.3.2.4, field 1**
• Column M = Quantity Price 1  M-12.3.2.4, field 6**
• Column N = Quantity Break 2  M-12.3.2.4, field 2**
• Column O = Quantity Price 2  M-12.3.2.4, field 7**
• Product Master Update
  • Column P = 12.3.2.2 1.DESC. 1 (item description)
  • Column Q = 12.3.2.2 3.CVEND# (vendor #)
  • Column R = 12.3.2.2 28.PROD CLASS (prod class)
  • Column S = 12.3.2.2 29.PROD TYPE (prod type)
• Product Detail Update
  • Column T = 12.3.2.3 15. STANDARD PACK (standard pack)
  • Column U = 12.3.2.3 16. VENDOR NUMBER (vendor number)
• Product Master Transmission Fields
  • Column V = 12.3.2.12  CATEGORY (category)
• Product Master Update
  • Column W = M-12.3.2.2, field 4. VEND DESC

*Must match exactly what is in Product Master field #4, vendor description.

**Columns L & M and N & O. **One thing to note, that there needs to be a value in L & M together...i.e. if something is in M it needs to have a corresponding qty in L.

Save the file in the C:/PICK directory and name it "price.txt" which is a tab delimited file-not an excel file. Make sure you close the spreadsheet before running the program.

Suggestion: You may want to begin blocking the Corp. updates for direct cost, etc... using Menu 12.3.2.9

This program MUST be run from the computer with the 'price.txt' file saved on it or it will not work.

Example of the file in Excel, where it is created:

Example of the file as a text document, after it is saved:

Updated 08/05/09