Replicating or Reverse Engineering Dynamics GP Windows and Features: A Risky Endeavor

By Steve Endow

Several years ago I had a customer who was migrating 5 years worth of data to Dynamics GP.  They needed to do a detailed data migration, so every transaction needed to be imported into GP.  Those who have done such a migration know what a hassle it can be.  It is often very difficult to extract and organize all of the data from the prior ERP system, and then importing it all into GP can be a major undertaking.  And to top it off, you invariably discover some errors in the source data that makes it even more difficult to import into GP.

As part of that project, the customer needed to import their AP payments.  And if they import their AP payments, they need to import the payment applications to reflect which invoices were paid by each payment.  Those of you who have used Integration Manager and eConnect know that although you can import AP Manual Payments, there is no way to import AP payment applications.  Let the fun ensue.

I was asked to import thousands and thousands of AP payment applications.  I thought sure, how hard could it be?  Heh, famous last words.

I was able to pull it off and produce a custom AP payment application import, but it probably took two times longer than I expected.  Fortunately the customer was gracious and actually paid me for all of my work.  The import worked well and helped them import their massive pile of historical payment applications.

That project taught me that even seemingly simple Dynamics GP processes can be deceptively complicated.

Fast forward to 2013.  One of my customers needed a way to apply a single AR payment to hundreds or thousands of invoices based on Batch ID and PO Number.  The standard AR apply window has no filtering options, so it would be impossible for them to try and use it.  One of their customers could have 5,000 open invoices, and a single payment might need to be applied to 1,000 of those invoices.

We looked into third party options and even had a Dexterity developer try and customize the window to add custom invoice filters.  But nothing panned out.  So I was stuck having to find a way to enable the customer to mass apply payments to piles of invoices with their specific filtering requirements.

Somewhat foolishly, I agreed to create a custom .NET window using Visual Studio Tools.  Boy, am I paying the price.  While the Dynamics GP Apply Sales Document window seems like a relatively simple window, when you dig into the details of what it is doing and how it is doing it, it is quite complex, and requires data management, state management, and UI development skills that I just don't normally need.  And there are a lot of details that make the process much more complex than I would have imagined.

After months of tedious work and a few redesigns, I finally have a solution that I think is robust enough and flexible enough to meet the customer's requirements while also working properly and managing all of the housekeeping that goes on behind the scenes with the Dynamics GP Apply Sales Document window.  On the plus side, I think the window is very powerful and flexible--it is exactly what the customer needs.  The data is displayed instantly, can be filtered instantly as the user types, and applying a payment to thousands of invoices requires just one click.  But developing the window has been much more difficult than I had anticipated.

While I think I'll be able to pull this one off, it has definitely left some scars, and I'm telling myself that in the future I need to try and decline any requests that involve reproducing Dynamics GP functionality or windows, or reverse engineering Dynamics GP processes.  Things are often much more complex than they might appear when using the Dynamics GP UI.

Certainly with enough time and money and development resources anything is possible, but based my experience, it is far too easy to underestimate such custom projects--they are risky endeavors.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Adjusting Prior Year Balances in Dynamics GP- Lesson Learned!

First, I want to preface this with the statement to PROCEED WITH CAUTION. I just thought the lessons learned in the post below might benefit someone.  But, as with anything, please test, test, test, and test some more if you follow this rabbit trail as well :)

It's not the most common thing, but we do sometimes get requests to adjust or update balances in a historical year (beyond the most recent year).  When we do, it is usually do to one of two scenarios (let's assume in this scenario that the current year is 2014, and we just closed 2013).

1.  During 2013, something was posted to 2012 on accident. 2013 is closed as normal, and the 2012 posting is uncovered during audit or CPA review.
2.  An additional aspect of the company is being brought in to GP, in to an existing GP company and there is a desire to migrate more than one year of history.

In some ways, the migration is fairly straightforward.  It seems logical that you would have to, in this scenario...

1.  Update the GL history table with the actual transactions (GL30000) for 2012
2.  Update the GL history table with the BBF transactions for 2013 (for the balance brought forward)

