CCS Point of Sale
You are here: start » office » inventory » excel_import
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
office:inventory:excel_import [2013/11/19 01:51] markphillips2 |
office:inventory:excel_import [2018/12/23 12:11] (current) |
||
|---|---|---|---|
| Line 16: | Line 16: | ||
| When a product or invoice import runs, It then imports all of the items into the transaction. If any of these columns are setup, (besides barcode), CCS will attempt to find the following field relations when importing. If it doesn't find them, it will add them to the system. | When a product or invoice import runs, It then imports all of the items into the transaction. If any of these columns are setup, (besides barcode), CCS will attempt to find the following field relations when importing. If it doesn't find them, it will add them to the system. | ||
| - | ^Invoice / Product Import|When an invoice or product is imported the following related information is updated:| | + | ^Invoice / |When an invoice is imported the following related information is updated:| |
| |Barcode| A unique barcode will be generated if one is not provided.| | |Barcode| A unique barcode will be generated if one is not provided.| | ||
| |Department |for the department column, if the department is not found it will be added.| | |Department |for the department column, if the department is not found it will be added.| | ||
| Line 31: | Line 31: | ||
| ===== Product Import ===== | ===== Product Import ===== | ||
| + | When you import a list of products. This field list is what you can reference to your document. We suggest you use one description field, such as 'class' or 'subclass' with a unique entry in that column to uniquely identify that import. That way if you need to edit the imported items after the fact you can quickly reference them.\\ | ||
| + | |||
| + | ^FieldName / |Description| | ||
| |ProductName|Description of Item, 50 characters| | |ProductName|Description of Item, 50 characters| | ||
| |SKU|Manufacturer code| | |SKU|Manufacturer code| | ||
| |BARCODEID|Barcode of item, does not need to be unique.| | |BARCODEID|Barcode of item, does not need to be unique.| | ||
| - | |UNITNAME|Description of Size or Unit description item is provided in. | + | |UNITNAME|Description of Size or Unit description item is provided in.| |
| |UNITPRICE| Standard price of the item.| | |UNITPRICE| Standard price of the item.| | ||
| |DEPARTMENT|Major group of item.| | |DEPARTMENT|Major group of item.| | ||
| Line 62: | Line 65: | ||
| |ALLOWABLE|Not used.| | |ALLOWABLE|Not used.| | ||
| |HCPCS|Not used.| | |HCPCS|Not used.| | ||
| - | |COUNTRYOFORIGIN|Description of orgin| | + | |COUNTRYOFORIGIN|Description of origin| |
| |LEADTIME|Time it takes to get the product after ordered from vendor.| | |LEADTIME|Time it takes to get the product after ordered from vendor.| | ||
| |CURRENTCOUNT|Current individual unit count of item.| | |CURRENTCOUNT|Current individual unit count of item.| | ||
| - | |REORDERLEVEL|Amount that indicates the item should be reordered. | + | |REORDERLEVEL|Amount that indicates the item should be reordered.| |
| |COLORDESCRIPTION|Description of item color| | |COLORDESCRIPTION|Description of item color| | ||
| |DEPTH|Numeric depth entry| | |DEPTH|Numeric depth entry| | ||
| |HEIGHT|Numeric Height| | |HEIGHT|Numeric Height| | ||
| - | |WEIGHT| | + | |WEIGHT|Numeric Weight of item| |
| |YEARSOLD|Numeric age of item| | |YEARSOLD|Numeric age of item| | ||
| |DATEMADE|Date format of item mm/dd/yyyy| | |DATEMADE|Date format of item mm/dd/yyyy| | ||
| Line 87: | Line 90: | ||
| |PIECEWEIGHT|If this item is for candy, or packaged items on a scale, this is the packaging weight of the item itself, including the package.| | |PIECEWEIGHT|If this item is for candy, or packaged items on a scale, this is the packaging weight of the item itself, including the package.| | ||
| |WICEBT|Put - 1 if this item is WICEBT| | |WICEBT|Put - 1 if this item is WICEBT| | ||
| - | |KCODE|Not used| | + | |KCODE|Not used.| |
| + | ===== Best Practices ===== | ||
| + | === Barcodes=== | ||
| + | All items imported should have a barcode, this can be a self generated sequential number. To create uniqueness, join two fields together, one number and one set of letters unique to the spreadsheet.\\ | ||
| + | |||
| + | === Barcode Formating === | ||
| + | |||
| + | If you are importing barcodes, it's suggested you put a letter in front of them. This prevents excel or other spreadsheets form confusing the number with a scientific notation that incorrectly converts the number into something it's not.\\ | ||
| + | |||
| + | == Validation of Import == | ||
| + | |||
| + | If you want to validate the import, there are two things to watch out for. You want to validate the total items imported, and the sequential results of them. The second part is done this way:\\ | ||
| + | Setup one column with a sequential number. This way you can view your import sorted by this number. You will want to use a field such as class, subclass, or style or another field that is not used by your import.\\ | ||
| + | You can then preview the sort window using the Pivot Reporting under Office - Reporting - Pivot Reporting. Under the Advanced Tab, use a SQL statement like this one:\\ | ||
| + | <note tip>Select ProductName,BarcodeID,Class\\ | ||
| + | FROM tblProducts\\ | ||
| + | ORDER BY CAST(CLASS AS INT) \\</note> | ||
| + | This statement uses the class field as the sequential number and sorts by this number. You can use the results of the statement to verify the import. | ||
| + | |||
| + | == Identifying Different Import Sheets == | ||
| + | |||
| + | If you are importing multiple sheets, use a unique identifier in one of the import columns to help you organize your results. If you need to delete the import because it wasn't improperly done, using the unique identifier greatly helps this. Example of a unique identifier would be to use the 'Style' field and enter '**Sheet1** in for each row. You could then do a product search on **Sheet1** and pull up only those items in the results. | ||
| ===== How to Import ===== | ===== How to Import ===== | ||
| Line 97: | Line 121: | ||
| To import from CCS a import template must be added. | To import from CCS a import template must be added. | ||
| - | 1. From the ToolBar Press Add. This adds a new Defined Excel Template Row. Type in the name of the import, press enter. Then select the import type: contact, Invoice Transaction, or Products. Pressing enter after selecting the type will automatically save the new entry. | + | 1. From the ToolBar Press Add. This adds a new Defined Excel Template Row. Type in the name of the import,\\ press enter. Then select the import type: contact, Invoice Transaction, or Products. Pressing \\enter after selecting the type will automatically save the new entry.\\ |
| {{:office:inventory:excelimport.jpg|}} | {{:office:inventory:excelimport.jpg|}} | ||
| - | 2. Select the fields from the center list and press the add arrow button. | + | 2. Select the fields from the center list and press the add arrow button. \\ |
| - | 3. Press - File - Generate Excel File. A new file will be created with the selected Headers. | + | 3. Press - File - Generate Excel File. A new file will be created with the selected Headers. \\ |
| - | 4. Using the file, a list of contacts, inventory or transactions will be generated. | + | 4. Using the file, a list of contacts, inventory or transactions will be generated.\\ |
| Stage 2 - Importing | Stage 2 - Importing | ||
| Line 109: | Line 133: | ||
| {{:office:inventory:exceldisplay.jpg|}} | {{:office:inventory:exceldisplay.jpg|}} | ||
| - | 1. Select the template to import from the list on the right. | + | 1. Select the template to import from the list on the right.\\ |
| - | 2. From the toolbar go to File - Import. Navigate to the excel file and press open. | + | 2. From the toolbar go to File - Import. Navigate to the excel file and press open. \\ |
| - | *.If this is a Invoice,when a transaction import is completed, the transaction number will be provided. | + | *.If this is a Invoice,when a transaction import is completed, the transaction number will be provided.\\ |
| - | + | ||
Trace:
