Archives

Resorting M2M List Boxes

Recently I came across the following problem: A company I assist had over 100 different shipping options in Made2Manage. You might question why a company would have that many shipping options, but when you consider there are many different options for a single carrier, they add up. For example here are a few of the options for UPS:

  • UPS Express Critical
  • UPS Next Day Air Early A.M.
  • UPS Next Day Air
  • UPS Next Day Air Saver
  • UPS 2nd Day Air A.M.
  • UPS 2nd Day Air
  • UPS 3 Day Select
  • UPS Ground

The list box is from the Sales Order (SO) Screen. The problem is that when you have over 100 shipping options and they are put in by your sales or customer service people as they are required, they aren’t in any specific order. It becomes an annoying hunt through the list every time you want to enter a new order. It’s interesting to note that the shipping options aren’t in order on a brand new install by default.

Default List Box

Original List

Luckily, M2M has a screen to manage this and you can spawn it by right-clicking on the list box while in edit mode. You then get the following window:

Default Edit Screen

List Box Edit Screen

I highlighted the area where you change the display order. A new order number must be entered for each item you wish to shift in the list. If you have more than a few items, this becomes tedious and wastes a great deal of time. Furthermore, when someone enters a new address after a re-assignment marathon, a new order number must be entered for each item requiring another shift. I should mention however, that if there are several list items with the same Display Order Number, they will be grouped so every item need not be changed.

I was asked to re-assign all of the shipping options alphabetically. Being a member of the genus species homo logicus, I knew I could code a better way to do this. So, I created the following code to automate the process. 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 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.

IF EXISTS (SELECT *
           FROM   SYS.OBJECTS
           WHERE  OBJECT_ID = OBJECT_ID(N'[dbo].[cspopupTEMP]')
                  AND TYPE IN (N'U'))
  DROP TABLE [DBO].[CSPOPUPTEMP]

SELECT *
INTO   M2MDATA12.DBO.CSPOPUPTEMP
FROM   M2MDATA12.DBO.CSPOPUP
       
UPDATE C
SET    C.FNORDER = (SELECT COUNT(* )
                    FROM   [M2MDATA12].[DBO].CSPOPUP
                    WHERE  FCPOPTEXT < C.FCPOPTEXT
                           AND [FCPOPKEY] = 'SHIPVIA')
FROM   [M2MDATA12].[DBO].CSPOPUP C
WHERE  [FCPOPKEY] = 'SHIPVIA'

--drop table m2mdata12.dbo.cspopupTEMP

The first 8 lines are a simple backup for the table before I begin. The next several lines consist of a relatively simple SQL Update statement which derives its value from a nested sub-query. SQL coding isn't like "normal" coding in that when done right, everything is executed in sets. A set is a group of data, and it must be acted upon as 1 unit. 'C' is simply an alias I chose for [M2MDATA12].[DBO].CSPOPUP in the main query. It shortens the query, makes it easier to read, and differentiates the fields in the nested query from the main query. Note my first WHERE statement where I compare FCPOPTEXT (the actual text that shows in the list box) from the nested query to the main query to determine the order number. I am setting the FNORDER equal to the count of items in the list that are alphabetically before the current item in the list. As it goes through my set, it then changes the FNORDER to match the order that item is in alphabetically in the set. The last line of code is commented out as it drops your temporary backup table. After you are confident that the script worked, you can run that line separately.

This code can be adapted to change most of the list boxes on the SO screen. Here are the results of my testing:

Re-sorted List Box

Re-sorted List

Feel free to ask questions, or suggest other issues to tackle with SQL, in the comments.

Free Visual FoxPro Videos

I appreciate free learning opportunities and will periodically share them with readers of this blog, both of you *grin*.

Some time ago I came across the site of Garfield Hudson. Of particular interest to me were his free VFP videos.

The videos are well done, and are worth viewing even for those who work with the SQL versions of Made2Manage. At least for the time being, Made2Manage reports are still created using Visual FoxPro. They are particularly helpful for those who started using VFP 6.0 and are now using 9.0 in that he gives a very good tour of the “new” IDE and such.

Vegas Confessions with Many Pictures (Safe for Work)

Although nothing happened like the last time I went to Vegas, I still managed to get myself in few weird situations.

I stayed in Vegas until Sunday evening but during the conference I was too tired to do much else besides have dinner with co-workers afterward. However, I started venturing out Friday and enjoying the sites of Vegas.

