Use the Price List Import

How to use Price List Import for Report Pro

Things To Note

• YOU MUST HAVE A VALID DATABASE BACKUP BEFORE YOU RUN THIS PROGRAM

• This tool will update parts in your management system inventory as well as insert new parts if the option is selected. Due to this it is extremely important to follow the steps exactly as follows. Do not skip any of the steps and refrain from data entry of any type until the verification step has been performed. This price list import reads part numbers, descriptions, costs, prices, and other details from an Excel workbook with the file extension ".xlsx". The idea is to supply a column name for each property of a part which is handled by this. Since not all values will always be listed in the spreadsheet, it is possible to specify a single default value for each property. Default values are only used when there is no mapped column name.

• If there is more than 1 worksheet in the workbook, the tool will read them all. The column names for any mapped properties must be the same on all worksheets.

• Because some Excel workbooks have been supplied where the column header takes up more than 1 row of data, it is necessary to enter the number of rows that the header data uses on the first screen of the import tool.

• There are 3 different pages where column names can be mapped or default values provided. The column names from the worksheet are displayed to the right of the inventory properties. Column names can be dragged-and-dropped. If the column names appear wrong, check that the correct number of rows of header data was entered on the first page.

Option Definitions

• How unique parts are matched for insert or update: A unique part is identified by the combination of the Part Number and the Manufacturer. Parts that exist in your management system that match the Part Number Manufacturer combination from your spreadsheet will have the mapped values from your import mapping definition updated in your Shop Management System to the values of the spreadsheet. If selected, unmatched parts on Part Number and Manufacturer will be inserted into your inventory.

• Remove embedded dashes from input part numbers: Often suppliers will provide spreadsheets in which the part numbers that they supply contain dashes (-). Check this option to have those dashes ignored/removed during the import.

• Insert new part numbers into inventory: Check this option if you would like all new parts from the spreadsheet to be inserted. Remember that if the Part Number and Manufacturer do not match an existing Part Number and Manufacturer in your Management System, it will be inserted as a new part. There is no undo option for this if a mistake is made in the mapping file or import process. The only option is to restore your database to the backup that you perform before performing the import.

• A Part Number is required. We treat the combination of part number and Manufacturer as unique. If a part number/manufacturer is encountered more than once in the input, the 2nd instance will be processed as an update to the part number. A blank Manufacturer is possible which could cause a duplicate to be entered or updated in your Management System because the combination of both part number and manufacturer are unique. For example: If the inventory in your Management System has the following part - Part Number: AF-12345, Description: Air Filter, Manufacturer: Fram and your spreadsheet for import contains - Part Number: AF-12345, Description: Air Filter, Manufacturer: , then the part line from the spreadsheet will be inserted as a new inventory part since the manufacturer is different

  • A Part Description is also required

• If a part number/manufacturer already exists in the database, the input is treated as an update.
  • The following properties can be updated: Part Description, Size, Cost, Selling Price, List Price, GL Account Code, Category, tire flag, taxable flag, and user entered price flag

• For GL Account codes, the mapped column in the spreadsheet can contain the unique database id for the Income Account being associated with the part. Most users will choose not to map this column, but select a default from the selection tool provided in the "Default if not mapped" column

• For Categories, the mapped column in the spreadsheet can contain:

  • The unique database id for the category

  • The category description as entered in the database

  • For Vendors and Manufacturers, the mapped column in the spreadsheet can contain 1) the unique database id of the Vendor or Manufacturer, 2) the code as entered in the database for the Vendor / Manufacturer, or 3) the name as entered in the database for the Vendor / Manufacturer

  • Please note that Manufacturers and Vendors for a part are only set during insert. Due to system requirements it is not possible to update them automatically.

• On the page titled "Data Columns with True / False defaults", the property "Is this item a tire?" corresponds to the Tire checkbox in the Inventory - Edit Part screen. A mapped column of data for this property may contain "1", "true", or "yes" for checked, and "0", "false", or "no" for unchecked

• On the same page, the "Taxable Y/N?" and "Tax Exempt Y/N?" properties correspond to the Taxable checkbox. The "Taxable Y/N?" value maps to the check box with the same values as for "Is this item a tire?" The "Tax Exempt Y/N?" value maps to the check box with the opposite properties. If a default value is required, set the value in the "Default if not mapped" column for the "Taxable Y/N?" property

• "User Entered Price Y/N?" corresponds to the "User Entered Price $" checkbox in the Inventory - Edit Part screen. Mapped column values are the same as for "Is this item a tire?"

How To

1. From your management system host computer

   1. Create a backup of your database

   2. Verify that the backup was successful.

      1. Often times your settings may be set to backup to an external drive and if that external drive is not connected, the backup will fail.

2. From Report Pro

   1. Select the Tools tab from the Report Pro header

   2. Select the Price List Import button - The Price List Import window opens

      1. From the Price List Import window

      2. If you want to keep embedded dashes in your part numbers, uncheck the box next to Remove embedded dashes from input part numbers, otherwise, leave it checked

      3. If you want to insert new parts from your spreadsheet, check the box next to Insert new part numbers into inventory

      4. At the bottom of the screen, Enter the number of rows that your input workbook uses.

      5. Select the Select Price List button

         1. A Windows Explorer window opens to navigate to the .xlsx file you would like to import

         2. Navigate to and select the file you would like to import - Please note that you cannot have the spreadsheet open while trying to import it

         3. Select the Open button

         4. Verify that the sample data on the screen appears correctly - If it does not, verify the number of header rows from your source file

         5. Select the Next Step button in the lower right corner

            1. A new screen is loaded that has three sections.

               1. The top left grid contains basic part description information with mapped values from your source spreadsheet and a default value to be set if the field is not mapped.

               2. The bottom left grid contains inventory price information with mapped values from your source spreadsheet and a default value to be set if the field is not mapped.

                  1. Typically, this is zero because it applies to multiple parts

               3. The right section is the list of fields in the header row(s) of your source spreadsheet that can be used as reference for entering the Input Column Header; however, we suggest using the drag and drop feature

                  1. Drag your source field from the right section to one of the rows in the left sections to populate your Input Column Header map.

               4. Repeat this for each field you would like to map

               5. Set a default value if not mapped for each of the fields.

                  1. Most times, on this step, you will have a mapped value for each of the fields, so no default is necessary.

               6. Verify your mapping for these fields and make edits if necessary

               7. Select the Next Step button to continue

               8. A new window opens with two sections to set the relational items to your inventory records.

                  1. The left section is your mapped values from your source data with default value if not mapped

                  2. The right section is the list of fields in the header row(s) of your source spreadsheet.

                  3. Drag your source field from the right section to one of the rows in the left section to populate your Input Column Header

               9. Repeat this for each field you would like to map

               10. Select a default value if not mapped for each of the fields

               11. Verify your mapping for these fields and make edits if necessary

               12. Verify that you have backed up your Shop Management System's database and no new data has been entered to it

               13. Select  Process Excel Workbook

               14. A new window opens that displays the progress of the import process.

               15. If there were exceptions during the process

                   1. Select the View Exceptions button

                   2. Review the exceptions

                   3. Make updates to your Management System, Source Spreadsheet and/or Your mapping file and reprocess if necessary

               16. Optionally save the results to a file for review in a spreadsheet or at a later time

               17. Verify that the inventory records that were updated/inserted, were done so correctly before resuming data entry in to your system

               18. If you find that the import did not perform the updates correctly, restore your database to the backed up version.

               19. If you find that the import did perform the updates correctly, resume data entry to your system