Feature Pack 1 for Microsoft Dynamics GP 10 added Cost Allocations to the Project Accounting module. I covered the basics in Part 1 so go there for the overview. I was a little stumped by the way unit based allocations worked so this post should provide at least the basics to get started with allocating project costs based on units.

Something else new in GP Version 10 is the ability to mass add budget lines in Project Accounting. As an example, if your company adds a new cost category that needs to be applied to a large number of projects you can do that quickly, in a few minutes.

For the last two weeks we've looked at the Project Accounting Cost Allocation added to GP 10 in Feature pack 1. This functionality allows users to take existing costs and allocate them across other projects. Today we are looking at a different animal, Benefit Allocations.

I hope you have been following the series of interesting Project Allocation articles with Mark Polino, MVP. In this third installment, Mark explores the allocation of payroll benefits across projects in Project Accounting guiding you through with…

How many of you have been caught off guard after installing project accounting on existing Dynamics GP installation, when it begins asking users if they want to add project…

In the first article of this topic, I explained the difficulties I ran into trying to assign customer aliases while importing Customer Project Info records into PA00501. In this article I'll present one possible solution for generating customer aliases that does not require any looping in my .NET code, automatically fills in gaps in the numbering sequence, and only requires one call to the SQL database.

I only recently discovered this approach (which appears to be a standard approach to the problem that many before me have already conquered), so I haven't yet thought of possible drawbacks or complications, but so far it seems to work in concept, and even better, it is a general solution that can be applied to many different situations where you need a robust, flexible, and fast method for getting a next number.

So I was already pretty convinced that looping through an arbitrary list of customer alias options was not practical, and that I was going to have to come up with a more comprehensive solution. What is the alternative to taking a subset of choices and checking each one of them against the list of existing customer aliases?

How about if we reverse the situation. Instead of asking "Is this single value already used?", what if we ask "What are ALL of the values that are already in use?". For that new question to make sense, we have to provide some context, so let's try "Out of all of the possible options of our entire value set, what are all of the values that are already in use?" Huh?

Let's consider that our entire set of possible customer aliases is 00000-ZZZZZ. So that's a "base 36" numbering scheme, which gives us 60,466,176 possible permutations. More than enough for any real GP customer. Hmmmmm. So instead of generating a single value, and then checking it against my alias list, what if I had a list of every single possible alias value at my disposal? Well, first of all, it means that I don't have to write a routine to generate a base 36 number, which is a pain, and I could therefore also probably eliminate any looping code. But how in the world could I possibly deal with a list of 60 million values?

If I think "procedurally", I know that I can't possibly loop through that many values. So loops and cursors are out of the question. But if I think in terms of "set based" operations, where SQL Server will magically sift through my request in a split second, the number of records effectively becomes irrelevant. (There are practical limitations that I'll consider later, but bear with me for now.) Okay...so...if I have a list of every possible customer alias, and I also have a list of every customer alias that has already been assigned in GP, what does that mean? It means that I can use a set based operation to compare the two lists, aka a SQL JOIN! And, if I'm able to compare the two lists using a JOIN, that also means that I can compare them in two different ways: An inner join, and an outer join. This was the "Eureka!" moment at which I knew there was hope.

If I can do an outer join between the two lists, that means that SQL Server will instantly tell me…

(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…

Due to Business Ready Licensing (where clients receive a "suite" of modules when purchasing Microsoft Dynamics GP), I have found more and more clients using project accounting in non-traditional ways simply because they already own the software. One of the most common non-traditional approaches is to use the module to track capital expenditures. Project accounting works great for the capturing of the myriad of costs associated with a capital project like labor, materials, consulting, and even indirect expenses like overhead and equipment usage.

One of the questions that always comes up in discovery is how to handle the "recognition" of the asset, when a capital project has reached a specific stage of completion and the construction in progress can be capitalized. I have found that by using the WIP (Work in progress) functionality of project accounting with a time and materials project, we can easily emulate the…

There is no way to do a negative transaction in Dynamics GP Project Accounting for transaction types other than Misc. Log transactions. Or is there? For Timesheets, Equipment Logs and Employee Expense you are stuck making adjusting transactions to reverse the original entry. Or are you?

Are you intrigued yet? Many people don't know that you can create a "Referenced"…