Bellagio Fountains

I love the Bellagio Fountains

Since the fountains aren’t far from the MGM, I didn’t run into too much craziness. I did run into a drunk guy in the MGM Saturday morning as I was heading out to go on an air and ground tour of The Grand Canyon. He was staggering back and forth towards the elevators as I was leaving them. For some reason, I attract drunk people. I think it’s because I don’t drink, and they feel that I’m not having a good enough time without it. Anyway, he confronted me, shook my hand, and offered me some of his Gardetto snacks which he was carrying in a fairly large bag. He had these things crushed all over his face along with saliva and god knows what else. His speech was very slurred and hard to understand.

Me: Do I know you?
Dude: Naw, want some chips???
Me: No, thank you. ::I try to walk away::
Dude: What? You too good to eat my chips???
Me: No, I don’t like them.
Dude: F’ You!
Me: Dude, I’m diabetic. Leave me the F’ alone (this was a lie by the way)
Dude: That sucks man.

I managed to get away from him as I don’t want to miss my tour of the Grand Canyon via small plane and bus. The plane trip alone was worth the price of admission, as the views were amazing. Unfortunately, my small camera didn’t deal well with the long distance shots very well.

Canyon

The tour includes access to the Grand Canyon Skywalk.

Skywalk

Let me tell you about that Skywalk. It’s an amazing attraction owned and operated by the Hualapai Indian tribe. As you can see, it’s a half circle that protrudes more than 50 feet from the edge of the canyon. It’s an exhilarating experience for everyone who walks it, except me. Many who know me know that I’ve always had a terrible fear of heights. Why did I decide to walk out over 4000 feet above ground on a glass Skywalk? Well, if you read about my last Vegas Adventure, you know that I don’t always make the best decisions. So, I get ready to walk this thing. They inform us that we have to lock our cameras, cellphones, and Ipods in keyed boxes before entering the Skywalk. So, I ask this Native American Lady why I have to give her my items, and she explains that they don’t want one of us to drop our camera and break this supposedly unbreakable Skywalk. I asked if she wanted my car keys, which are heavier than my camera, and she replied no. I then asked her if I could keep my ball pean hammer when I went out, and she looked at me like I was crazy but answered, “yes, that’s not a prohibited item.” I’m sure they had safety in mind and not the $100 they were charging people for personalized Skywalk photos after their walk. Everything is a scam. They even make you walk through an electronics detection machine to make doubly sure you don’t try to hide your terribly dangerous imaging devices.

Anyway, this thing is terrifying. One of the camera operators told me that earlier that day an older woman completely lost it on the Skywalk. She started sobbing, fell to her hands and knees with her eyes closed, and refused to move. They had to physically remove her from the Skywalk. In comparison, I didn’t do that bad. I didn’t walk the Skywalk so much as shuffle it. I had both hands on the outside railing and slowly, very slowly, shuffled sideways along it. As people can probably tell, I’m not shy, nor will I walk along pretending everything’s fine when it’s not. So, I’m slowly moving along and people are teasing me for being afraid. It can hold 70 tons, blah blah. Here’s the funny part. They criticized me because I’m a chubby guy that’s afraid of breaking the glass due to my weight, but not one of them would pass me. They’re pretending not to be afraid, but nobody wants to stand on the same piece of glass as the fat guy. Such hypocrisy.

I slowly shuffle along to the other side and enjoy an incredible view while fighting abject terror. The camera guys repeatedly reassure me that it’s indestructible. However, as I get near the end I see that one of the glass plates has been replaced by stainless steel version. Of course I ask about this and they tell me that one of the pieces broke and needs to be replaced. Yeah… indestructible…

Near the Skywalk you can take pictures of the canyon and there are no guardrails. This was scary as well. You also get a great view of Eagle Point which is sacred to the Hualapais.

Eagle Point

See the eagle?

After the Skywalk tour, we were bussed to another beautiful area to have lunch with the Hualapais. I thought this was supposed to be reasonably authentic American Indian food. Apparently the Native Hualapais, who survived for centuries in The Grand Canyon, ate lots of wild growing Macaroni and Cheese. ::snicker:: Actually, the food wasn’t bad, though generic, and it was all you could eat so I couldn’t complain much.

I ran into a fellow admin from the conference and hung out with her and her hubby there. I should mention that they were both in shape (he is a SWAT team member) and I was hard pressed to keep up with them. Remember the post where I said I wouldn’t be mountain climbing again? Well, I lied.

