ECM Auditing. Its Limitations and a Solution.

What is ECM (Engineering Change Management)?

The basics as to what it is and how to use it can be found on M2M Expert.

Here is an excerpt from the site. I’ve emphasized the text relating to today’s post.

This feature of Made2Manage manages the process of engineering changes in parts and associated documents such as job orders and sales orders. It provides a way to limit changes users can make to the database and a way to notify users of proposed changes in advance.

You can use ECM auditing to oversee changes users make to standard item master parts as well as documents (e.g., purchase orders, quotes, etc.).

How it works

Many companies, including my current employer, use this auditing to monitor changes to sales orders, quotes, and purchase orders. This is administered from the ECM tab of the CSPROD screen. If enabled, and I believe it is by default for all options except quotes, M2M will output all user induced changes to a SQL table called SYECAUDT. You can use the RPECAU, ECM Audit Report, to view them.

Sounds great, what is the catch?

ECM auditing is a good idea, and one that I suspect will be revamped and improved because of SOX compliance requirements. These improvements cannot come too soon because there are several problems with the system.

First, the report is not very intuitive. For example, the sort range descriptions don’t match the choices you make. You choose sales order and you get “Form Mnemonic” as a description. When you go to use it, you’ll see what I mean. I’ve written some Crystal Reports as a substitute for RPECAU and I may post them in the future if readers request it.

Second, M2M has not created a mechanism to allow the user to delete data older than a certain date. So, if you enable auditing of sales orders and leave it on, it will gather that data literally forever and take up needless space doing so. Since many companies have this enabled without realizing it, this is particularly troubling.

Third, it can take quite awhile to run the report depending on how many transactions are logged.

Finally, this audit runs every single time a table is changed and it uses resources to do so. Only enable the audits if you really need them.

A Perfect Example

I was recently working with a Made2Manage implementation that had been operating since 2001. They were tracking everything except Quotes with it and I ran some tests. Incidentally, the overall size of the database was approximately 11 gigabytes.
SYECAUDT Table Size: 470mb Record Count: 526,000

When I ran the RPECAU report on this database searching for a single job order number from about 2 weeks ago, I had to wait 21 seconds. I realize that this isn’t awful, but I’d rather not have users wasting time staring at their screens for that long if I can help it.

What can I do about it?

Well, SYECAUDT is a SQL table. You can use a SQL delete statement to cut down it’s size. Before I go any further, let me issue my standard disclaimer.

Important: All information on this site is provided “as is” for informational purposes only.
In particular, all code samples are provided “as is” without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

If you should decide to use any of the code taken from this site, make sure you test it thoroughly on a test company, or better yet a test server, before you ever use it on your live data.

Here is a sample SQL statement which will delete all records over a month old:

DELETE FROM M2MDATAXX.DBO.SYECAUDT -- Replace XX with your company number such as 01

Incidentally, never create a delete statement without a WHERE clause as it will delete every record in the table. In fact, it's best to create a SELECT statement first so you can examine which records will be affected when you take the next step.

For example, suppose you only wanted to delete sales order records older than 2 months. However, you also wanted to leave those records where users have deleted items from sales orders in the SYECAUDT table. Here is the SELECT statement:

SELECT * FROM M2MDATAXX.DBO.SYECAUDT -- Replace XX with your company number such as 01
AND fcaction <> 'D'
AND fcform = 'SO'

After you have run the statement and reviewed the records returned, you would then simply substitute "DELETE FROM" FOR "SELECT *" and you're all set.

Anyway, after running the first delete statement against that sample database I mentioned I got the following:
SYECAUDT Table Size: 7mb Record Count: 6,000

As you can see, that is quite a difference. How did that affect performance of the RPECAU report? I ran the report with exactly the same parameters above and received the response in 3 seconds.

In addition, you can set this up as a recurring job in SQL Server to automate future clean ups. I'll demonstrate how to do that in a future post.

“Don’t Panic!” A FastForms/VBA project goes awry….

Hitchhikers Guide

