Archives

New Year’s Resolutions

Calvin Hobbes

Calvin and Hobbes

As this is primarily a professional blog, I will not blather on about resolutions to eat less and exercise more in the new year, though I certainly intend to do those things.

This article is about the professional resolutions for improvement that I intend to make in the new year and I will tag a couple other people to do the same.

Without further ado, here are my New Year’s resolutions:

  1. I will continue to pursue SQL mastery, and find more ways to utilize that knowledge in my current position.
  2. I’ve recently started writing for another online SQL site which I will name after my first article has been “published.”
  3. I will study SQL Reporting Services to compliment my skills with Crystal Reports.
  4. I will pursue some kind of real time dashboarding solution for my current employer and gain skills in that area.
  5. Lastly, I purchased Rosetta Stone and intend to learn Japanese. I don’t know if it will impact my career, but I’ve always wanted to learn it. Domo arigato, Mr. Roboto
  6. I am going to tag two SQL Server experts who I greatly respect, and hope that they will continue the chain.

    Brent Ozar
    John Massie

    Do any of my readers have any New Year’s Resolutions to share?

Changing the Ship To Address and Ship Dates via VBA.

Over the years, one of the issues I’ve repeatedly encountered is the wasted time in changing Ship To Addresses and Ship Dates for multiple line item orders. My last employer made a product commonly sold with new building construction projects. Therefore, they often did not have a Ship To Address because the building was still under construction and may not be able to accept deliveries.

When you have a multiple line item order, you must change every line item manually to match. When dealing with 50 line item orders, as my last employer regularly had, someone has to spend 15 minutes changing each line individually. The code provided does that automatically. Now would be a good time for the 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 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.

First, you must initialize the form and create the control in the INIT Event of the SO Form.

VBA

Right Click on SO and choose components.

VBA

Choose the INIT Event

The code below creates a new connection string to the database if M2M has dropped it for some reason, and it draws a button on the form.


Public Function INIT() As Boolean

    If Trim(connstr) = "" Then
        connstr = GetCs1()
    End If
    
    With ThisForm.m2mpageframe1.page1
    .AddObject "cmdRollDate", "M2MButton"
    .cmdRollDate.Top = .txtFduedate.Top + 20
    .cmdRollDate.Left = 370
    .cmdRollDate.Width = 100
    .cmdRollDate.Caption = "Roll Date & Addr"
    .cmdRollDate.Visible = True
    End With
    
    INIT = True
End Function

What follows is the rest of the code necessary for this project. I’ll briefly explain it and if anyone has any questions feel free to ask in the comments section.

First, you must assign the code to an event, which in this case is the click event of your button. Please remember that when changing the button attributes you use something like M2mpageframe1.Page1.cmdRollDate.blahblah, however when creating the function for the event you replace those periods with underscores as seen in the code below. The next line is a simple error handler which will display any errors in a message box. Following this are the object declarations. A message box pops up verifying that the user really wants to change the shipping information because it’s entirely possible that the user clicked the button accidentally.

Next, variables are assigned values derived from the form. The code checks to make sure that there is a valid sales order number and due date before continuing. A database connection and a recordset object are then created. The recordset is set to use the database connection and the two update statements are performed. One updates the SOITEM table with the Due Date from the first tab of the Sales Order screen, and the second updates the Ship To Addresses in the SORELS to match the Sales Order default as well.

Afterward, the database connection is closed, and both the recordset and connection are released. The user is notified via message box that the process was successful and the screen is refreshed to show the changes.


Public Function M2mpageframe1_Page1_cmdRollDate_CLICK() As Boolean
    On Error GoTo errCode

    Dim Value As Single
    Dim DueDate As String
    Dim ShipAddress As String
    Dim SoNo As String
    Dim SoRev As String
    Dim CN As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim SelectStr As String

    If MsgBox("Roll Default Due Date & Addr to all items on this order?", vbYesNo) <> vbYes Then Exit Function
    
    DueDate = ThisForm.m2mpageframe1.page1.txtFduedate.Value
    ShipAddress = ThisForm.m2mpageframe1.page2.txtfshptoaddr.Value
    SoNo = ThisForm.txtFsono.Text
    SoRev = ThisForm.txtFsorev.Text

    
    If Trim(SoNo) = "" Then Exit Function
    
    
    If Trim(DueDate) <> "" Then