Canyon

See those tiny, multi-colored specks at the top, those are people. I climbed up that with the married couple gasping for breath most of the time. It was worth it, as the views were amazing.

Canyon

Canyon

Canyon

Canyon

Remember that most of these pictures feature me standing right on the edge. I cannot describe how unnerving this was. I kept imagining that a quick draft would cause me to fall to my death.

Canyon

Me pretending to fall.

After saying goodbye to my mountaineering friends I flew back to Vegas. I intended to go back to the Bellagio Fountains, but I grabbed the wrong bus and went to the Fremont Street Experience instead. I rode The Deuce, which is a system of double decker buses that traverse the strip, and you can ride all day for $5. Remember when I said I attracted drunk people? Well, try to imagine this situation. The bus was completely full with people standing everywhere inside it. I’m surrounded by party folk and have to repeatedly apologize to the women around me for accidentally bumping into them every time the bus jerked back and forth. Somewhere between my hotel and Fremont Street, this huge guy gets on the bus. Picture a 6’5″ tall Donald Gibb, assuming he had been living in a dumpster drunk for a year.

Ogre

Ogre – Nerds!!!

This guy could barely stand and was holding on with both hands. He keeps yelling out things that have nothing to do with reality. He tries to hit on one of the ladies near me, who is in her late 30’s, conservatively dressed, and obviously someone’s mom. I swear this is the truth, but swaying on his feet and slurring his speech, he told her, “I got dressed up today…… trimmed my nose hair and everything…. ::burps:: but I cut the inside of my nose…. and it bled like a %#@%@. You gotta boyfriend honey???”

He turns away to yell at the driver, and the mom begs me to switch places with her so she can stay away from this guy. I reluctantly agree. He’s mumbling about more stuff, and I am trying to ignore him. He starts humming a weird and broken tune really loudly. I know I’ve heard it but cannot place it because he is humming it so badly. Slowly, I get it. He’s humming the old Muppet Show theme.

It’s time to play the music
It’s time to light the lights
t’s time to meet the Muppets on the Muppet Show tonight.

Just as I realize what he’s singing he gets in my face, yelling a question at me. He has to repeat it 3 times, but I realize what he is asking is “Dude, what is your favorite Muppet band member?” I try to ignore him but he won’t let it go and he’s getting increasingly more animated about it. He tells me that his favorite is the dude with the saxophone and absolutely needs to know mine. Finally, desperate to get this guy to leave me alone I yell “Animal rocked dude!”

He looks at me like I had just explained the Theory of Relativity and promptly vomits at my feet. I told you drunks loved me. The bus driver throws most of the people off the bus to clean up the vomit and I had to walk the rest of the way to Fremont Street. My feet hurt so badly by the time I went home.

Anyway, I watched the Don McLean American Pie show as well as A Tribute to Queen. It was as amazing as the last time I saw the experience I also enjoyed the live musical acts playing on the street, and I blew money on souvenirs for my fellow computer geeks at work and my girlfriend. The return ride on the Deuce was blissfully uneventful.

My last “story” to share was near the end of my trip. All of the tours I took were filled with foreigners. On my air tour, I was the only American. So, I got on the hotel elevator with a bunch of folks from Europe and a couple who looked American. I heard the man say something to the woman, and it sounded like a Chicago accent. The Europeans got off the elevator and I continued to ride up with the couple. I turned and asked the man, “Have you noticed how many foreigners there are here? At times I’ve felt like the only American visiting Vegas. Any idea why we’re so outnumbered here?”

He looks at his wife, then at me and says, “Sir, I have no idea. We’re from Canada.”

Vegas Reflections – Consona Connect 2008

Elvis

Viva Las Vegas

SQL Administration

Don Rudo was once again on his game and enthusiastically talking about raid arrays, transaction logs, and BACKUPS BACKUPS BACKUPS!!! I had to pop in and out of his sessions to attend the others, but what I saw was very informative. There were a couple of interesting things I’d like to point out. Don himself mentioned the importance of having a test server. Also, while he did admit that Made2Manage’s use of the SA login wasn’t required, the vast majority of customers use it.

VBA and FastForms

