Project Accounting Integration Frustration: Customer Project Info

By Steve Endow

(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 PA Customer Options window, the customer alias defaults for you, so you typically don't notice it, don't pay any attention to the default value, and care how it is generated. If your customer ID is ACME001, your alias will default to ACME0. If your customer ID is 123456, the default alias is 12345. Simple, right? Not so fast, grasshopper!

As I'm importing 50,000 customers with blocks of sequential, 6 digit customer numbers, guess what. I have customer ID 123456, and 123457, and 123458. So...clearly I can't just use the first 5 characters of the customer ID, as all would have an alias of 12345.

So I did some tests in GP to see how it generates the alias. I found that if alias 12345 is taken, it will use 12341. If that is taken, it just increments the last digit, so 12342, 12343, etc. This is fine and dandy if you have customer IDs that are fairly distinct and well distributed, like ACMEROCKETS, or ABCMETALS. But if you have sequential, numeric customer numbers that are 6 digits or longer, you start to have some challenges.

Here's an example of customer IDs, and the default alias generated by GP. Think of this as a big train wreck occurring in very slow motion:

123450 = 12345
123451 = 12341
123452 = 12342
123453 = 12343
123454 = 12344
123455 = 12346 (12345 is already used)
123456 = 12347
123457 = 12348
123458 = 12349
123459 = 12340 (GP doesn't actually use zero, but for arguments sake, I included it)

Looks fine, right? 10 customers, 10 aliases. Simple and easy, right? Well, no, the train is definitely wrecking, it's just taking its time.

What happens for customers 123401 - 123410? In that case, the default numbering scheme then goes from using the first 4 characters of the customer ID, to the first 3. So customer 123401 will get an alias of 12310. But then what will customer 123101 use? See the problem?

This all leads to a preposterous situation where a customer 123700 might receive a default alias of 11000. It's just stealing numbers from another series, attempting to have the alias resemble the customer ID, and hoping they won't all need to be used.

So at first, before I realized how many customers I was dealing with, I thought I would just write a routine that would loop through alias numbers to find an available value--I basically mimicked the GP default alias generator. I used the first 4 characters of the customer ID, and if those 10 aliases weren't available, I moved on to the first 3 characters of the customer ID.

But then that resulted in duplicate aliases, as all of those 100 alias values were taken. So then I realized that I would then have to look through the aliases starting with the first 2 characters of the customer ID. That's 1,000 values. And even then I ran into situations where that wasn't enough.

The next step would be to use the customer's first 2 digits, and check 10,000 possible alias values. The train is definitely off the tracks at this point.

It became clear that there HAD to be a better way.

The quick and dirty approach first came to mind is to throw a letter into the mix. If my options included 12340 to 12349 and also 1234A to 1234Z, that gives me 26 more options--basically a "base 36" numbering scheme. Naturally that would work, right? Maybe as a temporary solution, but as thousands of more customers were created, I could still run into an issue. So I could do something like 123AA, where the last two characters could be alphanumeric. But if you try and write such a routine, it looks like a looping circus.

And there is another issue. This alias generation routine was in my .NET app, and in order to validate the alias, I have to make a call to SQL Server to check if the alias is in use already. So with every number I try, it's a query against SQL. Just plain bad design. If I were checking just 10 values, I'd let it slide, but thousands of values is out of the question.

So what's a better solution? I want to:

1) Generate a 5 character alias that "resembles" my customer ID
2) Make sure the alias does not already exist in PA00501
3) Generate the available alias values sequentially so that I don't have unecessary gaps
4) Eliminate looping in my code
5) Make one query against the database

This is actually a fairly common issue with business apps and databases, but there are many different nuances and business requirements around numbering, so there isn't necessarily "a solution" for all situations.

After thinking about the issue for a few minutes, I eventually remembered a story that a friend told me about a SQL Server guru that could magically generate a range of sequential numbers with a single SQL statement. That story led me to my solution, which I'll share in part two.


Link to Part 2:  https://dynamicsgpland.blogspot.com/2009/02/project-accounting-integration_20.html