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.
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.
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.
SQLWorks allows users to export data from almost any visible table (and most reports) to structured spreadsheet formats including Microsoft Excel (.XLSX) and comma-separated values (.CSV) – useful for manipulating data and analysing trends.
.CSV files are useful, but are basically limited to plain text organised with column breaks. Exporting to Excel provides much more scope for organising the output data for business purposes.
To make this more powerful, we’ve recently updated the options available when users export SQLWorks data to Excel to include:
1. Smarter Headers:
Column headers are delivered in a separate text format, with bold and underline – and now display numbered headers correctly. Where columns are clearly identifiable, Excel exports are also given column data formats (date / integer / text etc.) automatically.
2. Automated Column widths
SQLWorks will scan the data being exported and size each Excel column appropriately for the data being exported.
This helps minimise the amount of spreadsheet formatting the user has to do manually. Automatic column sizing also factors in the column header, and works between 5-80 characters.
3. Intelligent Alignment
In line with accounting display conventions, ‘text’ data is left aligned by default, while ‘date’ and ‘number’ data is now automatically right aligned (as are headers.)
This helps make it easier to label numbers with text in the same row – and to compare various numbers in the same column, even if they share a preceding digit, but have different orders of magnitude.
Each user’s default export type is accessible via their CRM preferences – allowing users who require their data pre-formatted in Excel to choose this as their preferred output.
Data can be exported from reports by choosing ‘File’ as the export destination, or by highlighting some or all rows in any SQLWorks table and selecting ‘Export’. If Microsoft Excel is your default application for .XLSX and .CSV files, the app will open the data on screen automatically.
For more data management expertise and support, please contact our team today.
If a business harnesses important physical assets, keeping track of these over time quickly becomes essential.
Whether it’s your own machinery, or a piece of equipment you maintain for a client, attributing costs to these accurately is an key part of your business financial planning – and helps you make smarter decisions about future investment.
SQLWorks supports this process with a dedicated asset register – allowing the user to save a range of information (including make, model, warranty and more) for both in-house and client assets to aid day-to-day business operations. Asset Register can be found in the Stock Ledger, and helps the user build a centralised and definitive list that can be controlled with permissions groups, like any other SQLWorks Ledger.
For in-house assets, SQLWorks users can also link these assets to Purchase Orders – ensuring that asset’s data contains a detailed maintenance history that includes financials.
Helpfully, this also works across multiple Purchase Ledger suppliers, so that maintenance costs (such as repairs or replacement parts) remain accurate even if an asset’s service contract changes hands. This helps a company stay flexible, since accounting and contract periods may be much shorter than machinery lifecycles.
Assets may also be optionally enabled as a special tab in a customer’s Sales Ledger account – ensuring a service engineer can easily access known information about a client’s asset as a part of routine account maintenance. This might include location, serial numbering for identification, or notes on past repair work.
SQLWorks ensures business managers can properly document and maintain a full asset register – itself an asset to a well-informed team.
For expertise and software assistance, please contact our SQLWorks Team today
Lineal’s SQLWorks Software is officially registered as a specialist UK digital technologies provider for manufacturing.
Made Smarter is a UK-Government backed initiative launched in 2017, following a nationwide review of the manufacturing sector – and includes a number of private and public sector organisations helping to modernise industry and drive adoption of productivity-boosting technology in the sector.
Recognised fields include a wide range of next-generation technologies for manufacturing such as robotics & process control, data and systems integration, mobile and wearable devices, sensor innovations, machine learning, additive manufacturing and augmented reality technology.
“We’re delighted to see SQLWorks listed among some of this country’s most advanced and cutting-edge technology solutions for manufacturing” said Lineal’s Managing Director Mike Matthews. “The Made Smarter review highlights adoption of modern technology as a keystone for industry in this country, and we want SQLWorks to underpin real advancement and strength in the industry.”
Businesses can read the original findings of the Made Smarter Review here, and learn more about Made Smarter UK here.
Managing outbound goods often requires an overarching view of business operations ‘as-a-whole’ – looking across all sales accounts, orders and products. We’ve given users the power to do this via SQLWorks Dispatch Planner.
Dispatch Planner gives order managers and warehouse teams a comprehensive dashboard to maximise visibility and control over orders. Dispatch Planner is accessible within the Stock Ledger, and (by default) loads a comprehensive list of all outstanding customer sales Orders with key information about each order.
Double clicking on an order allows a drill-down to the exact order, meaning shopfloor teams don’t need to access individual customer sales accounts to understand workloads and even check line-level detail.
Simple ‘traffic-light’ statuses indicate whether the required stock quantity is available for each order to be shipped in full. By selecting an order, dispatch managers can also view the same information for each order line of the order – with the same status indication based on free stock availability. Orders will only be considered ready to ship in full once every line on that order is available to ship.
When ready to ship, dispatches can be sent to dispatch and invoiced (and emailed automatically to the customer) in one smooth workflow, removing the outstanding order from the Dispatch Planner list.
High volume companies may lengthen or shorten the time horizon of the viewed orders, or filter by warehouse. Where goods are not ‘Ready to Ship’, SQLWorks will make the user aware of why an order is delayed – for example, showing Purchasing Delays.
For companies that use Delivery/Dispatch Notes prior to invoice, the Dispatch Planner will also allow the generation of new delivery notes from here, list any valid delivery note numbers for each order for cross-referencing, and require the user to select an existing delivery note before invoicing a previous dispatch.
Dispatch Planner shows the real power of SQLWorks: as the ability to cross-reference stock control and ordering globally across the business allows you to manage operations quickly, efficiently, and with confidence.
You can find more detail about how to use Dispatch Planner here
We’ve extended SQLWorks to include more powerful production planning/process routing in Version 10 – allowing Production Managers to masterplan working spaces, types of work, and employee skills to organise manufacturing capacity more effectively.
Workshop Map can be opened via the Products module in the NavBar, and uses three key elements:
These are places where work is done – normally a specific location or tooling area on the factory floor, and remember important data such as available working hours, setup/lag costs and more.
This is a type of manufacturing process – such as assembly, welding, mixing, painting etc, and can be restricted to specific work centres or employees.
This is the employee table used in SQLWorks CRM – listing your company’s available staff.
Each Process Route has a series of numbered steps (carried out in a specific order) called a Work Flow. To organise the Work Flow, the production manager simply chooses the Work Centre, Process, and Employee that is assigned at each step – by dragging and dropping them into the Workflow builder.
Workflow steps each carry associated costs and manufacturing times, allowing the system to build a comprehensive picture of the process route a finished product must follow to be completed in full.
Production managers can save Template Process Routes and assign these to Bills of Material – with the right default process routes being loaded automatically on new Works Orders to save time. Expected completion times are estimated automatically, and progress can be logged as each step of the Workflow is underway
Any Process Routes that are currently in use will be shown in the ‘Active Process Routes’ table, along with the details of the BOMs being produced which follow that process route.
Production Managers can also use this part of SQLWorks to generate reports (either from the perspective of Work Centres, Processes or Employee) to see outstanding and current Works Orders, and to gauge capacity from each.
This helps inform staffing decisions, shift patterns or identify production bottlenecks. Where there is a clash (for example, if a ‘Welding’ process employee has more welding hours due on Works Orders than is available in the calendar), SQLWorks will display ‘CLASH’ in red next to that Bill of Materials.
Overall SQLWorks’ Production Planning gives Production Managers the power to coordinate working spaces/resources, types of work, and personnel for maximum control. Production Routes help structure and streamline the manufacturing process, and organise manufacturing capacity more intelligently.
For expertise and software assistance, please contact our SQLWorks Team today
SQLWorks Document management has reached the next level, enabling tagging of engineering documents and automated printing alongside Works Orders.
As shown in our Introduction to Document Management, many types of files can be added and stored within SQLWorks, linked to other data, downloaded or referenced whenever required. Our development team have also enabled tagging of specific files as ‘engineering’ documents so they can be automatically transferred through to production.
Saving a .jpeg or .pdf to a stock item’s document tab is a simple drag-and-drop process, supported by tagging with the engineering tag. When a Works order is created for that Bill of Materials, those supporting documents can be pulled through for printing automatically.
This is especially useful for assembly instructions, photographs, blueprints and similar that help guide during the production process.
Setting specific criteria in ‘Accounts preferences’ and ‘Document Analysis’ which can be found on the Navbar, allows a specific ‘tag’ such as one named Engineering, to be selected when saving a document on a stock item. Tagged documents appear in the ‘Documents List’ in the top right of the Works Order automatically, and when printing or previewing your Works Order SQLWorks finds the associated document and asks you to confirm whether you would like to print the engineering documents.
This feature is especially useful if you have drawings, assembly instructions or diagrams for example, that need to accompany the Works order when sent to the shopfloor/workshop for the build process.
If you would like to find out more about how SQLWorks can make the Manufacturing processes more efficient and less time consuming, please get in touch with our SQLWorks team via email [email protected] or call 01271 441570.
Lineal Software Solutions are excited to announced the public release of our next generation of SQLWorks software.
Version 10 of our flagship business management suite harnesses more advanced manufacturing capabilities for the first time – introducing brand new material requirements planning (MRP) capabilities into our existing integrated accounting, CRM & stock control platform.
Automation is critical to productivity. SQLWorks MRP allows businesses to instantly gauge future demand for stock items & materials based on a flexible time horizon and existing stock availability, automatically generate purchase orders for approved suppliers, automatically roll-up part and cost changes through bills of material, and automatically create works orders for production centres on the factory floor.
“… We are clear that the faster adoption of technology will result in greater investment and in more manufacturing taking place in the UK.”
UK Gov ‘Made Smarter’ Manufacturing Review
These hotly-anticipated new features will help drive real business benefits, especially among manufacturing and logistics companies: including more intelligent and cost-effective purchasing, optimised stock holding, and coordinated forward planning of production.
Factory managers can now backflush manufactured items, auto-attach engineering documents, optionally roll-up updated part-costings from purchasing through the relevant kits, and even import new assemblies from popular engineering CAD software via drag-and-drop.
“… British manufacturing needs a factory reset.”
Mike Matthews, Lineal Software Solutions
MRP is an immensely powerful tool for running a business – and we’ve given users the ability to drill-right down into purchasing and production recommendations – finding the exact source of demand from potentially thousands of orders, to line level, with a simple double-click.
SQLWorks manufacturing abilities integrate seamlessly with other business processes across accounting, CRM and stock control – and we’re planning further extensions to functionality for release later in 2020: including detailed capacity planning, support for ‘Just-In-Time’ (JIT) style manufacturing, and more complex ad-hoc report building.
We’ve also made some updates to the visual identity of SQLWorks with this iteration to help make our software feel smoother and more accessible to new users, and expanded Lineal’s UK Software Development Team of Omnis developers to hasten our development cycle.
Mike Matthews, Lineal’s Managing Director, explained: “This is a terrific new leap forward for our SQLWorks software. The new release is our most advanced ever, and introduces powerful new manufacturing and logistical control to our existing business management tools.”
Measurable Business Benefits
One, fully-integrated platform
Heightened financial visibility
Hours of work duplication saved by automation
Greater manufacturing control
Optimised spending & stock holding
Genuine support for business continuity
“We believe the ability for industry to automatically complete important engineering tasks – like importing newly-designed assemblies, production planning, updating part costs and forecasting future operations – will prove a popular choice among manufacturers.”
“Post-lockdown many firms will be doing some serious soul-searching about whether their systems are really up to scratch. British manufacturing needs a factory reset. If you can’t innovate then you’re at a dead end, and modernisation will be an important part of the UK’s economic recovery.”
SQLWorks Version 10 with MRP is available NOW
Simple, fast and intuitive data entry. Detailed, responsive and targeted data analysis.
We recently rolled out ‘Client Review’, a simple way of tracking customer satisfaction using SQLWorks CRM.
The new ‘Client Review’ tab, which is available when you select a Company in SQLWorks, can be used by your staff to keep a log customer happiness, which is graphed over time.
Adding a new client review entry (such as during a catch-up call, or after a meeting) lets the user record positive and negative notes, score the client’s general approval of your service(s), and records the overall trend over time.
A top-level Client Review Report also gives line manager and and company management the opportunity to aggregate this data company-wide, or filter based on team, date, or by different client types.
Keeping track of client satisfaction across a customer base, and encouraging staff to maintain good client relationships, can prove a challenge for many businesses – something SQLWorks Client Review makes much easier.
The data format is flexible – allowing staff tracking customer satisfaction to gather information from catchup calls informally, while keeping an organised schedule for regular contact.
For CRM options and expertise, please contact our team today.
Stock Audit allows businesses to perform a comprehensive stock take, record variances, verify the results, report on valuation, and automatically adjust stock levels for accuracy.
Stock takes can be initiated, or continued, by clicking on ’Stock Audit’ under ‘Products’ in the Main SQLWorks Navigation Bar (1). To begin a new stock take simply click ‘Create New’ or load an unfinished stocktake via ‘Load Existing’. Each stock take is warehouse specific, and can be given a reference ID.
Users must choose whether the final results will ’Save Quantity’ (adjust the actual quantity to a fixed number established from the stock check) or ’Save Variance’ (adjust the actual quantity according to a variance from the theoretical quantity, incorporating quantity changes which may have taken place during the stock take.)
Clicking ‘Print Sheets’ generates a series of stock take forms, listing the stock items and locations under review, which can be printed and assigned to stock counters for counting. Printed stock take sheets include a notes field for stock counters to record additional information, but do not list the theoretical quantity, so as not to prejudice the accuracy of the stock take.
Once stock counters have returned their stock take sheets, stock controllers can enter counted values in the ‘Count’ column next to each item by entering the name of the counter in the ‘Counted By’ dropdown (2) at the top of the window. The ‘Variance’ Column automatically calculates the variance, and a note of the counter’s name is recorded in the ‘Count By’ column (3)
If required, stock controllers can print “check sheets” in order to have questionable variances verified by a second counter, and enter a revised quantity in the same field by using the ‘Checked By’ dropdown at the top of the window (2). The ‘Variance’ Column automatically calculates the variance, and a note of the checker’s name is recorded in the ‘Check By’ column (3). When printing check sheets you may enter a single product code or a csv of codes, each product including its current values for all bin locations are printed on their own page.
To review the records, clicking the ‘Print Variance’ button at the top of the window produces a report on the results of the stock take, grouping the variances on each item across all monitored locations, and calculating a monetary value for the overall positive or negative variance.
By clicking ‘Finish’, the results of either the ’Save Quantity’ or ’Save Variance’ stock take are applied to the SQLWorks Stock Ledger to bring the stock levels back into alignment with the stock take.*
For additional information on stock control, contact our team today.
*If the ‘Free Qty’ tickbox is ticked at the top of the theory column, the theoretical values for the stock audit are shown excluding any allocated quantity. SQLWorks will not accept a variance which adjusts the quantity of an item below the quantity of that item which is already Allocated to Sales Orders, and will instead adjust the Actual quantity as close to the recorded variance as allowed numerically.