Susan Edwards, Customizations Team Leader for Made2Manage, gave an excellent presentation on VBA and FastForms. One of the reasons I enjoyed it so much is that she fully admitted the limitations in doing this work and how it is far from an exact science. She also provided useful code that customers can edit to perform any number of different customizations. I took full advantage of her time and asked many questions about issues I’ve had creating customizations. The regular educators, do not write code for a living so they are much less likely to have an answer for any questions beyond the most basic level. For this reason, I urge Consona to use her or another consultant to teach classes at the next User Conference. For that matter, I’d like to see them offer advanced VBA/FF classes periodically which should be taught by consultants as well.

Shop Floor Data Collection and Manager

I attended the Shop Floor Data Collection Product Overview Session, however the Shop Floor Data Collection Roundtable never occurred. Instead a session was held by a John Long from W.S. Darley and Co., a Made2Manage customer, who showed us how he modifies report layouts to make them work for various business procedures. It was certainly interesting, but didn’t really apply to me.

For those who are using Shop Floor Data Collection, there are improvements coming with version 6.0. The product will run as a service rather than a program you need to open, an improved user interface, and several new tracking options and fields.

Product Roadmap for M2M and the .NET Future

This session was led by Frank O’Nell, Made2Manage Product Manager. He was clearly enthusiastic about 6.0 and seems very proud of the direction M2M is taking. Made2Manage has off-shored its development to India and he felt the product was better for it. He indicated that the Feature Teams have had a positive impact on the product as well.

There will be significant enhancements to Field Service, CRM, Shop Floor Data Collection and Manager, and others as well. Also, several of the screens have changed or been phased out.
Customer Inquiry (SYCSL) and Customer Screen (CUST) were specifically mentioned.

These changes will likely cause problems for your VBA/FastForms customizations. Of particular concern is Made2Manage’s addition of tabs to certain screens and their inconsistency of implementation. For example, they added an additional tab to the Sales Order (SO) screen and placed it to the left of the User Defined tab. User Defined is still page 4 (the path that the form is addressed at) and the extra tab to the left is named page 5. If you’ve created an additional tab already (and it’s named Page 5) what happens when you try to migrate? Even more of a concern is that on the CUST screen a tab is added, but the User Defined tab is being re-named to page 4 and the additional tab to the left will be page 3. There’s no excuse for the inconsistencies. Upon my first day back, I asked to be added to the beta testing group so I can evaluate these issues and plan accordingly. There’s no guarantee that my site will be accepted into the beta program.

Version 7.0 is supposed to be the .NET product that we’ve all been promised for several years. Mr. O’Nell explained that there were two ways to deal with the jump to .NET. Made2Manage could simply translate the current version (6.0) into the new technology platform or re-write it from scratch. Made2Manage elected the former path as they felt that re-writing the product would take too long and be cost prohibitive. He also indicated that Made2Manage wasn’t really going to translate the product, but rather they were going to write programs that would transform the code for them. Also, he claimed that all of the VFP reports written prior to 7.0 would continue to operate as normal as they had some kind of “trick” implemented. Trick was the word he used. Perhaps even more surprising, is their claim that you will be able run the old VFP M2M version along side the new .NET version on the same network because they will use the same SQL database. This would allow you to upgrade your computers one at a time, rather than all at once. The plan for 8.0 is that it would be .NET only and presumably they would stop improving all other versions.

What are my opinions of the upcoming versions? First, I am both enthusiastic and concerned at the same time about the 6.0 product. I’m anxious to drop VFP and move to a fresh, new environment. However sweeping changes, though necessary, will require myself and others to refactor our VBA and FastForms customizations. In addition to that, some user training will be required with this new version. In the past, users could use the system as they had before and only delve into the upgrades when they were ready. In this version, it seems to me that there have been substantial changes rather than simple additions, so the training will be necessary.

Second, as I mentioned above, I am disappointed that M2M still isn’t consistently implementing their changes. It seems to me that during the initial planning of 6.0, when additional tabs were being planned for various screens, that someone would suggest a consistent way of naming them. Also, I asked 3 separate Consona presenters what would happen to the tabs I added to those screens with FastForms and none had an answer. Since this product is set for GA release in a little more than 2 months, this concerns me.

Third, Made2Manage will still need to be manually re-installed on every single desktop for Version 6.0. This can be particularly tedious when it follows the actual data migration, typically late into the evening on a Saturday. It’s 2008, can’t Made2Manage be pushed from the network by now? Also, M2M will still ask you my favorite install question.

Fourth, I’m not sure why anyone would really want to run both 7.0 versions on one network if everything works as seamlessly as they claim.

