‘Negative’ Stock & Advance Batches

One of the questions we’re often asked surrounds the tricky issue of ‘negative’ stock quantities, and how SQLWorks can help warehousing operations balance flexibility with security.

By default, SQLWorks does not allow what some companies choose to call a ‘negative’ stock quantity. Ordinarily, the only stock that can be invoiced is the actual quantity of stock you hold, the act of invoicing moves it out of stock, and you cannot invoice a customer for stock you don’t have.

This is our recommended model, because it helps ensure business keep an accurate stock valuation and acts as natural protection against errors. While promises can be made to customers about stock owed, these remain only promises, and do not influence the company financials.

This model also makes intuitive sense to end-users: the ‘actual’ quantity will never be less than zero – reflecting the ‘actual’ amount a member of staff will find in a warehouse if they perform a stock check in person (either ‘some’ or ‘none’, but never a negative.)

However there are some instances where this principle runs into real-world difficulties. For example:

 

In fast-paced business operations or large factory sites, manufactured goods may need to be physically loaded for dispatch before a shopfloor member of staff or office team at another location are able to confirm in the system that the new stock quantity is completed. The negative stock restriction can then become a bottleneck, as a lack of stock quantity seen in the system holds up the dispatch & invoicing process, and delays the delivery of stock that was otherwise ready on time.

Production runs may not be exact – in some industries (such as printing) including and invoicing a small excess quantity or overrun of finished goods is typical: once again this can create a delay: where dispatch is held up by a tiny excess quantity that isn’t recognised in stock without some minor adjustments to data, and so can’t be invoiced in full.

Large production runs may have special contract terms or non ad-hoc invoicing arrangements agreed, where invoicing might sometimes run ahead of the production of stock. For this, a company would need to be able to invoice stock they ‘owe’ to a customer, and takes an acceptable level of risk for doing so.

 

SQLWorks offers three different solutions to this kind of ‘negative stock’ problem:

 

Solution #1 – Enabling the Dispatch Note System

Enabling the Dispatch Note system adds one extra step in the progression from Order to Invoice: the Dispatch Note. Before an invoice, the user must issue a dispatch note to a customer, which can contain stock lines selected from multiple orders, even those without stock quantity available to be invoiced yet.

The quantity ‘dispatched but not invoiced’ is logged separately under ‘Pending’ until enough ‘Actual’ stock quantity is identified. Once the user is ready to invoice, the company must invoice from the available lines listed on existing dispatch notes.

This system doesn’t allow stock to go ‘negative’, since the user must always ‘book in’ the actual quantity to eventually cover what was dispatched, before they can finally invoice the customer – but it does allow the customer to receive their goods promptly, while maintaining traceability over what was shipped. The Dispatch Note system is a good solution where the underlying issue is the time delay.

The authority to issue dispatch notes can also be optionally granted to warehousing staff, and this system also works in tandem with the Dispatch Planner.

 

Solution #2 – ‘Flexibility Mode’

The first of two ‘Advance Batch’ modes, this option permits SQLWorks to create synthetic batches of stock it doesn’t have physically available. Doing so allows a company to invoice for stock they don’t have yet – albeit with some important safeguards in place.

Firstly, these artificial advance batches are tagged as such behind the scenes to prevent any errors being introduced into the stock valuation. Secondly, when new stock quantity then arrives in from completed manufacturing or purchase order deliveries, this artificial stock quantity figure is absorbed first – reflecting the fact that the real stock was already invoiced out.

Thirdly, in this mode SQLWorks is only allowed to create as much artificial stock quantity as is already listed on outstanding Purchase Orders or Works Orders. While the user is now allowed to send an invoice for stock that isn’t quite ‘to hand’ yet, there has to be a corresponding entry on the system indicating from where the business was expecting that stock to materialise.

advance batches shown in SQLWorks stock control

For example: Advance and Adjusted stock quantities shown above, and a marker showing that stock batch 1016576 is an advance batch of x10 quantity that has been created by the system.

