Field help (TBFFMT)
Select the format type:
Fixed
This format is used to exchange fixed-format files with the bank, such as bank statements and bills of exchange.
The data file generated is based on the formula entered in the Formula field. The position and length of data are set in this file.
Variable
This format is used to exchange XML files with the bank, such as SEPA and non SEPA transfers and direct debit.
The data file generated is based on the formula entered in the Formula field. The data displays in tags that are entered in the Start separator and End separator fields. Selecting this format displays the Mandatory and Condition columns required in the setup.
For bank files subject to the SEPA standard (Variable type and bank file group SEPA bank transfer (SCT) or SEPA direct debit (SDD), only the following characters are authorized:
a b c d e f g h i j k l m n o p q r s t u v w x y z
A B C D E F G H I J K L M O P Q R S T U V W X Y Z
0 1 2 3 4 5 6 7 8 9
- ? : ( ) . , " and / (only inside a zone)
Space character
A control on characters is performed when generating the file.
- If a prohibited character is included in a chain of characters, it is automatically replaced with a blank space.
- If a prohibited character is placed at the start or end of a chain, it is automatically deleted.
For example :
<Nm>/COMPANY FATHER&SONS/</Nm>
After a new processing in Sage X3:
<Nm>COMPANY FATHER SONS</Nm> (deletion of start and end '/' characters in the tag and '&' character replaced by a blank space).
The Length, Formula and Title columns are common to both format types.
CSV
This format type generates a comma-separated values file. When you select this format, you can enter a Field separator and a Field delimiter. When the output file is generated through the bank file generation process, these are automatically applied to the fields defined as records. You can also use Header and Footer and Detail line definition as needed.
You no longer need to manually define field separators as part of the Fixed length type definition.
In the Records section, enter the definitions of each field of the CSV file. Each line in the grid represents one field.
Populate the following columns of the grid:
- Field type: controls the data type of the field
- Formula: defines the content of the field (=data). Same options as for other formats (i.e. reference to a database field, functions, fixed texts) are supported.
- Description: optional
Additional notes:
As the last record, you must enter the end of line delimiter character as a binary field (see example). End of line characters are not added automatically.
If a field separator character is part of an alphanumeric field, like a BP name or remittance information, it is removed when the file is generated to ensure file structure integrity.
Only one header and/or one footer record can be defined. If the header or footer contains several lines, you can define the entire structure within the file.
For technical reasons it is mandatory to always create a header record for the bank file definition even if no header lines are needed. In this case you need to create a dummy record. For example:
At the header record, in the Condition field, you can enter [F:TFB]BAN = 'DUMMY' or simply 1=2 and at least a single-record line (i.e., as an empty text field). The latter condition always equals FALSE and suppresses generating the record.
CSV bank file definition example:
Field type
Formula
Description
Alphanumeric
"Batch description"
Alphanumeric
func NEDBANKLIB.BATCHDATE_AND_INT_SUM(datetime$)
Alphanumeric
[F:BAN]SENNUM
Binary
chr$(13)+chr$(10)
This definition creates the following CSV output, assuming a semicolon ; was entered as field separator:
Batch description;20201051200;234AB3222XYZ945;<CR><LF>
Line 1 = first field: fixed text "Batch description"
Line 2 = second field: using a function to generate the required date and specific format
Line 3 = third field: this field in the file is populated directly from a database field with the value 234AB3222XYZ945
Line 4 = end of line character as binary representation (here: carriage return+line feed)