Finally, when I asked a few questions about upcoming versions, specifically regarding the VFP reporting “trick,” Mr. O’Nell said that he couldn’t explain how it would be done but said, “trust me.” I responded in kind that if our language was logical, trust should be a four letter word. The “trick” sounds suspiciously like the wrapper code for VFP that was implemented in the SQL upgrade. That was supposed to “work” as well, and it sort of did. Old reports ran, but many of them took ridiculous amounts of time to do so. Furthermore, if they are not being consistent in functionality in version 6.0, how can I trust that they will be able to write a program which will in turn, write the next version of M2M? I seem to remember Microsoft trying to pull this off with a translation program which would port your code from Visual Basic 6.0 to Visual Basic .NET and it was riddled with problems. I find it hard to believe that M2M will fare considerably better than Microsoft in this regard.

After what I heard, I cautiously look forward to the future but I don’t intend to migrate quickly. I’ll let others work the bugs out and I will be testing any new versions thoroughly before implementing them.

I’d like my readers, both of them, to feel free to share things that I failed to mention in the comments.

In my next post, I’ll share with you some of the interesting and funny things I saw and did in Vegas.

What do I hope to see/learn at Consona Connect 2008?

Over the years, I’ve been to many of these Users Conferences. Many of the sessions in past conferences have been the same content you could get over the net with the Made2Manage University. I’m not suggesting that the presentations weren’t done well or that the information presented wasn’t valid, it’s just that I’d seen it all before.

However, this year is supposed to be different in that the sessions will be taught by Made2Manage consultants rather than teachers. I hope this will change the content presented in turn.

So, what am I going to be doing at the conference?

  • Learning to implement Shop Floor Data Collection and Shop Floor Manager.
  • Listening about the M2M road map and the .Net future.
  • Listening to Don Rudo talk about SQL Server Administration. I suspect that I already know what he will say as I have seen at least 5 of Don’s SQL presentations. He’s a lot of fun to watch because he’s so enthusiastic and animated about it.
  • I’ll attend the Enhanced Process Flow Thru Innovative Form And Reports Design session.
  • I’ll try to get some VBA questions answered, if indeed there are answers.
  • Attending the Migrating to 6.0. presentation.
  • Networking with companies affiliated with my employer as well as with other Made2Manage admins. You can never have enough contacts in this business to help you solve problems associated with Made2Manage products.

I’m posting a picture of Anakin and I, so if any of you are going to the conference, stop me and we’ll talk.

David and Anakin

What will I not be doing this year? Mountain climbing, of course.

What happens in Vegas… The Final Battle.. or Something. (Part 3)

*** Note: This is a 3 part post and they are shown in the order I published them. Please read parts one and two below before reading part 3. ***

Try to imagine it. You’re stuck in the middle of the night on top of a mountain surrounded by short pines without food, water, flashlight, or warm clothes.

I carefully and slowly walk around the top of the mountain with the cell phone held up high. I’m hoping to get a signal, but it’s dangerous to walk around in the dark like that. I sit down to think about my situation. I just can’t bring myself to consider climbing back down the mountain. There’s no way in Hades I want to go plunging down the side of the mountain again tonight. I was lucky not to incapacitate myself the last time.

So, I continue to slowly move around the top of the mountain and watch the cell. After what must be hours, I get a slight signal. No bars per say, but the red light turns off. I immediately dial 911. Unfortunately, 911 location wasn’t working at this time so my call got routed to West Michigan. I quickly explain to the Michigan State Police what has happened, and then I have to wait while they transfer my call across the US, praying all the while that the call doesn’t drop.

As soon as this kind lady, who sounded like my grandmother, answered the phone she immediately demanded my cell phone number and instructed me not to call anyone if disconnected. She would try me back. As soon as I give it to her, the call drops. In fact, it drops so suddenly I wondered if she had gotten the number. So, I sit down on a rock, watch the stars, and think about the terrible turn my life had taken this day.

To be honest, I cannot remember exactly who each person who helped me was and which department they worked for so I am making up names. After at least 30 minutes, the phone rings. The kindly elderly lady interviews me.