If you have Advance Batches turned on, you can use a quick filter under the Search bar to see all the items that have active advance batches (i.e those with negative stock that will use up future incoming quantity). If you print a stock valuation, advance batches will be treated as a ‘negative’ quantity for valuation purposes.

 

Solution #3 – ‘Unrestricted Mode’

The second of two ‘Advance Batch’ modes, this option permits SQLWorks to create batches of stock it doesn’t have physically available, with fewer restrictions.

Firstly, these artificial batches are tagged as such behind the scenes to prevent any errors being introduced into the stock valuation. Secondly, when new stock quantity then arrives in from completed manufacturing or purchase order deliveries, this artificial stock quantity figure is absorbed first – reflecting the fact that the real stock was already invoiced out.

However, in this mode there is no upper-limit enforced by the quantity outstanding on existing Works Order or Purchase Orders – allowing the company complete freedom to invoice irrespective of actual stock quantity, or even potential stock quantity.

Adopting this setting has important stock valuation and tax implications, so should only be used with great caution.

 

For ERP and stock control expertise, please contact our team today.

Lineal Awarded Twin ISO Certifications

Lineal Software Solutions Ltd. has successfully been awarded two ISO Certifications – ISO 9001 and ISO 27001.

ISO is the world’s best known international standard for assuring business quality across a wide range of areas – including information security quality management, safety, sustainability and more. Highly prized among businesses, each ISO certification is extensively audited by an independent auditor to check compliance with the published standard.

iso badges

Each management system must be continually reviewed and improved. We benefitted from in-built advantages of their own SQLWorks ERP software – which included many of the controls necessary to implement a quality management system. In particular, auditors praised the way Lineal’s own software automatically logged a forensic record of the actions of Lineal staff, the efforts of the company to incorporate customer feedback, and the team’s work on safeguarding against errors.

Managing Director Mike Matthews praised staff for their efforts, explaining:

“In our industry, what matters most is trust in technical expertise. Everything looks impossible until you know how to do it! We are so proud of our team, who not only took on this intense challenge over many months but succeeded. Twice.”

ISO 9001:2015 (‘Quality Management’) checks that a business is carefully planning, following and monitoring all business processes, constantly reviewing results good or bad, and implementing improvements in an organised way.

ISO 27001 (‘Information Security’) requires businesses to maintain the highest standards of data security, privacy and information management throughout the organisation, and is especially important among businesses in the technology sector.

Our special thanks go to Balazs Bagi of Improved Ways Ltd, for his expert knowledge and vital assistance at every stage of our application, and to our external auditors from Alcumus.

Well done to our entire team for their hard work!

This project has received grant funding from Devon County Council via the Devon Elevation Fund & UK Community Renewal Fund.


 

Getting Started with Quality Testing

 

We’ve introduced some important quality assurance tools into SQLWorks – allowing users to formalise their quality tests at different points of the stock control lifecycle.

Quality tests can be customised extensively and introduced at various stages of your workflow – including when receiving goods in, when manufacturing finished goods, or whenever you choose to test a batch that is being held in stock. We’ve also included new abilities to generate, store and integrate Quality Certificates – which will be covered more fully in a future blog post.

 

qa test- types

 

About Quality Testing

Quality tests may be created as standalone templates for your business, and can be customised for a wide range of different metrics. Saved tests can be either advisory or pass/fail, and include numerical measurements, dates, text answers and more – as well as specifying a level of precision for measurement, up to five decimal places.

You can assign tests (or a subset) as the default to any stock item in the stock ledger, formalising the ‘official’ set of tests that apply to that item.

 

qa tests 2

 

Quality Testing on Receipt

One of the most useful places to perform quality testing is at goods-in, at the point you receive inbound stock from a supplier. This can help check that you received what you purchase ordered, and that nothing is defective, damaged or missing.

To support this we’ve extended quality testing to the line-level of our purchase order receipt window – where the user can specify not only what goods were received, any variation to quantity, and those goods’ supplier lot number – but also save quality tests applicable to that line.

