How to Export Data from Season (SQL Database)

1 year ago
DatosSupport
2 minutes

A follow on from how to create a season query, another way that you can export data from season it to use a SQL query or create a connection on excel that will export out key tables. This will only be compatible with your system if your database is running in SQL.

Connection on Excel:

From Excel you got to Data > Get Data > From Database> From SQL Server Database

It will then prompt you to enter the server name you can get this from your IT team or someone from DATOS

The next page will be the navigator, this will allow you to select the tables that you are looking to read data from, there is a list of the table names and what data they store from in the link below:

  • Custmast(300) – Main Customer information table
  • Ordrhead(45) – Sales Orders Header table
  • Ordrtrxs(46) – Sales Orders detail table
  • Salefile(310) – Posted Sales invoices header table
  • Saletrxs(315) – Posted Sales invoices detail table
  • Cashhead(355) – Receipts header table (Customer and nominal receipts)
  • Cashtrxs(360) – Receipts detail table (Customer and nominal receipts)
  • Pricbook(90) – Pricebook table
  • Creditor (400) – Main Supplier information table
  • Stocporh(145) – Purchase order header table
  • Stocpord(27) – Purchase order detail table
  • Purchead(410) – Purchase invoice header table
  • Purctrxs(415) – Purchase invoice detail table
  • Paymhead(455) – Payments header table (supplier and nominal payments)
  • Paymtrxs(460) – Payments detail table (Supplier and nominal payments)
  • Custpart(205) – Main Product information table

Now that all your data is exported onto the spreadsheet it will be everything in that table so you can either filter your data down or add an SQL statement to limit what you want to see i.e., orders put on between a certain date range, how many orders are coming from what route etc. one setting that is important to set up is the refresh properties you will want to enable background refresh, fresh every 1 minute and also refresh upon opening the spread sheet if you are saving reports for future use. screenshots below show how this can be done