But, here is the clincher...you also have to create BBFs for 2014 in the GL20000 (GL YTD Open).  Since the BBFs for 2014 were already created at the time of the close, they are not updated (even with reconcile) for the open year.  So there is a ripple effect for however many years you go back in time.  And, keep in mind that the reconcile process will NOT place transactions in to the GL30000 or GL20000, it will only update the balances in the account summary tables (GL10110, GL10111).

This may help you, or maybe it will convince you to never ever ever mess with the GL history tables (which is probably a very good idea).

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a senior managing consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Correcting Project Expenses

When someone needs to correct something in project accounting, I always push a little bit...asking what exactly they need to correct.  Is it the cost?  Or is it the billing?  Or is it something showing up to be billed that shouldn't?

Today when asking just those questions, I was reminded of the following KB article...which does a great job of breaking down which kind of return to do (when reversing cost from a purchase on a project).

http://support.microsoft.com/kb/961567

Happy correcting!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a senior managing consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Do your "non-profit" customers ask for a discount on software or services?

By Steve Endow

I recently had an interesting experience.  A Dynamics GP customer contacted me to purchase some software.  They tried the software, liked it, and said they were ready to purchase.  They then asked if I could provide them with a discount.  Given that the software is priced very reasonably and there is no annual enhancement fee, I don't typically provide discounts.

Then they said they were a "non-profit" and a "charity" organization.  They help kids, you see.

But then I did some searching.  This is probably obvious to many, but was something I had never needed to know before:  Non-profit organizations in the US are generally required to file an IRS Form 990 to publicly disclose their financials.  I found an old copy of the customer's 990 form and saw that they had millions in dollars of revenue and millions in assets.  Not just a few million, but lots of millions.  Maaaany millions.  Bunches and bushels of millions.

