Price Updates

The Price Updates function allows users to make pricing changes and modifications to Item Master records on a large scale using one or more criteria to affect the change. There are two options for using this feature: a filter version which can update most pricing/cost fields for items selected in a filter search, or a spreadsheet version which can update any fields on the Item Master using the Part Number or item ID number assigned by the system. General notes for both options include the following:

  • This transaction is not used to update or change pricing on an individual item; Individual changes are handled on a Part's Item Master record.
  • This function does not update Vendor Part Cross References.
  • The Price Updates function excludes parts with an active, default, non-regulatory Bill of Materials.
  • This feature may only be used to modify existing items. New items cannot be loaded into the system with this feature.
  • The Price Updates function cannot update User Calculations.
  • The ability to add values to Item Master User Fields is supported.
  • The ability to add values to Item Master User Fields is supported beginning in version 14.8.95.

Pricing changes via the pre-filter

Using this tool, updates may be made to pricing data only for Item Master records or Facility Part Cross Reference records, which are used to define unique MRP criteria, costs, and prices for a Part in specific Facilities. If, on the cross reference, the "Use Costs" and "Use Prices" flags are checked, the system will use the costs and prices specified on the cross reference when performing changes. If these flags are not checked, the system will use the price information stored on the Item Master to update any Facility Part Cross References.

The pre-filter does not allow the user to see the list of items to be updated prior to confirming the update action, so it is recommended to first view search results via Inventory > Item Master before changing items using the Price Updates tool. Verify your Price Updates pre-filter settings before running. If it is cancelled during the process of running, any changes made before the cancel will not be rolled back and will be saved

Item Master modifications via a spreadsheet

In addition to pricing changes made via the pre-filter option, the Price Updates function includes an option to modify any field on the Item Master record, including Facility Part Cross Reference fields and Item Master User Fields, using a spreadsheet prepared with values. When a spreadsheet is being used, any "dmprod" or "dmprod3" Item Master fields can be modified by using a properly formatted Excel spreadsheet. The Data Dictionary, available via System > Data Dictionary, contains the field name and description of all Item Master fields for the Parts tables.

Spreadsheet Guidelines

  • The Price Update will change any fields on the Item Master using the "Part Number" (pr_codenum) or "Retail Code" (pr_retail) to find the item.
  • The spreadsheet can contain one or more actual column names from dmprod or dmprod3 tables and those fields will be updated with values supplied on the spreadsheet.
  • Spreadsheet columns must have a header name matching the table column name (e.g. “pr_descrip”, “pr_taxable”, “p3_reorder”). To make updates to Item Master User Fields, the column header needs to match the user-defined dmd1.d1_field value (e.g. “u_Customer_Formula_No”, “u_NY_List_Price”). Formatting for the values that will be updated must be as follows, otherwise the import will result in an error.
  • To update search field values, use the field IDs (p1_id, p3_id, etc.).
  • To update pick lists, use the name of the pick list option (d3_value), not the field ID.
  • To update logical fields, use Yes and No to check or uncheck (respectively) the box.
  • One note about using a spreadsheet to update Facility Part Cross References: the values listed in the row with the first instance of the Item on the spreadsheet will be used for any Facility chosen in the application controls. It is recommended that only a single instance of each item be provided on the spreadsheet.
  • The Item Master User Search Fields (pr_user5 through pr_user9) can now be updated via the spreadsheet option.
  • A value of "0" in a spreadsheet will clear the field of its value.

While the spreadsheet option is more powerful, extreme caution should be exercised when using it, as spreadsheet updates go directly to the database without normal Deacom system data validations and formatting constraints.

When using the spreadsheet option in the web versions of Deacom, only the Excel format (XLSX) is supported; Deacom no longer requires Excel to be installed on the web server, provided that the spreadsheet imports will be performed with the modern XLSX Excel format. For the classic version of Deacom, spreadsheets need to be XLS format.

System Navigation

  • Inventory > Price Updates

Price Updates pre-filter

Button/Field/Flag

Description

Continue

If clicked, the system presents a prompt indicating the number of records that are about to be modified. On the resulting prompt:

  • If "Yes" is clicked, the changes will be made as selected in the pre-filter an a prompt will appear indicating the number of records that were updated.
  • If "No" is clicked, the transaction is canceled.

Change From

