Using Import/Export to Update Price Sheet Details

Subject

The steps here explain how to use the import and export feature in Rockton Pricing Management (RPM) to make changes to existing Price Sheet Details.

Resolution

 

Overview

Pricing can be set up in a variety of ways using Rockton Pricing Management (RPM). You can set up Price Schedules to base pricing on Item Cost, Item List Price, Price Schedules, Price Books, or a combination of these. With any of these pricing strategies, the Import and Export feature can be utilized. This example is specific to Price Books and updating Price Sheet Details.

When using Price Books, pricing is determined by data on Price Sheets and Price Sheet Details. You may have multiple Price Sheets to maintain. Using the Export and Import allows you to export the data from a page, make changes, and then import the information back into RPM.

In this example, there is a Price Book for annual pricing with two existing Price Sheets based on year. The steps here explain how to export the details from one Price Sheet and import the updated data into a new Price Sheet.

Export Data

1. In RPM, expand the Pricing menu.
2. Click Price Books and open the Price Book.
3. Verify the Price Sheet you are exporting from exists and the Price Sheet you want to import into exists.
4. Select the Price Sheet with the information you want to copy and click Edit.
5. From the Price Sheet Details grid, select Export.
6. If you wish to re-import the records, click Yes.

7. A PriceSheetDetails.xlsx file is created and downloaded.

 

Update Spreadsheet

1. Open the PriceSheetDetails.xlsx file using Microsoft Excel.

Note: In the spreadsheet, you need to update the PriceSheetID , the new PriceSheetName, and then make any adjustments to the Price column. See next steps on how to obtain the value for the new PriceSheetID.

2. Update the PriceSheetID values in the PriceSheetDetails.xlsx file using the ID information. There are 2 ways you can determine what the new value.

Option 1 – Run a SQL Query in Microsoft SQL Server Management Studio.

1. 1. 1. Run the following query against the RPM company database. Locate the PriceSheetID for the Price Sheet Name you want to Import into.

         select * from RPMPriceSheet

      2. Locate the ID assigned to the new Price Sheet Name.

Option 2 – Export a Price Book spreadsheet from RPM.

1. 1. 1. In RPM, expand the Pricing menu.
      2. Select Price Books and open the annual Price Book.
      3. From the Price Sheets grid, select Export.
      4. Select Yes when prompted if you wish to re-import these records.
      5. Open the downloaded PriceSheets.xlsx file using Microsoft Excel.
      6. Locate the ID the new Price Sheet Name where you want to import the data.

3. Update the PriceSheetName values in the PriceSheetDetails.xlsx file using the Name information entered in RPM. You can also verify the Name from the results using either method above.

                                                                                                                                     

4. Update the Price values in the PriceSheetDetails.xlsx file. This is the new pricing you will import into the newly created Price Sheet.
5. Save all changes to the PriceSheetDetails.xlsx file.
6. Back in RPM, go back to the Pricing menu.
7. Click Price Books and open the annual pricing Price Book.
8. Select the new Price Sheet and click Edit.
9. From the Price Sheet Details grid, click Import.
10. Locate the updated PriceSheetDetails.xlsx and click Open.
11. After the import has run, a message appears confirming it successfully completed.
12. Validate Price Sheets Details appears in the grid.