The Import Invoice Templates screen allows you to configure website or FTP information for a distributor and setup templates for invoice files.
Once a template for invoices has been configured, vendor invoices can be imported into AP invoice batches from the Import Invoices (Templates) screen.
It is recommended that you obtain the vendor's file specifications, along with some sample invoice files from the vendor, before you attempt to create an invoice template in Compeat. Your vendor rep should be able to get these for you, or put you in touch with someone in the company that can.
Note: Stock templates for some distributors are available for download from the Compeat website. To use these templates, simply download them from the Downloads page of the Client Login area, and then import them through this screen. Once imported, enter a local directory and save the screen. The templates will then be available for future use.
Note: It is not possible to link an imported invoice to a purchase order in Compeat. If a PO exists in Compeat for an invoice that has been imported, it must be deleted.
There must also be a vendor record on the restaurant inventory item’s ‘Par Levels/Vendor Info’ tab, containing the vendor item code and the split case purchase unit:
Match Vendor Pack Size When this box is checked, Compeat will attempt to match items in the invoice file with restaurant inventory items, based on the vendor pack size defined in the vendor record on the item’s ‘Par Levels/Vendor Info’ tab
Note: The vendor item code and pack size must exactly match the pack size in the invoice file, or an ‘Exact Match’ on the Import Invoices (Templates) screen will not occur.
Default Catch Weight to Lb. This applies to items that are set up as catch weight items in Compeat, that are matched to items in the invoice file. If this box is checked, Compeat will use a purchase unit of 'Lb = 1/Lb' for those items when the invoice is generated.
Credit Memo Fields to Invert There are some instances where credit memos will contain negative values for fields that should be positive in terms of Compeat. For instance, a negative quantity on a credit memo is actually a purchase. Thus, the quantity field would need to be “inverted” to a positive. These options are rarely applied and should only be selected if the data in the file is as described above.
Transmission Type Select the type of transmission that represents the way you will be receiving the invoice files from your vendor. If you receive the invoice files via e-mail, select ‘None’. If you download them from the vendor’s web site, select ‘Web’, and enter the address of the vendor’s web site in the ‘Host Address’ box. If your vendor publishes the invoice files to an FTP site, select ‘FTP’, and select the FTP Configuration from the dropdown menu.
Remote File Name Enter the file name the vendor uses for their invoice files. If a partial name is entered, i.e. “INV_”, when importing invoices a FTP browser will be launched and all files in the specified directory containing “INV_” in their file name will be available for selection to be imported.
Local Directory Enter the file path (or browse) to an existing folder on your computer where order files will be saved when you create them. For an FTP host, the system will look in this folder when you transmit an order, and then FTP to the host address.
In this section, you define the information about the vendor’s invoice files.
File Type This sets the basic file format type. There are 3 possible selections:
Delimited Fields of this type are separated by a character, such as a comma, tab, or space. Files of this type will often have extensions such as .txt, or .csv. This is an example of what data in a delimited file may look like:
Fixed Width In this type, fields are defined by a specific length of characters. Files of this type may also have extensions of .txt. This is an example of what data in a fixed width file may look like:
Excel Select for Excel spreadsheets.
Header rows to skip Most data files will have one or two header rows with column names. These should not be included in the invoice import. Specify the number of header rows in your data file, and Compeat will remove this number of rows from the imported data.
Minimum Row Length Enter the minimum number of characters that Compeat should consider as row with valid data in the file. Rows in the file comprised of less characters than the minimum amount will be ignored during the import process. This is mainly used if the file contains section headings, or other rows that do not contain invoice data, that are shorter in length than valid rows.
Text Qualifier For delimited data, specify the text qualifier used. Delimited data use a text qualifier to specify a string of words as one field, even if it contains a delimiter. Typical text qualifiers are single- or double-quotes. You can usually determine what text qualifiers are used by opening your data file in Notepad and examining the data. In the example below, the text qualifier is the quotation mark:
Field Delimiter For delimited file types, specify the delimiter. Delimiters separate different fields in a record (row). You can determine the delimiter type by opening the file in Notepad, and look for column separators.
Row Delimiter For fixed width and delimited file types, select a row delimiter. Row delimiters separate one record from another.
Record Format Type This indicates how the invoice data itself is formatted within the invoice file. There are 3 possible selections:
Single Row Per Record Select this if all of the data for each single record in the file is contained in one row of data. In this format, for example, a single row in the file would contain the invoice number, invoice date, invoice total, tax total, item code, quantity, split case, etc. Here is what the data may look like in a file with this record type:
Fixed Number Of Rows Select this if a single record in the file is comprised of multiple rows. In this format, for example, one row of the record may contain the invoice number, invoice date, and customer account number. The next row in the file may contain the line item information, and the row after that may contain the invoice total information. Here is what the data may look like in a file with 3 rows per record:
Rows Contain A Record Indicator Select this if the file contains a column containing a record indicator. A record indicator is used to denote the type of data contained in the row. For example, one row may contain a record indicator of ‘HDR’, denoting that the row contains invoice header information such as the invoice number, date, and account number. Another row might have an indicator of ‘DTL’, indicating that the row contains line-item detail information. Another may have an indicator of ‘SUM’, indicating the row contains invoice totals. What the indicators are and what they represent may vary from vendor to vendor. Ask the vendor for their invoice file specifications to help determine what the indicators refer to. When this format type is selected, a box will appear labeled ‘Indicator Position’. Enter the column number in the file that contains the row indicator. For delimited and fixed-width files, the columns are numbered left-to-right beginning with zero. For Excel files, the column names are used (A, B, C, etc.). Here is what the data may look like in a file with a record indicator in column 0:
The Fields section contains a data grid that is used to map fields from the order guide to fields within Compeat. Click the plus symbol in the grid toolbar to open a list of hard-coded Compeat fields.
Select the fields that you wish to map to and then click OK.
Note: The list of available fields may not exactly match those in a data file. For example, a "Item ID" in the data field may need to be mapped to the "Item Code" in Compeat.
Description grid options:
Field This field is non-editable and can be selected from a list of hard-coded options by clicking the plus symbol in the grid toolbar. This forms the list of Compeat fields to be mapped to the order guide fields. In order to successfully import the invoice file into Compeat, the following fields must be defined: Document Date, Document Number, vendor Item Code, and Amount or Amount Each. Some fields expect specific values:
Split Case This field expects a true or false value, with the true value indicating that the data in the record applies to a split or broken case item. In order for a true value to be detected, the data in this field must be 'y', 'yes', 't', 'true', or '1'. For a false value to be detected, the data in this field must be 'n', 'no', 'f', 'false', or '0'. If the data in the invoice file does not use one of these indicators, you can use the 'REPLACE' formatting rule to replace the values in the file with one of the accepted ones.
Credit Memo Ind. A true value in this field indicates that the data in the record is a credit memo. Data in this field must be 'cr', 'cm', or 'credit memo', to return a true value. Values of 'inv','i','di', or 'invoice' in this column will return a false value, indicating to Compeat that the record is not for a credit memo. If the data in the invoice file does not use one of these indicators, you can use the 'REPLACE' formatting rule to replace the values in the file with one of the accepted ones.
Invoice Ind. This is the exact opposite of the Credit Memo Ind. field; a true value indicates the record contains invoice data.
Location Use this field to indicate which column in the order guide should be mapped to this Compeat field. Delimited and fixed-width columns are numbered left-to-right, beginning with 0. For example if you wish to map the Compeat field to the third column in the invoice file, enter "2" in this field. For Excel files, the column names are used (A, B, C, etc.).
Length Specify the length of the field for fixed-width data types. This field is not relevant to other file types, and will only appear if ‘Fixed Width’ is selected as the file type.
Formatting Rules Shows any formatting rules applied to the field. Double-click to create or edit rules.
Record Ind. This column is visible when ‘Rows Contain A Record Indicator’ is selected as the record format type. Enter the record indicator here to specify what type of data the column contains in the indicated row.
Row # This column is visible when ‘Fixed Number Of Rows’ is selected as the record format type. Enter the row number within the record here.
You can apply functions to transform the data as it is imported. To create rules for a field, select the field and click on Edit Formatting Rules. These rules include functions for concatenating, removing spaces, converting case, basic math functions, and more. A brief description each function will display at the bottom of the dialog when you click on the function in the "Available Rules" list:
The Import Template and Export Template buttons can be used to export the current configuration to an xml file or import an existing configuration from an xml file. Pre-built templates from the Compeat website can be imported using the ‘Import Template’ button.
Once a vendor invoice template has been created, you must assign it to a vendor. This is done by opening the vendor in Compeat, and selecting the template in the drop-down labeled ‘Invoice Imports’, in the ‘Electronic Distributors’ section of the screen.
Here are some file data examples, along with how their import templates would be set up in Compeat:
File data:
(Compeat) File Settings:
(Compeat) Fields:
File Data:
(Compeat) File Settings:
(Compeat) Fields:
File Data:
(Compeat) File Settings:
(Compeat) Fields:
File Data:
(Compeat) File Settings:
(Compeat) Field:
