Favorites (Bar) Flummoxes FastForms Forms

Favorites Bar
For those that don’t know, the Favorites Bar is a M2M toolbar, which can be placed anywhere on the screen or docked in various places. It is a convenient way to bring up screens that you use often, and there is a real need for it. There is just one little problem.

It doesn’t work. Well, it does work, but it screws up a multitude of other M2M screens and processes.

To be honest, I don’t know how the Favorites Bar still exists. For awhile, it has been more or less a running joke in support that if a problem is not easily explainable, have the user disable the Favorites Bar and there’s a fair chance the problem will be solved. The screens on the favorites bar are pre-loaded and I believe this is what causes the problems. Since the screens do not initialize the next time they are selected, weird behavior results.

However, because it is so handy, users are going to use it whether you want them to or not. M2M has not provided a way for administrators to disable it, nor seen fit to fix the stupid thing.

Anyway, this particular post is about a FastForms issue I noticed when I customized a screen with a companion form. A companion form loads next to a standard M2M screen to allow the user to enter corresponding information. Some of my users complained that their companion form kept disappearing. I realized through testing that this would happen the second time they loaded the screen. When I disabled the Favorites Bar, everything would work as normal.

According to CR 117770 there is a fix for this particular behavior in version 6.0, but I have yet to test it.

Transaction Posting Problems (TRTR)

TRTR Error Message
I received a question from a reader recently saying that his accounting person could not post transactions in M2M. They received the error message shown on the left.

Since he is no longer on M2M support, he could not close his current month and did not have a lot of time to try to figure it out.

Anyway, the answer is simple and if you are on support, you can read this article in the knowledge base. When the transaction posting process starts, it creates a file in your M2MDataXX directory called TRTR.LOG. It does so to keep multiple users from attempting to use the screen at the same time.

When the Post Transactions to GL screen is closed, M2M automatically deletes the file. However, if your user does not have deletion rights to the M2MDataXX directory or they force M2M to close by pressing CTRL-ALT-DELETE the file remains. The next time the screen attempts to open, you receive this error.

Incidentally, the fact that users must have all rights to the M2MDataXX directory is a bad breach of security anyway. Your users can delete anything they wish in that folder. I wish M2M would address that. Also, it would be nice if that M2M error window would give you the ability to fix the issue from directly within M2M.

Anyway, if you are sure that nobody is in that screen, then you can safely delete the TRTR.LOG file to clear the error and post transactions normally.

M2M Searching Does Not Work With Zip Codes

I thought I would share this just because it’s interesting. M2M zip code fields do not search consistently because the textbox will not allow an asterix. For example, if you want to search for the following companies on the Customer screen (CUST):

Cust No	Company		        Zip
111111	Acme Inc  		90210
222222	Acme Rockets		90210-5678
333333	Acme			90217

If you enter “Acme” into the company name field and hit search, the screen will skip the search grid and display record 333333 because you have an exact match. However, if you search for “Acm” or “Acme*”, the search grid will show all four of these companies because none of these match exactly. If you search for zip “90210” only record 111111 will show up. Since you cannot enter an asterisk, the only way to search for zip variations is to leave the zip unfinished and ignore the extra records. It’s not earth shattering, but I entered a change request regardless.

Have any of you found other situations like this?

Happy Monday

As Garfield used to say, “I hate mondays.” This is what came up for me first thing Monday morning.

Yeah, that's helpful

Yeah, that's helpful

Don't Bother?

One of my talents has always been to take a look at current business processes and find ways to make them more efficient or ideally to automate them entirely. I was recently at a company with which I’m affiliated and noticed that one of the employees had four different spreadsheets open on his desktop at the same time. I casually asked him why this was.

It turns out that this employee has a weekly reporting requirement, and that requirement puts him into what I call “Excel Hell.” This is one of the reasons I formed PETE. They use ODBC spreadsheets to pull their data from M2M. As I watched, he went down his long list of procedures, copying this to here, formatting this column this way, summarizing the data and retyping it into another sheet, etc. It was exhausting and mind numbingly boring. After a few minutes of watching this, I determined how I could solve this problem with a set of SQL jobs to export his information into tables and then report from them. This would take me about 12 hours to build and test. To me this was a no-brainer because the employee “wasted” over 4 hours each week on this report.

When I approached his manager with the idea, I was told not to bother. “It gives him something to do,” the manager said. This is just one more reminder why I’m so very happy not to have a “real” job. Doing something, simply for the sake of having work to do, would drive me crazy. When I asked the manager for clarification he said, “Well, he doesn’t have enough work to do as it is. I can’t do without that position for these reasons… Therefore, your ROI numbers are skewed, because I won’t be gaining any value by saving his time right now. However, I’d like you to work on…”

I suppose his reasoning is correct, but that idea just rubs me the wrong way. What do you think?

Friday Poll, Just For Fun

M2M Expert

On the last “Web Support Spotlight Day”, I tried to submit a case to ask a question regarding the Customer Screen (CUST).

As many of us know the functionality of M2M Expert has not been updated in a very long time. As I entered the version of M2M and tried to select a screen, I had to try to figure out which category the CUST screen would fit in. So for today’s poll, guess which category contains CUST.

Which Category?

View Results

Loading ... Loading ...

Twitter : Cuz All the Cool Kids are Doing It.

Just a quick post to let anyone know who might care that I’ve been dragged kicking and screaming into the world of Twitter. Well, not kicking actually just kind of shuffling… and not screaming exactly… just kind of whining.. but I digress.

