Accounts Payable: Import Invoices Accounts Payable: Import Invoices
Third party invoices can be imported into the accounting system. The Accounts Payable Import Invoices screen is accessible from the Accounts Payable Invoices screen by pressing the Import Invoice button on the main screen. The following screen comes into view.
Add (button)
See Adding or Changing an Import Source
Change (button)
See Adding or Changing an Import Source
Delete (button)
Press the Delete button and the highlighted source will be deleted.
Preview (button)
Press the Preview button to see what the Imported Invoice will look like. If there are errors, the following screen will come into view.
Press the Proceed button to continue or the Cancel button to exit.
If the system finds invoice records that can be imported, it will fill the list box on the right with the invoice information and the screen will look like this:
Import (button)
Once records are showing in the preview list box, the Import button will be enabled. Click this button to import the invoices into the current invoice batch. The program will perform all the functions associated with invoice entry by hand. For example, the system will check for entries that will cause job costs to go over budget and display an information window if this is the case.
Adding or Changing an Import Source
Press the Add or Change buttons and the following screen comes into view.
Source Name
This is the name of the import source. There must be a name or the source will not be saved.
Import File Type
Invoices can be import from three types of text files.
Two Separate Files (radio button)
If this style is chosen, the user must define the field contents of two different files, a Header file and a Detail file. The tie between the records in the two files is the Invoice ID field. It must be the first column in both the Header and Detail records. For example, the first record in the Header file will have an Invoice ID number (let's say number 123) in the Invoice ID column. The detail file will then have one or more rows that have 123 in the Invoice ID column. These details are tied to the invoice.
One File with Header & Detail (radio button)
If this style is chosen, the user must define two types of records in the same import file, a Header record and a Detail record. Detail records must follow the Header record to which they belong. In this setup, the first field in both record types must be Record Type, either H for Header or D for Detail. The program will find a Header record and any Detail records immediately following will belong to that header.
One File, One Record (radio button)
If this style is chosen, the user must define only one type of record, but the record contains both Header and Detail information. When invoices are imported using this method, each imported invoice will contain one and only one line item.
One Invoice per Row / One Invoice per Vendor (radio buttons)
The text file being imported may contain more than one row of information for the same vendor. This can be imported in one of two ways. If the first option is selected, a separate invoice will be created for each row of information encountered. If the second option is selected, the system will create one invoice for each unique vendor in the import file, with invoice line items for each row of information found.
Allow multile jobs per invoice (check box)
If multiple rows of information are found for the same vendor, but not all the rows share the same Job ID, the system can either create one invoice with line items charged to multiple jobs, or multiple invoices for the vendor, one for each unique job encountered.
Import File Format (radio buttons)
Choose the format of the import file. Choose from Comma Delimited, Tab Delimited, or Fixed Position.
Date Field Format
Choose the form of the date that is desired. Click on the down arrow to select from the possible choices.
Import File Name
Enter the name of the file to be imported or click the lookup button and select the file. Below this field is a box that displays the field number and field contents.
Add (button)
Press the Add button to add a field to the box. The following screen will come into view.
Enter the correct contents of the field and press OK.
Change (button)
Press the Change button to change the field contents.
Delete (button)
Press the Delete button to erase the field contents.
Press the Invoice Detail File tab and the following screen comes into view.
Import File Name
Enter the Import File Name or press the lookup button and select the file.
Add
Press the Add button and the following screen come into view.
Enter the correct field contents and press OK.
Change
Press the Change button to edit the field contents.
Delete
Press the Delete button and the highlighted field contents will be erased.
Importing Pre-paid Invoices
Invoices can be imported as either "Open," "Paid by Handcheck," or "Paid by Credit Card." The system will treat the imported invoice as pre-paid if the text file being imported has a value in either the "Paid Date" or "Check Number" field. If this is the case, the invoice will be marked as "Paid;" otherwise, it will be marked as "Open."
If the value in the "Check Number" field is the word, "Card," then the invoice will be marked as "Paid by Credit Card." Otherwise, it will be marked as "Paid by Handcheck."
More Detail about File Layouts and Field Values
Case #1 - Two Text Files
In the case of two files, one file contains a record for each invoice, the other file contains a record for each line item detail. Records in the detail file are linked to a record in the header file by a common Invoice ID number. This Invoice ID number must be the first column in both files. Here are the sample contents of the two data files:
Text File 1:
Header for Invoice 1
Header for Invoice 2
Header for Invoice 3
Text File 2:
Detail for Invoice 1
Detail for Invoice 1
Detail for Invoice 2
Detail for Invoice 3
Detail for Invoice 3
Case #2 - One Text File with Two Record Types: Header and Detail
If one file is exported, it can contain two record types, a Header record and a Detail record. The first column in each record must be either the letter “H” for Header or the letter “D” for Detail. The details for each invoice header must follow directly after the header record in this manner:
Header for Invoice 1
Detail for Invoice 1
Detail for Invoice 1
Detail for Invoice 1
Header for Invoice 2
Detail for Invoice 2
Header for Invoice 3
Detail for Invoice 3
Detail for Invoice 3
Regardless of which method is chosen, the record layouts for the two types of records are shown below:
|
|
Field Descriptions for Header Records |
|
||
|
|
Field Contents |
Type |
Description |
Required |
|
|
Invoice ID |
Numeric |
User-assigned ID number (link to detail file) |
Yes |
|
|
Vendor ID |
Numeric |
JobView Vendor ID Number |
Yes |
|
|
Invoice Number |
String |
Vendor's Invoice Number |
|
|
|
Invoice Date |
Date |
Vendor's Invoice Date |
Yes |
|
|
Due Date |
Date |
Vendor's Invoice Due Date |
Yes |
|
|
Invoice Type |
String |
‘T' for Trade Invoice (detail line items attached) ‘S' for Subcontract Invoice (no detail line items) |
Yes |
|
|
Description |
String |
Description of Invoice |
|
|
|
Job ID |
String |
JobView Job ID (only necessary on ‘S' type) |
‘S' Only |
|
|
Cost Code |
String |
JobView Cost Code (only necessary on ‘S' type invoices) (e.g. “03000S”) |
‘S' Only |
|
|
Quantity |
Numeric |
Number of Units (optional on ‘S' type) |
|
|
|
Tax Amount |
Numeric |
Total amount of tax |
|
|
|
Discount Amount |
Numeric |
Discount offered |
|
|
|
Discount Expires |
Date |
Discount expiration date |
|
|
|
Retention Amount |
Numeric |
Amount of retention (optional on ‘S' type) |
|
|
|
Purchase Order |
String |
Purchase Order Number |
|
|
|
Total Amount |
Numeric |
Total Invoice Amount |
Yes |
|
|
|
|
|
|
|
|
Field Descriptions for Detail Records |
|
||
|
|
Field Contents |
Type |
Description |
Required |
|
|
Invoice ID |
Numeric |
User-assigned ID number (link to header file) |
Yes |
|
|
Quantity |
Numeric |
Number of Units |
|
|
|
Unit Cost |
Numeric |
Cost per Unit |
|
|
|
Amount |
Numeric |
Line Item Amount |
Yes |
|
|
Tax |
Numeric |
Tax Amount for this line item |
|
|
|
Total Amount |
Numeric |
Total Line Item Amount (Amount + Tax) |
Yes |
|
|
Job ID |
String |
JobView Job ID (only necessary if charged to job) |
|
|
|
Cost Code |
String |
JobView Cost Code (only necessary if charged to job) |
|
|
|
Equipment ID |
String |
JobView Equipment ID (only necessary if charged to equipment) |
|
|
|
Cost Category |
String |
JobView Cost Category (only necessary if charged to equipment) |
|
|
|
GL Account |
Numeric |
Account to be charged (only necessary if Job ID and Equipment ID are empty |
|
|
|
|
|
|
|
Case #3 - One Text File with One Record for Each Invoice
In this case, one text file is exported with each row containing all the information necessary to create a trade invoice with one detail line item. It contains only one record type, but the record contains information necessary to create both the header and detail of the invoice. It is organized in this manner:
|
|
Field Contents |
Type |
Description |
Required |
|
|
Vendor ID |
Numeric |
JobView Vendor ID Number |
Yes |
|
|
Invoice Number |
String |
Vendor's Invoice Number |
|
|
|
Invoice Date |
Date |
Vendor's Invoice Date |
Yes |
|
|
Due Date |
Date |
Vendor's Invoice Due Date |
Yes |
|
|
Invoice Type |
String |
‘T' for Trade Invoice (detail line items attached) ‘S' for Subcontract Invoice (no detail line items) |
Yes |
|
|
Description |
String |
Description of Invoice |
|
|
|
Tax Amount |
Numeric |
Total amount of tax |
|
|
|
Discount Amount |
Numeric |
Discount offered |
|
|
|
Discount Expires |
Date |
Discount expiration date |
|
|
|
Retention Amount |
Numeric |
Amount of retention (optional on ‘S' type) |
|
|
|
Purchase Order |
String |
Purchase Order Number |
|
|
|
Total Amount |
Numeric |
Total Invoice Amount |
Yes |
|
|
Quantity |
Numeric |
Number of Units |
|
|
|
Unit Cost |
Numeric |
Cost per Unit |
|
|
|
Job ID |
String |
JobView Job ID (only necessary if charged to job) |
|
|
|
Cost Code |
String |
JobView Cost Code (only necessary if charged to job) |
|
|
|
Equipment ID |
String |
JobView Equipment ID (only necessary if charged to equipment) |
|
|
|
Cost Category |
String |
JobView Cost Category (only necessary if charged to equipment) |
|
|
|
GL Account |
Numeric |
Account to be charged (only necessary if Job ID and Equipment ID are empty, and invoice is to be charged to a GL expense account) |
|