Me: Hello!
Dorothy: This is Dorothy of the Las Vegas Police Department. What is your situation?
Me: Well, my name is David Stein. I’m a tourist from Michigan and I’ve done something very stupid. I’m lost on top of a mountain, I think it’s Charleston, outside of Vegas.
Dorothy: Are you injured?
Me: I’ve twisted my ankle pretty badly. It’s swelling, but I’ll live.
Dorothy: Do you have water? Are you dressed warmly? Do you have a flashlight?
Me: (sheepish) Umm… none of the above…
Dorothy: Sir, what are you doing in the desert without proper clothing, water, or a flashlight?
Me: Ma’am, I’m stupid. I’m from Michigan and I’ve never been in the desert. Please save me.
Dorothy: Don’t worry, we’ll save you. Use your phone to get your GPS coordinates and I’ll call you right back for them.

I go through several attempts of getting the numbers and struggle to remember them until she calls back. I answer her call:

Me: blurts out the coordinates.
Dorothy: Alright, I’ll have Sargent Smith from Air Rescue call you back. Sit tight.
Me: Yes ma’am. Please don’t forget about me.
Dorothy: laughs… don’t worry.

So, I wait about another hour freezing on top of this mountain and listening intently for mountain lions who I am sure are just waiting for the right time to devour me alive. I think I’ve been away from my car for at least 4 or 5 hours now. Finally, the phone rings. Try to imagine Sargent Smith as an older leathery guy like John Wayne. He certainly sounded like it.


Me: Hello.
SS: This is Sargent Smith of Air Rescue. Is this David? (I swear the man asked this)
Me: Yes Sir.
SS: What is your condition son?
Me: Well, I’ve been out climbing mountains for about 5 hours. My ankle is twisted and swelling. I’ve had nothing to eat or drink, and I’ve got to pee really bad. (I added that last bit as a joke because I really needed to break the tension.)
SS: Don’t you have water?
Me: No Sir. (I cringe because I know what’s coming next)
SS: Son, why on earth are you hiking in the desert without water?
Me: Sir, I didn’t intend to hike. I got lost. I’m an idiot from the Midwest, who’s never seen a mountain before. Please save me anyway.
SS: Well, since you have to pee, you’ll need to catch that and re-drink it. (I swear he said this).
Me: (Incredulous) Umm… how long am I going to be stuck out here? You have got to be kidding me about drinking pee.
SS: Laughs. Yeah, I’m just fooling you. We do that to all the lost people. However, it will be a couple more hours before I can get you out.
Me: Sir, I’m not trying to sound unappreciative but why so long?
SS: Because hikers just found some guy on the other side of Vegas who was lost for over 2 days without water and he’s hallucinating. I’ll call you when the helicopter gets near you.

So, I wait another 2 hours, until I hear the helicopter’s wump wump wump… As soon as I hear the helicopter the phone rings.


SS: Alright Son, they are looking for you. Can you shine your flashlight at them?
Me: (cringing again) Sir, I don’t have one. Again, I’m an idiot, please save me.
SS: What are you wearing?
Me: A black t-shirt and ripped dark green shorts.
SS: How are they going to see you? Why don’t you just hide under a tree?
Me: Laughs. Well, I can signal them by using my camera to flash at them.
SS: Good, do that. I’ll be in contact with the pilot. Good luck.

So, I repeatedly take pictures of nothing but the dark outline of the copter and it hovers above me. A real mountain man comes down on a line and winch kind of like this.

Rescue Harness

I have a conversation with the Mountain Man. We’re yelling as to be heard over the rotors.

MM: Sir, I’m Jeff and I’m here to get you out.
Me: I’d greatly appreciate that Jeff.
MM: Put this harness on like I have mine (and he helps me).
MM: Are you a hiker or mountain climber?
Me: Do I look like one? (I ask this in a joking way as if you’ll remember, I am a large person)
MM: Well, how did you get up here with a bad ankle.
Me: Umm.. I was terrified. I could have carried that helicopter up the side of the mountain if I had to.
MM: How much do you weigh?
Me: Around 250lbs.
MM: Oh no. That winch isn’t rated for that much weight. We’ll have to have you dangling out on the line when we fly out. You won’t actually be inside the helicopter.
Me: (I was just about at my emotional breaking point) You have got to be freaking kidding me???
MM: Yeah I’m messing with you. It’ll pull you inside the copter.

So, he goes back up into the copter and I wait for the line to come back down. I hook myself up and I slowly raise into the air. I wish I could say that I handed it like this:

Robert Downey Jr.

Robert Downey Jr. Air America

However, the truth is that I am scared to death until Mountain Man gets me into the copter and buckled into my seat.

