September 2016 - SQLWorks

Importing your data to SQLWorks

importing –

If you’re new to SQLWorks, importing your existing data to SQLWorks can seem daunting. Fear not! We’ve prepared this handy guide to make this process easier.

Decisions about your data are yours – but at any stage, you can ask the SQLWorks Team for help.

 

About Your Data

Data imported into SQLWorks is categorised in two types: Static and Transactional.

Static data is fixed lists of ‘things’ – including companies, contacts, address, your stock list, warehouses and more. Transactional data includes list of transactions, stock movements and financial ledger entries like orders, invoices, credit notes and more. Static data must be imported first, followed by transactional data.

importing

 

Finding Your Data

Both your static and transactional data comes from whichever system(s) you use currently – this could mean importing from a number of sources, including:

  • An old software program (e.g.: Sage)
  • A patchwork of spreadsheets (e.g.: Microsoft Excel)
  • A legacy database program or file (odbc compatible)
  • Nowhere (because you’re a new or paper-based company)
  • Some combination of the above

It’s up to you what data you place in SQLWorks, however whilst some data is almost always needed SQLWorks (even if entered new), other data is optional. As a rule, names, codes, accounting and VAT entries will need to be imported, but the optional parts of how your business model works (e.g.: records of quotes, or past stock movements) are optional.

 

How To Import:

All data for importing into SQLWorks needs to be given to the SQLWorks team in one of two formats:

  • An agreed file format exported from another software (e.g.: Sage export file)
  • A comma or tab delimited spreadsheet, .CSV or .TXT file. (e.g.: If using Excel, it is helpful to save the files as a .CSV in the ‘save as’ menu)

If you provide data to the SQLWorks team in spreadsheets (or .CSV/.TXT files) these will need column headings grouping certain types of the data together. For example, in a stock list, all your stock codes need to be in the same column, under an identifiable heading such as ‘Stock Code.’ The SQLWorks team can help you with this stage if you get stuck.

Depending on what SQLWorks modules you will be using, you will need to import files for the following data (see table below). Compulsory data within these are marked – for example: every Company imported must have a name.

 

 

SQLWorks Core

CRM

ACCOUNTS

STOCK

Static Companies

  • Name
  • Company Code

 

Contacts

  • Name

 

Addresses

  • Line 1
  Sales Accounts

  • Name
  • Company Code

Purchase Accounts

  • Name
  • Company Code

Nominal Codes

  • Name
  • Nominal Code
 
Transactional    

 

Outstanding Sales Orders

  • Company code

 

Outstanding Purchase Orders

  • Company code

 

Outstanding Sales Invoices

  • Company code
  • Date
  • Amount
  • VAT

 

Outstanding Purchase Invoices

  • Company
  • Date
  • Amount
  • VAT

 

Bank Rec

 

1 Bank Account

  • Name, Acc & Sort Codes

 

1 Petty Cash Account

  • Link to Bank Account
 
Optional Static

 

 

 

 

Sales Leads

 

Projects

  • Project Code

 

 

Nominal Departments

  • Name
  • Department Code

 

Nominal Analysis Codes

  • Name
  • Analysis Code

 

Nominal Subheadings

  • Name

 

Budgets

  • Amount
Warehouses

  • Name
  • Number

 

Stock List

  • Stock Item Name
  • Stock Code
  • Sale Price
  • Purchase Cost
  • Current Stock Quantity

 

Warehouse Bins

  • Number
Optional Transactional

Tasks

 

Phone Logs

 

Actions

 

Emails

 

Historic Sales Quotes

  • Company
  • Date

 

Historic Purchase Quotes

  • Company
  • Date

 

Historic Sales Orders

  • Company
  • Date

 

Historic Purchase Orders

  • Company
  • Date

 

Historic Sales Invoices / Receipts / Credit Notes

  • Company
  • Date
  • Amount
  • VAT

 

Historic Purchase Invoices / Payments / Credit Notes

  • Company
  • Date
  • Amount
  • VAT

 

Purchase Invoices (Historic)

Stock Movements

  • Stock Code
  • Date

 

 

 

Did you know? Consolidated Invoicing

consolidated invoicing –

If you have customers who prefer to have their bills collated onto a single monthly or weekly invoice, SQLWorks can administrate the consolidated invoicing process for you.

Consolidated invoicing can be activated under the ‘defaults’ tab of the chosen company in your SQLWorks Sales Ledger by changing consolidations from ‘None’ to either ‘Monthly’ or ‘Weekly’, depending on how often you would like your invoices to be consolidated together.

