August 16, 2005

A Trucking Company?

I received a call from a prospect. They apparently got our name from the yellow pages in the phone book. Wow, the phone book. I think that’s the first real hit from the phone book in five years – or more.

The prospect apparently hauls propane and they want to track all the pending trips. Currently they are using Excel spreadsheets for this. It sounds somewhat confusing.

I’m not sure they are a good fit for our business, but being the database geek I am, I feel compelled to look at their challenge. The good news is they are only about a mile from my office.

Posted by Ted at 10:39 AM | Comments (0)

August 15, 2005

Client Is Back Up and Running

I received a call from a client Friday afternoon. Their server had gone down and their hardware guy had been working all day to get it back up and running. The problem had something to do with the SCSI-RAID adapter. They had been unable to enter anything at all on Friday.

Over the weekend I suddenly had self doubts. Had I set up the SQL Server backups? I could not remember setting them up.

This morning I showed up. The server was back up and running, but Great Plains would not run. That was to be expected. The first thing I did was to look in my backup folder to see if my SQL backups were there. There were! Breath sigh of relief.

Great Plains was already installed and would run IF I could get SQL running. I uninstalled SQL Sever and then re-installed it and then patched it the SP3A. My thought was to create the Dynamics and company databases and then restore from backup to them. The most current backup was from Wednesday night. That meant they would need to re-enter all of Thursday’s work.

After creating the Dynamics and company databases, I looked in the folder I had pointed them to and there were the database files from the previous install. This was very good news. Not only as the original data there, but the dates on the files showed them to be from Thursday evening.

I used the SQL Query Analyzer to detach both databases (sp_detach_db). Then I used it to re-attach (sp_attach_db) both debases only this time pointing to the existing database files. The SQL Sever Books Online was a big help with this.

After performing this, I ran a couple of queries to see if I could read the tables and data. I could! Excellent!

One last issue was user logins. Although the users were in the Dynamics database, they were NOT in the SQL Server Logins database. I had on my thumb drive and script with documentation that I had used for a client that had his sever stolen. I reviewed the docs and ran the script. All the users were good to go, except their passwords were set to blank.

I had the users log in and set their passwords and the client was back in business.

Posted by Ted at 04:48 PM | Comments (0)

June 07, 2005

How to do Poor Man's MRP in Great Plains

I did a Crystal report for a client that they use to determine what to order. It took us quite a bit to develop this report, but it has proven to be very handy. It uses historical sales data as well as inventory availability to recommend what to buy.

The report had one drawback. They use the Bill of Material module to do some very simple assembly. The report did not reflect those requirements. It now does.

We set the vendor for all the BOM items to the client. That way they can run the reorder report to determine what they need to assemble.

They then put Bills into Microsoft Great Plains Assembly Entry. Those assemblies are saved as “Not Released.” Those pending production batches are treated as On Order quantities by the re-order report so they don’t plan more than they need.

The report also summarized the components used in those production batches and includes them as requirements along with other inventory availability.

Now they can run the report to decide what to make, enter what they plan on assembling, and then run the report for the components to see what they need to buy.

I call it a “Poor Man’s MRP System.” However, if their manufacturing requirements were more than just simple, they would need to purchase the full manufacturing suite.

Posted by Ted at 05:16 PM | Comments (0)

June 06, 2005

Update Client Financial Statements

One client is running several companies in their Microsoft Great Plains. I spent some time with them formatting their financial statement. They then wanted to copy those financial statement formats from the one copy to the others.

I queried Microsoft’s Tech Support and they identified the following tables that I needed to update: AF40100, AF40101, AF40102, AF40103, AF40104, AF40105, AF40106, AF40107, AF40108, AF40109, AF40110, AF40200, and AF40201.

I made sure there were not financial statements set up in the other companies. Then I used the SQL DTS (Data Transformation Services) function to copy the data from the one company to the others.

It worked very smoothly.

I know I could have set them up with FRx. It does a very nice job of formatting financial statements. But I find that for clients with simple financial statement needs, the Advanced Financial report writer in Microsoft Great Plains seems easier for them to understand.

My more technically savvy users take to FRx quite nicely.

Posted by Ted at 08:37 AM | Comments (0)

May 09, 2005

Item Number Change

I’m going to make an attempt to change the last item number tonight. I’ve been back and forth with Microsoft about the item in question and the ASI00102 table. Now they tell me this table is an AnyView table from Accountable Solutions.

The client is not running AnyView, but they are running something called Advanced Inquiry – also from Accountable Solutions.

