Import Accounts Receivable Invoices

JobView allows third-party invoices to be imported into the Accounts Receivable module. The Import AR Invoices screen is accessible from the Accounts Receivable Invoices screen by pressing the Import button. The following screen comes into view.

Each saved Invoice Source is listed in this section with tools for adding new sources and modifying or deleting existing sources.

Once the import source has been defined and selected, press the Preview button to see what the Imported Invoice will look like. If there are errors, an error screen with appropriate error descriptions will come into view. Otherwise, the preview screen on the right will be filled with information from the import file.

If the information in the preview window correctly represents the invoices to be imported, click the Import button and the invoices will be inserted into the highlighted invoice Batch.

Press the Import button to bring the formatted information into the batch that was highlighted at the AR Invoices screen.

This is the name of the import source. There must be a name or the source will not be saved.

Three different file types can be used for importing third-party Invoices into the accounting system.

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.

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.

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.

Select the Date Format from the drop-down that is used to represent dates in the import file.

If the customer number from the outside source is not the same customer number in JobView, the user may create a Customer Number Equivalency Table here. For example: If customer numbers are stored as alphanumerically in the import source, a Customer Number Equivalency Table would be necessary to map these non-numeric customer ids to their corresponding customer numbers.

Check the box to enable the Customer Number Equivalency Table for the import file and press the Edit Table button to map the external customer numbers to the JobView customer numbers. The following screen will come into view.

Using the Insert or Change button, create or modify a customer equivalency item in the following screen:

Comma Delimited files save their data separated by commas like this:
Column 1,Column 2,Column 3,Column 4
Row 1 Value 1,Row 1 Value 2,Row 1 Value 3,Row 1 Value 4
Row 2 Value 1,Row 2 Value 2,Row 2 Value 3,Row 2 Value 4
...
Row N Value 1,Row N Value 2,Row N Value 3,Row N Value 4

Tab Delimited files save their data separated by tabs like this:
Column 1    Column 2    Column 3    Column 4
Row 1 Value 1    Row 1 Value 2    Row 1 Value 3    Row 1 Value 4
Row 2 Value 1    Row 2 Value 2    Row 2 Value 3    Row 2 Value 4
...
Row N Value 1    Row N Value 2    Row N Value 3    Row N Value 4

Fixed Position files use physical spacing to identify the separate data values like this:
Column 1            Column 2            Column 3            Column 4
Row 1 Value 1     Row 1 Value 2     Row 1 Value 3     Row 1 Value 4
Row 2 Value 1     Row 2 Value 2     Row 2 Value 3     Row 2 Value 4
...
Row N Value 1     Row N Value 2     Row N Value 3    Row N Value 4

This section allows the user to tell the system how to handle duplicate Invoices if they are encountered during the import process.

Select this option to include all duplicates in the import without warning the user.

Select this option to exclude all duplicates in the import without warning the user.

Select this option to have the system ask the user what to do if a duplicate is found during the import.

Enter the name of the file to be imported or click the lookup button and select the file.

Press the Insert or Change button to add a field to the table. The following screen will come into view.

Select the field contents from the available types in the dropdown list and then press the OK button to save the field to the table.

If One File, One Record is selected in the Invoice File Type section, this tab will not be visible.

Enter the Import File Name or press the lookup button and select the file. This field will not be available if the One File With Header & Detail option is selected in the Invoice File Type section.

Press the Insert or Change button to add a field to the table. The following screen will come into view.

Select the field contents from the available types in the dropdown list and then press the OK button to save the field to the table.

This is the preferred method if none of the invoices have more than one detail line item. In this Scenario, the exported text file will include just one row per invoice that contains both the Header and the Detail information.

