Template-based Import vs. Industry Standard File

<< Click to Display Table of Contents >>

You are here:  APFlow™ Module > Invoice Entry > Credit Card Importer >

Template-based Import vs. Industry Standard File

 

Credit Card Import templates can be added, edited, copied or removed from multiple locations within Sage Paperless Construction.

 

There are several ways to access the Add Credit Card Template:

1.From the ribbon toolbar, click APFlow™.

2.Click Manage APFlow™, ManageAPFlowButton.

3.Click the Credit Cards tab.

4.Click Add.

1.From the ribbon toolbar, click APFlow™.

2.Click Credit Card Importer, CreditCardImportericon-mh.

3.Click New Import.

4.Click New Template.

1.From the ribbon toolbar, click APFlow™.

2.Click Credit Card Importer, CreditCardImportericon-mh.

3.Click Manage Templates.

4.Click Add.

 

If the credit card statement uses one of the three standard formats, refer to the Industry Standard File sub topic.
If the credit card statement does not use one of the three standard formats and a template must be created, skip to the Template-based Import sub topic.
 

 

Industry Standard File

 

1.Download the credit card statement from the credit card provider.

2.Look at the file extension.
 
Most credit card companies offer the ability to download credit card statements into one of three standard download formats:
 
Quicken (.qfx)
Quickbooks (.qbo)
Microsoft Money (.ofx)
 

3.Click New Import.
 
A New Credit Card Import window similar to the following displays:
 
CreditCardImporterNewImportwindownew-mh
 

4.In the Import Name field, enter an appropriate name for the import.

5.In the File Name field, click the ellipsis, Ellipsis-mh to locate the file.
 
The New Credit Card Import window redisplays and resembles the following:
 
CreditCardImporterNewImportwindowstandardfileformat-mh
 
The template name that displays in the Template Name field is system-generated and cannot be modified.
 

6.If appropriate, click to select Use Vendor Mapping to Reapply Vendor Names.
This sets the APFlow™ system to systematically match imported secondary vendors with the vendor information saved from previous imports.
 

7.If appropriate, click to select Restrict Access to Me.
This limits the visibility to only the person who created the import.
 
Note: Individuals in a Supervisor role can view import files with restricted access.
 

For information on Secondary Vendors and details on available Secondary Vendor options throughout Credit Card Importer, click here.

 

 

Template-based Import

 

1.From the Credit Card Import window, click New Import.
 
The New Credit Card Import window similar to the following displays:
 
CreditCardImporterNewImportwindownew-mh
 

2.In the Import Name field, enter an appropriate import name.

3.For the File Name field, click the ellipsis, Ellipsis-mh to locate the file to import.
 
Note: This will be a file with a .csv extension.
 

4.Click New Template.
 
AddCreditCardTemplate-mh
 
Note: Invoice header fields display in green font; invoice detail line fields display in blue font.

 
 

5.To identify the column locations and to determine whether Charges and Credits are Negative or Positive, open the credit card statement in Excel.
 
CreditCardImporterCreditCardStatementSamplenew-mh
 
In this Excel sample, the header file occupies row 1. The Transaction Date is column 1, the Vendor  is column 3 and the Transaction Amount is column 8.
Charges are positive and Credits are negative.

 

6.To identify how the columns are separated and if double quotes or single quotes are used, open the credit card statement in Notepad.

7.Look to see how the values are separated.
 
If opening the credit card statement in Notepad, it should resemble the following:
 

Type,Trans Date,Post Date,Description,Amount,Category,Memo
Sale,08/03/2018,08/05/2018,ULTA #337,-51.14,,null
Sale,07/30/2018,07/31/2018,SHOE STATION #17,-115.48,,null
Sale,07/30/2018,07/31/2018,ACADEMY SPORTS #51,-82.43,,null
Payment,07/25/2018,07/25/2018,Payment Thank You-Mobile,"2,000.00",,null
Sale,07/21/2018,07/24/2018,WENDYS #1813,-18.22,,null

 

8.Determine if the text is separated by comma, tab, space or pipe (|).
 
In this Notepad sample, the text is separated by commas.

 

9.Next, look for any dollar amounts or other fields that are enclosed by single or double quote marks.
 
Text delimiters are typically used when the column delimiter is part of the value--in the case of commas in a currency value for instance--which may not be used on every line.
 
In this Notepad sample, double quote marks are used.

 

10.Return to the Add Credit Card Template.

11.In the Name field, enter the name of the template.

12.In the Default Vendor field, click the field name to access the Lookup Utility window.

13.Use the lookup field or scroll through the list of vendors and then click to highlight and select the vendor name.

14.Click OK.
 
The Vendor ID associated with the selected vendor displays in the Default Vendor field.

 

15.In the Column Delimiter field, use the dropdown list to select from one of the following:
 
Comma
Tab
Space
Pipe
 
Using the Notepad sample, Comma is the correct choice for the Column Delimiter field.
 

16.In the Text Delimiter field, use the dropdown to select one of the following:
 
None
Single Quote
Double Quote
 
Using the Notepad sample, Double Quote is the correct choice for the Text Delimiter field.
 
In the Notepad sample, there is an amount listed that includes a comma and is surrounded by double quotes: "2,000.00".
If None displays in the Text Delimiter field, the 2,000.00 entry will "be read" by the system as 2.
 

17.Identify if the charges on the credit card statement in Excel are listed as Negative or Positive. In the Charges text box, click the appropriate radio button.

18.Identify if the credits on the credit card statement in Excel are listed as Negative or Positive. In the Credits text box, click the appropriate radio button.

19.In the Skip first field, use the arrows to select the number of lines to skip.
 
Using the Excel sample, 2 is the correct choice for the Skip first field, as these are the header lines.

 

20.In the Field Name / Enter Column Position box, the field names that display in bold are required
 
Using the Excel sample, the Date Posted column is 1, the Amount column is 8 and the Description column is 3.

 

21.Click Preview.
 
The credit card statement using the template will display. This is how to determine if the information added to the Template Properties area of the Add Credit Card Template window accurately reflects the associated credit card file.
 
Note: This is an optional step.

 

Important

The Credit column is used when debits and credits are not combined in the same column in the file. When this occurs, set the Transaction Amount to the debit column and Credit to the credit column.

Any field in the Invoice Entry line section may be mapped to a field in the template.