Regardless, it’s time to cut to the chase on this. I’m going to do what I wanted to do yesterday afternoon. I’m going to delete the record that’s giving me the duplicate key error message in the ASI00102 and re-run the item number change for the lone remaining item number.

I will, however, wait until the SQL backup runs about 9:00 tonight – just to be on the safe side.

Posted by Ted at 05:18 PM | Comments (0)

May 08, 2005

Item Number Change Continued

The project to change about 2,500 of a client’s item numbers is done. Well, almost done. I have one item that refuses to change – just like some people I know.

When I try to change this item, I get the message . . .

Violation of PRIMARY KEY constraint ‘PKASI00102’ Cannot insert duplicate key in object ‘ASI00102’

I’m attempting to change item number 200-101687 to item number 101687. When I query the ASI00102 table through SQL’s Query Analyzer, I can see the item number 101687. That’s probably why I’m getting the error message I am. However, item number 101687 does not exist in the inventory module, but item number 200-101687 does.

I suspect I could just burn the 101687 records out of the ASI00102 table and I’d be good to go – but I could also be wrong.

I’ve decided to spend the money and ask Microsoft for their advice. Time was I had virtually unlimited access to Tech Support for answers. Now they charge for them.

They don’t wake up until about 10:00 Monday morning. I’m still pretty optimistic. I expect to shoot this last bird off the wire tomorrow night.

Posted by Ted at 02:44 PM | Comments (0)

May 07, 2005

Item Number Change

This weekend I’d doing an item number change for a client. I’m using one of the modules from the Microsoft Great Plains Professional Services Tools Suite.

I’m changing about 2,500 item numbers in their inventory module. Fortunately, this is pretty easy to do. I have a Windows XP Remote Desktop access to one of their stations. This station has the Professional Services Tools Suite loaded on it. I’m running this from home. I just can’t go very far this weekend.

I’ve started the process running. Now I just have to log in periodically to see if it is done. We did about 4,500 item numbers two weeks ago and it ran over 36 hours. I’ve gotten an earlier start on this one.

Posted by Ted at 10:24 AM | Comments (0)

April 27, 2005

Client Upgrade Complete

My Microsoft Great Plains client upgrade project is complete – for now. Last Sunday I finished the data conversion. Monday went on site and rolled out the installation to all the stations.

My biggest challenge was the custom application for their business. I had to move it from Pervasive to SQL. The programmer extracted the data from the Pervasive file to a CSV (Comma Separated Values) file.

It turns out there was some duplicate data in the file that I had to find and eliminate. That was a rather painstaking process, but once complete I was able to DTS the data right into SQL.

There was the usual task of modifying everyone’s desktop to their liking and I have to recreate the Purchase Order and one Invoice format. All in all, it went about as expected and the client seems pleased with the result.

I’ll be stopping back next week during their month-end to make sure that process runs smoothly.

Posted by Ted at 12:26 PM | Comments (2)

April 24, 2005

Item Number Change Done

I finished the client project to change some 4,500 of their item numbers. It ran somewhat slower than I had anticipated. I started it about 10:00 Saturday morning. It appeared to finish some time after 17:00 Sunday afternoon.

I think the reason it takes so long is it changes all the history tables. It's as if the previous item number never existed.

Posted by Ted at 06:55 PM | Comments (0)

April 23, 2005

Changing Inventory Item Numbers

I’m running a data change for another client. In this case they want to change some of their inventory Item Numbers. Microsoft Great Plains has a tool one can purchase for that and I am using it.

We’re changing about 4500 item numbers. I have an Excel spreadsheet with two columns, Old Item Number, New Item Number. I have saved that as a Tab-Delimited file and have fed that file to the Item Number Changer tool.

Now it’s just a matter of waiting for it to be done.

Posted by Ted at 10:46 AM | Comments (0)

Client Upgrade Continuation

I ran all the maintenance and reconciles on the client’s data last night. Then I started the Pervasive to Microsoft SQL migration.

This morning it was complete. I ran check links and it came through pretty good. Since it’s still in version 7.0 and I have no reg keys for that I could not compare report totals to previous version.

I have started the 7.0 to 8.0 upgrade. The client has a pretty nice server (Dual Xeon with RAID 5 SCSI hard drives). They have 2GB of RAM, but I have limited how much SQL can use so it doesn’t steal from the operating system.

The upgrade shouldn’t take more than a couple of hours.

Posted by Ted at 10:41 AM

April 14, 2005

6.0 to 8.0 upgrade