Set CN = New ADODB.Connection
        CN.Open connstr
        Set RS = New ADODB.Recordset
        Set RS.ActiveConnection = CN

        RS.Open ("UPDATE SoItem Set fDueDate = '" & DueDate & "' WHERE soitem.fsono = '" & _
                        SoNo & "'")

        RS.Open ("UPDATE SoRels Set fDueDate = '" & DueDate & "', fshptoaddr = '" & ShipAddress & "' WHERE sorels.fsono = '" & _
                        SoNo & "'")
        
        
        CN.Close
        Set RS = Nothing
        Set CN = Nothing
    End If
    
    M2mpageframe1_Page1_cmdRollDate_CLICK = True
    
    MsgBox "Date Rolled"
    
    ThisForm.m2mpageframe1.page3.lstSoItems.REFRESH
    
    Exit Function
    
errCode:
    MsgBox "ERROR: " & Err.Number & " " & Err.Description
End Function

This is a fairly simple example of M2M Database manipulation via VBA for you to use and customize to fit your needs. Please remember that whenever you are writing to your M2M Database directly, you risk data corruption should you make a mistake and this could cost you a great deal to have corrected.

I’m interested to read people’s opinions of my code and any improvement suggestions offered.

Accessing the M2M Database Using VBA.

A couple of weeks ago, someone sent me some VBA code which was giving them fits. I often receive requests like this and am glad to help when I can. I came across this bit of code:


Private Function fConn() As ADODB.Connection
Dim oCn As New ADODB.Connection
Dim sCnStr As String
    sCnStr ="uid=sa;pwd=HisPassword;" _
    & "driver={SQL Server};" _
    & "server=HisSQLServer;" _
    & "database=M2MData01;" _

The code above has the following obvious problems:

  1. This user is still using the SA login. This is a tremendous security risk.
  2. The password, database name, and server are directly identified in the code. The M2VEvents.Prj file can be read by text editors, and this is a recipe for disaster. I know there are other ways for an intruder to obtain this in the M2M environment, but I still feel it’s important to lessen those risks in any way we can.
  3. If you change your database login names or passwords, your VBA code will need to be changed as well.

I thought it would be a good idea to demonstrate the way I attach to the M2M Database, and I believe it is also the method currently used by the Technical Consultants at Made2Manage as well.

My current method involves creating a module in VB called “DefaultModule” and inside I place the following code:


Public Function GetCs1() As String
    Dim SQLUser As String
    Dim SQLPass As String
    Dim SQLServer As String
    Dim SQLDB As String
    
    SQLUser = Eval("FDecrypt(ALLTRIM(ocompany.fcsqluser), oSession.cEncryptKey)")
    SQLPass = Eval("FDecrypt(ALLTRIM(ocompany.fcsqlpass), oSession.cEncryptKey)")
    SQLServer = Trim(Eval("ocompany.fcsqlserver"))
    SQLDB = Trim(Eval("ocompany.fcsqldb"))
    GetCs1 = "DRIVER={SQL SERVER};DSN='';SERVER=" & SQLServer & ";UID=" & SQLUser & ";PWD=" & SQLPass & ";DATABASE=" & SQLDB & ";Connection Timeout=120"

End Function

Notice that none of the information is hard coded, it is all obtained from the M2M environment. Therefore, it’s harder for an unauthorized person to extract it, and if you change your database login and password, the change is automatically handled by your code. However, for some inexplicable reason, M2M can drop the information between screens. To remedy this I include the following in the INIT event of each screen using VBA:


Public Function INIT() As Boolean

    If Trim(connstr) = "" Then
        connstr = GetCs1()
    End If
    INIT = True
End Function

This code is cleaner, more secure, and just plain easier to understand. In the next post, I will share a small project that shows how to draw controls on M2M forms as well as manipulate the database directly.

Anybody have a different method or anything else to add?

Merry Christmas Everyone

I just wanted to take a moment to wish you folks a Merry Christmas and thank you for reading my ramblings.

