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.
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.”
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
Members of Lineal’s Software Development Team were recently invited to join the Omnis Software Engineering Team for a special 2-day visit focusing on development work regarding web features within SQLWorks.
Software engineers from both companies focused the dedicated day on the implementation and deployment of Lineal’s recent online work including business-to-business (B2B) trade ordering portals and online user survey systems.
The SQLWorks Team at Lineal wanted to thank the staff at Omnis for being excellent hosts and helping to craft an extremely useful and informative visit.
Omnis Software recently celebrated 40 Years in the industry, having been founded in 1979 by Blyth Computers Ltd. Co-founder Paul Wright.
Omnis technology has underpinned Lineal’s SQLWorks Business Management Software (integrating accounting, stock control, CRM and manufacturing) since the early 1980s. Lineal’s Managing Director Mike Matthews is a part of the international Omnis Technical Committee, and Lineal staff regularly help coordinate and take part in EurOmnis, the international Omnis software developer conference.
For Software development advice and expertise today, please contact our team today.
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.
This year Lineal’s SQLWorks Team traveled to Arnhem, The Netherlands, for the 4-day European Omnis Developers Conference – EurOmnis 2017.
Conference workshops offer the opportunity for developers from more than a dozen countries to exhibit their latest work, seek advice from other programmers and share best practice. A primarily educational event, EurOmnis brings together the brightest and best to further develop software projects and draw on a wider pool of knowledge.
Members of the Omnis Executive Team also attended to discuss the very latest developments of the platform, explain new features (such as remote debugging technology working live from the developer console) and gather valuable feedback from the wider Omnis developer community.
Managing Director of Lineal Software Solutions, Mike Matthews explained: “Over the many years during which we’ve helped organise EurOmnis, both the software itself and the Omnis Developers Group (ODEV) have gone from strength the strength.”
“This year’s conference studied some exciting breakthroughs in web development, mobile/tablet-friendly user interfaces, internet-of-things (IOT) examples, future API technology, and much, much more.”
“SQLWorks is designed to be a highly flexible business management software, and we greatly value contributions from the extraordinarily diverse range of industry sectors, creative skills and technical knowledge represented each year at EurOmnis.”
In addition to coding, EurOmnis 2017 attendees had the chance to visit and dine at the Kasteel Doorwerth, a medieval chateaux dating from before the 13th Century, and literary home of the Beaulieu restaurant, where Erik Hazelhoff Roelfzema wrote Soldier of Orange.
See you next year!
Lineal are Omnis and business software development specialists – learn more here.
SQLWorks includes a range of accounting locks which allow data entry for business accounting to be restricted to certain time periods.
SQLWorks operates date based-accounting rather than ‘Period-based accounting’ in the commonly understood sense – this gives companies greater granularity over how accounting is controlled.
Because of this, SQLWorks accounting can be locked down* based on a number of different periods and conditions, depending on your preference:
Financial Year Level Lock
Year Level Locking is available from Audit > Audit by Year in the main SQLWorks Navbar, by right-clicking on the financial year and selecting ‘Lock Year’.
Financial years that have been locked will display a ‘Yes’ in the ‘Lock’ column of the financial years list, and will prevent SQLWorks users from making any changes to financial transactions within that year. However, nominal journals may still be posted to a locked year.
This lock should only be used by your SQLWorks Accounting Admin, and acts independently of all other accounting conditions.
Monthly Lock (Current Financial Year Only)
This lock can be made available in your Accounts Prefs, where individual months, or months within specific ledgers, can be locked or unlocked for current financial year.
By default, users can only post in the designated active financial year – however transitionary options are available here to allow temporary posting into the final period of the previous financial year if this is still Year Level unlocked.
Because this lock can be turned on/off by a user with sufficient privileges and are always reversible, this forms a ’soft’ lock accounting managers can deploy as a highly flexible ‘Month-end’ control or similar.
VAT Locking (Quarterly Lock)
The SQLWorks VAT Ledger allows users to calculate the UK tax return due for each VAT Quarter.
Once the VAT return has been complete, the date period covered by the specified VAT Quarter will be automatically locked against further accounting entries.
Reporting VAT accurately is a legal obligation, and additional transaction entries will not be permitted into the locked Quarter once VAT has been calculated for reporting to HMRC. Journaling between nominal codes is still permitted.
Once you have set retained profit for a financial year (‘appropriated’ profit) the financial year will be locked, even if the financial year has not yet been Month, VAT or Year Level locked yet. No further transactions may be created in this year.
Because of this, Appropriation is usually only completed once year-end processing for that financial year has already been completed. For more information, please read: “126.96.36.199 – How to Set Retained Profit.”
*Under certain circumstances some accounting locks can be reversed – please speak to your SQLWorks team about this in more detail.
With SQLWorks Version 8 debuting later this year, we’re gathering feedback on what our clients would like to see after the upcoming major upgrade.
Version 8 will be our opportunity to make considerable improvements to both the technical capabilities and user experience of SQLWorks.
Our team’s development priorities have been shaped by the feedback our users have given us in recent weeks and months – please let us know your thoughts below:
Lineal will be launching a new major version of SQLWorks in 2017.
Version 8 will bring a host of new features to our flagship business management software, as well as provide existing SQLWorks customers a greatly improved user-experience.
Managing Director of Lineal Mike Matthews explained: “We were determined to release a major new version of SQLWorks this year, and wanted to implement the customer feedback we’ve been gathering in recent months.”
“By changing to the StudioWorks 8 framework, SQLWorks Version 8 will allow us to update the technology behind our business software – modernising the visual ‘look-and-feel’ to be more intuitive, adding greater flexibility, and offering more options for integration.”
Version 8 is expected to be available from Autumn 2017 – check back for more soon!