The Hitchhiker’s Guide to the Galaxy by Douglas Adams, which was a radio show back in the 70’s, is near and dear to the hearts of many geeks. It branched out into many forms, including books and video games, over the years. What I remember most fondly is the Commodore 64 game released in the 80’s.

For the past few days I have been working on a FastForms customization of the SYCSL Customer Service Inquiry screen. The users need to track a set of 10 dates and various other pieces of data per inquiry and I wrote some VBA code to control the interaction of the controls. This is a time sensitive project, so I had to drop my other projects and complete this one as quickly as possible.

As I was nearing the end of the project, they requested that I make the screen change to the closed state when certain criteria are met with these controls. I’ve never done this before, but there’s always a first time for everything. So, I start experimenting with code that looks like this:

Public Function BEFORESAVE() As Boolean
  If conditions are true then
    DoCmd ("")
  end if
End Function 

This code will “ring the bell” on the Action Bar for you and I was working towards controlling the change status screen from the BEFORESAVE event on SYCSL.

However, I ran into a dead end there as the status window doesn’t have a mnemonic and I wasn’t sure that it could be manipulated. I began looking for a SQL solution, but this isn’t my first choice because of the risks associated with direct manipulation of the database.

I opened TOAD for SQL Server, and intended to use it to compare the database before and after using M2M to close an inquiry. With this comparison, I would know exactly which tables are changed in the process. I intend to blog about this tool later. I was in a hurry, and restored the last backup from my production server back to my test server over Data 01 and to Data 06. I intended to use Data 06 as my static database to compare changes. You are using a test server right?

Now, perhaps those of you experienced with FastForms have already spotted my mistake. For those who haven’t, let me take a second to explain the basics of how FastForms works. When you add controls to a screen, the changes are recorded in Visual Foxpro (VFP) tables called formxmaster and formxitems. The actual data is recorded on the SQL server in tables named in a systematic way. If you modify the SO (Sales Order) screen and you are adding fields regarding the master table, your FastForms table will be named Somast_ext. Somast is the sales order master table, and the FastForms table is an extension of that.

So, if you are working as quickly as possible, and dealing with the normal interruptions in your day, you can slip up and do something very stupid such as restore your pre-modified database right over the changes you spent around 13 hours of work creating. My VFP tables were fine as they are untouched in a SQL restore, but the SQL tables were gone. It’s important to note that I couldn’t open up SYCSL in M2M or in FastForms without forcing M2M to quit via the Task Manager. Even though this was all on a test server, I was in a bind as this was a rush job. This all happened right before lunch, so rather than panic I went home and spent some time relaxing with my bird.

While I was home, I planned in my mind what my game plan would be. First, I thought I would export the form changes and attempt to re-import them hoping that would re-create the tables. That didn’t work as the export program requires the SQL database structures to export as well.

At this point, I thought I may just have to remove the VFP information about my customization and recreate all of them as fast as I could. I would have missed my deadline doing so. So, I thought I would just manually recreate the tables in SQL, but I needed a template to start from. Since my Data 01 company was hosed, I opened my Data 02 and intended to recreate the _ext table with a sample date field and then use that as a guide to re-create the entire first table in Data 01.

I then realized that through a “feature” (bug) of FastForms, I could save myself. Let me explain. When you begin to customize a screen, M2M asks you if you want to customize it for only the current company or all companies. I always choose the current company and assumed that it only customized that company. This is not true. It only customizes the VFP for one company but all the structures and fields for SQL are customized in both companies. I had this revelation because when I opened Data 02 and proceeded to add a DateTime field, all of the fields I created for Data 01 were already there.

I opened Query Analyzer and used it to output a script to create that table and applied it to Data 01. I’ll discuss SQL programming for M2M Administrators and how to use the Query Analyzer to do this in a future post. After re-creating the table in Data 01, I tried to open the screen in FastForms and got an error which mentioned that it couldn’t find wp_SYCSLM_EXT. This is a stored procedure created by FastForms. I used Query Analyzer to output a script to recreate that in Data 01 as well.

