There are two sections in this article:
Setting up a Sales Invoice (Table Based) template
Using a Sales Invoice (Table Based) template
To set up a Sales Invoice (Table Based) template:
Open Excel, select the Access tab.
Click Templates, Setup Update Template. The Choose your data connection page appears.
Note: With AOI v5.14.9 or earlier just click Setup Update Template.Check the Data Connection field shows the required database, if not, use the drop-down to select the required database. Click Next.
Expand Sales Ledger and select Sales Invoices (Table Based). Click Next.
Drag Customer Code from Available Fields into Column Fields.
Select the Customer Code and click Set Range. A box will appear Choose Column range for Customer Code.
In Excel, select a cell and then drag down to create a small column, e.g. cells B3 to B10.
In the Choose Column range for Customer Code window, click the red down arrow icon. The Cell Range appears on the template and the remaining available fields become active.
Drag the other fields required over to column fields, for example, Trans Reference, Date, Do Not Batch, Net Value, Description, VAT Amount , etc. into the column fields box in the order required.
Click Next. The Choose your template settings page appears.
Enable changes to be written back to Access Accounts is ticked by default and cannot be changed. The colours can be left as default, or changed to personal preference. The Add column totals and Add row totals do not work with this template, so leave these un-ticked.
If you want to save the template for next time use, tick Save Template and add the name required. This name will then appear in the Existing Templates drop-down on the first page of the setup wizard.
Click Insert. The template is simply column headers, and now ready for data to be populated.
To use a Sales Invoice (Table Based) template:
Populate each row below the headers with the data required. The text will be in blue, which means it can be written back (imported) into Financials. Notes about filling in certain column fields are given below.
To write the data back to Financials, either:
In the Access tab ribbon, within the Excel Templates section, change the drop-down that shows Selected Cells to read Complete Template. Alternatively, you can select the specific rows to be written back.
To review the update before importing, in the Access tab within the Excel Templates section, click Review, the icon and wording will be highlighted in green.
Note: If Refresh is clicked, the template will be emptied of all values, both updated and not updated.Click Update. If Review has been selected, a Review before update window appears. Check that the upload is correct and click Update. An Updating template data... window appears showing the progress of the update. Text in rows will go green if successful, or red with a comment added about why the update failed.
If successful, there will be a new entry in the Sales Ledger for the customer code account within Financials.
Notes about filling in certain columns:
Customer Code needs to have an existing valid customer code, otherwise an error message will be produced.
Trans Reference (the invoice number), must be populated with a value, or an error message will be produced.
Date is not essential, and will use the current date if empty.
Do Not Batch, the value must be set to either 0 (which will send to the batch) or 1 (which will post it to the live account, skipping the batch).
Due Date is not essential, and will use the current date if empty.
Net Value, at least one cell must have a value, even if other rows have no net value.
Note: Gross Value will be worked out by Financials and is therefore not a field.Sub Ledger is only needed if using Financials with Chart of Accounts.