I just completed a client upgrade to Microsoft Great Plains 8.0. This particular client had been on 6.0 on Pervasive on a Novell network. This means they had to replace all their hardware as well as upgrade their software.

Normally I like to set up a remote access and do upgrades like this over a weekend. Much of the upgrade is to start a process running and then wait. If I run this remotely I can just log off and then come back later to check on it progress. Sadly the only internet service they had was dial-up. They are supposedly getting DSL soon.

The upgrade was process was to first get their version 6.0 on Pervasive running on the new Windows 2000 server. Then I upgraded them to 7.5 on Pervasive. Then I migrated them from Pervasive to SQL Server. The last was to upgrade from 7.5 to 8.0. Version 8.0 only runs on Microsoft SQL Server.

The upgrade went smoothly, as I pretty much expected. This client’s operation is pretty simple as they only run Receivables and Payables. They still type purchase orders and customer invoices by hand.

I sat with the payables person to make sure vendor checks printed correctly. I like to say, half jokingly, “As a vendor, payables are the first module I like to set up.”

There was some trepidation in the staff, but after they saw how easy it was, they settled in nicely.

I feel a little frustrated in my meager selling skills. Perhaps if I was a better salesperson and not such a geek, I could persuade them to acquire and use more of the Microsoft Great Plains modules.

When a client uses Great Plains to print invoices, it keeps very detailed sales history. That makes it possible to do very detailed analysis of what customers are buying and which customers are doing the buying. I really feel helps people to better manage their business.

But, so far, I have been unsuccessful in making that case.

Posted by Ted at 05:13 PM

April 10, 2005

Correcting the Historical Aged Trial Balance

Great Plains comes with a Historical Aged Trial Balance for both Accounts Receivable and Accounts Payable. I call these two reports HATBs. They tend to be very accurate and allow the user to compare there accounts receivable or accounts payable schedule with any date in the past. This is particularly useful when your accountant wants an aged trial balance for the prior year-end.

The exception to their utility is when the installation was not a “virgin” installation, but was an upgrade from the old DOS version of Great Plains. The old DOS version did not enforce all relational database concepts as well as Great Plains on SQL Server does. This less than perfect data gets directly converted into Great Plains.

I have a client that migrated from the old DOS version about this time last year. They are now starting to need the HATBs a lot. The one for payables, surprisingly, works fine. The one for receivables was about $100,000 off.

My method to fix them is to print a detail report from both the normal aged trial balance and the HATB for the current date. Then I compare them line for line and look for the differences.

In this case, I found only two customers with problem documents. In one case, the document went back to 1997. I figured who needs stuff that old and deleted it.

In the other case, I found two cases in which an invoice and a check both had the same document number. That means I had two checks with the same document numbers as two invoices. This had greatly confused Great Plains and they were cross applied to each other back and forth - twice.

In this situation, since I’m working in the SQL Query Analyzer, I take my time. A mistake means a whole restore from backup. Since I was using pcAnywhere over the internet and the client only has about a 128KB internet service, I had to be doubly careful. When I have this slow of a connection, it is very easy to get ahead of the system and wind up making some moves you’d really rather not make.

There were two tables that were affected, the history table and the apply table. I renamed the payments to different document numbers in the history table and then changed names in the apply table. Then I deleted the cross apply records in the apply table. I ran Check Links to reset any keys that needed tweaking. Then I re-ran the HATB for the current date.

It matched the normal aged trial balance to the exact penny! I also ran the report for 3/31/05, 2/28/05, 1/31/05, and 12/31/04 and it matched the General Ledger totals to the penny!

I sent the client an email, telling her of the results, which she should open Monday morning. This should make her job a lot easier.

This is a time-consuming tedious process, as I want to get it right the first time. However, once it’s done, I don’t have to repeat it.

Posted by Ted at 09:32 PM

March 30, 2005

Client Upgrade

I just completed a client upgrade from Microsoft Great Plains 5.5 on Pervasive to 8.0 on SQL Server.

I went to the client site last Friday to install SQL Server and copy the Great Plains CD’s to the server. I also installed GoToMyPc to their new server so I can access it remotely. Then, after they closed Friday night, I started the upgrade.

First I copied their data to the new server, but still in version 5.5. I ran the file maintenance to make the data as clean as possible. Then I started the 5.5 to 7.5 upgrade.

Saturday morning, I reviewed the progress and both companies (they have two) had upgraded successfully. I install and set up the two companies in the SQL server and started the Pervasive to SQL Server migration running.

Later in the day I reviewed the Pervasive to SQL migration and found complete and successful. That’s when I install version 8.0 and started that upgrade running.

