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

StaticCompanies

  • 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 TransactionalTasks

 

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

 

 

 

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.

Understanding Ledgers

SQLWorks includes four main ledgers for customer transactions: Sales Ledger, Purchase Ledger, Sundry Cash Ledger and Petty Cash Ledger

For accounting, these transaction ledgers are collated into two analysis ledgers, your live Nominal Ledger and your Bank Ledger as described below.

 

Transaction Ledgers (Sales/Purchase/Sundry Cash/Petty Cash)

Your Sales and Purchase Ledgers control account centric transactions for selling and buying to a particular customer/supplier, typically involving an ordering process and a separated invoicing and payment process (i.e. debt and credit).

Sundry Cash Ledger is for payments to and from those whom you have no ‘account’ with, and therefore is best suited to financial transactions that have no delay in payment (for example, a simple cash sale). Because of this, your Sundry Cash Ledger should be used for direct sales & expenditure, or for moving funds into and out of your Petty Cash Ledger.

Each record in any transaction ledger will appear automatically in your nominal audit. SQLWorks follows standard double entry bookkeeping rules, in that each financial transaction has two associated nominal postings. When running a nominal audit SQLWorks uses your nominal profile in preferences and the list below to automatically generate the audit records from the records in the transaction ledgers:

Financial Transaction TypeSide 1 Posts to:Side 2 Posts to:
SL InvoiceInvoice Line Nominal CodeCreditor Account
SL Credit NoteCredit Line Nominal CodeCreditor Account
SL VATCreditors VAT Control CodeCreditor Account
SL ReceiptBank Account Nominal CodeCreditor Account
SL Currency VariationVariance Control CodeCreditor Account
SL SettlementSettlement Control CodeCreditor Account
PL InvoiceInvoice Line Nominal CodeDebtor Account
PL Credit NoteCredit Line Nominal CodeDebtor Account
PL VATDebtors VAT Control CodeDebtor Account
PL PaymentBank Account Nominal CodeDebtor Account
PL Currency VariationVariance Control CodeDebtor Account
PL SettlementSettlement Control CodeDebtor Account
Cash BookCash Record Nominal CodeBank Account Nominal Code
Cash Book Income VATCreditors VAT Control CodeBank Account Nominal Code
Cash Book Expense VATDebtors VAT Control CodeBank Account Nominal Code
Petty CashPetty Record Nominal CodePetty Account Nominal Code
Petty Cash Income VATCreditors VAT Control CodePetty Account Nominal Code
Cash Book Expense VATDebtors VAT Control CodePetty Account Nominal Code

 

Bank Ledger

Your Bank Ledger records the actual record of payments and receipts. A payment can be exist in any of your three main transaction Ledgers (Sales/Purchase/Sundry Cash). Here you can group and organise payments into deposits to exactly match your Bank statement during the bank reconciliation process.

Your Bank Ledger can include multiple bank accounts, against which to record different types of payments. Each account must have a different nominal code that is used when automatically posting the payment records from SL, PL & SCL in the nominal ledger. Note that you cannot enter a payment/receipt record in SL, PL or SCL without selecting the bank account first.
 

Nominal Ledger

Whilst your Bank Ledger records the actual movement of funds, your Nominal Ledger also considers debit and credit transactions from the invoices in your Sales/Purchase Ledgers. The Nominal Ledger gives you a constantly updated window into the profit and loss for each part of your business. By using the ‘audit by year’ you take a snapshot of your business from which you can view P&L, Balance sheet, Trial Balance and drill down into actual live data.

Your Nominal Ledger audit pulls live data from the all of the financial transactions in your Sales and Purchase Ledgers, and from all of the records in Sundry Cash Ledger and Petty Cash Ledger. The value is posted to the nominal code stored on the record and the other side of the nominal posting is decided automatically as explained above, VAT is also posted automatically to the VAT control account.

The nominal ledger also loads nominal journals, non-financial records that serve only to move figures between nominal accounts. Certain processes in SQLWorks create journals automatically, such as end of year appropriation and changes that affect stock valuation.

The Nominal Ledger can have up to three tiers: Nominal codes, Analysis codes (i.e. Sub Nominal codes), and Department codes, each of which can be crosschecked against another to breakdown spending or revenue in different segments of your business for more targeted analysis. The nominal audit creates a record of the value against each individual combination and then pools together the data as per your reporting requirements.

Your Nominal Ledger provides an understanding of your accounts, which includes amounts owed and owing as well as gained or lost, for financial analysis.

 

Understanding Ledgers

 

Did You Know? Custom Search

Lineal SQLWorks Custom Search is designed to help you manage even the largest of business databases quickly and efficiently, so being able to find what you need easily is vital.

When a simple search isn’t quite cutting it, trying using SQLWorks’ ‘Custom Search’ tool for a more targeted search. This can be found by clicking the small dropdown arrows beside the ‘Search’ Field on the Search Bar, and selecting ‘Custom Search’ at the bottom of the list. This opens the ‘Custom Search’ window (pictured below.)

Custom Search

From here users can search different attributes of list entries in the left hand field, using a search criteria in the middle column (such as ‘contains’, ‘begins with’ etc.) relating to the text typed in the right hand field.

By pressing the (-) or (+) buttons on the right, users can also add or subtract extra conditions to build a more complex and specific custom search query, helping to find the needed entries.

‘Search for all records matching’ chooses the criteria searching, such as ‘Any’ or ‘All’ of the chosen terms, and by using the ‘Saved Searches’ dropdown menu users can set Custom Search to the ‘Last Search’ or save a custom search for next time.

Press ‘Search’ and SQLWorks will narrow down your list to the chosen custom search terms. Happy hunting!

 

For more information, contact our team today: 01271 375999