You can import both price matrix and price contract data from a spreadsheet. This data can be added and updated.
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.
Review your data prior to importing: improper spreadsheet formatting can result in import errors.
- Separate Columns: Each piece of data being imported must be in it's own column on the spreadsheet.
- Exact Matches (case sensitive): To match on data, it must be entered on the spreadsheet exactly as you have it setup within Inform.
- Code Matches: Data must match on Code fields, not Descriptions.
Ex: A 10% increase must have the number 10 in it's own column, and the % sign in a separate column.
Ex: A Product number of ABC123 will not be updated if your spreadsheet has abc123.
Ex: A Product Line for Cleaning Supplies has a code of CLEAN. In this example, CLEAN would which would be entered on the spreadsheet.
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.
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.
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.
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.
Once you understand the available data and configure your spreadsheet, using the information above, you can begin the price matrix or contract import.
- Go to Sales > Pricing > Price Matrix Import.
- 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.
- 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.
- If you are setting Quantity Breaks, make sure to review Quantity in the Price data section above to make sure have entered in properly.
- 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.
- 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.
- 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.
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.