Sunday morning I reviewed the 8.0 upgrade and it too looked good. I ran check links on the smaller company and the started check links running on the larger company.

Sunday night all the data maintenance was done. I was unable to upgrade the reports dictionary and left that for Monday morning.

Monday morning I upgraded the reports dictionary from 5.5 to 7.5 to 8.0. A few forms need to be tweaked, but it went smoother than expected. Then did a CD installation on a station. From there I created an installation template and rolled out the client side installation to all the other station.

The rest of the day was spent tweaking the system were necessary and training users on the new interface. The 5.5 to 8.0 jump is a very big one and I’m afraid most users where going through some culture shock.

But, they didn’t beat me up too badly. Besides, I expect that and I have a pretty thick hide. I showed them some of the new whiz-band features and some users were impressed.

This is a relatively new client for me. They previously used a different Great Plains reseller. I don’t like to speak evil of the dead, but I would have set them up quite a bit differently.

Unfortunately this is not the first time I’ve come into a situation where I took over from someone else and I’m just surprised many people don’t think through all the implications of setup and offer the user some of the various options.

I feel I can do quite a bit to help this client reduce some of their workload while giving them better and quicker access to key data. But clients sometimes choose not to pursue potential benefits for multiple reasons. Sometimes it’s the money. At $140+ per hour for my time, it can be expensive to do. Also, many clients are so absorbed by the day-to-day operations that they can’t see the benefit from what Great Plains can do.

I fight those same constraints in my own professional life. I’m guilty of the same sins as my clients, so I understand.

All I can do it so plant seeds and water them from time-to-time.

Still, from what I see, I think I can do some very nice work for this client and I think they’ll be pleased with the results. But that is ultimately their decision.

Posted by Ted at 12:02 PM

March 16, 2005

Great Plains and Adobe Acrobat

A little bit ago one of our clients upgraded to Microsoft Great Plains 8.0. Last week I was back for some follow-up.

One of the things the client wanted to make sure was working was emailing documents out of Great Plains as PDF files.

In Great Plains you can print a PO, customer invoice or anything to the screen. After printing to the screen, you can just click on Send To >> Mail Recipient (PDF). Click here to see the screen.

When you do that, Great Plains will launch Microsoft Outlook and then insert the document you have on the screen as a PDF attachment to your email. You have only to enter the email address and appropriate text and send to your recipient.

This was not working as advertised. You must have Adobe Acrobat Writer installed for this to work. We did have it installed, but some stations were Acrobat 5.0, some were Acrobat 6.0 and some were Acrobat 7.0.

We queried Microsoft Great Plains tech support and they said they had not fully tested Acrobat 7.0 compatibility. In our own testing we found conflicting results. We found that if the document was a big enough PDF, Adobe choked creating the PDF and we received an error message:

“When you create a PostScript file you have to send the host fonts. Please go to the printer properties, "Adobe PDF Settings" page and turn OFF the option "Do not send fonts to host Distiller."

It was a long hard struggle. In the end, we put everyone back on Adobe Acrobat 5.0. It was the only version that worked all the time. It was also a lot faster than Acrobat 6.0 or 7.0. The client was less than pleased that we had to use the older version, but reliability took precedence over latest and greatest.

Posted by Ted at 12:30 PM

March 01, 2005

Client MSDE Upgrade

A client had upgraded from the old Great Plains Accounting (DOS) to the Microsoft Great Plains last year.

Well, actually, he purchased the upgrade and then hired me to do the installation and work about April of last year.

When purchasing Microsoft Great Plains there are two databases that can be used: Microsoft SQL Sever and MSDE (Microsoft Desktop Engine).

Both are really SQL, but while the runtime version of SQL costs $450 for the server and the $100 for each client, the MSDE is free.

The downside is, MSDE comes with NO SQL management tools and it limits the user to a 2GB database. The client had been sold Great Plains with the MSDE database.

Last week he received the message that he was very near to the 2GB limit.

He called, I ordered, and today I installed the Runtime version of SQL Server 2000. According to the TechKnowledge from the Microsoft Great Plains web site, SQL would see the MSDE and upgrade it.

I backed up the important stuff and then followed their recommendations.

Guess what? It worked. It didn’t take very long and with the addition of new registration keys, he was back in business. Actually, he was never out of business, but it would warn all users the size limit was close.

I don’t really know what would have happened had he reached the limit, but I suspect it would not have been good.

Now he is only limited by server disk space.

Posted by Ted at 08:46 PM