Spice Up Your EFTs: Custom Data Fields in Dynamics GP

Table of Contents

Spice Up Your EFTs: Custom Data Fields in Dynamics GP - A Casual Guide¶

Hey everyone! Dealing with Electronic Funds Transfers (EFTs) in Microsoft Dynamics GP can sometimes feel like navigating a maze. Especially when you need custom data fields that aren’t readily available. This guide breaks down those tricky custom fields, offering clear explanations and workarounds to make your EFT processing smoother.

image just illustration

Understanding the Need for Custom EFT Fields¶

Many banks now offer services like check printing and CEPS programs that require a flat payment file. While Dynamics GP’s EFT functionality handles standard NACHA formats, creating custom flat files often requires a bit more finesse. This usually means setting up custom formats and sometimes even delving into Dexterity customizations.

Navigating the Customization Landscape¶

Before we dive in, it’s worth mentioning that custom formats aren’t officially supported in Dynamics GP. Microsoft’s support policy for EFT configurator files is generally one field or error message per support incident. If the field doesn’t work where you need it, you’re looking at a Dexterity customization.

Decoding the Custom Fields¶

Let’s get to the heart of the matter – those custom fields and how to wrangle them:

Unique File Identifier / EFT Document ID¶

This handy feature generates a unique number for each EFT file. If you map this field across different line types, the same number will appear on each line, incrementing with every new EFT file. Here’s how to map it:

• EFT Next File Number: Maps to Data Field
• Table: Checkbook Electronic Funds Transfer Master
• Field: EFT Next File Number

Important Note: The bank prenote file will show zeroes in this field.

Batch ID in Batch Header/Batch Control Lines¶

Dynamics GP groups all payments into a single batch within the EFT file. This is usually fine – most banks aren’t concerned about the number of individual batch IDs from Dynamics GP. You’ll see one Batch Header and one Batch Control line per file. This is by design and generally accepted by banks.

Sequence Number¶

This field is designed specifically for the Addenda Line type. If it doesn’t work elsewhere, you’ll need a customization.

Invoice Numbers and Amounts¶

These should be mapped in the Addenda Line:

• Invoice Number: Maps to Data Field
• Table: PM Apply To History File

• Field: Apply To Document Number

• Invoice Amount: Maps to Data Field

• Table: PM Apply To History File
• Field: Applied Amount (If that doesn’t work, try Apply From Apply Amount and double-check the amount.)

Keep in Mind: You’re limited to fields within the PM Apply to History table (PM30300), so details like invoice descriptions or PO numbers aren’t accessible. The EFT file focuses on payment info, not invoice details beyond what’s in the apply record.

Count and Sum Fields in Header Lines¶

Generally, ‘count’ or ‘sum’ fields function only in footer lines (Batch Control and File Control) after the lines have been listed. They typically won’t work in Header or Detail lines. Test to confirm.

Number of Addenda Records on Detail Line¶

The Addenda Count works only in the Addenda line type, not in Detail, Batch Control, or File Control lines. The system can only count Addenda lines after they’re listed.

Workarounds:

• Detail + Addenda: Check if your bank accepts this option, available in Batch Control and File Control lines.
• One Check Per Invoice: Generating the EFT file this way (instead of “one check per vendor”) results in one addenda line per Detail line. You can then hard-code ‘1’ in the Detail Line.

Line Count¶

This field works as intended only in the File Control line, counting all lines in the file. In other line types, it will only count preceding lines.

Other Date Formats (MM-DD-YYYY or MM/DD/YYYY)¶

While the configurator dropdown offers only formats like MMDDYYYY or DDMMYYYY, you can achieve different formats by splitting the date field into five smaller fields:

1. Field 1: Length 2, Left Justify, MMDDYY (grabs MM)
2. Field 2: Length 1, CONSTANT, “-” or “/”
3. Field 3: Length 2, Left Justify, DDMMYY (grabs DD)
4. Field 4: Length 1, CONSTANT, “-” or “/”
5. Field 5: Length 4, Left Justify, YYYYMMDD (grabs YYYY) Or Length 2, YYMMDD for a two-digit year.

Final Thoughts and Next Steps¶

Setting up custom EFT file formats is complex. While official advisory services for this are no longer available, you can leverage the default formats in Dynamics GP as a guide. They often include the standard fields you might need. You can open a support case for help with one field at a time.

We hope this casual guide demystifies those tricky custom EFT fields. Share your thoughts, questions, and experiences in the comments below! We’d love to hear what you’re working on and how you’re tackling these EFT challenges. Come back and visit again if you need other information!