Wednesday, September 30, 2015

GP Contract Administration - Importing Contracts and Contract Lines

We just completed our third project doing a data migration (or integration) to the GP Field Service Contract Admin module.  Unfortunately, Microsoft Dynamics GP never released an eConnect adaptor for the Contract Admin module.  But, doing direct SQL inserts into the master tables for contracts was easier than it would normally be.

Microsoft added SQL Triggers to the SVC00600 (contract header) and SVC00601 (contract lines) tables that automatically fill in default values, calculate billing periods, revenue recognition, and a lot of other stuff.

If you need help integrating to this module, email me and we can set up a time to discuss.

Thursday, January 10, 2013

Resetting SSRS Report page numbering based on a group

We had yet another situation today where a client was using a SQL Server Reporting Services (SSRS) report as an invoice, and needed the page numbering reset every time a new invoice started (one execution of the report includes invoices for multiple customers).

For instance, if the first invoice is 2 pages long, and the second invoice is 3 pages long, the page numbering on the 5 pages of the reports should be:

Page 1 of 2
Page 2 of 2
Page 1 of 3
Page 2 of 3
Page 3 of 3

This was a real pain back in the day.  In fact, before SQL Server 2008 R2, I don' think it was possible to reset the "of x" portion of the page numbering.  I was only ever able to reset the current page number to 1 when a new group started.

But, with the introduction of SQL Server Reporting Services 2008 R2, it is possible to do this without a lot of .NET coding behind the report.

Here's a link to the best blog post I've found on this topic.  For our purposes, all we needed to do was set the PageName property for the invoice header group to be the InvoiceNumber from the dataset.  Then set the page properties as listed in this blog.


Thursday, November 15, 2012

Ask for your source code!

We just completed a project where a client wanted to upgrade to GP 2010, but they were using a GP Dexterity customization that was written many years ago by a VAR which had gone out of business.  Unfortunately, this client did not have a copy of the customization's source code, which meant we had to re-create the customization from scratch.  Well... partly.

Using Dexterity Utilities, we were able to merge all of the physical components (fields, tables, forms, reports) from their GP 8.0 installed custom dictionary into a new GP 2010 development environment.  Everything was preserved, except for the sanscript code.  But, at least a lot of table/field setup and windows layout work was prevented.

Once the old GP 8.0 components were in our GP 2010 development dictionary, we were able to use Dexterity to add the sanscript code back into the dictionary.  We did have a few clues.  For one, the placeholder "(missing)" was in every place were code used to be.  So, we just had to fill in the blank.  But for table activities, we did have to do some good old-fashioned reverse-engineering to figure out witch tables were updated when, and with what values.

All told, it was a challenging project, but we still got it done in what I would image was less time than the original development effort, and we were able to keep the solution 100% compatible with historical data.

So here's the moral of the story... if you need to customize GP, be sure that your VAR is willing to share the source code with you in advance.  And when the development is done, but sure you ask for a copy of the source code.  This will come either in the form of a "Development Dictionary" (which zips down to about 20MB) or an "Extract Dictionary" (which zips down to usually about 1MB).  Either of these could be used by another GP developer to salvage your investment.

Wednesday, June 27, 2012

Microsoft Dynamics GP allows duplicate items? Sort of.

We have a client here in Minnesota that reported having duplicate item numbers in Microsoft Dynamics GP.  Knowing that the item number is the primary key of the item master table (IV00101.ITEMNMBR), I knew this could not be true.  So there must be some invisible character.

We did find that after the item number an additional ascii character existed, character #160, which is the non-blank space (nbsp from HTML).  A user must have copied and pasted an item number from a webpage that included a nbsp character after the item number, and GP allowed this action.

To correct the issue, we changed the item description for the item with the char(160) to be "Invalid".  Then, we used the GP Professional Service Tools Library's Item Combiner tool (which is now free to current GP customers) to merge the "Invalid" version of the item into the valid version of the item.  Problem solved!

Thursday, June 7, 2012

Microsoft Dynamics AX Security Errors

This week I was doing some report modifications for a Microsoft Dynamics AX client in Minneapolis, when I found that I was unable to run the basic SalesInvoice report in AX.  When I ran the report, I received these errors:

Error while setting server report parameters. Error message: The permissions granted to user 'domain\userid' are insufficient for performing this operation. (rsAccessDenied)

The permissions granted to user 'domain\userid' are insufficient for performing this operation. (rsAccessDenied)

No help online for this error.  But I pulled in one of McGladrey's local SQL Server Reporting Services experts, and the two of us reviewed the situation together.  We found that someone had overridden the folder security on the "StaticReports" folder within the "Microsoft Dynamics AX" reporting folder.  Because of this, the "Dynamics AX Users" group was missing, and therefore the report would not run.  We simply reverted security back to the parent folder (which was still correct), and the issue was resolved.


Wednesday, June 6, 2012

Microsoft Dynamics GP - Custom Multi-Entity

This past month we completed a large Microsoft Dynamics GP project in Minnesota where unique requirements drove us to support a framework within Great Plains that supports multiple legal entities within a single SQL Server database. 

This is where the power of Microsoft Dynamics GP comes into play.  As it turns out, there is a single intervention point within GP that handles the posting of all GL entries.  We had one GL segment dedicated to legal entity.  All we had to do was to add a trigger to the posting of all general ledger entries that checked the balance of debits and credits by legal entity.  If the debits and credits did not equal by legal entity, then this solution automatically added "due to" and "due from" entries to balance the journal entry, just before the entry finished posting.  Then the posting contined through, and the intercompany affect was realized. 

A great project, a great success.  Time to celebrate.

Thursday, October 6, 2011

VBA: Compile error when line breaks used

I originally found this bug on GP 9.0, and just this week found that it is still a problem in GP 2010.  VBA developers beware!

It is a common practice in VBA development to use a line continuation character so you can wrap a long single line of code onto multiple lines.  This continuation character is an underscore (_) preceeded by a space.  Here’s an example:

Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
    Alias "SHBrowseForFolderA" (lpBrowseInfo As BrowseInfo) As Long

In GP, if you use line continuation characters in any “Declare” statements at the top of your VBA code window, everything will compile and work correctly.  However, when you export your code to a .package file, then re-import it into GP, it will add an additional “()” to the bottom of the imported code, causing a compile error when you choose "Compile" from the Debug menu.

Declare line break error
Compile Error:
Syntax Error

To prevent these additional characters from being added to your code when the package file is imported, remove the line continuation characters from your declare statements.

Below is a VBA module I've created that opens the windows folder browser window and returns the selected path.  The declare statements in this example have line continuation characters, so when you import this package and compile, the issue will happen to you, too!