The correct tests for each line are automatically derived from that stock item’s defaults saved in the stock ledger, and results are automatically saved onto each new batch of stock that is created in the system when you receive the goods in – confirming that new stock has had its quality verified.

 

qa testing goods in

 

Quality Testing Manufacturing

Quality testing is also now available within Works Orders, helping you to verify that your finished goods have been manufactured correctly for sale.

Testing is listed under the ‘Inspection’ tab of a Works Order, and can be completed for each batch of finished goods that will be created by the works order.

Once again, the default tests are drawn directly from those specified on that stock item’s defaults.

 

qa inspection

 

Batch Quality Testing

Alternatively, a quality test may be completed for any given batch of stock via the ‘Batch Info’ tab of the stock ledger.

This is useful when you want to complete quality testing as a standalone process, test a batch at random, or look for something advised by a supplier (such as during a recall.)

SQLWorks gives businesses exceptional traceability over batches, the quality of your component stock, and the quality of your finished goods.

Introduction to eSignature

 

eSignature functionality is here! We’re delighted to announce this much requested feature as an integrated part of our SQLWorks platform:

eSignature allows a SQLWorks user to generate a single-use URL for a PDF report format (such as a contract, letter or order template) to send to an external signatory. The signatory can access the PDF through their web browser – signing a (touchscreen compatible) signature field that is then merged onto their paperwork in the correct place.

Each signed document is then automatically saved back into your main SQLWorks database, allowing you to integrate this critical step into workflows, projects, and your other business operations.

 

esignature process

 

Signatures can be an essential CRM checkpoint, and can be used as evidence that a customer has approved a purchase, agreed to the next stage of a bigger project, or consented to important terms & conditions.

To confirm identity, signatories can be verified with pre-saved security questions, can be given time-limits to sign, and can be required to sign multiple times within the same document before submission is allowed.

Most importantly, SQLWorks eSignature functionality does not require expensive ‘per-signature’ costs as is the case for many well-known competitors – or any special prerequisite software for the signatory – and can be integrated with a wide range of SQLWorks other functions.

 

esignature

 

We expect this tool will evolve as SQLWorks companies find new and exciting uses for online signature technology – using the power of integrated software to make the approval process easier and more efficient.

For business software advice and expertise, please contact our team today.

Using Shipping Controls

According to recent ONS figures, over 30% of businesses in the UK experienced difficulties getting the stock they needed during 2021, as supply chain issues continue to bite. Ensuring goods and materials arrive on time, in the right place, has become an important part of maintaining productivity.

SQLWorks offers a range of optional shipping controls that can help businesses better manage challenging supply chains, including:

 

Consolidations

Consolidations allow a purchasing manager to group individual purchase orders together into a consolidated group (for example representing the contents of a single shipping container) and assign meta-data to that consolidation, such as freight vessel numbers, suppliers, delivery details, docking dates and more.

Each new consolidation can be managed from the Purchase Ledger – simply select the purchase orders that need to be grouped and move them across the screen from left to right to assign them to the container.

consolidations

Here you can also manage other important information about that container, including actual delivery circumstances, additional on-costs or customs duty, and complete the shipping process.

On-costs can even be automatically split among the contents of that container, allowing a business to factor in the cost of shipping goods into those stock items’ cost-prices, sale-prices, and their eventual profitability.

 

Barcoding

SQLWorks permits stock-handlers to assign a barcode to any stock item, for use elsewhere in the platform. Intelligent Stock Ledger search fields allow the user to also search based on these bar codes, identifying a stock item even if only its bar code is known.

barcoding

Besides each stock item’s ‘Bar Code’ field you can also generate a preview – this same technology can optionally be used on a company’s templates for orders, pick sheets, delivery notes and many others, to help speed-up stock identification wherever that paperwork needs to be used.

SQLWorks uses EAN13 barcode generation that is accurate, internationalised, and works with a wide range of 3rd-party barcode scanning devices.

 

Dangerous Goods & Tariff Codes

With the need to declare tariff codes for internationally shipped goods, and dangerous goods codes for anything hazardous, these can be assigned to any stock item in SQLWorks under the ‘Advanced’ Tab.