As an adult I have to buy my own presents, so this year I bought a few things that interested me.

I bought a couple of these:

Lightsabers

Star Wars Force Effects Light Sabers

I bought the Obi Wan and the Darth Vader Light Sabers. I’ve always wanted them and found them for half price online.

I also bought this Nintendo Neon sign I found on Craigslist.

Nintendo Neon

It’s an official Neon sign that Nintendo placed in game stores in the 90’s. Yes, I am a geek. 🙂 That particular picture isn’t of mine, but it’s the same sign.

Finally, I took Anakin, my cockatoo, to see Santa Claus to tell him what he wanted for Christmas.

Anakin and Santa

Anakin whispers in Santa’s ear.

Anakin and Santa

Anakin promised not to have an accident on Santa’s head.

Anyway, Merry Christmas Everyone.

Choosing a Made2Manage Consultant

The Bobs

The Bobs – Office Space

As I mentioned in my first post I’ve been working as a Made2Manage Administrator for more than 10 years. However, even if you’ve been one of my regular readers, you may not be aware that I’ve also been an independent consultant for the last several years as well. Over the years I have worked with several Made2Manage employed consultants as well as independents.

Occasionally I get emails from clients asking me what to look for when hiring a consultant, so I’ve decided to discuss it here to help those of you who are facing what might be a daunting task. Below are some questions and issues you should address with possible candidates.

  1. Is the prospective consultant professional in his communication and manner? Computer geeks are notorious for lacking communication skills. You should be very aware of how your consultant communicates as the lack of these skills may hinder your projects. Communication skills are every bit as important as technical skills and harder to learn as well. The hilarious SNL skit, Nick Burns Your Company’s Computer Guy, illustrates how true this is.

    Nick Burns

    Move!!!

  2. Is the prospect experienced? Do they have an impressive skill set? How well does that skill set fit your consulting requirements? Beware of hiring someone who specializes in only a couple of Made2Manage technologies as they will attempt to address your issues with that limited skill set. This can result in less than optimal solutions as they try to fill a round hole with a square peg. If your project requires coding and report customization, be sure to hire someone with VFP, SQL, VBA, and possibly FastForms experience. Additionally, if you’ve purchased an optional module such as M2M Advanced Reporting, you would want someone experienced with it as well.
  3. How quickly does the prospect return calls? Most consultants are not available 24/7 but the good consultants return calls within one day.
  4. Is your prospective consultant realistic in how he assesses your project? No consultant is perfect and I would warn you against anyone who promises the moon, or who seems too good to be true.
  5. What are the prospect’s rates, completion policies, and guarantees of work? Rates are not as important as a consultant’s policies on what is billable and what isn’t. For example, if quoted 4 hours of labor for a report customization, is that an estimate or is the cost not to exceed that amount? Upon delivery of code, are you billed additional hours for corrections required after testing on your system? Most of the consultants, as well as Made2Manage itself, bill for everything, including the time required to remove bugs from their code.
  6. Additionally, does this prospect offer free help such as with a blog or in the online forums? Can you call the consultant and ask a few quick questions without incurring consulting charges? Again, often the answer is no, and you will be charged in 15 minute increments.
  7. Does this prospect have examples of their work to show you prior to being retained by your firm? What have they actually done?
  8. Is the consultant willing to train your staff to perform some of these tasks in the future?

What questions/issues do you feel should be addressed with a prospective Made2Manage consultant?

Data Utilities – Convenience or Crutch?

Recently I’ve been assisting a company with discrepancies in inventory master and inventory on hand tables. One problem is negative quantities in their inspection which can’t be removed, and they also have problems with inconsistencies in their on hand quantities of various parts.

So, a user brings the Material Availability (RPMAVL) and the Inventory Evaluation (RPIVAL) Reports to me and we compare them with a search from the On Hand By Location (INVOH) Screen. None of them show consistent data.

I explained to the user that M2M has data utilities, which are the first step in troubleshooting these kinds of issues. They are found under Utilities, Maintenance, and Data Utilities.

Data Utilities

