Menu

Import price matrix or price contracts

  • Updated
Import price matrix or price contracts

You can import both price matrix and price contract data from a spreadsheet. This data can be added and updated.

Warning
The import process is not reversible. Changes will update instantly.

See the sections below to learn about the data you can import, formatting the spreadsheet, and running a successful import.

ClosedPrice data
Field Name Definition Sample
Customer The Customer Number. When mapped, pricing levels for specific customers will be imported. When paired with the Contract Number, use the customer number to load Customer Price Overrides. Note that for Customer Price Overrides, the Price Contract must already exist prior to importing. 001375
Shipto The ShipTo Number. When mapped, pricing levels for specific ShipTo locations will be imported. Note: This must be paired with the Customer field. 00000005
Customer Category The Customer Category Code. When mapped, pricing levels for customer categories will be imported. SHOWROOM
Product The Product Number. When mapped, pricing levels for specific products will be imported. 325780
Product Line The Product Line Code. When mapped, pricing levels for specific product lines will be imported. EQUIPMENT
Major Group The Major Group Code. When mapped, pricing levels for specific major groups will be imported. Office
Price Group The Price Group Code. When mapped, pricing levels for specific price groups will be imported. 5%Load
Contract Number The Price Contract Number. When mapped, prices & costs can be imported into specific Price Contracts. When paired with the Customer, this can be used to load Customer Price Overrides. 1084-1599
Branch The Branch Code. When mapped, pricing levels for specific branches will be imported. 01
Vendor The Vendor Number. When mapped, pricing levels for specific vendors will be imported. 00324
Quantity

Quantity Breaks. To use this, the product must appear in multiple rows, with a unique quantity in each row.

The import will create new breaks and update existing breaks.

Optionally, you can clear existing breaks by entering a Quantity of zero in the first row (for the product).

This clears out existing breaks, while allowing new breaks to be imported.

 0