Amazingly, the pilot doesn’t take me straight back to my car. They buzzed around Vegas and gave me a tour. It was an amazing ride. At this point I asked the Mountain Man how much the fine would be. I figured they gave me the tour because there was going to be a huge “stupid tax” I would have to pay and I was more than willing to do so. Again, I was amazed to find out the rescue was paid for by the tax payers of Nevada. They did tell me that I could donate to a fund for new equipment as the Mountain Men were volunteers. I did so after I returned to Michigan. They sat me down right near my car and suggested I go to the hospital to have my leg looked at.

So, why did I tell this story? Well, because parts of it are really funny, though I suspect it’s better when I tell it in person. Also, it was a great experience because I learned something about myself. I learned that I can go farther than I thought, and it’s important to test your limits from time to time. For another, I learned that I need to watch more SurvivorMan.

Finally, I learned to take tours and refrain from getting myself into stupid situations.

In fact, this year I’m going to skip the rental car.

What happens in Vegas… Part 2

“It is an ironic habit of human beings to run faster when we have lost our way.” -Rollo May

Spoken in an old time radio announcer’s voice:

When last we left our “hero”, he had left his car to take a picture of the desert in the mountains near Lee Canyon. Of course he managed to get himself lost, and couldn’t find his way back to his car. As he became more and more agitated, he moved faster and less carefully until…..

Anyway, I quickly climbed down the mountain. I had to be at least 500 yards from the valley floor when I slipped and took the express elevator to the bottom, flat on my behind. Do you remember the movie “Romancing the Stone” and the muddy fall to the bottom of a mountain? Both Kathleen Turner and Michael Douglas slip to the bottom and end up fine and dandy.

Romancing the Stone

Douglas lands on Turner

Aren’t movies wonderful? The reality is somewhat different. I hit the ground at incredible speed, my knees buckled, and I fell forward onto the rock floor on my face and head. I think I blacked out. When I came around, I had this incredible pain in my chest. I pushed myself up to a sitting position and found something embedded in my chest.

Cactus

I landed on something similar to this.

Instinctively I reached towards my chest to determine what was wrong and damaged my hand on the spines. I had to use my car keys to pop the cactus ball out of my chest. So, now I’m at the bottom of the canyon and there is no stream or road. It’s nearly pitch black and I can’t see much as there isn’t any moonlight.

At this point I have a definite shift in perspective from a self reliant, confident man to a state perilously close to panic. I stopped thinking about being embarrassed to be lost, and started thinking that my name might be in the news in a few years. I could literally die out here.

I take stock of my situation. I’m very cold, bleeding from the chest and hand, completely lost, disoriented, in the dark, and already thirsty, but at least I”m not seriously injured. I stand up and I realize that I have no idea which mountain I fell down and therefore which direction I came from. I also realized one more important thing. I was wearing comfortable shorts on the way down the mountain. Somewhere along the way, they ripped almost completely up my backside. Luckily I was wearing some novelty boxer shorts, I think they were Super Mario Brothers (yes, I am a geek), and the pattern kept them from tearing. So, Mario literally saved my a$$.

I decide right then and there that I am not spending the night in that canyon. I’d like to pretend that this was a movie like decision where the hero has had enough and just won’t take it anymore. This was more like I’m cold, hungry, and afraid and don’t want to eaten by rabid wild animals. I check my cell phone and unsurprisingly it has no signal.

So, I start back up the mountain. The climb is exhausting, as to call me stocky would be charitable. So, I’m dragging my chubby butt up the mountain in the dark and I step into a crevice and twist my ankle pretty badly. I flopped down on Mario on the side of the mountain to check my ankle and it’s rapidly beginning to swell. It is at this time that my survival drive really kicks into overdrive and I begin to get extremely angry and determined to get through this. I hobbled over to one of the stubby pine trees and broke it off at the base. I stripped the branches off, shoved it into my armpit, and used it as a crutch to continue climbing. At this point I am no longer calling out in a manly way, I am screaming for help.

I’m in extreme pain, but at this point it doesn’t matter. I’m aware of me making pained noises and swearing, but I keep climbing. Every so often I stop, take a breath, and check my cell still without signal. I am hoping that as I climb upwards, I will find a road or get a cell signal. I’m not sure exactly how long I have climbed and I’m using those crappy pines as hand holds because the incline is steep enough that I don’t trust my balance.

