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)

June 01, 2005

Client Replaces Server

My client that had their office broken into last month got their new server from Dell. The hardware guy set it up and I set up Great Plains on it, backed up Great Plains on their old server, copied the backups to the new server, restored the backups, and ran the script that Microsoft provides for changing to a new server.

Since this was a controlled transfer, I was able to run a script that captures users’ passwords before the transfer. Thus, users never saw any difference – except Great Plains probably now runs a lot faster.

The new server is a Dell PowerEdge 2800. Their previous server was a 2600, but that model has been discontinued. It was dual SCSI hard drives in a RAID 1 configuration, dual 2.8GHz Xeon processors and about 3.25 GB of RAM. I cranked SQL back to about 1GB. They don’t bang on it too hard and they should do fine with these settings.

All I have to do now is to make sure my SQL backups run and set up the SQL maintenance to run every night.

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

May 20, 2005

Client Back Up and Running

I got the client that had their server stolen back up and running yesterday. Their hardware guy has an interim server running. It has only 256MB of RAM and an IDE hard drive, so it’s rather show.

I had worked the Microsoft so I was well prepared when I went on site. Their most current backup was dated May 4, 2005, but they seemed to be taking it all in pretty good spirits.

When the thieves broke in they took a crowbar to every door and forced their way in. All locked file cabinets were forced open with a crow bar. It turns out those locking file cabinets only keep the honest people out.

The thieves got some cash, but not a lot. But they left the one girls un-cashed paycheck sitting on her desk. They took the servers, but left all the workstations and monitors behind. They took three pairs of scissors and some merchandise inventory. They helped themselves to snacks and drinks that were in the office.

The thieves used the drinks but left the empty bottles and cups behind. From these the police will extract DNA.

When I left last night the payroll girl was reprocessing her payroll for the last two weeks. Fortunately she still had her reports so she could re-enter them and match them up. Then she was going to run the current payroll so everyone could get paid.

The called Dell and gave them the Dell service number for the stolen server. That way, if anyone calls Dell about the server, Dell will immediately know the server is stolen and can contact the authorities.

The only data lost from restoring from backups was everyone’s password. We just need to reset their passwords and they will be good to go – aside from having to re-enter two weeks of work.

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

May 17, 2005

Client Recovery on Hold

I spoke to the hardware guy about the client’s interim server. He said he is waiting for a tape drive that will read their tapes. Until we have the capability, we are dead in the water. It looks like the recovery will not take place until tomorrow afternoon at the earliest.

I've got all the documentation I need from Microsoft's tech knowledge and I have all the scripts I will need on a flash drive. I'm just waiting on them.

Posted by Ted at 01:53 PM | Comments (0)

Client was robbed

I just received a call from a client telling me someone broke into their facility and stole their server. Apparently the thieves first cut the phone lines disabling the security system. Then they broke in and took the servers.

What I don’t know is how far back the backup tapes go. The hardware guy is supposed to have an interim server sometime after lunch. When he gets to the client site, he’ll call me and I will go out and get their Great Plains back up and running on the interim server.

Posted by Ted at 09:23 AM | Comments (0)

May 09, 2005

Success at Last!

Well I waited for the SQL backup to finish, deleted the errant records from the ASI00102 table, and then ran the item number conversion. This time it went willingly.

I know it’s a small thing, but when they give you trouble it’s very satisfying to finally kill them off.

Posted by Ted at 09:21 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)

April 29, 2005

Copernic Desktop Search Engine

I few weeks ago I downloaded and installed the Copernic Desktop Search Engine. I will say that I’m very pleased with it.

I had previously used the Google one, but was unhappy that it would not search files on my server. I keep all important files on my server as it get backed up every night.

The Copernic engine allows me to specify which folders I want to index and later search. I am particularly pleased with its ability to search my Outlook emails.

I have about 20 folders in my Outlook that I route mail to. I don’t delete a lot of email. I have every Microsoft Great Plains tech support incident I ever had in my Outlook.

If I run into a problem that I think I have spoken to tech support before, I just pull up Copernic, specify the tech support folder and type in a word or two. It takes less than one second to find the relevant entries. Outlook’s search takes a lot longer.

The other day someone was asking for some documentation for a project we did with them in 1999. Using the Copernic search engine, I found the documents in about two minutes.

I am very pleased.

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

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

April 01, 2005

Getting upgraded Payroll to run

Last night I received a call from a client which I had recently upgraded to Microsoft Great Plains version 8.0. This was the upgrade that went from 5.5 on Pervasive to 8.0 on SQL.

It had gone more smoothly than I had expected. But then I always maintain the attitude, “Expect the worst, hope for the best.”

One of the modules they use the Payroll. I was able test most modules, but until they did a paycheck run, I could not be sure the upgrade was 100% successful.

When one does payroll in Microsoft Great Plains there are several steps to the process. They are...

