General Interest

Tutorial: Import price lists

This tutorial describes how you can import existing price lists that you may have received from your vendors into Microsoft Dynamics CRM Online.

In Microsoft Dynamics CRM Online, a price list is a group of specific prices that can be charged for each unit in the unit group of a product. Price list items are a combination of price list, product, and unit group. In Microsoft Dynamics CRM Online, price list items store information about how a product is priced, what amount will be charged for an item in the product catalog, how prices for products are rounded in quotes, orders, and invoices, what discounts are applied to a product based on volume purchase, and so on.

To import price lists, you must understand how price lists are related with other components of a product catalog. A product catalog is the central location for you to store information about your organization's products, including prices, discounts, unit sizes, and related information. So, before you import your price lists, you must set up the product catalog components in the following sequence:

  1. Create the unit groups and units for your products. You use units to measure item quantities, and you use unit groups to collect the different measurements that your products are available in, such as gallons, liters, or cases.
  2. Create empty price lists, to which you can add products. A price list specifies what price is to be charged for each unit.
  3. Create product records for each product that your organization sells, and link each product to a unit group and a default unit.
  4. Add your new products to one or more price lists as price list items. Even though you specified a default price list in the product record, you must also add the product record to the price list.

Lesson 1: Example of price list import

Let's assume that your organization sells the following two products:

Product

Price

ABC cooldrink

$2.00 per liter

XYZ cooldrink

$5.00 per liter

Here are the steps that you must follow in sequence to successfully import your price list data into Microsoft Dynamics CRM Online:

  1. Create the unit groups and units for products. In this example, create a unit group called Beverage, and create units as .5 liter, 1 liter, and 2 liters.
  2. Create a price list directly in Microsoft Dynamics CRM Online or import a price list in the following format:

    Name

    Currency

    PriceListP1

    CAD

    For more information about how to import a price list, see Importing a price list using the Import Data Wizard.

    The price list record has two mandatory fields in Microsoft Dynamics CRM Online: Name and Currency.

    The value in the Name column need not be an existing record in Microsoft Dynamics CRM Online because you are creating or importing new price lists. However, if you are providing the value in the Currency column of the source file, the value must map to a valid value for Currency in Microsoft Dynamics CRM Online. This is because Currency is a lookup field in Microsoft Dynamics CRM Online.

    Tip

    To find out the existing currencies in Microsoft Dynamics CRM Online, open the Currencies page by clicking Settings, clicking Business Management, and then clicking Currencies in the Navigation Pane.

  3. After you create or import a price list, create the two products in Microsoft Dynamics CRM Online: ABC cooldrink and XYZ cooldrink. Alternatively, you can import product data by using a .csv or .txt file, or you can download the template for data import for the product entity. For more information about importing a product, see How Product Records Are Imported.

    The following table shows the exact column headings and the minimum amount of data required in the Product source data file.

    ID

    Name

    Unit Group

    Default Unit

    AB-Z019B-58

    ABC Cooldrink

    Beverages unit

    1 Liter

    XY-A891W-44

    XYZ cooldrink

    Beverages unit

    2 Liter

    Now that you have created products and units in your product catalog, you can import multiple price list items to associate with the price lists by using the Import Data Wizard.

  4. Import price list items into Microsoft Dynamics CRM Online.

    When you import price list items, you must include the required columns in your source file for all business-required fields in Microsoft Dynamics CRM Online.

    Because the source data for price list items includes some of the business-required fields that have references to records that already exist in Microsoft Dynamics CRM Online, to automatically map the data, make sure that:

    • The source column headings for all business-required fields match the field display name values in Microsoft Dynamics CRM Online.

      To identify display name values, open the form for the record type as if you were creating a new record. The labels on the form typically match the display names.

      If the column headings are not the same as the display names of Microsoft Dynamics CRM Online fields, you can manually map your columns to Microsoft Dynamics CRM Online fields during import.

    • Data exists for all business-required fields that are of lookup type.
    • Data in the column that connects the records is valid.

      When you import a price list item, make sure that the value in the Price List column is an existing price list record in Microsoft Dynamics CRM Online.

    The following table shows the exact column headings and the minimum amount of data required in the Pricelistitems.csv file. The first row contains the field names in Microsoft Dynamics CRM Online and the remaining rows are imported as data.

    Price List

    Product

    Unit

    Pricing Method

    Quantity Selling Option

    Amount

    Percentage

    Rounding Policy

    Rounding Option

    Rounding Amount

    PriceListP1

    ABC cooldrink

    1 Liter

    Currency Amount

    No Control

    2

     

     

     

     

    PriceListP1

    XYZ cooldrink

    .5 Liter

    Currency Amount

    No Control

    5

     

     

     

     

    Because Price List, Product, and Unit are lookup fields in Microsoft Dynamics CRM Online, the values in the corresponding columns of your source file must already exist in Microsoft Dynamics CRM Online or other source file that you are importing. Pricing Method and Quantity Selling Option are option set fields. If your source data contains values that already exist for these fields in Microsoft Dynamics CRM Online, map your source data values to the values in Microsoft Dynamics CRM Online. Otherwise, create custom option set values during import. For more information, see Import records from a file.

    Your source file must contain columns for Percentage, Rounding Policy, Rounding Option, and Rounding Amount only if in your source data, you have specified the Pricing Method as Currency Amount.

Lesson 2: Identifying existing values of fields in Microsoft Dynamics CRM Online

To identify the existing values of fields in the previous table, open the entity form and click the Lookup button or the drop-down list for each field on the form.

For example, to identify existing values of business-required fields for price list items:

  1. In the Navigation Pane, click Settings, and then click Product Catalog.
  2. Click Price Lists.
  3. Open the price list that you want.
  4. Under Common, click Price List Items, and open a price list item record.
  5. Click each lookup field or option set field to identify the valid values for that field.

Lesson 3: Importing a price list using the Import Data Wizard

To import the PriceList.csv file, do the following:

  1. Click the File tab. Click Tools, and then click Import Data.
  2. Click Browse, locate and select the PriceList.csv file, and then click Open.
  3. Click Next and follow the directions that appear on the screen in the Import Data Wizard. For more information, see Import records from a file.

Follow the same instructions to import the Pricelistitems.csv file into Microsoft Dynamics CRM Online.

Related Links

Applies To:

Microsoft Dynamics CRM Online December 2012 Service Update

(For CRM Online, CRM 2013, and CRM 2015, see CRM Help & Training.)