I am of course Made2Mentor on Twitter and if my Palm Pre is actually working you can find me tweeting away about SQL, Crystal, and my many hobbies. I may talk about Made2Manage as well if M2M people start following me.

Why Doesn’t T-SQL Have a Date Conversion or a AllTrim() Function?

In all versions before 2008, SQL Server only supported a DateTime data type, not separate Date or Time types. Therefore, every date field in M2M is actually DateTime, and this can be inconvenient for reporting. Your CEO typically doesn’t care what time an invoice was processed, only which day. Therefore, one typically uses one of the following conversions to derive only the date:

SELECT DATEADD (dd, 0, DATEDIFF (dd, 0, GETDATE ())) -- Actually produces 2009-09-03 00:00:00.000
SELECT CONVERT (CHAR (8), GETDATE (), 112) -- 20090903

They do, however, have functions to pull out the year, month, or day of the week. Why not a fully optimized function to extract the complete date?

Furthermore, most languages include a trim or alltrim function which trims spaces from both sides of a string. I’m really tired of using something like the following:

SELECT RTRIM (LTRIM (inmast.fpartno))

Once again, couldn’t Microsoft optimize this process with a built in function? It should be noted that after I wrote this I ran across Pinal Dave’s excellent article on the subject where he suggests making a simple custom function to achieve this.

Do any of you have similar gripes about SQL Server or T-SQL?

PETE- People for the Ethical Treatment of Excel


“If you only have a hammer, you tend to see every problem as a nail.” – Abraham Maslow

Before you start posting your negative comments, let me just say that I think Excel is a wonderful tool for business intelligence. Excel’s position for data analysis will only continue to grow in future versions of SQL Server.

With that being said, many people use it as a poor man’s database. In nearly every company I’ve worked or consulted with, someone has a byzantine method for collecting and analyzing data with it. For example, rather than using Access or another database to keep historical data, users will continue to export to Excel and create ridiculously large spreadsheets with data going back years and then complain that the performance gets slower and slower over time. Well, what do you expect? You opened a 150mb file on your computer, and it’s hogging your system resources. The other problem I’ve seen is that users will create a new spreadsheet to contain data collected weekly. You’ll see this huge drill down into folders something like:

Z:\Operations\Order Administration\Reports\Weekly Order Admin Report\M2M BACKLOGS\2009\July\Week 1\ week 1 Product Class 01.xls

Incidentally, there were separate spreadsheets for each of the 20 product classes all exported from the Sales Order Backlog Report (RPBKLG). These spreadsheets are nearly impossible to analyze over time. The user’s typical solution is a monthly and yearly spreadsheet which summarizes their weekly exports. How exhausting! I’ve dubbed this behavior as “Excel Hell.” Needless to say: spreadsheets were never meant for this kind of abuse.

Another issue with using Excel in business intelligence is that although many people have a basic understanding of Excel use, they do not understand the structure of their database. Consequently, I regularly get complaints that the reports I write are “wrong.” Their reasoning is that my numbers do not match theirs and since the company has been using their numbers so long, mine must be wrong. However when I ask how their numbers are derived, I almost always hear, “Well, Bob created this spreadsheet years ago. It pulls from M2M in real time.” This is very similar to the situation I described here (link to Inigo Montoya post). Ugh. So, I have to spend time looking at Bob’s query, which he “wrote” with a drag and drop wizard, to find his error.

Did you know that if a user edits their query in Microsoft Query, it file locks the corresponding tables in the database? I have one executive who insists on doing this, tweaking the queries every so often, and within minutes I get a call from the purchasing department that they cannot add or edit purchase orders in M2M. What fun.

In the spirit of full disclosure, I have to admit that I was guilty of Excel abuse in the past. I created an entire e-commerce ordering solution for an employer using nothing but Excel and VBA. Yes, it worked and was a huge improvement in their ordering process, but it created data storage problems because each order took over 4mb to store. I created this system 10 years ago, and they are still using it to this very day. If I had the knowledge at the time to create and manipulate databases that I now have, I never would have used Excel.

I think it behooves us as IT Professionals to stop the insanity. I suggest forming a new organization PETE, People for the Ethical Treatment of Excel.

Who’s with me and what was the most egregious abuse of Excel that you have ever seen?

Who Wrote This Crap?

George Carlin

“Did you ever notice that other peoples stuff is crap, but your crap is Stuff?” George Carlin

Last year I came across an article with the same title on Jeff Atwood’s “The Coding Horror.”

Whether I am looking at Made2Manage or another vendor’s code or code created by a previous employee that I’ve been asked to review and refactor- I’ve certainly had this feeling. We all have.

It is indeed a knee-jerk response to dislike code written by another person; however, this is not really what I’m writing about.

Over the past two years, I have really thrown myself into learning SQL Server in general. More specifically, I have focused on writing better SQL code. While looking through some code about six months ago, I again had the same question flash through my mind: “Ugh, who wrote this crap?” It wasn’t aliased or optimized and was very difficult to read.

Then it hit me! The culprit was…. ME. I wrote that code the previous year. It struck me just how far I have come in my SQL development, and I immediately refactored it. The same thing has happened several times since then.

I suspect (or at least hope) the rest of my professional life will be filled with moments like this- realizations of just how far I have come along with the knowledge that I always have a lot more to learn.

Page 22 of 33« First...10...2021222324...30...Last »