We need to export our sales for each product to a spreadsheet for forecasting and order calculation.
I would like to replicate the 'Sales By Product Details Report' with the API.
The difficulty is that we have to get the sale list and then loop through for each sale and download the invoice lines, however I have not managed to extract the invoice lines from the JSON in excel.
Does anyone know a more direct way of accessing invoice lines or how to get the invoice lines from the Sale JSON?
Old question I know but still worth answering.
I find the best way to deal with this sort of issue is to drag the data into a database and do of the workings there.
I have a Dear to Zoho analytics feed deployed to a few clients, including consolidation of multiple Dear accounts of different base currencies. The best way to get advanced reporting is to use a tool designed for the job; zoho analytics is quite powerful and very good value.
@Ricky Hobson thanks for reviving this! When you say "drag" and "database" could you elaborate?
@Tim Richardson this sounds interesting can you please email us details? info at maltbyandgreek.com Cheers!
Hi Stef K,
What I mean is having a database such as an Access or SQL database and then using the API to pull the data from Dear into it.
From there you would have a datasource you could pull into something like Excel and pivot on it or get a developer to create a report for you.
Hope that helps.
Hi Ricky, thanks for clarifying. So in order to get the sales at the invoice line level would you suggest the following structure:
Pull Sale List into a table.
For each sale in Sale List pull the Sale Invoices.
For each Sale Invoice pull the Sale Lines
It's perfectly doable but I was hoping there was a more direct method like the 'Sales by Product Detail Report'.
Do you know of a good JSON parser for Access that I could use to get the data from the API and put it into Access tables?
Have a look at this page on my web site http://www.sussexitsolutions.com/Dearsystems-Dear-API-Integration.html. It gives an example of pulling data from Dear using Newtonsoft JSON.
I once pulled a lot of data from Dear into aExcel, and then uploaded it back to Dear (the entire product database) ... quite insane but it was what the client wanted. The spreadsheet was excel, but the code was python thanks to this project: https://www.xlwings.org/
To make it fast I did local sqlite caches and concurrent threads doing API calls. They are still using it three years later. Getting python to talk to access is even easier, since it is odbc.
python is a good language, there are many examples and libraries, but if you know .net you probably want to stick with it.
If you are interested in python examples I can put some code examples somewhere.