I’m sure many of you have used them, and I’d bet that everyone has had to run “Ship Fix” at one time or another. We take these steps for granted when we need to fix issues. However, this user asked, “Why doesn’t M2M just fix these issues so that we don’t have to run these utilities?” This set the hamster wheel in my head moving, and I thought I would share those thoughts with you.

Why are utilities necessary?

Well,the most obvious answer is that utilities compensate for bugs in the Made2Manage software. These bugs can be in the software directly, or they could simply be a case where the software is not protecting the user from making a mistake. For example, Made2Manage will allow you to receive obsolete parts or even part revisions that are not in the item master to begin with. There may be valid reasons for this, but most often these are mistakes that need to be corrected. When the user attempts to do so, they can cause problems like I described above. In my opinion, regardless of the cause of the error (M2M or User Error), the problem lies with M2M.

All of these problems can of course be fixed without the use of a Data Utility by directly manipulating the SQL database, which is exactly what these utilities do. Data Utilities offer a more convenient way for users to fix these issues rather than copying and pasting SQL scripts into Query Analyzer. Also, consistency is more certain when data utilities are used rather than scripts provided by support.

Why don’t they just fix M2M so the utilities are unnecessary?

  1. M2M is a complex system and it’s simply easier and less expensive to fix occasional data inconsistencies than to properly diagnose problems with the code or user behaviors which are problematic.
  2. These problems tend to be infrequent and are therefore difficult to catch. The issue is similar to intermittent problems with your car. Your car never makes the noise in front of the mechanic.
  3. Customers would be inconvenienced by the troubleshooting process. In all fairness, M2M can only do so much testing of their product. They cannot afford to employ a testing team the size of a typical M2M customer company to enter data for years on end in the hopes of finding these problems themselves. They would request volunteers from a group of customers which receive this error a lot and ask them to troubleshoot this problem using SQL Profiler or a similar tool. This could hamper database performance and many customers aren’t willing to endure the inconvenience.

For those reasons, I can understand why M2M uses the data utilities.

However, what excuse can be made when even the data utilities are buggy as well?

M2M Releases Version 5.6 SP3…. umm..6 weeks ago.

Let me preface this by saying that very few M2M Admins are more active than myself. I am on M2M Expert nearly every day. I found out that SP3 was released through the Los Angeles Area Made2Manage User Group on Google Groups.

Am I the only one who didn’t know SP3 was released? It wasn’t posted in the “Latest News” section, nor was there an e-mail informing admins about its release. It wasn’t posted in their own users forum either. I know it was posted in the “Latest Downloads” section, but who really checks there?

I get plenty of e-mails from M2M regarding their latest add on modules in an attempt to increase sales. How about informing us of something important via the same method?

I know that people from M2M read this blog, what do you guys think? Would you like M2M to e-mail you when they release these service packs?

Answers are Easy, the Questions are Hard.

In the past year I’ve been doing a lot of T-SQL (Transact-SQL) programming. My current employer has required a great deal of it, and I have searched out several people and places on the internet to gain this knowledge.

However, as I’ve progressed on this journey I’ve noticed a curious thing. The typical situation begins when I run into a problem with some code I’m writing. I’ll google the issue or function I am struggling with. If you regularly ask questions in some internet forums without first searching for the answer yourself, the regulars will become annoyed and ignore you.

If the google search is fruitless, I will post a question to a forum such as SQL Team. However, it doesn’t take long to learn that you must be exceedingly detailed with your question or the experts will not bother to answer. This attention to detail is simply common courtesy as these experts are providing free help, and why should they waste their time to ask you a multitude of questions about your question.

The pattern I’m noticing is this: In forming my detailed question I am forced to step back from the problem and examine it, and in doing so I often answer my own question. Simply taking the time to properly form my question, answers it. When I mentioned that on the SQL Team forum, I got similar responses from the other members. In fact, a couple of them said that their employers had a policy that you first had to pose your question to an inanimate object (such as a human cardboard cutout or a teddy bear) before you could ask a senior programmer.

Have any of you found that answer your own questions by simply forming them?

Incidentally, my cardboard cutout would probably be something like this.

Yoda

Please Santa, Can I Have an Error Log in a SQL Table for Christmas.

Christmas Story

A Christmas Story

