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
CM00101
Checkbook Electronic Funds Transfer Maintenance
CM00102
Checkbook EFT Optional Header
CM00103
Checkbook EFT Format Header
CM00104
Checkbook EFT Format Detail
CM20202

Checkbook Transaction Electronic Funds

EFTTYPE
1 – purchasing
2 – sales

CM20203

Checkbook EFT Transaction Batch

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

CM90001
Checkbook EFT
SY06000

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

Register