Import invoices to Xero (summary report for export)

Continuing the discussion from Send my Orders to Invoices in my Accounting Package:

Create new reports for export of order info so can be imported into accounting packages

  • to start with will make exactly compatible with Xero because that’s what we’re getting the most requests for (other users should be able to manipulate relatively easily into the import format for whichever accounting package they’re using)
  • Steps:
  • Create new top level Report heading / category for Invoice Export
  • Create new reports for Xero import
  • NB. these reports probably need to check for when Enterprise (Shop/Hub) is GST registered or not, and adjust calcs and rows accordingly

Invoice Exporter

Aiming to get the exported csv to exactly match the Xero importer (so no manual fiddling is required).

There are some *required fields in the Xero Importer that are not currently available in OFN, so ideally these parameters could be set and sent into the report.

These are outlined first for the simpler Invoice Summary report, and then some additional requirements described in Invoice Detail report.

Xero Invoice Importing.xlsx (37.0 KB)

Invoice Summary

This report is designed to create an order ‘summary’ entry in your accounting system for each order, including the correct amount for (total) produce costs, fees, shipping costs and sales tax.

It does NOT break out each line item in the order. See sheet one of Xero Invoice Importing

The following parameters need to be sent to the report, or calculated

  1. *InvoiceNumber options to populate with order number OR start from “X”
  2. *InvoiceDate default to today’s date, but ability to override e.g. with order date
  3. *DueDate default to 7 / 14 days or custom?
  4. *AccountCode - these are required for import. We might start with just doing a really basic set one account code for now, and then user can modify line items once in xero if they want, so one parameter to set *AccountCode e.g. food sales, online orders - whatever you have set in your system
  5. *TaxType
  • for now we’re just going to do Australia which only has to deal with one tax variable in this report (GST or no GST)
  • for Aus, I think the only two we need for this report are

  • later we may need some way to map these from ofn to accounting package, as they differ in different countries. The full list of tax codes are here - http://developer.xero.com/documentation/api/types/. you might have to copy and adapt the report if you want to get it working overseas.

I’m having just so much fun testing tax. And trying to think through invoice importing into xero and accounting packages at the same time (based on Customer totals reports and the sales tax report). and guess what, it’s almost impossible to get things importing correctly the way we’ve set this up. yay.

the problem is that the importers need to know:

  • the tax code for each line item, and
  • whether taxable line items include tax or not

So, having a line item that may or may not include some components of tax (e.g. a product that may be taxable or not, combined with fees that may be taxable or not) basically makes it impossible to track your sales tax correctly.

Is it correct to assume that the sales tax report that’s been built was designed for and works correctly for stroudco based hubs? and that we should not change this one because it’s working for you guys? @NickWeir @lin_d_hop @Aidan

AND/OR you are currently manually reentering stuff into accounting packages and would prefer to import? If so, please see if you think this would work for you

I think I have worked out how to do a couple of reports that will mean users can export/import accounting info that will handle tax correctly. We’d need two:

  • One for summary invoices (e.g. just for accounting purposes, not to be used for packing sheets etc)

  • a more detailed one with actual line items (but not with fees included) - so prices displaying differently than on the shopfront (potential customer confusion)

Unneeded line items would not appear so that user doesn’t have to remove them from the draft invoices created

Does this make sense to people? any thoughts? If it makes sense, I’ll update spec above and look for someone to build it

NB. There is no need at this point to use these invoices for Customers (unless you want to) so don’t worry too much about the confusion.

Invoice importing report template - general.xlsx (46.7 KB) in case anyone wants to play / adapt it

@lisahill this will be relevant to you guys as well - not sure if you want to weigh in now or later

This looks good for Stroudco. Yes our current system works OK but it does require us to do manual importing to QuickBooks. I like what you are proposing

@openfoodnetwork yup, you’ve officially made my eyes glaze over, this is out of my league… i’ll show this discussion to our finance team and see if they can provide some info/feedback.

these changes now incorporated into the new spec above

Hei everyone,
We are investigating in Norway about accounting/invoicing/admin tools, I just wrote a post on a more general reflexion, but seems connected to that post: Beyond accounting... other connections needed
I just saw that Zapier can connect Spree and Xero by the way… I’m not really technical so don’t know if that’s useful or not…
Would love to have your feedback :slight_smile:

zapier is a really easy to use tool, so if there is already an integration between spree and xero, that would be awesome. But I just went to check and i don’t think it does (see below) - are you seeing something different from me?

Hum… I just saw that both were on the list of the 181 integrations…: https://www.marketingtechblog.com/zapier-workflows/ but it seems that Spree is not on the Zapbook… I just emailed them :slight_smile:

@openfoodnetwork I just got an answer from Zapier (contact@zapier.com):
"Sorry about the confusion! I’m afraid we don’t have a native integration for Spree, I’m not sure why that link included it. If Spree supports sending or receiving data via a URL (otherwise known as a webhook), then you could potentially get something working that way today, but that’d be the only method right now: https://zapier.com/zapbook/webhook/xero

It does look like Spree has an API though, so an integration might be possible in the future. There’s a couple ways new apps get added to Zapier, this link has more info on the process and how you can help: https://zapier.com/help/getting-new-service-zapier"

Maybe that can be useful?

@openfoodnetwork checking in to see what the progress is in terms of a xero report? We’ve made the transition across to xero and are super keen to be pulling data direct from OFN into Xero as soon as we launch… any chance?

@Kirsten this is the other thing on my urgent list… let’s add Xero to our chat tomorrow.

This is done for the basic ‘summary’ and can be accessed via reports

Detailed line item and fee breakdown versions coming up . .

@sstead I have created this new category ‘to docs’, which is in OFN partners (which means only visible to kind of core people) . . basically the intent is to park things in here that have come through development and are ‘done’, to keep track of what needs to be updated in either the user guide or super-admin how-to about this new functionality.

Once user guides are updated, we can ‘close’ the page and put it back into done. To discuss if I’m making no sense?

THANKS :smile:

Good idea!
In terms of documenting the Xero report… this is all I’ve put in the user manual…

“Xero Invoices
This report creates CSV files which can be imported into the accounting package ‘Xero’ to generate invoices for customers”

If you want me to put any screenshots in, showing how it works at the Xero side, send them over with a description. Otherwise I suppose we can assume that anyone using Zero will know what to do with the report.