At the time of this writing, the M2M error log is still being kept in a text file, not surprisingly called error.log. I understand that this is probably the most expedient way for M2M to capture it. I suspect it’s a lot easier code-wise to simply append to a text file rather than create a database structure and use SQL to insert error records into it.

Additionally, if your SQL Server goes offline then SQL error logging would of course do the same. However, I also suspect that if your SQL Server goes down, the error.log file won’t have much in the way of relevant data anyway.

Why do I care which format this file is in? For the most part, I want this change for the reporting possibilities. I can theoretically write reports on the text file using Crystal, but if the format was an SQL table, the reporting possibilities are limitless. Off the top of my head, the following are some of the reports I’d like to create.

  1. Wouldn’t you like to know how many errors you are logging each week and to be notified if they sharply increased?
  2. Wouldn’t you like to be immediately notified of certain errors? We all know that M2M throws many superfluous errors, but I would like to be notified via e-mail of those which are critical.
  3. I would like to see which users are generating the most errors and of what kind, so I can suggest better ways for them to use M2M or to suggest they pursue online training.
  4. I would like to use T-SQL to remove the cascading errors from the log before looking at it. Cascading errors are those errors which are simply a result of the first error and they can most often be ignored. In my opinion, they needlessly clutter the log.

These are just a few of the things I’d like to do with this information. There is a change request for this issue, number 11346, which covers this issue but it has been in “Awaiting Approval” status for over 4 years.

I urge my readers, both of you :), to add your companies to this change request to motivate M2M to implement this change.

It's Beginning to Look a lot Like Christmas

Christmas Lights

I’ve always loved Christmas Lights, but some people go overboard.

M2M can’t be decorated like that, but you can add a touch of holiday spirit. Follow the directions below and you can have your active cell indicator switch daily from green to red, the colors of Christmas.

Green

Green

Want to try it? Simply follow these steps:

  1. Download this Zip file and unzip it. Essentially the colors.prg will change the color of the active control on M2M screens for every user. It flips them back and forth between red and green if run in the month of December. If run in any other month, it changes the color back to the default yellow.
  2. You should try any code first on your test server. Now is a good time for the 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 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.

  3. Copy the Visual FoxPro program files colors.prg and colors.FXP to your report program file directory. For example, mine is L:\M2MData\Reports\Prgs.
  4. Next we have to create a “blank” report in Made2Manage. Open the RPMAKE screen, the simplest way to do so is to click Transfer and quickly type RPMAKE and then enter.
  5. Click Add, enter a report ID such as “Colors” and a description such as “Holiday Colors.”
  6. Add a “Default Data Source” by clicking Browse and navigating to colors.prg and then save the report.
  7. Color Screen

  8. Click on the Selections tab and click Add. Enter anything at all for selection name such as “Trash”. M2M reports will not run without a selection, but it doesn’t need to be valid.
  9. Color Screen

  10. As a test, open the report you created by clicking Transfer and then COLORS and run the report. Since the report isn’t printable, you will get the following screen.
  11. Color Screen

  12. Click OK to close, exit Made2Manage, and log back in to re-set your settings.
  13. Open any form you normally would such as the Sales Order (SO) screen and click modify. The background of the control will now be light red. If you run the report and re-start M2M again, your highlighted control will be green.
  14. How can you make the colors automatically switch every day for the month of December? Follow the instructions found in my Command Scheduler article to run this report each night.
  15. When adding to Command Scheduler do not forget to set the frequency to “Daily.”
  16. Color Screen

Besides being fun and festive for the holiday season, this type of customization can have a practical use as well. For example, one of the companies I work with doesn’t use calendar months but instead uses a system they call Red Fridays. The Red Fridays are determined before the year begins and each month ends with one. These can be found on the Accounting Years screen (AYRS). Anyway, I’ll adapt the code to change the background color to red on the last day of the period by querying the GLRULE table, and default it to yellow for the rest of the month.

Incidentally, if you decide you want to change your users default color back to yellow, simply substitute the yellow.prg and yellow.fxp files (after copying them to your report prgs folder) in your Holiday Colors report and run it. This will return everyone back to normal.

A big thank you goes to Ray at NightScaping for his help with the VFP code.

So, what do you folks think?