Implementing custom bank statement formats requires careful attention to Electronic Reporting mapping, proper format activation, and correct import job configuration. Let me provide a comprehensive solution addressing all three areas.
First, let’s properly configure the Electronic Reporting mapping for your custom bank format. The key is understanding the ER architecture for bank imports: your custom file format must map to the BankStatementDataModel, which then maps to D365’s bank transaction structure.
In Organization administration > Electronic reporting > Configurations, you should have three ER components:
- BankStatementDataModel (Microsoft provided)
- Your custom format configuration (e.g., CustomBankFormat_v1)
- Model mapping that connects your format to the data model
The error you’re seeing indicates the model mapping is incomplete or incorrect. Open your custom format configuration in the ER designer. For each field in your bank’s CSV file, you need to create a mapping to the corresponding data model element:
<FormatMapping>
<SourceField name="TxnAmt" type="string"/>
<TargetField>TransactionAmount</TargetField>
<Transformation>NUMBERVALUE($TxnAmt)</Transformation>
</FormatMapping>
The transformation functions are crucial. Your bank’s “TxnAmt” field needs conversion from string to decimal. Use ER formula functions like NUMBERVALUE for amounts, DATEVALUE for dates, and TEXT for string fields. If your bank uses different decimal separators (comma vs period), add format specifications: NUMBERVALUE(TxnAmt, “.”, “,”) to handle European number formats.
For date fields, if the bank provides dates in DD/MM/YYYY format but D365 expects ISO format, add a transformation: DATEVALUE(TxnDate, “DD/MM/YYYY”). These transformations happen during import and ensure data compatibility.
Create calculated fields in the ER format for any data that requires manipulation. For example, if your bank provides debit and credit in separate columns but D365 needs a signed amount, create a calculated field: IF(TxnType=“DR”, -1*NUMBERVALUE(TxnAmt), NUMBERVALUE(TxnAmt)).
Second, ensure proper custom file format activation across all required areas. This is a multi-step process that must be completed in sequence:
-
Complete the ER configuration: In the ER configurations tree, right-click your custom format and select “Change status” > “Complete”. Draft configurations cannot be used by the import engine.
-
Activate in import formats: Go to Cash and bank management > Setup > Advanced bank reconciliation setup > Import statement formats. Click “New” and select your custom format from the list. Set it as active and assign it a priority number if you have multiple formats.
-
Link to bank account: In Cash and bank management > Bank accounts, open each bank account that will use this format. On the “Reconciliation” FastTab, set “Import format” to your custom format name.
-
Verify in import job: When running the import (Cash and bank management > Bank statements > Import statement), ensure your custom format appears in the format dropdown. If it doesn’t appear, the activation steps weren’t completed correctly.
A common activation issue is version conflicts. If you’ve modified the format after initial activation, you must deactivate the old version, complete the new version, and reactivate. The system won’t automatically switch to newer versions.
Third, configure the import job correctly to process your custom format. The import job configuration involves several parameters that affect how files are parsed:
In the import statement dialog, select your bank account and custom format. Click “Setup” to access advanced import options:
- File encoding: Set to UTF-8 if your bank uses UTF-8, or Windows-1252 for ANSI files
- Skip header rows: If your CSV has column headers, set this to 1
- Delimiter: Specify comma, semicolon, or tab based on your file structure
- Text qualifier: Set to double-quote if text fields are quoted in the CSV
These parsing parameters must match your actual file structure or the import will fail before reaching the ER mapping stage.
Test your configuration with a small sample file first. Create a test file with 3-5 transactions and run the import. Check the import log in Cash and bank management > Bank statements > Import statement log. The log shows detailed parsing results and identifies which specific field mappings failed.
If you see “ER format element not found” errors in the log, it means your format mapping references data model fields that don’t exist or are spelled incorrectly. The BankStatementDataModel has specific field names like TransactionAmount, TransactionDate, StatementId, etc. Your format mappings must use these exact names.
One additional consideration: if your bank includes transaction details in multiple lines or uses complex structures, you may need to configure sequence elements in the ER format. For example, if each transaction has multiple line items, create a repeating sequence in the format that processes each line and aggregates the data.
After completing these configuration steps - proper ER mapping with transformations, full activation sequence, and correct import job parameters - your custom bank format should import successfully. Test thoroughly with various transaction types (debits, credits, fees, interest) to ensure all mapping scenarios work correctly before moving to production use.