Archives

FastForms is Out of Options.

In a recent post, I mentioned that FastForms date fields should be avoided due to a critical bug.

Well, later in the project I discovered a few bugs with FastForms option boxes (also called radio buttons.) The first bug is relatively minor in that they always have to be oriented vertically, rather than horizontally like most of the standard M2M option boxes are displayed.

However, the second bug makes them exceedingly difficult to use. In fact, M2M support was not able to diagnose the problem and it took me nearly a week of investigation and headache to determine what was going on. The issue is that your search will intermittently not work when you have FF option boxes on a form. In my case, this was an Item Master customization and my users were telling me that searching for certain part numbers didn’t work, but we determined that browsing for them did.

Eventually, I used a profile trace and looked through the transactions that occurred when searching failed. Anyway, it turns out the problem is simple. Normally, when you search a form on M2M every single control goes blank and does so for a very good reason.

VFP

Item Master screen in search mode showing native M2M Option Box

If you enter anything into most of those cells, they get included in the search string to the database. However, the FastForms option boxes do NOT clear when you hit search so the values from the record before are included in the search. In other words, if the record you’re looking for doesn’t have the same field values for those option boxes as the one previously on screen, the search will fail. This is why my problem was intermittent. I did notice however, that even the native M2M option boxes do not clear, yet are not included in the search string.

So, what are my options? Well, I could write VBA code to clear those boxes whenever a search is selected or I could use the pull down combo boxes instead. I chose the most expedient option and went with the combo boxes.

Made2Manage Comes Through in a Storm.

I know I’ve been hard on Made2Manage at times. However, when the situation warrants it, I will be the first one to praise as well.

Today I called M2M support to discuss a Shop Floor Data Collection issue and the connection sounded different than normal. I asked the tech why and she explained that almost the entire staff was working from home due to incredibly bad weather. I just wanted to take a moment commend them for thinking ahead, having an emergency plan, and following through with it. It was nice to get live support, and not have to resort to another “Web Support Spotlight” day.

Cycle Counting Woes

I was contacted by a company recently who had some questions regarding cycle counts and how to track them. Basically, they had an S.O.P. (Standard Operating Procedure) and had been following it to the letter for years, but had no way to track cycle count data.

When I investigated further I found that the only way to export cycle count data was as a .MBK file. What’s an .MBK file you ask? Good question, I had to look it up. That’s the extension for a dBASE IV file. What can the “regular” user open them with? As far as I know, they can’t. The aforementioned company would export these files once a month to their file server and the files just accumulated there. Since they could not open these files, they were manually entering everything into Excel spreadsheets.

VFP

This is the screen where cycle counts are exported for permanent record.

When I tried to verify this with Made2Manage support, they indicated that I should use Visual FoxPro to convert them to the Excel format. This isn’t much of a solution since I don’t want to stop what I’m doing every time they perform a cycle count, nor do they want to track me down to do it. M2M’s recommendation is for the user to have VFP installed on their machine so they can do the conversion themselves. This is the point of this article, by the way.

Never give any user access to Visual FoxPro. You’re giving them the keys to the kingdom. They can make any number of catastrophic changes in M2M as well as get your database login and password. In fact, since most M2M installations use the SA account, the user would then have unlimited access to any database on the server.

I’m going to work on a VBA customization to circumvent this .MBK file issue and export the data to a SQL table where it can be accessed through various reporting systems including VFP, Crystal Reports, and SQL Reporting Services.

Stay Tuned.

Friends Don’t Let Friends Use FastForms Date Fields

Over the past year I have become quite proficient using FastForms to customize Made2Manage screens and adding functionality to the core product.

However, FastForms has significant problems and drawbacks including:

  1. The FastForms editor has a clunky, unintuitive interface.
  2. Stability problems abound in both the FastForms screen and with it’s functionality in M2M.
  3. Tab Indexes, the order in which controls are sequenced when you press TAB, only partially work.
  4. You shouldn’t design FastForms in your production company, yet the export and import of these customizations from a test company isn’t always successful either.

I’ve created about half a dozen change requests myself with this product. I recently ran across some problems with FastForms date fields including a critical bug. The first problem, which is relatively minor, is that you must format them (by controlling their width) not to show the time portion of the Date/Time field. This is annoying but can be worked around. The second problem is critical because it prevents their use entirely.

I recently performed a customization to the Item Master screen (INV). A couple of the fields were going to be Date/Time fields and one of them would indicate when the part was created. In my testing I found that I got an error when I entered realistic dates into the INMAST extension fields.

VFP

In other words, if the date in the FastForms date field falls outside the recommended range of plus or minus three years, the user gets a strange error when editing anything on the INV screen. Obviously, this is not a solution. What is the solution then? Create a character field in the database instead and use a regular textbox to store the date. This is not an optimal solution as the character field requires more than twice the number of bytes of storage as a date/time field.

All of this is reflected in the change request 159797.

Have any of you encountered other critical bugs?

MySQL – Yeah, I was wrong.

Years ago, I asked one of M2M’s senior technicians why they didn’t simply use a free database system such as MySQL. The reason being that Microsoft’s SQL licensing fees prohibit many of Made2Manage’s customers from migrating. Therefore I reasoned that if M2M had chosen MySQL instead, then by this time nearly everyone would be on a newer version and the support costs would be considerably less. Also, nearly any system must be less troublesome than Visual FoxPro.

The idea sounded plausible to me, and nobody at M2M could give me a reason why they chose MSSQL instead. Umm… yeah, I was wrong.

I came across this article regarding the latest and greatest release of MySQL.

If you take the time to read that article you will be amazed that they released a database platform in GA status which is littered with critical, crashing bugs.

Don’t expect that all critical bugs that you may have encountered in 5.0 to be fixed in 5.1. Even if we have fixed a big majority of the bugs from 5.0 some really critical ones still haven’t been addressed.
If you plan to use any of the new features of MySQL 5.1, regard these as if they would be of beta quality. Test any usage of these features extensively for in close-to-live scenarios before putting them onto a production server.

We still have 20 known and tagged crashing and wrong result bugs in 5.1 35 more if we add the known crashing bugs from 5.0 that are likely to also be present in 5.1.
# We still have more than 180 serious bugs (P2) in 5.1.

While I am often hard on the folks at M2M, they did at least call this one correctly. In terms of database systems, you obviously get what you pay for.

I feel like such a SAP…

As I’ve mentioned before, I really like Crystal Reports, but their support issues continue to be a problem since they were purchased by SAP.

I recently wiped my computer at work and set about re-installing all of the software. In doing so I lost access to the SAP support website. Unfortunately, there isn’t a phone number for support anymore, and I believe my sales representative has left the company. Therefore, I was reduced to calling random sales people until I found someone who could provide my user number again. SAP doesn’t use your e-mail address or an intelligent user name, but rather a number that looks like S68700812.

Anyway, after all of this, I try to log in and this is what I get:

SAP Error

This is a multi-billion dollar technical company and their error message appears to have 3 different fonts and color schemes. I just love how SAP is asking me, “What happened?”.

As I mentioned before, Crystal used to have the best technical support I had ever seen. I guess my question for them is “What happened?”

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.

Page 28 of 33« First...1020...2627282930...Last »