EFT Tables Used in Dynamics GP

In Dynamics GP, it’s common to need a report on EFT transactions or other information, but you can’t use a built-in report or SmartList to get that info. Instead, business analysts, developers, or consultants often need to create customized SSRS or SmartList reports for this purpose.

Below, you’ll find a list of all the SQL tables you may need for EFT information in GP, as well as step-by-step instructions on how to add an SQL view to your existing “Vendor Addresses” SmartList in GP.

Physical Name
Display Name
Checkbook Electronic Funds Transfer Maintenance
Checkbook EFT Optional Header
Checkbook EFT Format Header
Checkbook EFT Format Detail

Checkbook Transaction Electronic Funds

1 – purchasing
2 – sales


Checkbook EFT Transaction Batch

1 – pending generation
2 – marked for generation
3 – already generated (not used)

Checkbook EFT

Address Electronic Funds Transfer Master

(Customer/Vendor Banking Information)

3 = RM

4 = PM


How to Add EFT Info to GP’s Vendor Addresses SmartList

  1. Log into SQL Server Management Studio
  2. Run Create View statement against the relevant database
    create view view_name as
    select VENDORID, BANKNAME, EFTBankAcct
    from SY06000 SQL code for running a Create View statement from the relevant table
  3. Log into GP > SmartList, select the “Vendor Addresses” SmartList, and select “New”. This pulls in the existing SmartList so that you can edit it.SmartList interface, with the New button visible in the top-left corner
  4. Enter the name of your new SmartList, then select your new view in the “Database View” column.
    Entering the new list name in SmartList Designer
  5. Confirm the Relationship. In this example, you can link the VendorID fields between the tables.  By choosing Left Join starting with the existing SmartList table, the SmartList will include all vendors.  By switching the relationship to an Inner Join, the list will only show vendors who have EFT information entered.  In our example, we will use the Left Join option.
    Screenshot of the SmartList Designer interface, showing the relationship with Left Join selected
  6. Click OK, and now you will see the results of your new SmartList. From here, you can modify it by adding or removing columns, or changing your search parameters.
    The results of the new SmartList

If you have any questions about reporting in Dynamics GP, please reach out to us.

Dynamics GP Coffee Break | Setting up ACH and EFT in Dynamics GP

This webinar will go through the basics of setting up ACH / EFT for accounts payable and accounts receivable. We'll walk you through the settings and the default setups.

May 21
9:00 am – 9:25 am

Register here

Webinar - Setting up ACH and EFT in GP

May. 21
9:00 am – 9:25 am