APPLIES TO RELEASE 190730. Columns may be slightly different for older releases
New inventory items and updates to existing inventory items can be imported into Crate Pro 6 by using the Import/Export option. However, some inventory configuration and ongoing management steps are faster to complete in Crate Pro 6 versus using the Import/Export option.
Export the entire inventory prior to starting and save the file as XLS or CSV. This is in case of errors, this will be your backup.
What's faster to complete inside Crate Pro 6
- Selecting Active and Inactive Materials: Top of the window in Inventory > View: Design Fields List: Check or uncheck the boxes in the list for items you will use or not use.
- You can modify the Waste % here for each item (what's listed is an average for companies using Imperial or Metric dimensions)
- Some changes such as changing lumber/timber from CDX to OSB it may be faster to rename the material versus entering a NEW item
- the benefit to changing the item information on an existing material is that you have common values for waste and install times to view. While these values may not be accurate for your company, it does provide a guideline.
- If an item has been used in a Design or added to a Project - it can not be renamed and you will need to add a new item with the Item ID you prefer.
- Updating your costs : Bottom of the window in Inventory > Select the ACTIVE list of materials you want to update > click on magnifying glass
- Enter your cost for that material based on the unit of measure shown
Important thing to be aware of:
- When exporting you may select to export a single material group, such as Lumber or just Fasteners, or your entire inventory. The export report will export all material; active an inactive so within your CSV or XLS file you will want to sort and either delete or ignore inactive materials.
- However, if you are planning to import your data back into CP6, you may find it easier to work with a single material group (as shown by individual tabs in Crate Pro 6) because the required information changes per group and there will be a large number of tabs that do not apply to each group
- You can add your new data into the export, and update existing data for items to be modified.
- For checkbox fields in the Inventory module, they are displayed as a '1' active, or a '0' inactive. Some spreadsheets do not show a '0' value so enter a '0' for all items that will be changed to Inactive
- Because you're using a spreadsheet, you can copy/paste much of the repeating data as needed
- Do NOT delete or add columns if you will be importing this file
- Rows with items in it can be deleted but this will NOT delete them from within your Crate Pro 6 Inventory module.
- It's recommended to make all items you will not use by making them Inactive either in Crate Pro 6 or via your spreadsheet by entering a '0' in the Active column
- DO delete blank rows if any exist - these are items that someone selected the 'ADD' button in Crate Pro but never completed entering the required information
- An import can be in a CSV or Excel file format.
Steps for Inventory Export/ Import
1> Export the file to work in first by open Inventory module > Select Import/Export at the top of the window > Select the group or All groups to export > click on Export
- Name the file - I suggest using a date and name to easily identify this files contents
- Save as Type: selecting an Excel file will maintain the column headers. A .CSV file will not include the field headers but is another option for file import
- Check the box "Automatically Open File"
2> Make your adjustments and additions to the spreadsheet as needed
3> Importing: When complete- save the updated file and open the Inventory module > Select Import/Export at the top of the Window > select Import to go to the Interim table > Select 'Get Interim Records > point to the updated file
- Select to Update Records AND also ensure the 'Add New Data as New Records' is checked.
- For field mapping select 'Matching Name'
- Changes are usually detected but in the window that shows which source field (your spreadsheet) is mapped to the target field (in CP6 ), you may see the field not showing it will be updated. Click on the MAPPING for that field to chose 'Import this field' (Not all fields [columns] need to be updated because changes have not been made.)
- Release 180730 & 180729 the mapping icon is an arrow or equal sign that can be selected if it doesn't appear to be active and you are aware there have been changes made.
- Select the checkbox "Perform the auto-enter options ..."
- Select IMPORT
- The data will appear in the Interim Table for you to review for errors prior to actual import. This screen will scroll to allow you to see all columns
- Items that are green exist in CP6 and will be updated if changes were made during import
- Items in blue do not have the required Item ID field entered and need to be corrected prior to import
- Items in yellow are duplicates of existing items and need to be deleted prior to import
- Items in red contain field errors which need to be corrected prior to import
In the sample spreadsheet attached:
- The columns highlighted in YELLOW require data entry and can NOT be left blank.
- The columns highlighted in GREEN require data entry based on the material group selected
- The columns highlighted in BLUE are optional for data entry
REQUIRED FIELDS: Do not leave these fields blank - Depending on the version of Crate Pro 6 - the column number may be different - the column header is shown along with the column name for easier identification. (shown is the full export for release 190730)
1> A / AddAcct: Enter the login name of the User who is making the changes. If preferred, you can copy/paste any name that appears in this column. If there are errors with the material, knowig who made the entry can assist in correcting the errors.
2> F /: Facility ID - Go to Preferences > Company and locate your Facility ID. Enter this into the import spreadsheet and repeat it to the last record you'll import
3> H / ItemID: - This must be unique from every other inventory entry. DO NOT CHANGE AN EXISTING ITEM ID .If you want to add a similar item, enter it as a new record. The Item ID is what appears on the cut lists
4> J / Active: - Active items - enter a '1' for items that are active // enter a '0' for items that are showing active that you want to move to the Inactive tab
5> AB / Category: - This is the Category the inventory item will be listed in. Suggested to copy/paste from an existing field.
6> AC / TL: - Tracking Level. Tracking level is the unit of measure you purchase this material in AND will be the unit of measure you enter your material costs in. If possible, all items in an inventory tab should use the same tracking level so cost updates are less prone to error. In most tabs the Tracking Level must be A or B. Some inventory groups allow for A to F for the Tracking Level.
7> AM / Cost: - Your cost for this item based on the unit of measure you set in the Tracking Level. If you're not using CP6 to calculate material costs, you can enter '0'
8> AQ / Wt: - Weight of the item based on the selected unit of measure for your Tracking Level.
9> AS / UM_TL: - Enter the text that you prefer to see when setting the unit of measure you buy these items in. Example: Box, Roll, Sheet, Pink Dogs, etc. This is strictly a text field and does not affect calculations.
10> AU / MU_Des_Default: - This is the default markup as set in Preferences > Prices > Design Markup. While most often this will fill in automatically, some security settings will leave this field blank which will negate any profit for this material. COPY/ paste the values from other same category items. Important - this MUST match the default markup as set in Preferences for this material group.
- AT / MU: - if you prefer an override to the default markup - you can set it here. Use %: so .3 is 30% whereas 3 is 300%
REQUIRED FIELDS BASED ON INVENTORY GROUP: Please review your export for existing values that are already applied.
- Column M to Q / Thick-Wide-Long-ThickNom - WideNom: - Category Lumber, Plywood, Padding, some Fasteners, some Other. Note that for lumber 'Nominal' values are to ISPM tracking and 'Nominal values for customers using metric material are the same as Thick-WIde-Long values
- Column R / Waste - Value will be zero unless a fraction is entered
- Column S / Time: enter time to cut, install or apply a particular amount of this material. It's recommended to review each inventory category to determine the time being determined.
- Column T /TimeWrapPrep: -Time required to prep your Wrapping materials. Time to take off holder, roll it out on table, measure and cut the piece needed, the return the roll to storage
- Column U / TimePrep: - Fastener prep time - generally for fasteners such as bolts that require pre-drilling in addition to install time
- Column V / FastSpaceFactor: - Spacing factor for Fasteners that is multiplied by the fastener spacing value in Preferences > Construction tabs for the style famiy. This value tells CP6 how far apart to space that specific fastener, which also determines the quantity of fasteners to use
- Column W / AddThickAtCorners: - Wrapping - when wrapping is applied there is an extra % of thickness that is automatically pushed in the corners around the content item. You add a % of the used material that is counted toward the total used/costs
- Column AD / LumCleat: - Lumber (timber) - will this material be used as a cleat?
- Column AE / OthrRiser: - Other tab - Items that will be used as Risers such as Donuts and Skid Mates. Be sure to enter a "thickness ' value to get a correct height value when a riser is used.
- Column AG to AL / [Fastener usage location] : - this is the checkbox in Fasteners tab that tells CP6oin what capacity you would use this fastener. Enter a '1' for each field that you would use that fastener
OPTIONAL DATA FIELDS
Fields shown in blue are optional for data entry. You may chose to enter the information into your spreadsheet if desired. ALL data imported can be modified in the Inventory module