How to set pricing & discounting rules


Setting up pricing and discounting rules enables the end user to better manage customer accounts, and stock items.  Rules are restrictions or qualifiers that qualify the rule, and they are only applied if that condition is met.  You can create and set, specific rules for stock item costs and descriptions which are linked to individual customers.  As well as set parameters for when these price and discount rules will be automatically applied.

Managing pricing and discounting rules across multiple customer accounts and stock items can sometimes get quite complicated and tricky to keep on top of.  SQLWorks has made this process easier to set up, control and manage giving you greater flexibility and accuracy ensuring rules are adhered to across the whole of your customer portfolio.

The pricing and discount summary page gives managers and admin personnel the ability to view all rules that have been created, as well as the control to amend and save at a top level, which will automatically apply these rules across the platform.

The module can be found on the main nav bar under the Products Extras section.  The rules can be viewed on multiple tabs, and pricing rules can be imported and exported in bulk quickly and efficiently using an xls or csv file.  Discounting rules can be applied as line discount or an order discount, giving you the flexibility and control to apply rules at various stages of the ordering process.

Price & discount tab

Alternatively, rules can be viewed and managed from the Sales Ledger or Stock Ledger.  In both ledgers the Price & Discounts tab can be found in the top section of the window.  The rules listed apply specifically to the sales account or stock item selected and can be added, deleted and edited from this tab.  Once added the rules are linked to the corresponding ledger automatically and any order created that meets the criteria of one of these set rules, will adjust automatically..

Examples of the different types of rules that can be applied:

  1. Qty or value price breaks to reduce cost for higher value orders
  2. Limited time promotions
  3. Discounts by brand or other custom fields
  4. Specific prices or discounts by delivery site or currency
  5. Order level discounts based on value
  6. Customer specific part names & descriptions
  7. Priority based rules for complex ordering systems
  8. Rules targeting categories of SL account or product
  9. Separate rules for trade or retail customers
  10. Combinations of all of the above

To use customer specific part names & description to search for the item when creating a new customer order it is slightly different, using the customer lookup icon – as shown below.  Part names and descriptions automatically adjust on the item line once the criteria of a rule is met.

customer lookup

For more information, please see our full guide on Pricing and discounting features, or contact our team today.

Getting Started with Query Builder

Query Builder:

Query builder is a powerful reporting tool that advanced users can use to interrogate the SQLWorks database, cross-reference data in new ways, and get insight from business analytics.

Working in steps across the screen, Query Builder guides the user across four main tabs including ‘Setup’, ‘Tables’, ‘Conditions’ and ‘Preview.’

Setup tab allows users to create new queries, and choose the base layer data for their query from their SQLWorks database. For convenience, users can save queries here, opt whether to share them with other users, and re-run their favourites to generate new results as system data is updated.

In the Tables tab, the user can drag in related tables to cross reference different data within the database, and build new comparative reports that don’t already exist by default by selecting one or more data columns from within those tables.

query builder gif

The Conditions tab is perhaps the most powerful – allowing the user to assemble or subdivide the existing data in ways to extract very specific report outputs. Three tools are available here – Filter By, Sort By, and Group By. Filter By options can be used flexibly to exclude irrelevant data an target subsets, while Sort By allows you to re-order everything based on a specific table column to highlight priority data first.

Group By allows users to aggregate data in powerful ways – for example totally values per company, automatically calculating averages into value fields, or totalling costs. Programming criteria are available to do this, including ‘Distinct’, ‘Count’, ‘Concatenate’, ‘Maximum’, ‘Minimum’, ‘Sum’ and ‘Average’ – with columns ordered or re-designated to structure the report as you wish.

query builder group by

In the Preview tab, users can preview the results of their query and export to a range of formats including Excel spreadsheet (.xlsx), Comma-separated value (.csv), text (.txt), PDF (.pdf), print formats, or even email the results direct to themselves.

We hope users will find Query Builder to be a potent weapon in their arsenal of reporting tools – giving you new ways to identify patterns, compare and contrast relative data, or summarise from a new perspective.

 

For more information, please see our full guide to using query builder, or contact our team today.

Did you know? Importing Journals

SQLWorks Accounting includes the option to enter Journals both manually, but also offers the chance to save time by importing journals from a spreadsheet of data directly to your Nominal Ledger.

When in the Nominal Ledger click ‘Journal’ in the top toolbar, and the add Journals window opens. To begin importing your journals, click the ‘Import Journal Option, and browse for the file you wish to import.

Imports must be imported using a fixed format from a template file: a copy of which can be found within your SQLWorks installation ‘Misc’ Folder as a Microsoft Excel spreadsheet – or downloaded here. You will need to enter your data and save the file as a tab-delimited text (.txt) file before importing.

SQLWorks will warn you if the data you are seeking to import is old, and may block you from entering nominal journals into locked accounting periods.

Once importing journals has run successfully, your nominal journal data will appear in the list of journals to be added, and users can commit them to the Nominal Ledger by clicking ‘Save and Close’.

 

For accounting advice and support, contact the SQLWorks team today.

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