tariff codes

We use formats from the UK Department for International Trade’s published 2021 dataset as our starting point.

Much like pricing or descriptions, shipping codes can be merged wherever stock items appear on documentation – please discuss with the SQLWorks team if your business has any special requirements.

 

Please speak to the SQLWorks Team to learn how our software can transform business operations. Contact us.

Introduction to Backflushing

Backflushing is an alternative method of allocating components or materials to manufacturing, whereby stock is deducted only after production has been completed.

Backflushing makes it easier to work with quantities that would otherwise be highly impractical to ‘pick’ for use on Works Orders, and is a particularly useful technique when:

    • Components are too small or numerous to be ‘counted out’ in a sensible timeframe. (e.g.: screws, nuts, bolts, other low-cost parts used in volume.)
    • When materials are divisible, liquid, molten, gaseous, in lengths, weights, or otherwise issued ‘approximately.’ (e.g.: paint, ingredients, chemicals etc.)
    • Where a ‘spare’ quantity needs to be returned to stock after manufacturing is finished. (e.g.: scrapings, leftovers, recycled material.)

In SQLWorks every component on a Bill of Material (BOM) has a default allocation method – either ‘Standard’, ‘Backflush’ or ‘Return’ which defines how stock of this item will be ‘used up’ in the Stock Ledger. These work as follows:

    • ‘Standard’ – This is the default, and is used for all items that need to be counted out in advance of production. Components will be removed from stock when the user clicks ‘Take’ on the Works Order, before production begins.
    • ‘Backflush’ – Components will only be removed from stock after the Works Order is built, and the user is then asked to specify how much has been used.
    • ‘Return’ – Similar to backflushing, however components will only be removed from stock after the Works Order is built, and the user is then asked to specify a ‘spare’ quantity that gets returned to stock.

By changing a component stock item to ‘Backflush’ on a BOM, nothing will be removed from stock at the ‘Take’ stage, instead being deducted only after that BOM has been ‘built’ on a Works Order. The user can then specify how much has (or hasn’t) been used.

This is significantly more practical for certain items – although production managers may need to initially estimate the stock quantity of a Backflush item that they expect to use on its BOM (e.g.: how much paint an item typically needs.)

The user can edit a component’s default type by right clicking on it in a BOM, and choosing ‘Change Component Type.’ Components set to Backflush will be marked with a small ‘B’ symbol in the BOM.

When ‘Taken’ within a Works Order, only Standard items will listed as being removed from stock, and Backflush items will always show a taken quantity of zero (with the user then prompted after the ‘build’ stage for the correct quantity used for Backflush, or quantity excess for Return.)

backflushing

For many manufacturers, drawing down stock ‘post-production’ in this way may be the only way to accurately update your usage of resources. Using backflushing is an efficient way of tracking manufacturing using more fluid components, and allows SQLWorks to keep your production precise.

SQLWorks Live on Microsoft Windows 11

SQLWorks Version 10 is officially live on both Microsoft Windows 11, with early tests suggesting a significant performance improvement.

Microsoft Windows 11 is the first major Windows release for six years, and introduces a raft of visual, technical and security updates to the world’s most widespread desktop operating system.

Our SQLWorks Software Development Team – which uses both Apple macOS and Microsoft Windows for development – works hard to ensure our software maintains the best possible lifespan and interoperability, without the need for companies to spend on paid version upgrades. This includes advance testing on preview build/upcoming operating systems via virtual machine, to ensure our customers won’t experience compatibility issues.

You can learn more about upgrading to Windows 11 here, including the require minimum specifications. Uptake across industry PCs is expected to be somewhat slower than for Windows 10, as many pre-2018 processors prior to the Spectre and Meltdown incidents won’t support the update – meaning that the adoption of Windows 11 will be driven predominantly by new devices.

SQLWorks is still fully operable on any 64-bit version of Windows 10, which itself remains fully supported by Microsoft until October 2025.

Video: Manufacturing Brilliance

 

[subscribe_to_unlock_form]

 

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.