(This is a two-part post. In this first article, I'll describe the issue I ran into, and in the second article, I'll propose a more complete solution for dealing with the issue that can be applied to other situations where unique document/transaction/record numbering is required.)


I recently developed a project accounting integration using eConnect. The integration reads 4 columns from an Excel file, and then creates all of the records in GP to fully setup the project, from the customer, to the contract, to the PA accounts, all the way through to the project budget and status flags. There were a few interesting learning experiences along the way, but the most challenging was one that I least expected: the PA Customer Options window. When you create a new customer and have PA installed, there is a Project button in the lower right corner of the customer window. This record is normally setup automatically when a customer is created in GP when PA is installed. But it is not setup automatically by eConnect.

I know, you're thinking "How hard could that be? There's only ONE required field!". That's exactly what I thought too!

The first bump occurred when I found that eConnect does not have a transaction to create the PA Customer Options record. Okay, not a big deal, I just traced the data back to the PA00501 table. It's a simple table, and I was able to just use the zDP_PA00501SI stored procedure to insert my record. I only had to pass in two pieces of data: customer number and customer alias. Simple!

After a few records imported, I had fleeting touch of self-pride, until I got this error:

Violation of PRIMARY KEY constraint 'PKPA00501'. Cannot insert duplicate key in object 'dbo.PA00501'.

After checking the PKPA00501 index, I saw that it was complaining that I had a duplicate customer alias. And that's where the arcane fun starts.

The PA Customer Alias field is a very annoying field that is limited to 5 characters. Yup, just 5. Normally, when you open the…