Archive for June 2013
We’ve had macros in Sage 300 ERP since version 1.0A. In the early days we used CABLE (the CA Basic Language Engine) as our macro language. This was a macro language version of CA-Realizer which we used for UI development back then. It was fun creating the development environment with debugging capabilities and such. Amazingly CABLE macros are still supported in Sage 300 ERP and if you run a CABLE macro (*.mac) you will get this environment:
With version 4.0A we introduced Visual Basic for Applications (VBA) as our macro language. We did this hand in hand with introducing our first COM interface a4wcom. This interface is still around, but generally we use the newer Sage 300 ERP COM interface a4wcomex.
We provide macros as a method of customizing the product which doesn’t require the SDK. While it does require programming, the Basic language used is simpler than say using C, Java, C# or C++. So hopefully more people can provide meaningful coded customizations that are largely upgrade safe. Generally VBA is a very powerful development environment and we’ve seen some amazing pieces of work implemented as macros. Plus VBA is the macro language used by Microsoft Office, so there are many technical resources, books, courses and such to help you with your development. Further you can use macro recording to help you with some starting code.
For many of our customers, the ERP package handles their financial accounting needs, due to regulations on using Generally Accepted Accounting Practices (GAAP) these are pretty standard. However especially in the operations modules a lot of businesses want custom calculations and procedures to more exactly match their particular business. Whether this is enforcing additional government regulations, implementing custom pricing models or whatever. These are very varied and we need to provide a powerful framework so that these can be accommodated whatever they may be.
At the same time we need these customizations to easily migrate from version to version so that customizations don’t then lock a customer into a particular version and prevent them from ever upgrading.
A powerful macro language with deep hooks into the product is an ideal way to accomplish these goals.
Both CABLE and VBA macros are fundamentally based on our Sage 300 ERP Business Logic Objects or Views. The API for all our Business Logic Objects is the same, so once you learn one, to some degree you learn them all. For a bit more info on our Business Logic, have a look at this blog posting. For an example of creating Orders have a look at this posting.
This can be a great mechanism for say importing data from an external system. VBA can access the API of the external system, extract the data and then feed it into our Business Logic to do things like import G/L Journal Entries or O/E Orders.
Using the VBA Forms capability you can create your own screens that interact with our business logic and perform your custom tasks. The VBA forms library/system is a very powerful but easy to use system for creating potentially quite sophisticated UIs.
The API to our business logic that the macros use is the same as the API used by our UIs, so you know that anything you can do in a UI, you can also do in a macro. It also guarantees that this layer is heavily tested and supported.
Generally the main interfaces to our business logic stays the same. As we add features we add fields, but as long as these aren’t required fields and you don’t need to use these features then your macro can remain the same from version to version.
With version 5.0A, we gave VBA the ability to customize our product’s User Interface Forms. We accomplished this by re-writing all our UIs from CA-Realizer to VB. The new VB UIs were created as ActiveX controls themselves and hence could be hosted on standard VBA forms. Then each UI contained a uniform set of methods, properties and events to all VBA macros to interact and customize them.
There is a little work to upgrade when you go from version to version. With each version the screen control gets a new class id, so you need to remove the old versions control reference and add the new one. Otherwise the code should remain compatible and continue to work. I documented how to do this in this blog posting.
Another great use of macros is to automate recurring tasks. Besides business logic we give you full access to printing reports, including setting all the report parameters. These can be either Crystal Reports of Financial Reports. I blogged on printing through macros here and a bit more information on customizing reports here. Plus from the Business Logic you have access to all processing functions like Posting Batches or running Day End.
So you can write a macro to print out all your month end reports. You can write a macro to go through and process un-posted batches. Or whatever other recurring process you want automated.
Customization through macros is a powerful technology to personalize your ERP and to allow you to achieve greater efficiency. VBA is an industry standard macros language and gives you great power to customize Sage 300 ERP.
Anyone that knows me, knows I don’t have much hair left, but my recent experiences with Microsoft’s Group Policy Objects hasn’t helped at all. Basically we found we had a case where some functionality to do with accessing data in the Microsoft Azure cloud worked perfectly on all computers except any Sage employee’s main desktop computer. Sage, like any big company, has an IS department that sets GPO policies to set various things to ensure all our computers work with various internal systems and adhere to company security policies among other things.
So we figured the problem was something to do with a GPO setting, but what? How to figure it out? Anyway even with a lot of help from IS people and Microsoft support this ended up taking quite a long time to resolve. But along the way I learned quite a bit about GPO (something I’ve happily ignored in the past) and thought I’d share a few things I discovered in this blog posting. In some ways this blog posting is going to be similar to my posting on Windows Bit Rot since it ended up involving many of the same things.
Group Policy is a way of centrally managing computers from a centralized location. It is tightly coupled to Active Directory and Windows Domain management. Often it’s thought of as a security mechanism, but it really is intended to configure a computer, including but not limited to security settings.
GPO is rather adhoc, various settings are controlled by it and they are checked individually by the various components of Windows. All GPO settings are stored in the registry and any running Windows program checks GPO whenever it does something that can be configured by GPO. Over time GPO has grown and grown as each version of Windows adds to the list of things that can be configured this way. One problem is that it isn’t documented what checks the various settings and further the return codes when things fail are often not logged anywhere or are themselves undocumented.
Further GPO settings come from four different levels in the ActiveX hierarchy as shown in the picture below. All these settings are combined to produce the set that controls your computer.
All these group policies are set on various servers in your domain and then pushed out to your computer on a regular basis. Actually your computer runs a Windows Service: “the Group Policy Client” which retrieves these.
All the group policy settings are stored in the registry, if you look through the registry most keys with “policies” in the name are to do with GPO. But there are many other settings without policies in the key that are still set via GPO. These are stored in C:\Windows\System32\GroupPolicy\Machine\registry.pol and merged into your registry.
If you are the Administrative user on your computer then you can edit registry settings change them to what you might need, however the next time you connect to your domain, these will all be set back on you. This is especially annoying for properties that require a reboot to take effect.
The Group Policy Client Service
Since this Windows service can be annoying, perhaps you might like to stop it? If you run the Services UI from Administrative Services then you will find out that you can’t stop or configure this service. But darn it, if you are the administrative user on a computer, why can’t you? Well services are really configured from the registry, so you can go to the registry to change this. However when you first try you will find out you don’t have security rights to change this registry key. The registry key is HKEY_LOCAL_MACHINE\System\CurrentControlSet\services\gpsvc. You need to first grant yourself access by changing the security settings of the registry key and then change the value of Start to 4.
This will then stop your group policy settings from being reset all the time. But you will get a warning about this and only members of the Administrators group will be able to logon.
OK so now we can change configuration items controlled by GPO, by editing the registry and we can stop the GPO service to stop them being refreshed. However this doesn’t set anything back to the default we still need to find the setting that is causing us problems and try changing it. One way is to Google various group policy settings and someone will have documented the matching registry changes. Another way is to run Microsoft’s Process Monitor program.
This program will spy on all calls to check the registry. Just configure it to only spy on the program you are interested in and hopefully this will help you identify the GPO setting causing you problems. Basically you will need to Google the other way from the registry key back to finding the GPO setting that changes it.
Online forums are a great place to post questions and to look for answers. Chances are someone has run into the same sort of problem and the answer is just a Google away. I especially like the TechNet forums and Stack Overflow.
We eventually solved our problem when finally someone else with the same problem got to the bottom of it an answered some posts on the Technet forums (namely this very helpful thread). Basically a remote desktop service was failing from our Sage computers with no good error message or return code. We found an obscure undocumented error happening in the Azure gateway server’s event log. Microsoft Tech Support had no idea what was causing this. There was no way to map this back to the settings on the local computer. In the end someone figured out that the new RDP 8 protocol since it uses standard secure internet protocols rather than its own protocol means that it can be affected by the GPO configuration settings for such and which ones matter and what IS departments might do that can stop things working. In this case an obscure protocol setting that no one can remember why it was set, but probably for some long ago abandoned legacy app.
It seems like as we move forwards, that the Windows environment is getting more and more complicated. Not only do we have all the complicated relationships of Windows domains and active directory interacting with local domain computers. But now we have complicated interactions with the Internet and we are moving much of our IS infrastructure up to the cloud.
So now we need to worry about dozens of servers and local computers talking correctly and securely to each other. Where everything needs correct configuration. This sounds like something that GPO should help with?
I think the problem is that as things get more and more complicated, as more and more GPO settings are added with each version, as our IS systems get larger and more complicated, what we are seeing is that it is very hard to predict the effects and consequences of various changes. Suddenly a change that we don’t think should effect much, perhaps making a local app work will suddenly break something else and cause a very hard to track down error code that is recorded somewhere quite removed. If it isn’t detected right away matching it back to what changed can be quite difficult.
Figuring out what is configured gets difficult since configuration settings are a combination of local settings and then four levels of GPO organization which are all combined. Plus then many organizations outsource their IT and then they don’t even know what GPO settings are being applied, they are just assured they are necessary for their systems to run correctly. Worse IS departments are very loath to remove GPO settings since they can’t predict the effect it will have and worry about being blamed for stopping something working.
To some degree GPO settings for Windows reminds me of database admins tuning older versions of Oracle. There was a myth that to make Oracle performant, you needed a good DBA and that DBA had to do a lot of tuning of all the Oracle configuration parameters (and there were hundreds of these). Our experience (especially in newer versions of Oracle) was that the default settings were always better and most tech support questions were answered by undoing the work of the DBAs and going back to the default settings.
Or are starting to see things like Chaos Theory’s Butterfly Effect, where a small change in GPO on one local computer causes a cloud to go dark halfway around the world?
GPO can be a useful way to manage and configure a large set of computers. But often these settings can have negative impacts in very unexpected places. Often the default settings in Windows are the good ones and GPO should be used very sparingly or troubleshooting problems in your environment becomes quite difficult.