APPLIES TO RELEASE 19073n. Column titles 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 instead of using a spreadsheet.
Export the entire Crate Pro 6 inventory prior to starting and save the file as XLS or CSV. This is in case of errors, this will be your backup. You will also use this as your guide as columns and rows should not be deleted or moved.
What's faster to complete in Crate Pro 6
- Setting your Active and Inactive Materials from the existing inventory items: Setting Inventory
- Top of the window in Inventory > View: Design Fields List: Check / uncheck the 'Active' boxes next to each item to indicate you will have the item in your Inventory or not. Unchecking does not delete the items, it moves them to the 'Inactive' tab should you ever need the item later:
- You can set the Waste % here for each item (what's listed is an average for companies using Imperial or Metric dimensions)
- Updating your inventory item costs: CP6 offers a very fast method to update your material costs. However, always be aware of the unit of measure you chose for that material. Fast Update of Costs
- For example: You may select to enter the cost of a Cam Lock by 'each' and nails by 'the box'. When updating costs in a list view, check the column verifying the unit of measure you are using.
When is it a good to import your information?
- When you have a long list of new items to add to the existing database
Important thing to be aware of:
- When exporting you may select to export a single material group, such as only the Lumber or only Fasteners, The export will include all materials in that Category; active and inactive.
- Generally the last column in the spreadsheet indicates if the item is Active or Inactive in Crate Pro 6, To simplify it, you can delete or ignore the inactive items. Deleting rows here will NOT remove them from Inventory in CP6.
- 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
- You can add your new data into the export, and update existing data for items to be modified.
- There are field that you can not modify when importing back into CP6. Please review below
- Do NOT delete or add columns if you will be importing this file. Deleting columns is only acceptable if your O/S field matching software will allow unused columns to be skipped and/or you can change the order of the Source or Target field data
- 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 to the spreadsheet
- 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
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
Depending on your O/S, the field matching box will have default selections you may need to change
- Select to Update Records AND also ensure the 'Add New Data as New Records' is checked if you're adding new line items
- For field mapping options select 'Matching Name'
- In the window that shows which Source field (spreadsheet) is mapped to the Target field (in CP6) confirm the field names for all columns that will be imported are shown and the same.
- There may be field not showing they will be updated and you HAVE made changes or additions to: Click on the MAPPING for that field to chose 'Import this field' (Not all fields [columns] need to be imported if changes have not been made.)
- The field mapping icons will change depending on your operating system. You may have an arrow or equal sign that can be selected to import or not. Or you may see a box indicating IMPORT or DONT' IMPORT a specific field.
- Select the checkbox "Perform the auto-enter options
- Select IMPORT or CONTINUE or OK
- 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