Okay, so they may have lots of revenue and assets, but the services they provide are very costly, they claim to do a lot of charity work, and much of the assets and revenue appears to be related to a foundation, not the operational entity that handles things like accounting.  Who knows, maybe the accounting department is sitting on milk crates in a basement and using old PCs with CRT monitors (no joke, I had a client that didn't have enough chairs in their accounting department and I sat on a milk crate for a week).  I do have empathy for accounting departments with limited budgets!

But then I got to the page that listed the executive compensation.  The top executives were paid lots, and lots of money.  One was paid well over a million dollars.  In one year.  

Non-profit?  Charity?  For the kids?  I think they will do just fine if I don't give them a few hundred dollar discount.

As many people know, "non-profit" is just an IRS tax designation and doesn't mean that the company doesn't make a lot of money or doesn't have a lot of money, and it doesn't mean that they can't afford their ERP software or related consulting services.  And many executives at non-profit organizations are paid plenty of money, as I now know.

But when I hear "non-profit", I'm a sucker, as I still generally think of charitable organizations or community services groups that are hardly rich.  I have provided discounts to a few smaller organizations that happened to be non-profit, but clearly were not counting their millions.

So, when a customer asks you for a discount, what do you tell them?  How about when they tell you they are a non-profit?  What if they say they are a "charity" and "help the kids"?

Call me a skeptic, but from now on I'll be checking an organization's Form 990 before offering any discounts in the future.

I'm definitely not an expert, so if there is some important detail about non-profit organizations that I am missing that might justify giving wealthy ones a discount, I'm interested in hearing about it.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

No plan survives contact with the enemy: Budgets and Project Plans

By Steve Endow

"No plan survives contact with the enemy"--this is a paraphrased version of an oft cited quote by Field Marshall Helmuth Carl Bernard Graf von Moltke (that's quite a name).

I'm currently working on a project that involves integrating Dynamics GP with a customer's internal e-commerce web site.  The customer's web site is used to capture customer information when they sign up for a subscription service.  I was asked to develop a solution that could receive that information in real time and store it in Dynamics GP.  This involved new customer information and credit card information and integration with two web services.

I had a few phone calls with the GP partner and client, we discussed the general requirements, and then we came up with a high level task list and estimate.  It seemed relatively straightforward.

Then the project started.  We knew that there were a few open questions, but not too long after we started working on the tasks, new details emerged.  There were some requirements that were more complex than anticipated, new requirements that were not anticipated, and some increase in scope due to new feature requests.  We adjusted our design and resumed working on the tasks.  Over half way through the project, yet another requirement surfaced that will affect a few parts of the overall solution that was a combination of "Oops, I forgot to mention that" and "It would be nice if...".  No problem, we have a plan to accommodate the requirement.

Okay, so all of this isn't too surprising--these are all fairly common events in technology projects.

But in this particular case, it all seemed more visible because the client is very conscientious about project management and receiving regular updates on tasks and timelines.  I've spent hours just updating the simple Excel task list every week, and had to give up completely on an MS Project plan that I originally created--there were just too many changes to try and track the project in detail in Project.

After updating the budget vs. actual report and reconciling all of my time against the originally planned tasks vs. new or changed tasks, it reminded me of the "von Moltke" quote.  It seems that technology project plans aren't terribly different than battle plans.  You don't always have all of the details or knowledge before you start a project, and things change as the project progresses.  (Come to think of it, anyone who has done home remodeling can probably also relate...)

Usually, that means that projects grow, become more complex, and require more time and/or money to complete, despite a customer's desire to have a fixed project plan with a fixed budget.  Plan accordingly, and expect all project plans and budgets to require adjustment as a project progresses.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Why is my Dynamics GP database growing / getting so large / taking up so much space?

By Steve Endow

There was a recent forum post by a user who had noticed that his Dynamics GP system database was growing, and was apparently looking to understand how to monitor the growth.

I have dealt with this question before, but never remember the query, so I wanted to post it here for future reference.

My approach is to run a query that shows the number of records and the size of each database table in the database.  Usually, most of the database is utilized by a handful of tables, so once you find your top 5 or 10 tables, you'll quickly understand what type of data is consuming the space in your database.

I found this Stack Overflow thread that addresses the issue and offers a few queries.  I prefer the query that was most upvoted because its result set is in a single clean set that can be copied and pasted into Excel.

The query lists I have modified the Order By clause to sort the largest tables to the top.

SELECT
   t.NAME AS TableName,
   s.Name AS SchemaName,
   p.rows AS RowCounts,
   SUM(a.total_pages) * 8 AS TotalSpaceKB,
   SUM(a.used_pages) * 8 AS UsedSpaceKB,
   (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
   sys.tables t
INNER JOIN      
   sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
   sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
   sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
   sys.schemas s ON t.schema_id = s.schema_id
WHERE
   t.NAME NOT LIKE 'dt%'
   AND t.is_ms_shipped = 0
   AND i.OBJECT_ID > 255
GROUP BY
   t.Name, s.Name, p.Rows
ORDER BY
SUM(a.total_pages) DESC


And the results look like this:

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Will eConnect import transactions into a batch that is in Batch Recovery?

By Steve Endow

Will eConnect import transactions into a batch that is in Batch Recovery?

The short answer is: Yes!

A client asked me this question today and I didn't have an answer, as it is something that I haven't considered  or tested before.

The client uses a third party requisition system that is constantly importing batches into Dynamics GP--I believe they are purchase receipt batches.  The requisition system can import transactions into GP using two different batch IDs--either the username plus the date, such as "JSMITH20140502", or a single common batch ID, like "IMPORTRCTS".

Since the client has many users of the requisition system, he was hoping to avoid having dozens of different batch IDs to post in GP.  But he wondered if he used a single batch, and there was an error in the batch causing it to go to batch recovery, what would happen when the requisition system tried to import more transactions into that same batch?

I didn't know, so I performed a test.  Using my Dynamics GP Batch Load Test application, I imported 10 payables invoices into a batch called TEST.

I then modified the distributions in one of the invoices so that they were invalid.

I then posted the batch, forcing it to go to batch recovery.

When a batch is in recovery, GP will not allow you to open or edit the batch.

If I would have recovered the batch at this point, it would only contain one transaction--the invoice with the invalid distributions.  The other 9 transactions posted successfully and would no longer be in the batch.

I then used the Batch Load Test application to import more transactions into the TEST batch.  I received no errors, and the import completed successfully.

I then recovered the TEST batch.

When the batch was recovered, it contained 11 transactions.  The 1 transaction with the error, and the 10 new transactions that I imported.

So, this demonstrates that eConnect, unlike the GP application, will allow you to add new transactions to a batch that is in recovery.

Is this a bad thing?  Or a good thing?  I don't know that it is either bad or good, but it means that you should design your workflow accordingly.

This particular client wanted to automatically post his batches using Post Master Enterprise.  But if he uses a single batch and it goes to recovery, transactions will pile up in that batch until someone uses the Batch Recovery window to recover the batch--since eConnect will import transactions into a batch that is in recovery.

Anyway, this is one of those things where you don't know the answer for sure until you test it and confirm how GP or eConnect behaves.  This particular behavior is consistent with other eConnect "differences", such as:

Will eConnect import transactions into a batch that is being edited in GP?  Yes!

Will eConnect import transactions into a batch that is being actively posted in GP?  Yes!

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

eConnect error: All transaction type child nodes must contain the same document number

By Steve Endow

I developed a new eConnect GL JE import for a customer and during an initial test, it worked fine.

But during a subsequent test with a new sample data file, the customer would receive the following eConnect message:

All transaction type child nodes must contain the same document number. Please ensure your transaction is not missing this unique identifier for the element JRNENTRY. (If you would like the document number generated, please ensure a document number value is not contained in the document number element JRNENTRY.)

The journal entry would import into Dynamics GP properly, despite the error.  I don't recall ever seeing this error before, so I was puzzled.  I first checked the serialized XML that was being generated, and it looked fine.

< ?xml version=\"1.0\"? >
< eConnect xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" >
< GLTransactionType >
< eConnectProcessInfo xsi:nil=\"true\" / >
< taRequesterTrxDisabler_Items xsi:nil=\"true\" / >
< taGLTransactionLineInsert_Items >
< taGLTransactionLineInsert >
< BACHNUMB >CMS30< /BACHNUMB >
< JRNENTRY >24440< /JRNENTRY >
< CRDTAMNT >0.00< /CRDTAMNT >
< DEBITAMT >170618104.45< /DEBITAMT >
< ACTNUMST >999-9999-99< /ACTNUMST >
< DSCRIPTN >Contr< /DSCRIPTN >
< /taGLTransactionLineInsert >
< taGLTransactionLineInsert >
< BACHNUMB >CMS30< /BACHNUMB >
< JRNENTRY >24440< /JRNENTRY >
< CRDTAMNT >170618104.45< /CRDTAMNT >
< DEBITAMT >0.00< /DEBITAMT >
< ACTNUMST >999-9999-99< /ACTNUMST >
< DSCRIPTN >Contr< /DSCRIPTN >
< /taGLTransactionLineInsert >
< /taGLTransactionLineInsert_Items >
< taAnalyticsDistribution_Items xsi:nil=\"true\" / >
< taGLTransactionHeaderInsert >
< BACHNUMB >CMS30< /BACHNUMB >
< JRNENTRY >24440< /JRNENTRY >
< REFRENCE >CMS Contr 04/23/2014< /REFRENCE >
< TRXDATE >04/23/2014< /TRXDATE >
< TRXTYPE >0< /TRXTYPE >
< /taGLTransactionHeaderInsert >
< taMdaUpdate_Items xsi:nil=\"true\" / >
< /GLTransactionType >
< /eConnect >


I was puzzled.  I then pulled up another eConnect GL import I had developed to see if I was missing anything in the XML--but the XML structure was identical.

But after another round of tests, I found that the first JE imported fine, but the second and subsequent JEs received the eConnect error.  Hmmm.

So here is the XML for the second JE.  Notice anything wrong?

< ?xml version=\"1.0\"? >
< eConnect xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" >
< GLTransactionType >
< eConnectProcessInfo xsi:nil=\"true\" / >
< taRequesterTrxDisabler_Items xsi:nil=\"true\" / >
< taGLTransactionLineInsert_Items >
< taGLTransactionLineInsert >
< BACHNUMB >CMS30< /BACHNUMB >
< JRNENTRY >24440< /JRNENTRY >
< CRDTAMNT >0.00< /CRDTAMNT >
< DEBITAMT >170618104.45< /DEBITAMT >
< ACTNUMST >999-9999-99< /ACTNUMST >
< DSCRIPTN >Contr< /DSCRIPTN >
< DOCDATE >04/23/2014< /DOCDATE >
< /taGLTransactionLineInsert >
< taGLTransactionLineInsert >
< BACHNUMB >CMS30< /BACHNUMB >
< JRNENTRY >24440< /JRNENTRY >
< CRDTAMNT >170618104.45< /CRDTAMNT >
< DEBITAMT >0.00< /DEBITAMT >
< ACTNUMST >999-9999-99< /ACTNUMST >
< DSCRIPTN >Contr< /DSCRIPTN >
< DOCDATE >04/23/2014< /DOCDATE >
< /taGLTransactionLineInsert >
< taGLTransactionLineInsert >
< BACHNUMB >CMS30< /BACHNUMB >
< JRNENTRY >24441< /JRNENTRY >
< CRDTAMNT >0.00< /CRDTAMNT >
< DEBITAMT >4975354.07< /DEBITAMT >
< ACTNUMST >999-9999-99< /ACTNUMST >
< DSCRIPTN >ContrRev< /DSCRIPTN >
< DOCDATE >04/23/2014< /DOCDATE >
< /taGLTransactionLineInsert >
< taGLTransactionLineInsert >
< BACHNUMB >CMS30< /BACHNUMB >
< JRNENTRY >24441< /JRNENTRY >
< CRDTAMNT >4975354.07< /CRDTAMNT >
< DEBITAMT >0.00< /DEBITAMT >
< ACTNUMST >999-9999-99< /ACTNUMST >
< DSCRIPTN >ContrRev< /DSCRIPTN >
< DOCDATE >04/23/2014< /DOCDATE >
< /taGLTransactionLineInsert >
< /taGLTransactionLineInsert_Items >
< taAnalyticsDistribution_Items xsi:nil=\"true\" / >
< taGLTransactionHeaderInsert >
< BACHNUMB >CMS30< /BACHNUMB >
< JRNENTRY >24441< /JRNENTRY >
< REFRENCE >CMS ContrRev 04/23/2014< /REFRENCE >
< TRXDATE >04/23/2014< /TRXDATE >
< TRXTYPE >0< /TRXTYPE >
< USERID >eConnect< /USERID >
< /taGLTransactionHeaderInsert >
< taMdaUpdate_Items xsi:nil=\"true\" / >
< /GLTransactionType >
< /eConnect >

Note that the LineInsert_Items include lines for the prior JE and the current JE.  For each JE that was processed, the number of lines kept growing, as they included all of the lines from all prior JEs.

Aha!  What causes this?  Forgetting to clear the LineInsert_Items array!  I had forgotten to add this line with each header loop:

glLines = new List< taGLTransactionLineInsert_ItemsTaGLTransactionLineInsert >();


And so that one missing line caused the bug, which caused the eConnect error.

As my younger daughter now says (following the lead of her big sister):  "Easy peasy macaroni cheezy!"

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Flat Tax Rates and Additional Amounts in Dynamics GP

Every time I train someone on payroll in Dynamics GP, I point out the flax tax rate fields available on both the Pay Code Setup (Setup-Payroll-Pay Code) and Employee Pay Code Maintenance windows. (Cards-Payroll-Pay Code).

 

 

And I emphasize that if a value is entered in either of these fields, it will override the tax settings for the employee and take the flat rate instead.  So, for example, an employee who is set up this way...

 

 

....with a Federal Filing Status of Married and 1 exemption, if there is a flat tax rate on their pay code then it will ignore the Federal Filing Status and number of exemptions when calculating Federal withholding.  However, and this a significant HOWEVER, it will NOT ignore the Additional Withholding amount.  So in the case above, the system will take 28% Federal withholding PLUS an additional $100 on the payroll.  This is true regardless of whether the payroll includes pay codes other than the one with a flat tax rate.  So, even if you run the Bonus payroll separately, it will still take 28% plus $100.

 

The devil is in the details, right?  So the options would be (feel free to chime in if you have other alternatives) to either adjust the flat percentage to offset the additional amount or remove the additional amount from the Employee Tax Maintenance window prior to building the payroll and then adding it back afterwards (not fun, I know).

 

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a senior managing consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.