At this time, I re-tested the SYCSL screen and got a more generic “Cannot Open SYCSL” error which is fairly common when working with FastForms. I believe it has to do with corruption of the VFP tables. The way to deal with it is to pack and reindex the formxmaster and formxitems tables and then run Createdbc. This is another reason that you need to use a test server as everyone has to be out of M2M to perform this maintenance.

I breathed a lot easier when everything came up as normal after that and I was able to do more work on the customization.

So, what did I learn from this? First, I’ll not make this particular mistake again. More often than not, a person becomes an expert through years of making mistakes and learning from them. Second, through this mistake I learned more about the behavior of FastForms which will prove valuable in the future. All in all, a very good day.

VBA and Yellow Mnemonic Inconsistencies

When a screen has been customized with VBA, the mnemonic background is yellow. The reason for this is for the admin, or Made2Manage techs, to quickly determine whether a screen has been customized or not. FastForms customizations are designated by an italicized mnemonic.

Yellow SO

Over the years, I’ve noticed a few screens which do not properly show this.

  • ABOUT – The information screen accessed at help, about.
  • ARINV – Accounts Receivable Screen.
  • SYCSL – Customer Service Request

The inconsistent behavior with the ABOUT screen doesn’t matter as I don’t know anyone who’s customized it. However, the inconsistency can be a problem for the other screens if you don’t remember it.

What other screens have you come across that don’t designate properly?

Blah Blah. Click ignore to get back to work.

As I mentioned in a previous post, this is a typical red box error you will get from Made2Manage.

Red Box Error

Compared to some of the red box errors, this one is actually descriptive.

Some other examples are:

107 Operator/operand type mismatch.
5 Record is out of range.
13. Alias ‘SLCDPM’ is not found.
1429 OLE IDispatch exception code 0 from Microsoft OLE DB Provider for SQL Server

Now in all fairness, many of these error descriptions will make sense to experienced admins, but those admins are not standing over the shoulder of the users so they do not see them. Yes, the admin can and should be looking at their ERROR.LOG file, but how many of us do that on a daily basis?

Over my 10+ years of working with Made2Manage, I’ve come to the conclusion that this is what the user actually sees.

My Red Box Error

Please pardon my lack of Photoshop skills.

I was reminded of this a week ago when a user came to me with a Made2Manage problem. Unfortunately, I had to ask him to wait as I was already on the phone with M2M support regarding another problem. 10 minutes later when I got back to him, he informed me that he had “fixed it.” I asked him to explain and he told me that he got several red box errors and kept clicking ignore until they went away. I asked him what they said and he replied, “Dave, I get so many that I don’t read them anymore.”

My users’ attitude regarding the technobabble in error messages remind me of a favorite “Far Side” cartoon:

What dogs hear

Fortunately, Made2Manage logs all of these in the Error.Log file, but when you go to look at it, the volume of errors can be daunting. Now in all fairness, normally only the first error of the cluster is important. In a later post, I’ll give some tips as to how to better manage your Error.Log.

In an effort to improve the product, these are what I view to be the problems with the current method of error management. These are not just related to Made2Manage, but to software in general. Why don’t users read the messages? Why should they?

  • Users don’t care about errors, they care about getting work done. Error messages are seen as getting in the way of work.
  • There are simply too many errors.
  • Most errors can be cleared by clicking ignore, or with a Made2Manage restart, without penalty. In a sense, the program is crying wolf.
  • They all look the same to the user. How would they know if they really should notify their Admin right away? They’re all red.

Accordingly, these are the actions I would take to fix this problem.

  • Errors must be less frequent
  • They must be in regular english.
  • The error messages should suggest an action. If it truly is a grave error, the user should be instructed to stop and notify the admin immediately.
  • To that end, errors should have differing severity. The different levels should look different.

The ultimate goal of course would be life without error messages at all.

Has anyone ever answered yes?

M2M Home Page

All joking aside, do any of you intentionally answer yes to make M2M Expert your homepage while installing M2M?

Do you answer yes to make M2M Expert your homepage?

View Results

Loading ... Loading ...

Hold On To Your Butts… (You Need a Test Server)

In a previous post, I emphasized the importance of testing customizations.