I manage to make it to the top of the mountain. This is not good news as it means I have not run into a road, any kind of help, or a cell signal.

I can tell you that I have never felt so helpless as when I reached the highest point on the mountain and still had no signal…..

Will our “hero” survive in spite of himself? Part 3 continues here.

What happens in Vegas.... Part 1

This article isn’t about the User Conference which was held in Vegas a few years ago. This is about me and my life and death struggle against my own stupidity.

Some have found this story hard to believe, but I swear it is true. This was my first trip to Vegas, so I decided to stay over for a week and enjoy some of what Vegas has to offer.

However, I don’t drink or gamble. I don’t have any spiritual issues with it, but it just doesn’t interest me. Instead I went went on an ATV ride around the rim of the Grand Canyon, a similar trip to the Valley of Fire, drove out and toured Hoover Dam, and other fun stuff.

However, little did I know that a simple car trip to Lee Canyon and the mountains outside of Vegas would be the most memorable part of the trip. I had a few hours to kill in the evening after the conference was over and I decided to take a drive to the top of Mount Charleston and around Lee Canyon.

Lee Canyon

Beautiful, isn’t it?

Anyway, on the way up to the top, I passed a scenic overlook and intended to take some pictures there of the sunset on my way back down. When I got to the top, I was surprised at how cold it was up there. I was wearing shorts and a t-shirt and the temp went from 100 degrees when I left Vegas to well below 70 at the top of the mountain.

When I got back to the scenic overlook there had been a violent crime out there and the police had it roped off. I went back up the mountain until I got to a place where there was a wide shoulder and I could park. I grabbed my camera, cell phone (which wasn’t working well in the mountains), and climbed up the shoulder to get a picture. Now, keep in mind the mountain is covered with these 6 foot tall pine trees. They’re all water deprived, wind tortured, and look like they’re suffering from radiation poisoning. I kept climbing, hoping to get to a crest, so I could take a picture.

Now, as someone from the midwest, I remember news stories over the years about motorists who did something stupid in dangerous environments. You’d hear something like, “Tuesday, hikers found the remains of a guy from Michigan who was reported lost 2 years ago. In skeletal hands were found a cell phone, camera, and a an identification book for desert rattlesnakes. Authorities have no idea why he left his vehicle in the desert.” I, like my friends, would ask, “who could be stupid enough to do that?”

Well folks, I am that dumb. I tried to walk in a straight line, but there were no landmarks other than identical scrubby pines. After a few minutes of climbing/walking I realized I wasn’t going to find a clearing so I turned around and headed back. Of course, I never found my way back. I remember exactly how I felt.

10 minutes in…
I still thought I would find my way back to my car.
20 minutes…
I knew I was lost and was extremely angry with myself for being so stupid. I wasn’t worried yet and didn’t call for help for fear of looking stupid. I got myself into this, and I could get myself out.
60 minutes…
It’s getting dark and my anger is rapidly turning to fear. At this point, I am trying to talk myself out of being afraid. I’m a man, and I can handle myself. However, I had already visited the Natural History Museum and had seen the stuffed Mountain Lions which inhabit those mountains. Around this time, I start yelling (in a manly way) for help. The roads don’t typically go over the tops of the mountains, but between them. So, I begin to climb down the down the mountain in the hopes of finding the highway.

Then I fell….

Will our hero survive the fall? Part 2 Continues Here

Code is never finished, only abandoned.

I enjoy reading a little bit right before bed, and I’m a big fan of Uncle John’s Bathroom Readers. Where else can you read short articles about Star Wars trivia, how the invention of currency came about, and the famous hoaxes of the last century all in one sitting?

Bathroom Reader

Anyway, I found the following quote in the book pictured above.

A poem is never finished, only abandoned.- Paul Valery
French critic & poet (1871 – 1945)

I marked it because it made me think about the way I work with code. Whether I’m creating VFP or Crystal Reports, writing VBA code, or transact SQL, I regularly go back and improve my work.

There are several things which may trigger me to do so. I may have learned a new programming technique or perhaps something just flashes in my mind and I realize a better way to do something I’ve done in the past. I feel compelled to go back and “fix it.”

It’s this commitment to constant improvement which makes you a better admin and programmer.

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
WHERE       FTDATE <= DATEADD(DAY,-31,GETDATE())

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
WHERE       FTDATE <= DATEADD(DAY,-62,GETDATE())
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.

Page 31 of 33« First...1020...2930313233