Updating Item Prices in Bulk with Price Modifier

Updating Item Prices in Bulk with Price Modifier

Overview

Price Modifier is the Master Data function in ClassicPOS that changes item selling prices through a price change document. This article shows how to build that document from an Excel file and finalize it for your outlets.

You export your items to Excel, change the prices, and copy them into an import template. You then import the file into Price Modifier and confirm the change.


Choose Your Method

You can build a Price Modifier document in more than one way. Pick the one that fits the size of your change.

Method What you do Prices come from Use it when
Add items by hand Click Add item, then use Scan or Find Item, and edit each price What you type You change a few items
Import Item List Fill the import template with item codes and prices, then go to Functions > Import Item List The file You change many items and each one gets its own price.
Import By Item Code Fill the import template with item codes only, then go to Functions > Import By Item Code The current price of each item in the system. Prices in the file are ignored. You want to load many items first and set the prices in Price Modifier afterward. Steps are in Import by Item Code Instead.

For adding items by hand, Value Editor, copying an earlier price change with New from this and Margin Profile, see What is Price Modifier Function in ClassicPOS?.


Prerequisites

  • Access to Master Data > Item List and Master Data > Price Modifier.
  • The Price Modifier permissions for your user group. An administrator sets them under User Management > Group Permission: select the user group, find the Item category, tick the permissions below and click Save.
    • Allow to create price change: lets you save a new price change.
    • Allow to update price change: lets you save changes to a price change that is Open or Confirmed.
    • Allow to confirm price change: lets you confirm a price change.
    • Allow to update master price: lets you tick Change Master Price when you confirm.
  • Microsoft Excel on your computer. The export opens in Excel, and the steps use its formula window.
  • The Price Modifier import template, saved as a CSV file. You generate it inside Price Modifier (see Generate the Import Template below).

Price Update Flow

The job has six stages. You work in Xilnex, then in Excel, then back in Xilnex.

# What you do Where What you get
1 Export the item list Master Data > Item List An Excel file with your items and prices
2 Change the prices Excel New prices in a column of the exported file
3 Generate the import template Master Data > Price Modifier > Functions > Generate File Format An import template file
4 Copy the codes and prices into the template Excel, import template A CSV import file
5 Import the file Master Data > Price Modifier > Functions > Import Item List A Price Modifier document you can save
6 Confirm the price change Price Modifier > Confirm The finalized price change

Export the Item List

Start from your current item list. The export gives you the item codes and prices to work from.

  1. Go to Master Data > Item List.
     > 
  2. In the Show list, select which items to show. Choose based on your business type.
    Show option What it shows
    All Items Normal, serialized and package items
    All Matrix Items Items with color and size only

    The list also has All Serialized Items and All Package Items.

  3. Click the three-line icon at the top right of the screen.
     > 
  4. Click Export, then Export to Excel.

  5. In the Save As window, select a folder you can find again.
  6. Enter a name in File name.
  7. Click Save.

  8. Click OK on the Export Completed message.

The exported file opens in Excel.


Change the Prices in Excel

In the exported file, put the new prices in a separate column. You can type them or use an Excel formula. For example, if D2 holds the first selling price, =ROUNDUP(D2*0.94,1) reduces it by 6% and rounds up to one decimal place, so 158 becomes 148.6. Copy the formula down to the last row.


Generate the Import Template

You can generate the import template inside Price Modifier.

  1. Go to Master Data > Price Modifier.
  2. Click Functions, then Generate File Format.

  3. In the Save As window, select a folder you can find again.
  4. Enter a name in File name. The file type is already set to CSV Comma-delimited text files (*.csv).
  5. Click Save.


Copy the Item Codes and Prices into the Import Template

