The File Layout in General Ledger determines the fields that are included in a GL file, based on the requirements of your finance system. You should begin by checking with your finance team or accounting software provider the file specifications required for the general ledger file.
The General Ledger File Layout allows you to create a new File Layout or edit an existing one. If you need to remove the Pad Char value from a field, move the field back to the Source List and then add it to the Destination List again. Ensure it is in the correct position.
The File Details tab holds information such as the default file name, file extension and file location. You can choose from one of several File Layout templates or design a custom layout.
File Layout Templates
The General Ledger Integration includes a generic file layout template, as well as templates for third-party accounting software, e.g. QuickBooks®. When you load a File Layout template, the details of the batch file are automatically loaded and defined. The fields on the File Details tab are not populated by the template and must be completed manually. After you have loaded a template, you can edit the Layout by adding or removing rows and fields. Ensure that you save the edited file layout with a new code and description.
QuickBooks is a trademark and service mark of Intuit Inc., registered in the United States and other countries.
If the General Ledger templates are not suitable, you can create a custom File Layout by selecting the rows and fields to be included.
Creating a File Layout
If you are using a template and don't need to customise it, once you have completed step five, skip to step ten.
Go to General Ledger Integration, Maintenance, Batches, File Layout.
Select Add.
Select a template from Load from Template. To create your own file layout, skip this step. When the template is loaded, the rows and fields are automatically populated.
Enter a File Layout Code. This should be unique.
Enter a File Layout Description
Use the New Header Row, New Detail Row and New Footer Row buttons to add rows to the layout.
To remove a row, select the tab and click Delete Selected Row. The File Detail tab cannot be deleted.
To add fields to a row, click on the corresponding tab, e.g. Detail 1, then use the buttons to move fields from the Source List to the Destination List.
Use the single green right arrow to add selected fields to the Destination list or use the double green right arrow to add all fields in the Source List to the Destination List.
Use the single green left arrow to remove selected fields from the Destination List or use the double green left arrow to remove all fields from the Destination List.
Use the blue up and down arrows to change the field order in the Destination List.
Set the parameters for the field. Some of the parameters are not editable, e.g. Name, Data Type, and Source.
Length sets the number of characters.
Field Format / Rule is where you enter date formats, etc. For User_Defined_Option, you can include Substring and Join syntax.
Pad Char allows you to fill the field with the specified character. If you need to remove the Pad Char value, move the field back to the Source List and then add it to the Destination List again. Ensure it is in the correct position.
Left Align, when ticked, starts the field from the left. Unticked starts from the right.
Decimal Places defines the number of decimal places in the output.
Group and Summarise allows you to group the output by the selected field, e.g. Ledger_Account and then summarise (total) the values, e.g. Credit.
Repeat Steps 8 and 9 for each row, as required.
Click on the File Details tab.
Enter the Name of Output File. This is the name of the file when generated.
Enter the Extension of Output File. The extension determines the file type, e.g. TXT, CSV, XLS.
Enter the Path of Output File. This is the location the batch file is saved to, and should contain the full file path, e.g. drive letter and directory path. You can also use the Browse button at the end of the field to navigate to the location.
Select an option in the Action if file exists. The value selected here determines what happens when a new batch file is generated, but a previous copy exists. You can choose from:
Choose action
Overwrite file
Append to file
Append date & time to file name
Append count to file name
Always Append Date & Time to file name
Always append count to file name.
Select the File Format. This is the format of the file, e.g. Comma Separated, Fixed Width. Depending on the selection, you may also need to select:
Field Width
Field Delimiter - characters such as quotation marks are placed around text values in the output file and enable the finance package to differentiate between text and other information in the uploaded file.
Field Separator - defines where each field starts and ends, e.g. comma.
End of Line Character - allows software to recognise where one record in the file ends and the next one begins.
Click OK to save the File Layout.