Field Contents	Type	Description
Invoice ID	Numeric	User-assigned ID number
*Customer ID	Numeric	JobView Customer ID Number
Customer Name	String	Customer Name
*Invoice Number	String	Our Invoice Number
*Invoice Date	Date	Invoice Date
Due Date	Date	Invoice Due Date
Paid Date	Date	Invoice Paid Date
Check Number	String	Number of check if the invoice is paid
Invoice Type	String	‘T' for Trade Invoice (detail line items attached) ‘P' for Progress Billing (no detail line items)
Description	String	Description of Invoice
Job ID	String	JobView Job ID
Income Account	Numeric	GL Account for tracking Income
Line Item ID	String	JobView Item ID
Line Item Description	String	Description of Line Item
Line Item Quantity	Numeric	Number of Units
Line Item Unit Price	Numeric	Price per Unit
*Line Item Amount	Numeric	Line Item Amount
Line Item Tax Amount	Numeric	Tax amount for this line item
Line Item Date	Date	Date for this line item
Line Item Ticket #	String	Ticket number for this line item
Line Item Location	String	Location for this line item
Text line Item	String	If a value is found in this field, the system will add a text line item to the invoice with this value as the text
Tax Amount	Numeric	Total amount of tax
Tax Code	String	JobView Tax Code for calculating tax
Discount Amount	Numeric	Discount offered
Discount Expires	Date	Discount expiration date
Retention Amount	Numeric	Amount of retention (optional on ‘P' type)
Freight Charge	Numeric	Freight Charge
Miscellaneous Charge	Numeric	Miscellaneous Charge
Work Order	String	Work Order Number
Purchase Order	String	Customer Purchase Order Number
Invoice Amount	Numeric	Total Invoice Amount
Contact	String	Name of customer contact
Address	String	Customer Street Address
City	String	Customer City
State	String	Customer State
Zip	String	Customer Zip Code
* Required Field

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:

Detail for Invoice 1
Detail for Invoice 1
Detail for Invoice 2
Detail for Invoice 3
Detail for Invoice 3

Example Header File with Invoice ID, Customer ID, Customer Name, Invoice Date and Invoice Number fields:

“1”,”34”,“Clark Resources, Inc”,“12/14/09”,“332988”
“2”,”17”,“Aberley Cabinets”,“12/15/09”,“332988”

Example Detail File with Invoice ID, Description, Quantity, Unit Price, Amount, Text Flag and Text fields:

“1”,“Service”,“12.5”,“85.00”,“1062.50”,“F”,“”
“1”,“ ”,“ ”,“ ”,“ ”,“T”,“This is for services rendered in completing survey requirements”
“2”,“Service”,“3”,“85.00”,“255”,“F”,“”
“2”,“ ”,“ ”,“ ”,“ ”,“T”,“Services provided December 8 according to contract agreement”

If one file is exported, it will 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
Header for Invoice 2
Detail for Invoice 2
Header for Invoice 3
Detail for Invoice 3
Detail for Invoice 3

Example export file. Header records have the letter “H” as the first field and include Customer ID, Customer Name, Invoice Date and Invoice Number fields. Detail records have the letter “D” as the first field and include Description, Quantity, Unit Price, Amount, Text Flag and Text fields:

“H”,”34”,“Clark Resources, Inc”,“12/14/09”,“332988”
“D”,“Service”,“12.5”,“85.00”,“1062.50”,“F”,“”
“D”,“ ”,“ ”,“ ”,“ ”,“T”,“This is for services rendered in completing survey requirements”
“H”,”17”,“Aberley Cabinets”,“12/15/09”,“332988”
“D”,“Service”,“3”,“85.00”,“255”,“F”,“”
“D”,“ ”,“ ”,“ ”,“ ”,“T”,“Services provided December 8 according to contract agreement”

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
*Invoice ID or Type	Numeric	Two Files: User-assigned ID number (link to detail file), One File: “H” for Header
*Customer ID	Numeric	JobView Customer ID Number
Customer Name	String	Customer Name
*Invoice Number	String	Our Invoice Number
*Invoice Date	Date	Invoice Date
Due Date	Date	Invoice Due Date
Paid Date	Date	Invoice Paid Date
Check Number	String	Number of check if the invoice is paid
Invoice Type	String	‘T' for Trade Invoice (detail line items attached) ‘P' for Progress Billing (no detail line items)
Description	String	Description of Invoice
Job ID	String	JobView Job ID
Income Account	Numeric	GL Account for tracking Income
Tax Amount	Numeric	Total amount of tax
Tax Code	String	JobView Tax Code for calculating tax
Discount Amount	Numeric	Discount offered
Discount Expires	Date	Discount expiration date
Retention Amount	Numeric	Amount of retention (optional on ‘P' type)
Freight Charge	Numeric	Freight Charge
Miscellaneous Charge	Numeric	Miscellaneous Charge
Work Order	String	Work Order Number
Purchase Order	String	Customer Purchase Order Number
*Invoice Amount	Numeric	Total Invoice Amount
Contact	String	Name of customer contact
Address	String	Customer Street Address
City	String	Customer City
State	String	Customer State
Zip	String	Customer Zip Code
* Required Field

Field Descriptions for Detail Records
Field Contents	Type	Description
*Invoice ID or Type	Numeric	Two Files: User-assigned ID number (link to header file) One File: “D” for Detail
Item ID	String	JobView Item ID
Description	String	Description of Line Item
Quantity	Numeric	Number of Units
Unit Price	Numeric	Price per Unit
*Amount	Numeric	Line Item Amount
GL Account	Numeric	GL Account for tracking Income
Relieve Inventory	T/F	Instructs the system to relieve item from inventory
Inventory Location	String	Location ID of location from which to pull inventory
Inventory Area	String	Area ID of location from which to pull inventory
Text Flag	T/F	If True, this is a text line item and only the Text field needs to be filled
Text	String	Text for text line item
* Required Field