Standard EFT/Check Export formats exist however, if these do not meet your bank's requirements, a format can be created and customized as necessary.
See the User Guide for more information on the process to Export EFT/Check files.
EFT/Check Export formats can be viewed by clicking the EFT/Check Exports link from the Main Menu. Edit a format to view specific details. The general information about the format (in the top section) will determine the structure of the file. Please note that activating Header and Control records will change the tabs available for the contents of the file format.
Interpreting file formats
Prior to defining the file format, obtain the requirements, either from your bank or they may be available by searching online (eg searching for "CPA 005 EFT"). The file format should include the expected output format (eg fixed length text or .csv) as well as a definition of the header/details/trailer records to include and the details of how to structure each record (in particular the order, length and expected contents).
A fairly standard file format will be a fixed length text format with Header, Details and Trailer records defined. For each record, there will be:
- The character position (normally from/to).
- Field size. This should correspond to the character position from/to.
- Format. Normally alphanumeric, numeric or the date format required.
- Data requirements to determine the contents of the field. Note: when customizing an export format, the data may either be fixed text (same for every single instance) or picked from a field.
Use the information within the file format to customize the EFT/Export format.
Create a New EFT/Check Export Format
When defining a new export format, either copy an existing format and make changes or create a new format.
This includes the instructions to create a new export format and add the rows in each section to define the:
Once added, each row can be edited in the grid if necessary.
Create Format
- Click the EFT/Check Exports link from the Main Menu.
- Click Add.
- Enter the following information:
- Description - Name of the export format.
- Header - Determines if a header is exported with the export file.
- Yes - Header information is configured in the Batch Header tab.
- No - No header information is included in the export file.
- Multiple - Header information is configured in both the File Header and the Batch Header tabs.
- Control - Determines if the export file uses a control value.
- Yes - Control information is configured in the Batch Control tab.
- No - No control value is included in the export file.
- Multiple - Control information is configured in both the Batch Control and File Control tabs.
- Fixed - Determines if the file is exported in a fixed length format.
-
- Yes - Overrides the configuration in the Delimited field. and exports a fixed file length. All rows (including headers and controls) are expected to export at the same length. Fixed lengths files do not have delimiters between fields.
- No - Relies on the value configured in the Delimited field to if text fields have quotes around them and the delimiter between fields.
- Quoted - Behaves the same as selecting No.
-
Delimited - Determines how each field is separated in the export file. This only affects the export file if the Fixed field is set to No.
- CSV - Creates a comma separated value export file. Each field is separated by a comma and text fields have quotes on either side.
- None - No delimiters and no quotes are inserted into the export file. Same as a fixed length file.
- Tab Delimited - Creates a tab delimited export file. Each field is separated by a tab and text fields have quotes on either side.
- File - Default location and file for the export.
- Export Quotes for CSV - This is not used for export formats. Set the Delimited field to CSV to export as a CSV file.
File Header
If Multiple is selected in the Header drop-down, define the format of the file header. If No is selected in the Header drop-down, skip to Batch Header.
-
- Click the File Header tab
- Click Add.
-
Enter the necessary information:
- Order - Determines the order the field displays in the export file. Regardless of the value entered, it is reset in increments of 10 when on save.
- Column Label - Column or field that contains the data to export, or:
- <Empty Field> - Select this to set a blank or zero filled field. End position is required for this column.
- <Specify Constant> - Select this to set specific for every record. Type the text in the Value field. Common use of this is a value of "H" for Header, or "D" for Detail as defined in the file format.
- Fill Factor - Fill to use for fixed length export files. If a value is less than the fixed length, this is what is used to fill the remaining required characters. This is also used to determine the format for date fields. If the Fixed field in step 3 is set to Yes, this field must have a value.
- Start Position - Determines the start position of a value if only part of a data's field needs exported. Use this if the result length is shorter than the actual length of the field.
- End Position - Determines the end position of a value if only part of a data's field needs exported. Use this if the result length is shorter than the actual length of the field.
- Show Decimal Point - If checked, the decimal point is included in the export file. Only available when exporting numeric fields.
- Number of Decimals - Number of place values after the decimal to include in the export value. Only available when exporting numeric fields.
- Click OK.
Batch Header
If Yes or Multiple is selected in the Header drop-down, define the format of the batch header. If No is selected in the Header drop-down skip to Details.
-
- Click the Batch Header tab.
- Click Add.
-
Enter the necessary information:
- Order - Determines the order the field displays in the export file. Regardless of the value entered, it is reset in increments of 10 when on save.
- Column Label - Column or field that contains the data to export.
- <Empty Field> - Select this to set a blank or zero filled field. End position is required for this column.
- <Specify Constant> - Select this to set specific for every record. Type the text in the Value field. Common use of this is a value of "H" for Header, or "D" for Detail as defined in the file format.
- Fill Factor - Fill to use for fixed length export files. If a value is less than the fixed length, this is what is used to fill the remaining required characters. This is also used to determine the format for date fields. If the Fixed field in step 3 is set to Yes, this field must have a value.
- Start Position - Determines the start position of a value if only part of a data's field needs exported. Use this if the result length is shorter than the actual length of the field.
- End Position - Determines the end position of a value if only part of a data's field needs exported. Use this if the result length is shorter than the actual length of the field.
- Show Decimal Point - If checked, the decimal point is included in the export file. Only available when exporting numeric fields.
- Number of Decimals - Number of place values after the decimal to include in the export value. Only available when exporting numeric fields.
- Click OK.
Details
Configure the fields to include in details section of the export. This is required for all EFT/Check Exports.
-
- Click the Details tab.
- Click Add.
-
Enter the necessary information:
- Order - Determines the order the field displays in the export file. Regardless of the value entered, it is reset in increments of 10 when on save.
- Column Label - Column or field that contains the data to export.
- <Empty Field> - Select this to set a blank or zero filled field. End position is required for this column.
- <Specify Constant> - Select this to set specific for every record. Type the text in the Value field. Common use of this is a value of "H" for Header, or "D" for Detail as defined in the file format.
- Fill Factor - Fill to use for fixed length export files. If a value is less than the fixed length, this is what is used to fill the remaining required characters. This is also used to determine the format for date fields. If the Fixed field in step 3 is set to Yes, this field must have a value.
- Start Position - Determines the start position of a value if only part of a data's field needs exported. Use this if the result length is shorter than the actual length of the field.
- End Position - Determines the end position of a value if only part of a data's field needs exported. Use this if the result length is shorter than the actual length of the field.
- Show Decimal Point - If checked, the decimal point is included in the export file. Only available when exporting numeric fields.
- Number of Decimals - Number of place values after the decimal to include in the export value. Only available when exporting numeric fields.
- Click OK.
Batch Control
If Yes or Multiple is selected in the Control drop-down, define the format of the batch control. If No is selected in the Control drop-down this will not be visible.
-
- Click the Batch Control tab.
- Click Add.
-
Enter the necessary information:
- Order - Determines the order the field displays in the export file. Regardless of the value entered, it is reset in increments of 10 when on save.
- Column Label - Column or field that contains the data to export.
- <Empty Field> - Select this to set a blank or zero filled field. End position is required for this column.
- <Specify Constant> - Select this to set specific for every record. Type the text in the Value field. Common use of this is a value of "H" for Header, or "D" for Detail as defined in the file format.
- Fill Factor - Fill to use for fixed length export files. If a value is less than the fixed length, this is what is used to fill the remaining required characters. This is also used to determine the format for date fields. If the Fixed field in step 3 is set to Yes, this field must have a value.
- Start Position - Determines the start position of a value if only part of a data's field needs exported. Use this if the result length is shorter than the actual length of the field.
- End Position - Determines the end position of a value if only part of a data's field needs exported. Use this if the result length is shorter than the actual length of the field.
- Show Decimal Point - If checked, the decimal point is included in the export file. Only available when exporting numeric fields.
- Number of Decimals - Number of place values after the decimal to include in the export value. Only available when exporting numeric fields.
- Click OK.
File Control
If Multiple is selected in the Control drop-down, define the format of the file control. If Yes or No is selected in the Control drop-down this will not be visible.
-
- Click the File Control tab.
- Click Add.
-
Enter the necessary information:
- Order - Determines the order the field displays in the export file. Regardless of the value entered, it is reset in increments of 10 when on save.
- Column Label - Column or field that contains the data to export.
- <Empty Field> - Select this to set a blank or zero filled field. End position is required for this column.
- <Specify Constant> - Select this to set specific for every record. Type the text in the Value field. Common use of this is a value of "H" for Header, or "D" for Detail as defined in the file format.
- Fill Factor - Fill to use for fixed length export files. If a value is less than the fixed length, this is what is used to fill the remaining required characters. This is also used to determine the format for date fields. If the Fixed field in step 3 is set to Yes, this field must have a value.
- Start Position - Determines the start position of a value if only part of a data's field needs exported. Use this if the result length is shorter than the actual length of the field.
- End Position - Determines the end position of a value if only part of a data's field needs exported. Use this if the result length is shorter than the actual length of the field.
- Show Decimal Point - If checked, the decimal point is included in the export file. Only available when exporting numeric fields.
- Number of Decimals - Number of place values after the decimal to include in the export value. Only available when exporting numeric fields.
- Click OK.
Copy an EFT/Check Export Format
If a minor change is to be made to a format that is already configured, you may wish to create a new format based on a copy of the existing format. This can be useful to make changes to a format that is already correct.
- Click the EFT/Check Exports link from the Main Menu.
- Select the export format to copy.
- Right-click and select Copy.
- Enter the name for the new export format into the New Format Description field.
- Click OK. The new export format is created. This can be altered as needed.
Troubleshooting
- Headers/Controls
- Be aware that all tabs are visible in the format but can only be altered and used according to the selections in the main format definition. "Yes" will use the Batch header or control and "Multiple" will use both the Batch and File header or control.
- Control location - Review the Accounts Payable Configuration, under the Payments tab to change the EFT Control Location to the Header or the Footer of the export file.
- Problems saving a format set to Fixed = Yes? Check each tab of the format to ensure the sum of the Result Length column is the same for each tab. Each part of the export format must be the same record length in total.
- Your bank requires a "Fixed length CSV" file. Normally these formats are configured as either fixed length or CSV. Combining these has been done by inserting text fields containing commas between each field but this can be tricky and you may require some assistance.
- Order - Please note that the Order is automatic and will always display in increments of 10. To move a line, change the number in the Order field. For example, to move row 90 to in between 20 and 30, change "90" to "25" (or 21, 22, 23 or any number between 20 and 30). On save this will move the row and recalculate the order field values to increments of 10 again.
EFT/Check Export Security
There are several access privileges that determines if a user has access to process a payment, assign check numbers and export a batch. You can find these access privileges on the Access Privileges tab on the Accounts Payable Configuration screen. Below are the access privileges that may affect your EFT/check export process.
Supplier Payments
Making supplier payments is controlled by the Payment Selection access privilege. Roles/users assigned to the privilege are allowed to select vouchers for payment and enter manual payments, wire transfers and drafts. Roles/users not assigned to the privilege are not able to select vouchers for payment. If no roles/users are assigned to the privilege, it is considered inactive and all users can select vouchers for payment.
Assignment of Checks/References
Assigning check and reference numbers to payments is controlled by the Assignment of Checks/References access privilege. Roles/users assigned to the privilege are allowed to assign check and reference numbers to payments. Roles/users not assigned to the privilege are not able to assign check and reference numbers to payments. If no roles/users are assigned to the privilege, it is considered inactive and all users can assign check or reference numbers to payments.
Allow Access To Export Batch
This access privilege controls the exporting of batches. Roles/users assigned to the privilege are able to export batches. Roles/users not assigned to the privilege are unable to export batches. If no roles/users are assigned to this option, it is considered inactive and all users can export batches.
Comments
0 comments
Please sign in to leave a comment.