Looking for something Else?
Overview
This is a feature that allows the user to import a .csv file into the Sculpture Inventory System containing purchase or item information. This feature will automatically build/create new items for the user and can define the attributes of the item to make an item setup much easier. Though the specific use is defined as the ability to import invoices from individual vendors, a user can utilize this for a number of other uses as well.
Some electronic invoices (like Sysco) have size and unit combined into one column |750 ml| vs. |750 | ml| There are excel functions that can separate out the text of two words if space is included into two columns.
Definitions
File Delimiter
A file delimiter is a character used to specify the boundary between separate, independent regions in plain text or other data file formats. The image below is an example of a comma separate file (.csv) as it appears in excel and in a text file. Each comma in the text file correlates to the cell wall between one column and the next.
The inventory platform accepts files that have data delimited by two different characters. The Comma ( , ) or the Pipe ( | ). This is a standard basic format of most files.
File Qualifier
A file qualifier, also known as a text qualifier, is a symbol or character that more clearly defines the boundary between separate, independent regions in plain text or other data file formats. This is especially useful when the data that a user is attempting to import contains characters that match the delimiter. For example, the comma-separated format is a widely recognized standard, but when this is used for financial data, values over $999 have the potential to be split a comma.
Reviewing the example above, a user can see the outcome of a qualifier. It is important to note that without the qualifier, Column 3 changes drastically.
Match Item by:
The invoice importer will match items in the existing database based on the criteria selected within this drop-down.
Barcode/Product Code/Bin#/Item Code Only
The importer will match the item by specific barcode, product code, Bin#, or Item code. The matching item’s size will be disregarded and not considered when mapping an item.
Both Size AND Barcode/Product Code/Bin#/Item Code
The importer will match the item by specific barcode, product code, Bin#, or Item code and then also reference the size of the matched item. The size provided will need to match the size of the existing item in the database, else the uploader will suggest a new item to be created.
Vendor:
This label has changed to “Default Vendor”. The selected vendor is used only when the CSV file does not have a “Vendor” column. Users can upload files that contain invoices from multiple vendors. When the “vendor” field is specified, the “Default Vendor” is ignored.
Field Descriptions
Item Identification Fields:
One of these 5 fields must be specified in order for the invoice to import properly (excluding “Not Used”).
- Not Used: Column is to be ignored
- Name: Item’s specific Name
- Is used to potentially match an existing item with an item being uploaded
- Bar code: Bar code / UPC of the pack or package purchased
- BIN NO: Bin number of the pack or package purchased
- Product Code: “Vendor Product Code” – Vendor code of the pack or package purchased
- Item Code: Code assigned to an item in the Item Setup beside the barcode field
Item Quantity Fields:
- QTY: Quantity / Number of packs (bottles/kegs/each) or packages (cases, boxes, etc. containing multiple packs) purchased or weight purchased when applicable.
- Must be Numeric
- Assumed 0 if the field is empty
- Weight: Weight purchased (Food Only)
- Unit of Purchase :
- For purchases by weight: unit of measurement such as g, kg, lb, etc.
- For purchases by count: unit descriptor such as “BT”, “CS”, etc. that differentiates the same product code used by a vendor for different packaging of the same item (e.g. Vendor ABC uses product code 1234 for both the individual bottles and the cases of Beer XYZ. On the paper invoice, the vendor would specify what is sold in a separate column with keywords such as “BT” and “CS”.)
- Split: designed specifically for Sysco (US) invoice file uploads, or other imports, that contain a column called split. Whenever there’s an “S” it means the item is bought in broken cases and the purchase qty refers to # of BTL instead of # of CASE (partial case purchase).
Item Price Fields:
- Unit Price: Unit Cost per Item, pack, or package purchased
- If the field is empty, the cost is calculated given that the QTY is not zero and the TOTAL COST is provided.
- Assumed 0 otherwise
- Original price per purchased unit, before discount or any other type of price adjustment, is applied.
- Unit Deposit: Deposit per purchased unit
- Unit Discount: Discount per purchased unit
- Unit Tax: Tax per purchased unit
- Unit Delivery: Delivery per purchased unit
- Unit Misc. Adjustment: Other price adjustment per purchased unit
- Line Total Discount: Quantity (or weight) x Unit Discount
- Line Total Deposit: Quantity (or weight) x Unit Deposit
- Line Total Tax: Quantity (or weight) x Unit Tax
- Line Total Delivery: Quantity (or weight) x Unit Delivery
- Line Total Misc. Adjustment: Quantity (or weight) x Unit Misc. Adjustment
- Ext. Price: Total Cost: Used to calculate Unit Price when Unit Price is not provided (Unit Price – Line Total Discount + Line Total Misc. Adjustment = Ext. Price )
Item Attributes Fields:
If any of the item’s fields allow for the item to be matched with an existing item in the database, the existing item’s data will be utilized.
- Class: Item’s specific class
- If any of the item’s fields allow for the item to be matched with an existing item in the database, the existing item’s data will be utilized.
- Beverage: if the class does not match or is left blank any of: “Liquor”, “Wine”, “Champagne”, “Beer”, “Coolers”, or “Kegs”, it will be assumed as a class of “Miscellaneous“, with a category of “UNKNOWN“.
- Food: if the class does not match or is left blank, the item will be created with a class of “Food” and a category of “DEFAULT“.
- Category: Item’s specific category
- Spelling must match existing Categories.
- State: Solid / Liquid State of an item that is only specific to food inventory items.
- Solid – Enter “S” for the item to be denoted as a solid
- Liquor – Enter “L” for the item to be denoted as a Liquid
- Blank – Item is assumed Solid if no Unit of Measure is present, Item is assumed Liquid if the unit of measure is that of a liquid (mL, L, oz..etc)
- Size (numeric): Taken as the volume or weight of each pack IF a unit of measurement is specified in the UNIT field; otherwise, no size is assumed
- Must be numeric
- If the field is empty, assumed 0 for count-and-weigh beverage, or no size for everything else
- Size is strictly for the measurable size of an item like “750” (ml) or “500” (g)
- Unit: can be one of the following types:
-
Unit of Measurement: must be one of g, kg, dry oz, lb, mg, ml, L, oz, imp oz, cL, 100-mL, US gal, US tbsp, US tsp, US cup, US quart
-
Unit Description: anything that doesn’t match one of the units of measurement (e.g.EACH, BOTTLE, BOX, PACK, etc.)
-
- Pack Description :
- Beverage: the description is assumed “BOTTLE” or “KEG” depending on the class, so the PACK DESCRIPTION field is not read
- Food: By default, when “300” and “g” is specified as the SIZE and UNIT, the item will be imported as “300 g / EACH”. To replace “EACH” with “BAG”, for example, enter “BAG” in the PACK DESCRIPTION field. If “1” and “BOX” are specified as the SIZE and UNIT, the PACK DESCRIPTION field is not read
- Case Size (numeric) : Package Size / number of items in the package
- Must be numeric and greater than 1
- This is a multiplier of individual units – such as 24 bottles, or 60 units of an individual item
- Case Description : Case / Bundle / Pack
Invoice Header Fields:
- Invoice Number: Vendor Invoice Number
- Invoice Date: Vendor Invoice Date
- Vendor Name: Vendor Name
- Invoice Total Tax: Total tax per invoice
- Invoice Total Delivery: Total delivery per invoice
- Invoice Total Deposits: Total deposits per invoice
- Total Misc. Adjustment: Total of miscellaneous price/cost adjustments per invoice
- Invoice Due Date: The date on which payment is due by the Vendor to the client
F.A.Q.
What criteria does the invoice importer use to match items with existing items in the location? – The importer attempts to match data in the following order.
- Barcode
- Bin No.
- Product Code
- Item Name + Class Name
Are Items marked “Do Not Import” added to a Black List of any sort? – A Black List type feature is in design for development but not a current system that is active.
Step-by-Step
Below is a detailed step-by-step walk-through of the process of importing an invoice into the Bevinco 2020 platform.
Step 1 File Delimiter
Review the file that is to be uploaded and determine the delimiter. This should be either a comma ( , ) or a pipe ( | )
Step 2 File Qualifier
Select a file qualifier if one is required. Not all files require one.
Step 3 Match Item by
Make a decision as to how the search/match feature should reference and match invoice items to the existing database.
Step 4 Vendor
Select the vendor of the invoice from the drop-down list.
Step 5 Import File
Browse for the file that is to be uploaded to the platform.
Step 6 Exclude Header
The header of the file can be included in the imported file to assist in matching the columns in the imported file that of the corresponding names in the system. If they are included in the file, a user would want to have the system skip the header, otherwise, they will be imported as data.
Step 7 Date Format
Select the format of the Month, Date, and Year by your region.
Step 8 Match Column Criteria
Match each column to the best of your ability given what is provided in the source file.
Step 9 Review Invoice for Import
Review the Vendor Name, Date, and Invoice # of the invoice that is to be imported. Select “Review Item Match” to see all items
It is important to note that the invoice number was not included in the file that was uploaded. Due to this, the Bevinco system assigned UPL and the date as defined in step 7.
Step 10 Review Item Match
When reviewing the matched items and items that will be created new, it is important to review all of the information presented.
1) New Item – If an item is not matched to an existing item by the search criteria that was identified in Step 3, it will be created as new.
2) Class / Category Selection – All New Items allow a user to map the correct Class and Category to the items
3) Pack Size – The pack size is the quantity or multiplier of the item. For example, this line item may be a single bottle of beer, but a pack size of 24.
4) Matched Name – If the item matches a name by specific criteria, OR a user wants to manually map it to a specific item, a user can de-select “create new” and type the name into this box. This box auto-populates a list of inventory items from either this client-only or the entire database.
5) Matched Purchase Unit – When matching a name (brand), the matched purchase unit drop-down can be utilized to select the specific bottle UPC or volume that exists under the brand selected.
6) Do Not Import – Marking an item as “Do Not Import” will exclude it from the import process.
7) Non-Stock / Non-Inventory – When checked, the line is not imported, but a sum of all “non-inventory” lines’ extended price is included in the invoice header and adds to the grand total.
Step 11 Imported
Selecting “import” from the top of the page in step 10 will import all items into the database and generate the invoice as defined. The final screen will display as below:
