Hi ND,
Thanks for sharing your scenario. Based on your setup and the document you provided, it looks like you're trying to export a payment file from the Payment Journal in Business Central using a custom HSBC BACS format, and you're facing two main issues:
- The header row is missing in the exported CSV file
- Some required fields (like Batch Name, Sort Code, Account No.) are not appearing in the output
Here’s how you can address both:
1. Exporting the Header Row
Business Central doesn’t include headers by default in CSV exports. However, since you’ve already defined them in the Data Exchange Definition, make sure:
- The first row is configured as a header in the column mappings.
- The “Include Header” option is set to Yes in the column definitions.
This should ensure that the header row appears correctly in the exported file.
2. Handling Missing Fields
Fields like Batch Name, 1RG Bank Sort Code, and 1RG Bank Account No. are not part of table 1226 by default. You have two options:
Option A: Extend Table 1226
- Create a table extension for
Gen. Journal Line or the table used in your export.
- Add the missing fields and populate them either manually or via AL code during journal entry.
Option B: Use Related Tables
- If the data exists in related tables (e.g., Bank Account, Vendor Bank Account), you can retrieve it using a custom codeunit or field mapping logic in the Data Exchange Definition.
If you plan to implement international payments later, you might also consider including the SWIFT Code now to future-proof your setup.
3. Optional: Custom Codeunit
If standard mapping doesn’t allow access to all required fields, a custom codeunit can:
- Extend the export logic
- Populate missing fields
- Format the CSV output as needed
Expected Output Format
Here’s an example of what your CSV file should look like:
Transaction Type,Remitter Batch Reference,Customer Reference,DebtorAgent ClearingMmbrId,Debit Account Number,Beneficiary Name,CreditorAgent ClearingMmbrId,Beneficiary Account Number,Transaction Amount,Payment Currency,Requested Processing Date
NURG,BATCH001,INV123,123456,12345678,Vendor A,654321,87654321,1000.00,GBP,25/07/2025
Best regards,
Daniele