The template is the file Price Modifier reads. It must be a CSV file when you finish.

  1. Open the template you generated.
  2. In the amended export file, copy the item codes. For matrix items, copy the matrix codes.
  3. Paste them into the Item Code column of the template.
  4. In the amended export file, copy the new prices.

  5. In the template, right-click the first cell of the price column that matches the price you are changing. Price 1 to Price 7 in the template match the seven price fields on the item, in the same order. Your business can rename these fields, so Price 1 may be called Sale Price, for example. Under Paste Options, click the paste-as-values icon (123).


  6. Press Esc. Do not press Enter after you paste.
  7. Click X at the top right to close the template.
  8. Click Save when Excel asks to save your changes.

  9. In the Save As window, select a folder you can find again.
  10. Enter a name in File name.
  11. Set Save as type to CSV (Comma delimited) (*.csv).
  12. Click Save.

  13. Click Yes when Excel warns that some features might be lost.

  14. Make sure the file is saved, then close it.

You now have a CSV file ready to import.

Note: If a price cell is empty, Price Modifier imports it as 0. It only applies the prices you select under Price to update, so leave unselected any price you did not fill in.

Import the File into Price Modifier

  1. Go to Master Data > Price Modifier.
     > 
  2. Click Functions, then Import Item List.

  3. In the Open window, browse to the folder where you saved the CSV file.
  4. Select the file.
  5. Click Open.

  6. Set the Effective Date. This is the date the new prices take effect.
  7. Under Price to update, tick the prices you imported and untick the rest. Price Modifier does not do this for you after an import. A price you leave unticked is not changed.
  8. Click Save if you are not ready to finalize the price change. Saving a new price change needs the Allow to create price change permission.

To finalize the price change, follow Confirm the Price Change below.


Import by Item Code Instead

Use this when you only have a list of items and want to set the new prices inside Price Modifier. Price Modifier takes each item's current price from the system and ignores any prices in the file.

  1. Generate the template as shown in Generate the Import Template.
  2. Fill in the Item Code column only. Prices in the file are not used.
  3. Save the file as a CSV file, the same way as in Copy the Item Codes and Prices into the Import Template.
  4. Go to Master Data > Price Modifier, click Functions, then Import By Item Code.
  5. In the Open window, browse to the folder where you saved the CSV file, select the file and click Open.
  6. Change the prices in the list. You can edit each price, or use Value Editor, which is described in What is Price Modifier Function in ClassicPOS?.
  7. Set the Effective Date. Under Price to update, tick the prices you changed and untick the rest. The file does not set Price to update for you.
  8. Click Save, or go straight to Confirm the Price Change below.

Open a Saved Price Modifier

Use these steps to return to a price change you saved earlier.

  1. Go to Master Data > Price Modifier List.
     > 
  2. Select the document number of your price change.
  3. Double-click it.

  4. Check the Effective Date.
  5. Check the selling prices.

To finalize the price change, continue with Confirm the Price Change below.


Confirm the Price Change

Warning: Confirming applies the new prices exactly as you set them in Confirm Settings. Check the effective date and every selling price first. You can cancel a confirmed price change until it is triggered and its status changes to Completed. A Completed price change cannot be cancelled.
Info: Confirming needs the Allow to confirm price change permission. See Prerequisites for the full list of permissions.

Step 1: Open Confirm Settings

  1. Click Confirm at the bottom right of the Price Modifier screen.

Step 2: Choose the Price Settings

Under Price Settings, tick the prices you want to update.

Setting What it does
Change Master Price Updates the global price only. Needs the Allow to update master price permission.
Change Location Price Updates the local price only at the outlets you select. An outlet with no local price setting does not change.
Auto Create Local Price for empty fields Updates the local price at the locations you list, even when a location has no local price.

Step 3: Choose the Price Change Location

  1. Under Price Change Location, click Add Branch.

  2. Tick each branch you want to update.
  3. Click Apply in the Update Item Pricing to Other Branches window.

Info: By default, the system adds the branch you are logged in to.

Step 4: Apply the Price Change

  1. Click Apply in the Confirm Settings window.

After you click Apply, Price Modifier updates the prices based on the Price Settings you selected. If the Effective Date is today, the prices update right away and the status changes to Completed. If it is a later date, the status stays Confirmed until that date, and you can still cancel the price change until then.