The Business Edge enables you to import new Inventory records as well as update specific fields in existing Inventory records. Below is a listing of the information that can be imported from various menu selections in the Import File Data System.
Product Definitions
· Enter Inventory Relationships
· Enter Alternate Part Relationships
· Enter Product Properties Relationships
· Enter Inventory Class Relationships
· Enter Bill of Materials Relationships
· Enter Notes Relationships
· Enter Substitute Relationships
· Enter New Product Code Relationships
Product Qty, Price & Locations
· Enter Price Table Relationships
· Enter On Hand Relationships
· Enter Branch Stock Relationships
· Enter Customer Bin Relationships
· Enter Locations relationships
· Enter Product Price Level
This procedure will cover updating existing Inventory Master File records. The procedure for importing is the same, what will change is the data that you include in your spreadsheet. When importing data relating to Inventory the Product Code is always a mandatory field.
The fields in the Inventory Master File that can be updated include the following:
Product Description, Default PO Cost, List Prices, Standard Cost, Standard Price, Classification, Primary Vendor, Re-order Points and Stocking information, Default Location, Cert Required, ARS Type, UPC Codes, Discount Table Code.
It is very important to note that when importing List or Cost, The Business Edge stores these values at the “each” or per piece value. If your units of measure are per hundred or per thousand you must divide the price by 100 or 1000 before importing it.
The steps that you will follow to update your Inventory are listed below. Each step will then be covered in detail.
A. Create a Text Tab Delimited file and save it on The Business Edge server
B. Define the text file in The Business Edge.
C. Import the file into The Business Edge catalog system.
D. Check the imported data to be sure that the data is correct.
E. Setup File Relationships.
F. Update the Files.
E. Delete the imported File.
You can create your file in a spreadsheet application like Excel. The first column of your spreadsheet should always be the Product Code. Once you have completed your spreadsheet, it must be saved to The Business Edge server as a Text Tab Delimited file. In Excel, when you are ready to save your file, Click on File and then Save As. On the top of the Save option box you will have an option as to where you would like to save the file. Navigate to locate The Business Edge server starting with Network Neighborhood or Network Places. After you locate the server, click on the server, and you should see a directory with your login name. Click on that folder. On the bottom of the save option box there is an option for a file name. The file name must be one word. Save as type must be set to Text (Tab delimited).
The next step is to define your file in The Business Edge
Utilities
Import File Data
Enter & Edit Delimited Catalogs
If this is a new catalog you can enter a Catalog Name, this is a name that is meaningful to you. You can have multiple catalogs defined and re-use the definitions for future updates as long as you keep the same exact format of the data. If you have previously setup the definition you can type in the name you would like to select, or press F3 – Lookup to select from a list of existing catalogs.
External File Name – This is the location of the text file that you saved on the server. The layout of file is as follows: /usr/users/username/filename.
Pad Numeric References – This is defaulted and does not have to be changed.
Delimiter – This is defaulted to Tab. Unless you have a file saved in a different format other than Text Tab Delimited you can simply press enter or esc. If you file is a Comma delimited you could press spacebar and select a different delimiter. Below is an example.
After completing the first screen you will now go to a screen where you can define each column in your spreadsheet that you have saved to the server. Press enter on New Line. You can now enter a Description, Length, and Type to represent each column of the spreadsheet. All Columns must be defined in a new line, even if they are blank. The first New Line should be Product Code; the length you enter here represents how many characters you want to see on the screen when you are checking your imported data. A good standard for Product Code might be 15. If you have many columns in your spreadsheet, it is a good idea to keep the lengths short, this will enable you to fit more columns on the screen. If you have Product Codes that are longer than 15, they will still be imported with their actual length, they will just be cut off when viewing the data on the first screen. The Type can be Text, Numeric or Date. Product Code would be Text. The only time you would select Numeric is when the Data that you are importing is required to be a number. i.e. Cost and List must be defined as a number because they will be used in calculations. If your Product Code is all numeric you should still define it as Text since the system will not be doing any calculations with your Product Code.
Note: the length for Cost and List is defined as 10.5 so that you will be able to see 10 total characters with 5 numbers after the decimal. Below is an example:
After you have setup your Catalog definition the next step is to import the data. Select Import File Data from the Import Menu.
Utilities
Import File Data
Import Catalog Files
Select your Catalog by typing in the name or you can press spacebar and enter to see a drop-down box of all of the catalogs that have been defined. Highlight the catalog you would like to import and press enter to select it.
The external file name that you typed in when you first defined the file will display on the screen. You can change the file name here if you need to. When everything looks correct you can press escape or enter and the system will import the data into the catalog system.
If you get a message stating that the file cannot be found, check the file name and case. The file name must be exact. Something like leaving off the .txt at the end of the name or typing it in upper case instead of lower case will cause this type of error.
After the file is imported the system will display the number of records that were imported. You can then press escape to continue
The next step is to check the data to be sure that it was imported correctly with the right column headings. Select Enter Manual Catalog Information from the Import Menu. Type in your Catalog name or you can press spacebar and enter to see a drop-down box of all of the catalogs that have been defined. Highlight the catalog you would like to check and press enter to select it.
Utilities
Import File Data
Enter Manual Catalog Information
The data will be shown in a scrolling window. The number of characters shown on this screen is simply the number you allowed for when you defined your catalog. If you cannot see all of the data in a particular column you can press enter on a particular line to drill into a record and see all of the data. Below are examples of both screens.
You can actually edit or delete the data in the screen above. If you see a problem with all of the records, you will want to Delete the Catalog information, fix your spreadsheet or definition depending where the error is and re-import the data. If the data is good you can proceed to the next step, setting up the file relationships.
Utilities
Import File Data
Enter File Relationships
Inventory Relationships
Product Definitions
Enter Inventory Relationships
This is where you will tell the system, which columns in your spreadsheet match the fields you would like to update in The Business Edge. As mentioned earlier you must always have the Product Code in your data, it should be the first column of your spreadsheet. You can press spacebar and enter to select the fields in the catalog that match up to the corresponding fields you want to update.
If you have extra data in your spreadsheet that you do not want to update do not enter it on this screen.
The next step is the update the records.
Utilities
Import File Data
Update Files with Imported Data
Update Inventory Data
Update Product Definitions
Update Inventory from Catalog Info
Type in your catalog name or press spacebar and enter to select from a drop-down box. You can now choose which fields you would like to import by changing the selection to Yes/No. The last question on this screen asks if you would like to create New Inventory Records. When updating data be sure to answer No here. If you answer Yes, the system will create new parts automatically using the defaults in Enter and Edit Inventory Defaults.
After answering all of the questions the system will update your inventory file. A summary will be displayed when it is complete showing how many records were updated. An example is shown below.
The final step is to delete the Catalog Information. If you are planning on using the same format over again you should always be sure to delete previous records.
Utilities
Import File Data
Deletion Options
Delete Catalog Information
Type in your catalog name or press spacebar and enter to select if from a dropdown list. Confirm by answering Yes to Delete all Catalog Records.
