Special Ed from Crank Yankers
I’ve received a few e-mails asking where I’ve been lately. I got a killer virus and I finally had to wipe my primary machine and install all the necessary software. However, that’s not the primary reason I have been silent as of late.
Some of you know I’ve been riding my butt off lately. I joined a recreational team because I work and train with two of the members. Anyway, my coworker came to me and said that the leader would like to use my joining the team to get free stuff from sponsors like drink mix, gels you eat while riding, etc. I wasn’t really paying attention so I told her to do whatever she wanted; I am certainly willing to take free stuff. I later found out that I need to document my mileage to submit for this program. You see, they have me signed up as a “special needs” rider because of my weight. Yes folks, I am the bicycling equivalent of a Special Olympian. It doesn’t matter that I put more miles on the bike than any of them, this is how I’m labeled. So, when we go to rest stops, I act mentally challenged, say Durh! a lot, and miss my mouth with the water on purpose. What the heck, it gets laughs.
I decided that I needed some real bicycling jerseys to use on these group rides, so I didn’t look like a compete goober. However, they are difficult to find in my size because most serious bicyclists weigh 150 lbs. So, I ordered several jerseys along with a bunch of expensive bicycling equipment online and they arrived yesterday. None of the jerseys are sized consistently and the only one that remotely fit was bright red and at least a size too small. So, my team kept saying all day, “Hey Koolaid!!!”. Of course, in response I had to say, “Oh Yeah!!!” with a ridiculously low voice.
Anyway, I finished my first group ride in 3 years last week. We made it through 47 miles and I did pretty well. My muscles cramped a bit here and there, but the worst thing that happened was sunburn. In fact, I got up at 6am the next morning to take a 10 mile recovery ride on the bike. My weight is coming down and my stamina is going up. I can do anything.
Oh, and if you’re wondering if I still intend to do the Hotter Than Hell Century ride at the end of the summer the answer is…
Have you ever wondered what happens when you reach 100 revisions of a Sales Order? No? Me neither, but it happened to us last week.
My current employer has long term contracts and the sales orders can stay open for 5 years or more. Items are added, changed, or removed all the time so 100 revisions isn’t as outlandish as it sounds.
Well, when you hit revision 100, the screen displays ** and that value is written to the database. There are no revisions after that.
Made2Manage created a Change Request for it, number 163578b and their workaround is to add and carry the sales order into a new one for a fresh set of revisions. However, there’s no way to actually tie the orders together so you lose your history anyway.
Oh well, you learn something new everyday.
I was in the process of waiting for M2M Tech Support to link my employer to yet another Change Request for me. When I received it, I noticed that the status said, Reviewed. What exactly does that mean? I know it means that they reviewed it, but does anyone care?
I asked the Tech what the real disposition was for the CR and he claimed not to know even though it was a few years old. I decided to browse other CR’s for the Sales Order (SO) screen and came across huge numbers of them. Many of them had other statuses such as Closed, Approved, Awaiting Approval, and Rejected.
For that matter, what does Closed mean? Awaiting Approval?
Just for fun, I looked back into the earliest CR’s and found several dating back as far as 1997 that were still in the Reviewed status.
Now, I know that M2M is not going to approve all of the change requests. However, in my opinion there should only be a few statuses. They would be something like Awaiting Review, Duplicate (for CR’s accidentally requested twice), Approved (and it should indicate a proposed release), and Rejected.
We know that the folks at Consona read this blog. Do any of you folks have other suggestions for the change request system?
I’ve mentioned before that Crystal Reports is a fine product, but I don’t recommend that Made2Manage customers purchase the server package. It’s expensive and their support is lacking.
However, I found out one other thing that it lacks this week. It doesn’t support usage statistics either and I cannot audit who is in the system at various times. Crystal Licenses are concurrent and if your users are not careful, they will close their browser window rather than manually logging out. This holds their license for 20 minutes by default. It’s not difficult to see that this will force you to purchase additional licenses. Apparently the Crystal Reports Server product which costs approximately $8,500 for 5 concurrent licenses does not include basic auditing. In order to get that, you must purchase their Edge Product which costs around $19,000 for 5 concurrent licenses. Both of those prices include the mandatory first year paid maintenance.
Furthermore, I learned that I was qualified to upgrade to Crystal Reports 2008 for free since I had current, paid maintenance. However, when I downloaded the latest version, I was not able to update the desktop design studio. That product was included with my XI Server package, but according to SAP was not under maintenance.
Therefore, I have the right to upgrade my server to 2008, but can not actually produce 2008 reports. Since their support has been less than satisfactory, you can imagine the response I got from my superiors when I requested that we purchase the latest desktop studio.
It was something like, “Whatchoo talkin ’bout David?”
While I fully understand why different programming languages use different syntax, wouldn’t it be nice if they could at least agree on a comment structure?
I can’t tell you how many times I’ve typed something like this into the VBA studio:
-- Make sure to declare the recordset
And of course it fails because VBA uses the single apostrophe to make comments.
Is it really too much to ask for that the Microsoft languages share the same comment structure?
Any sufficiently advanced technology is indistinguishable from magic. – Arthur Clark
One of the factors which draws me to programming, and especially database programming, is that most people cannot understand it. To most people, computers, networks, and the internet etc quite simply work by magic. They know how to turn it on, and the basics of using the technology, but can’t or simply do not want to understand it. Beyond that, the process of creating, administering, or reporting from databases is simply foreign to them. I have always been driven to understand why technology works, and look at my job as being paid to work on puzzles every day. However, part of its allure, is the satisfaction of knowing something that others don’t; I have never wanted to have a profession that just anyone could do. So in essence, I like being a magician and always have.
I may not pull rabbits out of my hat, but to the average user, what I do may as well be
magic. In fact, most IT managers don’t really understand what we do either. One of my geek interests is reading science fiction and fantasy novels. I have a large collection of the Dragonlance Series created by Margaret Weis and Tracy Hickman, and they were wonderful reads while I was in my teens.
Raistlin Majere – The Ultimate Geek
One of my favorite characters is Raistlin Majere, the tortured and misunderstood soul who’s yearning for power drives his endless quest for magical abilities. He spends the vast majority of his time studying difficult magic spells utterly incomprehensible languages (cough.. Visual FoxPro… cough…) to perfect his craft. Does this sound like anyone you know? Anyway, his intelligence and knowledge of strange and mysterious powers enables him to come close to conquering the entire world. Fear not dear reader, I do not have aspirations of world domination, but this character had a profound impact on me while growing up. Is there truly that much difference here:
Abdis tukng! Kumpul-ah kepudanya Kuasahan!
Burus longang degang birsish Sekalilagang!
Degagng kuashnya, lampar Terbong kilat mati yangjahat! Xts vrie!
lcfilter2 = STRTRAN(UPPER(lcFilter),["],['])
lcfilter2 = STRTRAN(UPPER(lcFilter2),[.T.],[1=1])
lcAdvFilter2 = STRTRAN(UPPER(lcAdvFilter),["],['])
lcAdvFilter2 = STRTRAN(UPPER(lcAdvFilter2),[.T.],[1=1])
lnHandle = CURSORGETPROP('ConnectHandle','jomast')
IF EXISTS (SELECT *
WHERE id = Object_id(N'[dbo].[fn_base36]')
AND xtype IN (N'FN',N'IF',N'TF'))
DROP function [dbo].[fn_base36];
To the average person are those really all that different from each other?
As I write this, flying at 34,000 feet above our world, I wanted to take a moment to reflect on one of my motivations for continuing to pursue this knowledge and be the best at it. If you’ll excuse me now, I’m going to open up my ancient book of runes to study my magic so I can move up to the level 3 spells.
At times I have discussed security on this blog and mentioned the challenges of being secure when using M2M. However, this post has nothing to do with Consona.
I read several blogs on a regular basis for programming and SQL information. In fact, I think I’ll list the blogs I recommend in a future post. One of the sites I frequent is Simple Talk, and I enjoy their SQL Server articles. Eventually, I decided to comment on an article. The site requires you to sign up to comment, so I did so.
This is the message I got from them thanking me for signing up.
From: Simple-Talk – Automated Email [mailto:firstname.lastname@example.org]
Sent: Thursday, March 26, 2009 4:35 PM
To: David G. Stein
Subject: Simple-Talk membership details
You applied to join Simple-Talk, and may now login.
To login, please visit:
After logging in you can change your password here:
They included the user name and password directly in the e-mail. This is a bad practice for several reasons. The first of which is if someone gets access to my e-mail account they can now get into Simple Talk as me. They can spam the blogs and whatnot.
Now you may ask, “So what?” Well, the second reason is that most people use the same login for many if not all of the sites they visit. Don’t you do that? It’s human nature to do so.
So, if I used the same login for Simple Talk that I had for my online banking, Amazon, Ebay, Paypal, or other sites, the person who hacked my e-mail now has access to very sensitive areas of my life.
I’m sure we’ve all seen small sites which make mistakes like this, but this is a blogging site for programmers of all types, and one would think they would know better.
What sites have you noticed that also include this information in their e-mails?
In a previous article, I mentioned the inadequacy of Made2Manage cycle counting in that you cannot track your cycle counts over time and make that data available to your users. Well, true to my word, I have created the following customization so that you can do just that. Now would be a good time to post 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 expressed 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.
The first step in this customization is to create a database to hold your cycle count data. I would recommend creating a new database with a name such as Customizations or CompanyName_Customizations. Substitute the name of your database into the script below to create the table which will contain your cycle count data. The table created is nearly identical to the INPHYINV table which temporarily holds cycle count tags in M2M with an additional field for cycle count date.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
CREATE TABLE [dbo].[CycleCounts] (
[fcappby] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcarea] char(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcbin] char(14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fccountby] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcdescr] varchar(35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcentby] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcissuedto] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcjobno] char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcloc] char(14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fclot] char(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcpartno] char(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcrev] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcprodcl] char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcsite] char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcstatus] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcstkuom] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcuom] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fddate] datetime NOT NULL,
[fdexpdate] datetime NOT NULL,
[fliserror] bit NOT NULL,
[fliswip] bit NOT NULL,
[fnconvrate] numeric(11, 5) NOT NULL,
[fncount] numeric(17, 5) NOT NULL,
[fnoperno] numeric(4, 0) NOT NULL,
[fnonhand] numeric(17, 5) NOT NULL,
[fnpcnt] numeric(7, 2) NOT NULL,
[fnqtyapp] numeric(17, 5) NOT NULL,
[fntag] int NOT NULL,
[fnunitcost] numeric(17, 5) NOT NULL,
[flisprepop] bit NOT NULL,
[flisprinted] bit NOT NULL,
[fdfinish] datetime NOT NULL,
[timestamp_column] timestamp NULL,
[identity_column] int IDENTITY(1, 1) NOT NULL,
[fmdescr] text COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (' '),
[fac] char(20) NOT NULL,
[fdTaken] datetime NOT NULL,
[fcudrev] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (' '))
/****** Object: Index [dbo].[area] ******/
CREATE NONCLUSTERED INDEX [area]
FILLFACTOR = 90
/****** Object: Index [dbo].[identity_column_idx1] ******/
CREATE UNIQUE CLUSTERED INDEX [identity_column_idx1]
FILLFACTOR = 90
/****** Object: Index [dbo].[issuedto] ******/
CREATE NONCLUSTERED INDEX [issuedto]
FILLFACTOR = 90
/****** Object: Index [dbo].[JOBORDER] ******/
CREATE NONCLUSTERED INDEX [JOBORDER]
([fcjobno] , [fnoperno])
FILLFACTOR = 90
/****** Object: Index [dbo].[LOCATION] ******/
CREATE NONCLUSTERED INDEX [LOCATION]
([fcloc] , [fcbin] , [fclot])
FILLFACTOR = 100
/****** Object: Index [dbo].[parttag] ******/
CREATE NONCLUSTERED INDEX [parttag]
([fcpartno] , [fcrev] , [fntag] , [fac])
FILLFACTOR = 90
/****** Object: Index [dbo].[status] ******/
CREATE NONCLUSTERED INDEX [status]
FILLFACTOR = 90
/****** Object: Index [dbo].[tagno] ******/
CREATE UNIQUE NONCLUSTERED INDEX [tagno]
([fntag] , [fac])
FILLFACTOR = 90
Next since we will be accessing the database via VBA, you will need to follow my instructions for connecting to the M2M Database. Per the instructions, set up a default module in your m2vevents.prj with the appropriate database connection code. Use the following customized VBA code to populate your new database table with cycle count information. Every computer which needs to run this code (or really any VBA code) must have VBA for M2M installed. Instructions for doing so can be found here here.
Open M2M and make sure that you have a practice m2vevents.prj file loaded. You can do so by typing ALT-F8. Unload the standard, if there is one, and select your practice m2vevents.prj. Then close that window and navigate to Physical Inventory Tag control window via the Transfer Window by typing ALT-T and PHYTAG. Right click on the mnemonic and choose Activate. This will create the module to hold the customizations for the PHYTAG screen. Copy and Paste the following into it:
Public connstr As String
Public Function INIT() As Boolean
If Trim(connstr) = "" Then
connstr = GetCs1()
INIT = True
Public Function cmdArchive_CLICK() As Boolean
Dim CN As ADODB.Connection
Dim SelectStr As String
Set CN = New ADODB.Connection
SelectStr = "INSERT INTO DATABASENAME.dbo.CycleCounts" + vbCrLf + _
" (fcappby, fcarea, fcbin, fccountby, fcdescr, fcentby, fcissuedto, fcjobno, fcloc, fclot, fcpartno, " + vbCrLf + _
" fcrev, fcprodcl, fcsite, fcstatus, fcstkuom, fcuom, fddate, fdexpdate, fliserror, fliswip, fnconvrate, " + vbCrLf + _
" fncount, fnoperno, fnonhand, fnpcnt, fnqtyapp, fntag, fnunitcost, flisprepop, flisprinted, fdfinish, fmdescr, fac, fdTaken, fcudrev) " + vbCrLf + _
" SELECT fcappby, fcarea, fcbin, fccountby, fcdescr, fcentby, fcissuedto, fcjobno, fcloc, fclot, fcpartno, fcrev, fcprodcl, fcsite," + vbCrLf + _
" fcstatus, fcstkuom, fcuom, fddate, fdexpdate, fliserror, fliswip, fnconvrate, fncount, fnoperno, fnonhand," + vbCrLf + _
" fnpcnt, fnqtyapp, fntag, fnunitcost, flisprepop, flisprinted, fdfinish, fmdescr, fac, DATEADD (dd, 0, DATEDIFF (dd, 0, getdate ())), fcudrev" + vbCrLf + _
" FROM INPHYINV"
Set CN = Nothing
cmdArchive_CLICK = True
Now, instead of M2M creating a relatively useless .MBK table and emptying the INPHYSIS table, the customized code will export the cycle count tag information into your custom table and that information can be kept as long as you wish.
In a future article, I’ll provide SQL scripts to analyze the data. Also, I’ve asked a friend to create a sample M2M report with to work with the data and I’ll provide that as well.
I got an e-mail from one of my readers asking me if something was wrong because I haven’t been posting as much as I normally do.
I love this bike.
Most people don’t believe me when I tell them, but I am actually a long distance bicyclist, or rather I was. Nearly 3 years ago, I was riding my recumbent bike, a Burley Taiko, and I was attacked by a dog. I slipped in my stupid bicycle shoes and broke my tailbone on a curb. It still isn’t right, but doctors can’t really do anything about it. It really bothers me when I fly or take long car trips, but the rest of my life is unaffected. Well, that’s not true exactly; one of the most important problems with my tailbone is that it kept me from bike riding.
For the past year, I have been changing things about my life, and I have finally decided to move on. I will be gifting my Taiko to a friend, which was not a trivial investment at around $2300, and for the past several weeks I have been looking at upright bikes. The other day I was fondling a Cannondale Bad Boy, and the sales lady looked at me in an unusual way. In an attempt to explain, I jokingly told her that I felt like I was cheating on my other bike.
In actuality, this is the bike I used to ride 200 miles a week on. This is the bike that I rode centuries (100 mile rides) with my buddy. I was hit by a car twice on this bike and kept going. I finished one century bleeding profusely from the leg. This is the bike that allowed me to compete with everyone else because it didn’t hurt my back, knees, and wrists. This bike is a reflection of me: unusual, misunderstood (you should see the way people stare at you when riding it), larger than life, and totally bad ass.
Anyway, the sales lady asked “Why are you so attached to it?” I replied in a solemn way, “My bike is a her…”
Well, to make a long story short, I bought a used bike the other day. Actually, I practically stole it. I took it to the bike shop yesterday to have it looked over and asked for a kick stand. He looked at me like I was soft in the head and said, “Son, real bicyclists don’t have kick stands. It will add almost a pound of weight to the bike.” To which I replied, “Sir, do I look like a “real” bicyclist?” Anyone who knows me also knows that this is laughable as a pound of weight on the bike is the least of my worries. I can make that up by skipping a meal.
2008 Specialized Sirrus
I got out on my “new” bike and rode about 10 miles. It was epic, a meditative experience. God, how I missed riding. I only wish my riding buddy was down here to ride with me.
My goal by the end of the season is to ride a century. In fact, I want to accomplish one of the most difficult centuries around, the Hotter ‘N Hell. It will take a heck of a training regimen including a fairly strict diet and bicycling nearly every day, but I’m going to do it. I’m on the internet this morning buying special socks, shorts, shirts, clipless pedals, etc.
My knees hurt this morning, the wrists not so bad, and I feel like I assaulted by that awful bicycle seat; but I will do it.
I can do anything.
This is just a quick F.Y.I. that the first service pack for both SQL 2008 and M2M Version 6.0 were recently released.
You can download the service pack for M2M 6.0 here and the SP for SQL 2008 here.