Mapping Fields

When importing transactions for the first time - you'll be prompted to map (identify) what each column/field represents (amount, name, account number, etc...).  The system will save your mappings - and you can update them at any time.

• Not all columns/fields need to be mapped (ok to skip fields and leave as red)
• The order of the columns/fields does not matter.
  
  

This document covers:

1. Mapping Amount
2. Commonly mapped fields
3. Mapping with Profiles
4. Additional fields / reference

1. Mapping Amount (Critical read - don't 'collect' from employees)

Golden Rule
Negative amounts decrease your bank account (ie. vendor, employee payments), and
Positive amounts increase your bank account (ie. customer collections)

Amount related fields

Amount - If you have the amounts signed as noted below, simply use the 'Amount' field.

Amount_Reverse_Sign - This flips the sign.  It has the effect of multiplying all of the amounts by a negative 1.
For example, if you are making vendor payments and all of your amounts are shown as positive amounts , you have two options:
--Open the file in Excel and multiple all of the amounts by a negative one (not recommended), or
--Simply map the field as 'Amount_Reverse_Sign' (highly recommended)




Other fields such as Debit/Credit fields, Sign (custom text) fields are detailed at the end of this page.

2. Commonly mapped fields (not using profiles)

Please remember that this page relates to the other party's data, such as your clients', employees' and vendors'.

Required fields

• Name 

This is the ACH recipient that you are paying or collecting from.  It does not need to be an 'exact' match of the name on the account.

• ABA_Routing_Number (if creating Canadian EFT;s, 'Financial Institution # + Branch Transit #')

The 9 digit number assigned to each bank.  By default, the system performs a checksum verification on each ABA number and transactions with invalid numbers are excluded from the file.

If using Excel and the leading zero is dropped, the system will automatically restore it.  Caution: If Excel is dropping the ABA leading zeroes, it may also be dropping the leading zeroes from the account numbers. Some banks may require the account number leading zeroes.

• Bank_Account_Number

This is the bank account number of the party that you are paying or collecting from.  Do not include spaces, dashes or other non-numeric data.  If leading zeros are included, we recommend including them on the import file (if zeros are dropped, change the field to a 'Text' format and zeros are retained).

• Amount (or other amount field - see prior section)

Optional fields

• Date - You can map the effective/transaction date.  If not mapped, the system will use the setting in ACH File Setup (default is next business day).
Notes:
If not mapped, the system will take into account weekends, but not holidays.
If mapped and the date is an invalid date, ie February 30th, the transaction will be excluded from the file.e record.

• Checking_or_Savings - The system defaults to checking if not mapped.  If you choose to map, the system recognizes C, CHK, Checking for Checking - and S, SAV, Savings for Savings.

• ID_Number* - [can be blank - this is the other party's identification in your system (i.e., payroll-- this would be the employee Account Number in QuickBooks). 

*NOTE:  Certain banks require ID Numbers, such as JPMorgan Chase, PNC Bank, and some Canadian banks.

There are over 40 additional fields available.  Please see the individual help topics.

• SEC_Code - If not mapped, the system will use the default SEC Code from ACH File Setup, with the default typically being PPD or CCD.  Examples of codes include PPD, CCD, WEB, TEL, IAT, BOC, ARC and CTX.

• ACH_Transaction_Code - A two digit code identifying the various types of debit and credit entries.
Note: Do not map if you are using Amount_Reverse_Sign. 
Examples include 22 (Credit checking account), 27 (Debit checking account), 32 (Credit savings account), 37 (Debit savings account), 

• ACH_Text_Addenda - Payment related information up to 80 characters to be included in a single addenda record (705) such as CCD and PPD (requires Corporate or Advanced license). Note: While ACH Universal will create this record in accordance with Nacha rules, this information will not make it to a typical business or personal (recipient's) bank statement.  If you want to send remittance information, we recommend using our email feature.

• ACH_Email_Address - Remittance information will be emailed to this address (requires Corporate or Advanced license). Note: The email address is not included in the file.  Setup of our email feature is required.

3. Mapping with Profiles

If your Excel or csv file containing your transactions does not include the banking information, you can store the banking information in ACH Universal's profiles.

Then, when you are importing your transactions - you simply map the 

--ACH_Name_Internal (Profile Lookup), and

--Amount

Notes:
--You can use either the ACH_Name_Internal (Profile Lookup) or ACH_ID_Internal (Profile Lookup), and
--Any amount related field.

If you are using stored Profiles for your import, see Profiles.

4. Additional fields / Reference

Additional Amount field options

Debit field - Creates an ACH Debit transaction, which collects funds from the other party (ie. customers).
Credit field - Creates an ACH Credit transaction, which pays funds out  to the other party (ie. vendors and employees).

Sign

Sign field - If your data has a field containing text indicating whether the transaction is an ACH Debit or Credit, you can map the field as the Sign field.  

Note: The default values are 'Debit' and 'Credit' and may be changed in Settings > 'Import, System' > Import.

Debit - Creates an ACH Debit transaction, which collects funds from the other party (ie. customers).
Credit- Creates an ACH Credit transaction, which pays funds out  to the other party (ie. vendors and employees).

No_decimals (feature, not a mapping field)

While not a mapped field, this feature enables you to import a file when the file does not contain decimals.

For example, in the import file the amount is 500 - and is $5.00.

If the amount you are importing does not contain a decimal point, from the main menu select the 'Settings' tab > 'Import,System' > 'Import tab'.  Scroll to 'Imported Amounts need decimals - G/L' and turn 'on'.

Suppress zero dollar transactions (feature, not a mapping field)

Note: By default, a zero amount will generate a pre-note (pre-notification) entry.  To filter out zero transactions, set the appropriate switch in File > ACH Setup.

Importing non-standard date formats (YYYYMMDD, etc.)

As a general rule, if you can display the date in Excel, ACH Universal can import it.  

However, if it is a non-standard format (per Excel), such as YYYMMDD, you need to set Custom Date Import options in ACH Universal.  

From  the main menu select Settings > Import System > Import tab.  Scroll to Custom Date Import (CDI) G/L - and turn it 'On'.  Then set the appropriate selections below it.

See also:  Updating your short date format and

               UK and Canadian date settings

Checking or Savings

If all of your transactions are to/from checking accounts (such as a vendor payment run), you do not need to map a column for Checking/Savings.  The system will assume 'checking' as the default.

If the data you are importing is mixed, i.e., containing transactions which will be received by checking and savings accounts, you will want to map the field as Checking_or_Savings.

The values in the data can be (case insensitive):

NOTE:

If you have a non-standard value representing checking/saving, you can select it through ACH > Setup > Last Panel > Advanced > Legacy 1.