This custom Export format will allow you to export information from the TIMESHEET ENTRY module into Xero in order to produce Invoices to your clients.
It will produce an excel file containing details of the client name, the number of "Units" and a price. When imported into Xero it will create a sales invoice.
| NOTE: See attached files at the bottom of the article. These can be dragged/dropped into the system to load these reports. |
You can find out more about the Invoice import process for Xero by searching on line (Google). These articles may help.
|
(NB: For support with XERO, please contact XERO)
|
https://central.xero.com/s/article/Import-customer-invoices-UK
and https://www.youtube.com/watch?v=-LUx_HHmOys
Contents (Click an Item to Jump to that section)
Xero Invoice Export - Plus Address
Xero Invoice Export - 1 Line per Day
Xero Invoice format export
The definition for Xero Invoice Format definition can be found in
Maintenance > Temp Centre > Setup > Timesheet Export Layouts
If the format is not in the system, then it can be imported by simply dragging/dropping a copy of the format onto this form within Influence. (See files attached to this article.)
How to Use the Export Format
It is possible to export entered timesheets in a format suitable for import into XERO. This can be done from within the [Timesheet Entry] option of Influence
i) Go into timesheet Entry and filter the list to show Entered Timesheets
ii) Click the [Export] button at the bottom left.
iii) When prompted use the drop-down to choose an export format suitable for XERO
iv) If it is the final version you wish to import into Xero then tick that it is a Payment Run or Invoice Run. If you want the system to produce invoice numbers then tick Autonumber Invoices and set the 1st Inv No.
| Optional: You may want to set a Prefix/Suffix for your Invoice numbers, e.g. INV-12345INF |
v) Set the export path to a suitable folder on your LOCAL PC.
| (Tip: You may wish to create a suitable folder on your own PC such as C:\Temp\Xero Files and then set this as the export path.) |
| NB: The default Export Path can be changed. The default is set by parameterPayroll,Paths(2) e.g. \\iflapp\Influence_Docs\Timesheets\TS-20190706.csv could be changed to C:\Temp\TS-20190706.csv |
Having run the export the system will automatically open the export folder so you can open or see the file which has been produced.
| Note: There are two output formats which can be produced. The BASIC format contains enough information to allow the creation of invoices but does not contain company address information relating to the invoice address. |
Example Output
Below is an example of the output file produced.
The conforms with the file specification for import into XERO and will allow you to import the information and create Draft Invoices within XERO.
The basic file layout contains:
| ContactName |
The name of the customer. This needs to be exactly as it appears in Xero. |
| InvoiceNumber |
An invoice number, generated by influence. The 'start' number can be entered when exporting the file, e.g. 286, and will then run sequentially (287, 288,289...etc) or can be generated automatically by the system. ## |
| Reference |
A reference for this invoice (This is Optional and is not required by Xero) |
| Invoice Date |
The invoice date in the format dd/mm/yyyy. |
| Due Date |
The due date in the format dd/mm/yyyy. |
| Description |
A description of the items on this invoice. It will contain the worker name, Pay Rate & Date of booking. |
| Quantity |
The quantity of items sold (typically "Hours" worked or "1" in the case of days). |
| UnitAmount |
The price of the items sold (typically hourly rate/daily rate). |
| AccountCode |
The Nominal ledger code in Xero to assign the item to. (This is often set to 200 in new Xero installations. You will need to find out what this is in your Xero system and it can then be adjusted to match. Check your XERO documentation but it is usually available from Accounts > Advanced > Chart of Accounts.) |
| TaxType |
The tax rate display name as it appears in Xero. |
The above is the minimum amount of information that must be supplied as part of the *.CSV import file for Xero.#
| [#] - Parameter PayrollB, Integer (6) controls the length/format of the invoice number. If set to 0 then the system will strip any leading zero's from the number. e.g. INV-68 as opposed to INV-00000068 |
| NOTE: If the parameter is changed, this will only affect new exports. Existing records will already have had the invoice number stored against the timesheet, so changing the parameter will not affect those. e.g. PayrollB, Integer6 = 8 would give an invoice No. such as 00015789 PayrollB, Integer(6) = 0 would give an invoice no as 15789 |
Optional
As well as the BASIC export format there is a slightly longer format which includes the Invoice address. the process for using this is the same as the basic format, but the output (*.CSV file) will include additional columns relating to the Invoice address
To find out more about importing Sales Invoices into Xero please see your XERO documentation or search online for "Sales Import into Xero"
A useful link is:
https://central.xero.com/s/article/Import-customer-invoices-UK
This video is also useful
https://www.youtube.com/watch?v=-LUx_HHmOys
Limitations and Clarifications
- If you are integrating with an existing Xero implementation then the names of the companies in Influence must exactly match the names of the existing companies in Xero. e.g. If the client is called MEGA Products LTD in Xero then it must be called exactly the same in Influence
Mega Products Limited = Wrong X
Mega Products Ltd = Wrong X
MEGA Products LTD = Correct
|
NB: As an option it is possible to set the 'Xero' name on the "Invoice Address" page of the company record if you do not wish to change the client name in Influence - but if you do so then EVERY client in Influence which you post to Xero needs to have the name set in this way; you cannot mix-n-match. |
- As part of the Xero import it is possible to specify if the addresses in Xero are imported and overwritten or not. TAKE CARE - Do not over-write the addresses if you have only exported the 'basic' file which does not include company addresses.
- When using the export format which includes the invoice address, then the invoice address is taken from the Invoice address page of the branch record, so this should not be left blank for any active firms where bookings are made.
(See below)
The Reports
Attached are several reports which can be downloaded/used. (Though they may require minor 'tweaking' to work with your system such as specifying the Account Code used by your Xero system for 'Sales')
Xero_Invoice_Export_v2.xml
This report will produce a file which can be used to general an invoice for each site with with 1-line per worker/Rate.
e.g.
| Description |
Qty |
Unit Price |
Total |
| John Smith, Warehouse (basic Rate) |
37.5 |
10.00 |
375.00 |
| Mark Jones, Warehouse (Basic Rate) |
25.0 |
10.00 |
250.00 |
| Steve Hall, Warehouse (Basic Rate) |
39.0 |
10.00 |
390.00 |
| Steve Hall, Warehouse (Overtime) |
5.0 |
12.00 |
60.00 |
Xero_Invoice_Export_(Plus_inv_Address)_v2.xml
Same as above, but the report export also include the INVOICE ADDRESS for clients. If the client does not exist in XERO, then the client will be created AND the Invoice address completed as well.
NOTE: If using this format the export file will overwrite/update any existing invoice address details in Xero every time the file is imported. (See Clarifications above)
Xero_Invoice_Export_V2_(1_Line_Per_Day).xml
This report will produce a file which can be used to general an invoice for each site with with 1-line per worker/day/Rate. So if a worker has worked at a site for 3-Days, they will have 3 individual rows on the Invoice.
e.g.
| Description |
Qty |
Unit Price |
Total |
| Andy Burns, Packer, Mon 22-07-2019 (Basic Rate) |
7.5 |
10.00 |
75.00 |
| Andy Burns, Packer, Tue 23-07-2019 (Basic Rate) |
8 |
10.00 |
80.00 |
| Andy Burns, Packer, Thu 25-07-2019 (Basic Rate) |
7.5 |
10.00 |
75.00 |
| Carl Dennis, Admin, Mon 22-07-2019 (Basic Rate) |
7.5 |
11.50 |
86.25 |
| Carl Dennis, Admin, Mon 22-07-2019 (Overtime) |
2 |
13.25 |
26.50 |
Note
These reports are supplied "As is" and should work with most XERO implementations. However, if you want to have these amended or adjusted or if large numbers of changes are required then Influence would charge for the time to amend the report to create a 'bespoke' version to your specification. These amendments would be charged at our standard hourly rate for consultancy.
Graeme Orchard
Comments