I have been spending a great deal more time ‘on the road’ in recent months due to the change in my position in July at K3 Syspro. As well as crossing England’s infamous North/South divide on a weekly basis, I have also come into contact with some very high-level customizations both within and outside of the SYSPRO system – some good, some bad. This has lead me to ponder how best to connect all of these technologies to work in the most effective way. The technologies I’ve come into contact with on my travels can be summarized as follows:
- SYSRPRO Trigger programs – simple triggers that fire from SYSPRO events as they happen
- SYSPRO Electronic Signatures – a much more granular and powerful event-driven technology that extends the possibilities available in SYSPRO Triggers
- SYSPRO Event Management – the ability to send emails, generate a log file and run standard and non-standard SYSPRO programs from events in the business process
- SQL Triggers – a technology available within Microsoft SQL Server to fire events from changes to core data in your SYSPRO database
- SQL Stored Procedures – another powerful facet of Microsoft SQL Server, allowing high-level business transactions to be handled at the core data level, outside of the SYSPRO program
- SQL Service Broker – an inherent feature of Microsoft SQL Server that acts as a queue, waits for ‘messages’ and executes business logic when these ‘messages’ are received
- K3 Syspro’s DataSwitch – a powerful automation product of K3 Syspro’s that can handle thousands of Business Object transactions in minutes, leveraging SYSPRO’s Business Objects framework
- Windows Scheduled Tasks – a long-standing feature of Windows that allows programs, scripts and automation to fire on various timers throughout the week
- VBScripting – a scripting language supported by SYSPRO programs to add additional functionality to the transactions and processes carried out in SYSPRO programs
The challenge as I have found is how to best leverage each of these technologies for the requirement at hand. With the power and flexibility behind these technologies, in particular SYSPRO and Microsoft SQL Server, it is possible to achieve the same goal many different ways. So how do you build a complex customization in the best way? I have some thoughts:
- Document your requirements thoroughly: Many of the available technologies are essentially fired from a particular ‘event’ in your business process; so work out what exactly this is, how often it fires and exactly what should/shouldn’t happen off the back of this. Document this two ways – in non-technical board-level terms, and in high-level technical terms. This will act as an instruction guide for those on both sides of the fence.
- Involve experts or understand the technologies thoroughly: Generally speaking, each of the technologies listed above is best used for a particular purpose. But only with those who know the products intimately are you able to choose the best product for the job and design a solution that meets generally-accepted best practice. This may involve additional expense but you really can’t beat sitting down with an expert.
- Be aware of knock-on effects and caveats: As follow-on from the above, be aware of caveats and potential pitfalls in the use of some of these technologies. For example, all global SYSPRO support desks are aware of the impact of poorly written SQL Triggers in SYSPRO SQL tables. This doesn’t mean don’t use them, it means be sure that another technology isn’t better suited for the purpose (SYSPRO Electronic Signatures for example). As with any new feature or solution (no matter how small), test, test and test again!
I firmly believe (and follow) the old adage “Fail to plan, plan to fail”. Planning additional time/budget to plan, agree and sign-off on the best technological approach for your high-level customizations is worth its weight in gold. Your entire solution will be quicker, more efficient and robust enough to withstand upgrades in the future.