Because this is a change to how invoices will be issued, this option is only open to those with sufficient accounts admin privileges, and will issue a warning message before saving the new invoicing settings for that company.

When a new invoice is now added for that company under the ’transactions’ tab, a delivery note can be printed as normal, but the invoice cannot be printed, emailed or otherwise sent individually whilst consolidated invoicing is still activated for the chosen company.

When you are ready for your invoices to be consolidated, click reports in the top right corner, and choose ‘Report Un-printed Invoices’ as when using SQLWorks bulk invoicing. SQLWorks will prompt asking if you wish to consolidate the invoices of those accounts for which this setting is activated, and once approved will begin consolidating invoices.

All outstanding invoice lines for the required companies will be consolidated onto the last invoice of the month/week in which it is dated, and can now be emailed/printed for invoicing as normal.

Your Quotes and Orders remain unchanged, but individual invoices will now be hidden from view, replaced by the new consolidated invoice ready for sending. In the event that you have outstanding consolidated invoices from previous months/weeks unpaid, these will also be ready for re-sending.

In this way you can easily invoice repeat customers without excessive paperwork, and ensure that you keep the number of transaction entries on a single SQLWorks sales ledger account to a manageable minimum.

SQLWorks can help streamline your customer invoicing: find out how.

Fact Sheet: Consignments & Consignment Stock

Consignments –

If you sell consignment stock through the premises of another company, SQLWorks can help you keep track of your consignments.

Stock locations can be managed in a number of ways, but the easiest way to hold your stock at another location is to create a new warehouse to represent this, named after the customer who holds this stock as a consignment.

To add a consignments warehouse, open ‘Products’ from the main nabvbar (1), open your Warehouse Map (2) and click the ‘New’ button on the top left to add a new warehouse to your list of warehouses. Name this warehouse after the consignment location, or the name of the consignment customer.

When creating the new warehouse, remember to check the correct radio button on the right hand side before saving, tagging the new consignment warehouse as ‘consignment wh’ or ‘retail store.’

You can treat this warehouse like any other – moving stock to or from the premises of your seller, raising customer orders and invoices against that company, and performing stock valuations.

If your consignment is large, you can also divide it into multiple ‘Bin’ locations, as you might for one of your own warehouses, and assign stock to the correct bins accordingly.

consignments

You can choose to change a customers’ default order type to ‘IWT’ (Inter-warehouse transfer) or CONS (Consignment) under the ‘Print and Orders’ Tab in a customers’ Sales Ledger account.

This function allows you to specify your (actually their) new consignment stock warehouse under “Warehouse to” for stock, to be moved into by default. In the case of IWT and Consignment stock, this order will then be removed to prevent invoicing a consignment stock re-seller or similar for the consignment before sale.

At all times SQLWorks treats consignment stock exactly as what it is: your stock, temporarily stored with someone else.

 

For help with stock control and warehousing: contact the SQLWorks team today.

Fact Sheet: Banking

Banking

SQLWorks includes a Banking Ledger to your record and plan all financial interactions with your bank accounts, monitor your statements and reconcile transactions.

Bank Accounts can be found under the ‘Bank’ button within ‘Accounts’ (1) and your bank accounts are displayed in the top left (2) – with the details of the selected account (including branch address, account numbers, sort code, balances and currency) all shown on the top panel. (3) From the Bank section you can also access your ‘Sundry Cash Ledger’ (for cash accounting), ‘Petty Cash Accounts’ and Foreign Exchange data (under ‘ForX.’)

Your unreconciled transactions are displayed under the main ‘Unreconciled transactions’ tab in date order. To reconcile, move to the ‘Statements’ tab, select a statement, and double click on an unreconciled item to set it as reconciled (or vice versa)

If you use printed cheques as a part of your business, you can access your unprinted cheques via the ‘Unprinted Cheques’ tab, which can be used in conjunction with a cheque printing machine or dot matrix printer.

Should you need to add a new bank account for your business, you can do so from the top toolbar by clicking the ‘Add A/C’ button. Each new Bank Account will require a unique Nominal Ledger code for that account and a currency chosen from your list of default currencies.

Your new account can be set as the default bank account in Accounts Preferences by saving its number in the ‘Default Bank Account’ field under the ‘Finance’ Tab. SQLWorks should normally be used to treat Credit Cards as bank accounts, with statements processed in a similar manner.

SQLWorks is designed to keep your banking as transparent as possible, and ensure that it’s always easy to match up the contents of your bank ledger to your real world finances.

 

For accounting software that matches your business: speak to us about SQLWorks today.