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
SQLWorks Version 10 is officially compatible with Apple macOS 11 (‘Big Sur’) and Windows 10 October 2020 Update (’20H2’).
Our SQLWorks Software Development Team – which uses both Apple macOS and Microsoft Windows 10 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.
Big Sur introduces a number of changes to Apple macOS, including visual enhancements, updated notifications, and performance improvements such as the ability to install updates prior to a restart.
Windows 10’s October 2020 update, coded 20H2, introduces a number of Windows 10 Start menu, taskbar and visual enhancements, and changes to Windows’ Settings controls.
In testing, SQLWorks performs around 5% faster under each operating system.
Although we generally recommend users do not install operating system updates on mission-critical devices during OS beta-testing, or immediately at point of new version release, keeping IT platforms up to date is both an important cybersecurity and business continuity measure – and users should seek to move to the latest operating systems once tested successfully.
Speak to our SQLWorks Team today.
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
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.”
“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
Learn more at www.sqlworks.co.uk or by contacting our UK Software Development Team today.
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.
For those using SQLWorks Hosted, the ability to access SQLWorks anywhere is a major advantage. However, doing this may mean you need access local resources to print a document from SQLWorks, connecting to a new printer on a network that is unfamiliar to SQLWorks.
To do this, you will need to allow your saved Microsoft Remote Desktop connection (where Hosted SQLWorks is accessed) permission to access your printer (one of your local resources ) You can either do this when first setting up your connection to SQLWorks Hosted, or edit your settings to include this option later.
On Windows, this option is available by editing your saved Remote Desktop Connection, and clicking on the ‘Local Resources’ tab. Tick the ‘Printers’ checkbox to allow printing from Hosted SQLWorks to your local printer.
On Mac, this option is available by editing your saved Remote Desktop Connection, and clicking on the ‘Local Resources’ tab. Tick the ‘Printers’ checkbox to allow printing from Hosted SQLWorks to your local printer.
SQLWorks includes a useful ‘drag and drop’ facility for saving documents against projects, sales leads, accounting records and more.
If your organisation stores files in a cloud storage app, SQLWorks can be integrated directly to view these folders in your ‘Documents’ tab, and files can be dragged and dropped into SQLWorks directly from the cloud.
This option can be set up from the main Navigation bar via Preferences > User Preferences > (User) > and by clicking the Setup Tab. By saving the file-path of your cloud app’s desktop folder into the ‘Remote/ Cloud Folder Path’ field, and clicking ‘Save & Close’, a SQLWorks user’s document tree will automatically default to view the chosen file location.
As long as your cloud app on your PC/Mac is able to sync to the cloud, SQLWorks will be able to make most up-to-date version of your synced files available for drag and drop.
This can be a useful way to make documents collected during fieldwork (eg: photographs) available to a main office – syncing direct from a smartphone, via your cloud app of choice, to be dragged and dropped against projects in SQLWorks.
Options include Microsoft OneDrive, Google Drive, Apple iCloud and Dropbox, as well as many other cloud file storage applications with a read/write access ‘desktop folder’ style client. (If you’re using a lesser-known cloud storage service with SQLWorks cloud link – we’d like to hear about it!)
This tool can also be used with a valid filepath to a location on a traditional on-premise server, and may be useful for businesses with very complex file structures, to help users automatically find the file location they would normally use for managing documents.
SQLWorks cloud link helps leverage all the flexibility of your favourite cloud app, to gather important files into your CRM, accounting and stock control system.
For additional support and assistance, please contact our SQLWorks team today.