The client called saying she could not Calculate checks. Since I was still at another client site, I said I would check it out, when I got home. She had email me, but had turned on my autoresponder that said I was out of the office, so she called my cell phone.

When I got home I logged into their system using my GoToMyPc connection and duplicated her process. When I tried to calculate checks, I received the error message, “The tax filing status record for tax code FED for status SIN cannot be read. Paychecks cannot be calculated.”

I used the SQL Query analyzer to look at the payroll tax tables. I had used the automatic update for them that comes directly from Microsoft Business Solutions over the net. As far as I could tell, they looked good.

Since I had previously done complete file maintenance, I did duplicate that effort.

I went online to Microsoft Business Solutions tech support Knowledge base. It’s always a challenge to figure out which parts of the error message to search for. But eventually I found one that referenced this error message: “Tax filing status record for tax code MO for status cannot be read. Paychecks cannot be calculated”

Now this was not the exact message I was getting, but it was very similar. The answer to this message was the user had a blank State Tax field.

I found the table that held the employee Federal tax information and looked at it using the SQL Query Analyzer.

I noticed when I looked at the Federal tax flag for all employees, it was MAR, SIN, or SINGLE. When I looked at the Payroll Tax tables, I could only find MAR or SINGLE tax flags.

I thought to myself, “Hmmmm. I wonder if the SIN is left over from the 5.5 and did not convert correctly.”

I went back into Microsoft Great Plains and pulled up one of the employees that showed a tax flag of SIN. When I did, I observed that there was no Federal tax filing status, married or single.

I changed it to Single and then looked at the same data in my SQL Query analyzer. What had previously been a SIN flag was now a SINGLE flag.

I was tempted to do a SQL script to reset all the flags that were SIN to SINGLE as that would have been very quick. But if I was mistaken in my theory, there would have been no way to restore back to the previous state.

I wrote a little macro to speed the process, but I changed all employees, one by one, that had a blank tax filing status to single.

I check the table again in the SQL Query Analyzer and all my SIN flags were gone. I tried to do the calculate payroll checks, and it ran like it had eyes.

I logged off the customer server, logged onto my office station and replied to the client’s email that the situation was resolved.

The best I can think is, the data in 5.5 must have been stored as SIN. But somehow, during the migration over two versions and two databases, that flag never got correctly updated.

But it’s good to go now.

Posted by Ted at 02:52 PM

Citrix - Resolved?

I have a client that is using Citrix to allow remote users to access Microsoft Great Plains. The users are in Connecticut and the server is in the Philadelphia area.

Microsoft recommends using either Citrix or Windows Terminal Server for this situation. The client has chosen Citrix.

Although it has worked well, they have experienced one user being unable to consistently log into Great Plains. She would get a message about there not being a user/password combination that she was using.

For a while, I and the MIS people at client, thought this was a user problem. But upon further analysis it appeared to be endemic to the installation.

Part of the problem was, it would happen randomly. That always complicates the issue. Today we worked on it hard and I think we may have resolved it.

The client has two Citrix servers. Citrix will automatically load balance. That is, it will seamlessly select the server being used the least as the user logs in. Once I found this out, I had the MIS person go through and make sure the SQL ODBC driver was correctly set up on both Citrix servers.

It turns out, one of the servers had the SQL OCBC driver incorrectly configured. We reconfigured it correctly and he was able to log in OK. One instance is not a guarantee, but since one server was incorrectly set up, Citrix will select the server being used least, and the problem was intermittent, I am cautiously optimistic, as Ronald Reagan used to say, the problem is behind us.

But I could be wrong. Time will tell.

Posted by Ted at 01:05 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 06, 2005

Setting Sales Order Processing Batch Numbers

A client recently migrated his data from the old GPA (DOS) to Great Plains on SQL server. He has the Sales Order Processing (SOP) module for entering customer orders.

The way they work is, each day they enter customer orders and invoices. The next morning, a girl downstairs reviews those orders and invoices and then posts them.

In the old DOS version, the guys doing the order entry had to stay out of the system until the girl downstairs had completed her work.

I felt their best bet would be to have a separate batch for each day of the week. That way they could still work upstairs, while the girl downstairs did her work. So, I created six SOP batches (MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY). They are open half the day on Saturday.

After a couple of days, it became apparent the guys entering orders would not always pick the correct batch to put the order in. Normally, once you start entering invoices into a batch, the batch number doesn’t change. But if you pull up a SOP document from another batch, then the default batch number is changed to the batch number on the document you just viewed.

To resolve this situation I created a SQL script that runs every night about 8:00 pm. It goes through all the SOP documents and looks at the date on the document. If figures out what day of the week this is and then, if the document is not in the correct batch, it puts into the right batch.

Now when the girl downstairs reviews all the invoices from the prior day, she can be sure she’s getting all of them.

This is one of the things I like about SQL. I can create scripts that run as frequently as I need and they can clean up a lot of the data – and they are completely transparent to the user.

Posted by Ted at 03:14 PM