Price (C#/L#/N)

To import prices, you must combine all four of these pricing fields. These identify where the price comes from (such as Net Amount, Cost Markup, etc).

Price (C#/L#/N): This field indicates the price is derived from either the Cost, List, or Net Amount. Cost fields should be entered as C1, C2, etc. Similarly, List fields should be entered as L1, L2, etc. If your entering a Net Amount, this field should be set to N.

  • Example: A cost markup such as (C1 + 25%) would have C1 as the data in this column.
  • Example: A net amount such as ($50.00) would have N as the data in this column.

Price (%/$): This field indicates if the price is based off of percentages or a dollar amount and should contain either a % or $ symbol respectively.

  • Example: A cost markup such as (C1 + 25%) would have % as the data in this column.
  • Example: A net amount such as ($50.00) would leave this column blank (as nets are always dollar amounts).

Price (+/-): This field indicates if the price formula is a markup or a markdown. For Net Amounts, this field indicates that the amount is positive or negative.

  • Example: A cost markup such as (C1 + 25%) would have + as the data in this column.
  • Example: A net amount such as ($50.00) would leave this column blank.

Price Percent / Net Amount: This field is the percent value or net dollar amount.

  • Example: A cost markup such as (C1 + 25%) would have 25 as the data in this column.
  • Example: A net amount such as ($50.00) would have 50.00 as the data in this column.
 C2
Price (% /$) %
Price (+/-) +
Price Percent / Net Amount 25
Cost(C#/L#/N)

To import costs, you must combine all four of these costing fields, which identify where the cost comes from (such as Net Amount, Price Markup, etc).

Cost (C#/L#/N): Indicates that the cost is derived from either the Cost, List, or Net Amount. Cost fields should be entered as C1, C2, etc. Similarly, List fields should be entered as L1, L2, etc. If your entering a Net Amount, this field should be set to N.

  • Example: A price markdown such as (L1 - 25%) would have L1 as the data in this column.
  • Example: A net amount such as ($50.00) would have N as the data in this column.

Cost (%/$) This field indicates if the cost is based off of percentages or a dollar amount and should contain either a % or $ symbol respectively.

  • Example: A price markdown such as (L1 - 25%) would have % as the data in this column.
  • Example: A net amount such as ($50.00) would leave this column blank (as nets are always dollar amounts).

Cost (+/-) : Indicates that the cost formula is a markup or a markdown. For Net Amounts, this field indicates that the amount is positive or negative.

  • Example: A price markdown such as (L1 - 25%) would have - as the data in this column.
  • Example: A net amount such as ($50.00) would leave this column blank.

Cost Percent / Net Amount: The percent value or net dollar amount.

  • Example: A price markdown such as (L1 - 25%) would have 25 as the data in this column.
  • Example: A net amount such as ($50.00) would have 50.00 as the data in this column.
 L1
Price (% /$) $
Cost (+/-) +
Cost Percent / Net Amount $
Eff. Date Effective Date .The date the Price Matrix or Price Contract will go into effect. 01/01/2019
Exp. Date Expiration Date. The date the Price Matrix or Price Contract will cease to be in effect 12/31/2019
Future Date The date when the Price Matrix or Price Contracts have products/prices/costs that will expire and should be replaced with an updated set of products/prices/costs. For Price Contracts, this field must be paired with the Contract Number field. 01/01/2020
Coop % The Co-op % amount. When mapped, a Co-op % (used in the Customer Rewards Program) will be imported. 10
Load % The Load % amount. When mapped, (cost) load percent amounts will be imported into Price Contracts. 10

Vendor Contract Number

  

The Future Vendor Contract Number & Effective Date. When mapped, these two Future fields will be imported on the header tab of Price Contracts.

Note: You must specify both the Vendor Contract Number and Date. Without a date, the future contract number will never go into effect.

 00258947

Vendor Contract Date

 01/01/2020
ClosedPrepare your import spreadsheet

Review your data prior to importing: improper spreadsheet formatting can result in import errors.

ClosedSpreadsheet Setup
  • Separate Columns: Each piece of data being imported must be in it's own column on the spreadsheet.

    • Ex: A 10% increase must have the number 10 in it's own column, and the % sign in a separate column.

  • Exact Matches (case sensitive): To match on data, it must be entered on the spreadsheet exactly as you have it setup within Inform.

    • Ex: A Product number of ABC123 will not be updated if your spreadsheet has abc123.

  • Code Matches: Data must match on Code fields, not Descriptions.

    • Ex: A Product Line for Cleaning Supplies has a code of CLEAN. In this example, CLEAN would which would be entered on the spreadsheet.

ClosedLeading Zeros

When editing your spreadsheet be cautious about leading zeros. By default, Excel trims leading zeros when the column formatting is set to General.

Consider changing your non-date fields to Text before making any changes or saving.

ClosedDefault Fields

Not all data fields need to be entered on your spreadsheet. The Default Fields make it possible to fill in some information automatically.

For example, if your spreadsheet is intended to update a single Product Line, you can select it from the Default Fields instead of entering that Product Line on the spreadsheet.

ClosedCSV Format

When you've completed your spreadsheet, save the file format as Comma Separated Value (.csv).

Note that if you reopen a CSV file in Excel, it will convert the columns back to General.

ClosedDecide on a price formula

When importing, you can update Prices, Costs, or Both.

These fields work in conjunction and therefore must be imported together for new formulas, however existing formulas can be updated with individual field mappings.

In your spreadsheet, add a column for each of the following that you want to add or update:

  • Price/Cost (C#/L#/N): Whether the price/cost is derived from the Cost, List, or Net Amount. Cost & List fields should be entered as C1 or L1, Depending on the cost/list field used. Net Amounts should be set to N.
  • Price/Cost (%/$): Whether the price/cost is based off of percentages or a dollar amount. For a percent, type %; for a dollar amount type $. Net amounts can leave this field blank.
  • Price/Cost (+/-): Whether the change is a markup or a markdown. Use + or -. Net amounts can leave this field blank.
  • Price/Cost Percent / Net Amount: The percent value or dollar amount of the change.
Desired result Price C/L/N Price %/$ Price +/- Price Percent/Net Amount
Net amount of $150. N     150.00
$10 less than the List 1 Price L1 $ - 10.00
25% higher than the C1 cost. C1 % + 25.00

Note: Percentage amounts are based on markup. Consider using the Markup Conversion chart, below.

ClosedImport price data

Once you understand the available data and configure your spreadsheet, using the information above, you can begin the price matrix or contract import.

  1. Go to Sales > Pricing > Price Matrix Import.

  2. In the File NameSelect the file location of the spreadsheet to be uploaded. It must be in either Comma Separated Value (.csv) or Tab Delimited Text file (.txt). If the file is currently in the Microsoft Excel format, open the file in Excel, select Save As, and choose Other Formats. Select the CSV format. box, type the path to the CSV import file, or click the folder icon and navigate to the path. If your spreadsheet has headers, such as Customer, Shipto, etc., then select the Has HeadersAfter each Import you perform, you will be prompted to save a template. This will save a copy of your field mappings for future use. If your spreadsheet columns remain in the same format, the next time you load the file you can select your template. check box.
  3. If you have omitted any data from your import spreadsheet, you can set the Default Fields Selecting any of the default fields removes the need to add this data to your spreadsheet. For example, selecting a Product Line would set all price/cost formulas and amounts to be applied to that Product Line, without having to map the Product Line column. If you wish to update multiple product lines at the same time, this setting should not be used. Note that Customer Price Overrides for Price Contracts must use the field mappings and not the default fields. for those fields. For example, if you select a default Product Line, then the price/cost formulas and amounts will be applied to that Product Line, without having to map the Product Line column. If you wish to update multiple product lines at the same time, don't set the default.
  4. If you are setting Quantity Breaks, make sure to review Quantity in the Price data section above to make sure have entered in properly.
  5. Map your data. Once you specify the csv file, the columns will become available for mapping in the Import Mappings list. Drop down the Mapped Column beside the Field Name that you want to import, and choose the correct value from the spreadsheet.

  6. Click Analyze. Inform ERP analyzes the mappings to validate your data. If it encounters any errors, it will display an Exceptions report, which will help you fix your data before retrying your importing. To fix data, click the Data tab. Changes will be applied here only, not to your original spreadsheet.

  7. Once you are satisfied with your data, click Import. When complete a pop-up notification will indicate how many rows of data were imported, created, and skipped (which includes locked and failed rows). If you'd like to see a report detailing why data was skipped, view the exceptions report by clicking OK. Usually skipped data is due to bad data, such as non-exact matches or poor column formatting.

ClosedMarkup conversion chart

All formula based percentages must be in markup format. Use the chart below to help identify the differences based on the desired Gross Profit amount.

ClosedSee also

Related articles