The Made2Manage implementation consultants that I’ve known have all recommended having a practice company. The directions for creating one are posted on M2M Expert, and of course this is a necessity. However, I take it a step further and keep a separate test server.

Why do I need a test server?

You need a test server for several reasons. First, as of Version 5.6, you still cannot change the background icon on the M2M screen. This makes it entirely too easy to mistake your test and live companies. This is especially tricky because people normally use a copy of their live data for practice. I myself have either accidentally made changes to my live data or made the opposite mistake several times over the years. I read somewhere that 6.0 is the target release which will enable you to designate different backgrounds for different companies, but until that happens I will advocate a test server. Our test server has a very distinctive background to remind me which version I am working on.

Jurassic Park

“Hold on to your butts.” – Samuel L. Jackson, Jurassic Park

The second and third reasons have to do with the scene above in Jurassic Park. Everything has broken down and the dinosaurs are creating havoc. Samuel Jackson’s character is being compelled by his boss to cut the power to everything in the hopes that it will boot back up and function. He is of course reluctant because he’s unsure of what will happen. This is exactly the feeling I’d get when if I created a complex SQL statement and ran it against my data.

Second, when working with SQL it is possible for novices to write queries which could cause performance issues. You do not want to be responsible for your entire company “timing out” of Made2Manage while you practice or learn.

Third, if you are working with SQL, and doing anything other than SELECT statements, you can accidentally make a mistake with your live data which could be disasterous. Consider the following code:

Delete from somast where fsono < '12345'

Assuming your live company is M2MData01 and your working with your test company which could have the name M2MData06, this is no big deal. You just deleted all sales orders with a sales order number less than 12345. However, if you weren't paying attention and M2MData01 was selected at the top of the screen, you just orphaned all the records in the soitem and sorels tables which correspond to all the somast records you deleted.

Fired Error

I wish the SQL Management Studio had this error.

Needless to say, if you're working on your test server anyway, who cares? You can fix it with a simple restore which takes minutes. In my opinion, SQL development simply requires a separate server.

Fourth, even with the latest versions of Windows, the software environment on our regular desktop machines are constantly being degraded over time. You surf the web for information, constantly use software unrelated to M2M development on them, and install and uninstall software on a regular basis. I advocate using a separate test machine so the software environment remains relatively constant and much less prone to errors.

Finally, everyone needs to create a test server whenever they migrate to a new version of Made2Manage. I'll discuss my migration and testing procedures in a later blog post. Since we all have one at one time anyway, why not just keep it and do your testing on it?

How much does it cost?