Pick list used to indicate if the pre-filter or a spreadsheet will be used to perform the function. Options are:

  • Pre-filter - Used for pricing changes only for either Item Master or Facility Part Cross References (see "Field To Update" description below).
  • Spreadsheet - Used to modify pricing or any field on the Item Master record, including User Fields, from an external spreadsheet.

Spreadsheet

File explorer used when "Change From" is set to "Spreadsheet" to select the spreadsheet that will be used to perform the function.

Field to Update

Pick list used to determine at what level changes will be applied. Options are:

  • All - Changes will be applied to both Item Master records and Facility Part Cross References.
  • Item Master Facilities Only - Changes will be applied to only Facility Part Cross References.
  • Item Master Only - Changes will be applied to only Item Master records.

Item Type

Pick list used to determine what Item Types, as indicated on a Part's Item Master General 1 tab, will be changed. Options are: All Types, Components, Finished Goods, Raw Materials, Subassemblies.

Facility

If selected, changes are applied to only Parts with this Facility selected on their Item Master Facility tab.

Revenue Account

If selected, changes are applied to only Parts with this account selected as the "Revenue" account on their Item Master Accounts tab.

Category

If selected, changes are applied to only Parts with this Category selected on their Item Master General 1 tab.

Sub-Category

If selected, changes are applied to only Parts with this Sub-Category selected on their Item Master General 1 tab.

Item Search 1-5

If selected, changes are applied to only Parts with these Item Search 1-5 selections on their Item Master User Fields tab.

  • Captions for these fields are managed via System > Maintenance > Captions.
  • Options for these fields are managed via Inventory > Maintenance > Item Search 1-5.

Item Planner

If selected, changes are applied to only Parts with this Item Planner selected on their Item Master General 1 tab.

Part Starts With

Applies changes to only Parts that begin with the string entered in this field.

Change Field

Pick list used to determine the field that will be changed. Options are: Current Burden, Current Freight, Current Labor, Current Material Burden, Current Materials, Future Burden, Future Freight, Future Labor, Future Material Burden, Future Materials, LIFO Cost, List Price, Purchase Price, Transfer Cost.

  • The Current and Future options update those same fields as listed on the Item Master Costs tab.
  • The remaining options update those same fields as listed on the Item Master Costs 2 tab.

Change Type

Pick list used to determine how the change will be applied. Options are: Add/Subtract, Multiplier, Set Price, User-Defined.

Based On

Pick list used to determine the field that will be used as a base for the change. Options are: all options available to the "Change Field" field, Average Cost, Itself, Last Cost, Last Freight Cost, Loaded Acct Cost, Loaded Current Cost, Loaded Future Cost.

  • Options other than those also available to the "Change Field" field are listed on the Item Master Costs 2 tab.

Factor

Displays the factor to be used in conjunction with the "Change Field", "Change Type", and "Based On" fields to process the change.

  • Example: To increase the "Current Materials" cost by 10%, the following would be completed on the pre-filter:
    • "Change Field" set to "Current Materials".
    • "Change Type" set to "Multiplier".
    • "Based On" set to "Itself".
    • "Factor" set to 1.10.

User Expression

Memo field used when "Change Type" is set to "User-Defined" to enter an expression to define how prices will be affected.

  • The fields available to use in the expression include all fields on the Item Master record, including the "Last Cost", "Average Cost", and "Loaded Costs 1-3" fields.

Cost Roll Up

Combobox with options of "Non Lab-Only", "Lab Only", or "None" (default).

  • Non Lab-Only - Parts being updated that are used on BOMs will update the appropriate parent Parts in the BOM structure.
    • Using this function updates an item's current costs, not standard costs. Standard costs are only updated using the "Update Acct. Std" button or via Inventory > Cost Updates.
    • The "Current Materials" value on an item will update automatically if its BOM is saved or if the current total on one of its components is changed, assuming the "Roll Up Material Costs From BOM" flag is checked for the Part on its Item Master Properties tab.
    • If using this tool to roll up BOM costs, the system will recalculate the costs on each BOM and put that amount into the "Current Materials" field.
  • All - Same as Non Lab-Only, but will also include any BOMs in the cost roll ups for Lab-Only Parts.
  • None - Default. Parts will not be updated that are used on BOMs.

Active Items Only

If checked, only Parts flagged as "Active" on their Item Master Properties tab will have pricing changes applied.

Saleable Items Only

If checked, only Parts flagged as "Saleable" on their Item Master Properties tab will have pricing changes applied.