I can imagine the accounting folks cringing, but this is not as expensive as it seems. My "server" is nothing more than a desktop PC with the following:

  • Windows XP - Included
  • Visual Foxpro 9.0 - Around $600 (Necessary to create/edit M2M Reports
  • SQL Server 2005 Developer Edition - Less than $100

According to Microsoft the requirements for SQL 2005 Developer Edition are:

Processor - 600-megahertz (MHz) Pentium III-compatible or faster processor; 1-gigahertz (GHz) or faster processor recommended
Memory - 512 megabytes (MB) of RAM or more; 1 gigabyte (GB) or more recommended

Who doesn't have an extra computer in storage which exceeds those requirements?

So by my estimation, it costs you one throw-away PC, and around $700 for the software. This buys you convenience, stability, and insurance against data incidents.

Isn't your job worth $700?

SA! Why’d it have to be SA?

Admin Note: Please review the comments section for corrections for this blog post.

Indiana Jones

“Snakes! Why’d it have to be snakes?”- Indiana Jones

The Made2Manage ERP system accesses its database with one login. I suspect the reason they do so is because it makes programming easier. So, while all of your users log into M2M with the user name assigned by the System Administrator, every database command is sent to SQL via the SA (System Administrator) single login.

In my opinion, this has several drawbacks.

  • Tracking database changes by user is difficult to impossible. M2M provides ECM auditing, but this has issues as well. I’ll follow up with a post on that at a later date.
  • By default, SA has all rights to all databases on the entire server. Using this is inherently insecure.
  • Changing the SA password is a bit more complicated in this situation and you should do this periodically.

As a user, you don’t have a choice in this, but many administrators I’ve spoken with perpetuate the problem by using the SA login themselves. For example, I recently had a company send me an Excel spreadsheet with an ODBC connection which pulled data from their M2M Sales tables. They wanted my help adding a link to another table in the SQL query. However, when the sent it to me they used the SA login and the password was plainly visible for anyone to see. Not only can the user see any data in the tables that they want, but they could easily write to the database, truncate or drop tables, etc. You could argue that I would need access to their network to harm them, but any one of their own employees could. A few years ago, I warned another Admin against this practice and he told me “it’s alright, none of my users would know how to do that.” To me, that’s as unacceptable as having a pet cobra because it’s gentle and rarely bites.

Therefore, if you use an outside reporting vehicle such as Crystal Reports, ODBC Spreadsheets, or SQL Reporting Services, you should create an account with read only rights. If you’d like, you can create report logins with read access for specific tables and a login for managers with access to all tables.

Do you really want to risk someone getting access to your payroll tables?

This makes me see red.

Red Box Error

If you haven’t seen this error, then you haven’t been working with Made2Manage long enough. In case anyone reading this hasn’t seen it, the error indicates that the expected printer name wasn’t found. The most common cause is when a user saves their printer of choice in the printer setup of a Made2Manage report. This would be convenient if it wasn’t a global setting thereafter for every user who runs the report. Any user who doesn’t have “Daves Printer” installed on his machine gets this error, which is fairly useless because it tells the user basically nothing. However, to Made2Manage’s credit, the answer is found in their knowledge base.

Can’t these preferences be kept per user, or per machine, to avoid these hassles?

Typically, people save their printer preferences inside a report because they always print a particular report on that printer. Perhaps the printer is their local printer (which often causes 1957) or a specialized printer such as dot matrix. You can get almost the same functionality by always printing to the “Default” printer.

To quickly change your default printer, try the following:

  • Click Start, then Printers and Faxes (in Windows XP) to show your current printers.
  • Right click and drag create a shortcut to your printers on the desktop.
  • Right click on the desktop shortcuts, and choose “Set as Default.”

Putting them on the desktop gives you quicker access to them while running reports, and allows you more flexibility without running into the annoying red box error.

What we got here is… failure to communicate.

Cool Hand Luke

“What we got here is… failure to communicate.”- Cool Hand Luke

In a previous post, I described my basic process for creating customizations. For many programmers, writing code is much easier than communicating with users, so I’d like to expound upon that process.

Essentially, the problem is this. The user should have a good idea of what their problem is, but they often have no idea how to fix it. They don’t understand what is possible so they don’t really know what to ask you to do. On the other hand, you as the programmer obviously know what you can do, but you don’t do their job and you don’t really understand their problems. If you are not diligent, you can miss vital points in this process and you will waste time in the long run.

Yesterday, I attended a meeting with Made2Manage users and the management at my current employer over a relatively simple customization. They want to be able to track when certain events occur in the process of a specific type of order. As they explained their problem, my mind was racing ahead weighing my alternatives for solving it. I was so busy thinking of what could be done that I wasn’t really hearing what they wanted. While my mind was planning a set of tables with required fields in an external database and thinking about the .Net front end I would write, they became frustrated because they actually wanted something very simple. What they actually wanted was a FastForms customization to add an extra tab to a form and to tie those fields to the master table. In my mind, I was envisioning building Halo, and what they really wanted was Pong.

As I left the meeting, I realized that I had forgotten the first step in my process. Listen to the user and repeat what they say back to them in your own words to make sure you understand them. Never assume that you know what the user wants. Listening is not easy, it is a skill to be learned. An example, of this method of parroting can be found in the attached video which shows one of MAD Tv’s best skits.

An Excerpt

Candidate One: What people need to understand is that the rules we live by during peacetime no longer apply. America is at war.
Candidate Two: No. No. What folks have to comprehend is that the laws we abide by are no longer applicable. America is in a non-peacetime period.
Candidate One: No. No. The government must be allowed to bend the laws in order to protect it’s people.
Candidate Two: I disagree, the administration should be allowed to take liberties with the rules to defend it’s citizens.
Candidate One: You don’t know what you’re talking about.
Candidate Two: And you sir, are unaware of the words that are coming out of your mouth….

Yes, this video is a parody of political debates and double talk, but it illustrates the process of parroting very well.

After your user feels that they have thoroughly described their problem and you have confirmed that you understood them, you can begin to plan your customization. Assuming you have the skills, you should offer the user several customization options that will address their problems. Explains the pros and cons of each and let them choose (with management approval of course) which customization option to pursue. If they explain the problem and you tell them how you’re going to solve it, they will feel as if they have no input over the process and chances are greater that they will be disappointed with the final product.

In my opinion, the key to being a good programmer is not that you’re fluent in the myriad of programming languages available, but rather how fluently you communicate with your users.

First learn stand, then learn fly. Nature rule, Daniel-san, not mine. (VBA)


Typically, “Hello World” is the first bit of programming we all do. It is the simplest, and most basic way to interact with the computer, essentially Marco Polo for adults.

Important: All information on this site is provided “as is” for informational purposes only.
In particular, all code samples are provided “as is” without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

For the first VBA snippet I’m going to post, we’ll do something slightly more involved. Let’s suppose that your superior wants you to verify that the “Ref Name” text box on the Sales Order (SO) screen is filled out. They intend to use it to document a project name for each sales order and they don’t want the users to have the option of leaving it blank.

As I mentioned in my last post, I approach this with small steps and achieve success on each step before continuing. So, begin by opening the SO screen, right click on the mnemonic, choose components, and BEFORESAVE to get to the VBA environment.

The first small step is essentially the same as “Hello World.”

Public Function BEFORESAVE() As Boolean
 If Trim(ThisForm.m2mpageframe1.page1.txtfordername.Value) = "" Then
    MsgBox ("Please specify a project name.")
 End If
End Function

You may ask, “How do I know the name of the text box?” The simplest way is to click on it and hit Ctrl-Shift-R. This will copy the “path” of the control to the clipboard. Incidentally, you have to add the Trim function because the field can contain blank spaces and that isn’t the same thing as “” to a computer. This code simply notifies the user that they have made a mistake, but doesn’t prevent them from doing so. To prevent them from continuing we have to stop the BEFORESAVE event. Also, it would be nice that when we did so, the original screen would come back up with the text box focused so the user could immediately fix their mistake.

Public Function BEFORESAVE() As Boolean
 If Trim(ThisForm.m2mpageframe1.page1.txtfordername.Value) = "" Then
    MsgBox ("Please specify a project name.")
    BEFORESAVE = False
    Exit Function
 End If
End Function

That bit of code will force the user to enter information into the text box or it will not let them save. Yes, I know this code isn’t exactly Halo, but this is our first project after all.

What if management only wants numbers entered into this field? Well, you could use the following.

Public Function BEFORESAVE() As Boolean
 If Not IsNumeric(Trim(ThisForm.m2mpageframe1.page1.txtfordername.Value)) Then
    MsgBox ("The Ref Name field can only contain numbers.")
    BEFORESAVE = False
    Exit Function
 End If
End Function

As you can see, the code is not difficult to read and understand. In a previous post, I mentioned that one of the negatives of using VBA in Made2Manage is that it doesn’t handle error checking very well. That isn’t entirely accurate. Most of the code I’ve seen written by others does not include error handling code. If you want your code to show any errors it encounters, modify your code as below.

Public Function BEFORESAVE() As Boolean
On Error GoTo errCode
 If Not IsNumeric(Trim(ThisForm.m2mpageframe1.page1.txtfordername.Value)) Then
    MsgBox ("The Ref Name field can only contain numbers.")
    BEFORESAVE = False
    Exit Function
 End If
    MsgBox "ERROR: " & Err.Number & " " & Err.Description
End Function

As I mentioned above, I'll be including VBA, SQL, and VFP code which you are free to use and modify. Feel free to comment if there are questions or suggestions for improvement.