MBS Guru

Dynamics GP ActiveX component can't create object run-time error 429

2011-12-28T15:03:00.007-06:00

Recently I was asked to look into an error a client was receiving while standing up a new Citrix server installation for their Dynamics GP 9 clients:

NOTE: If you are recieving this error while attempting to upgrade from Dynamics GP 9 to Dynamics GP 10, see this article.

When we hit the debug button to look at the underlying VBA code, we saw this line:

Based on the line of VBA code above, I knew that a connection to a database was needed, and the RetrieveGlobals9.dll was not installed on the new Citrix server. You might see slightly different code in your VBA, but the important part was that it was trying to call CreateObject on RetreiveGlobals9.

The first step to solve this problem was to download a copy of RetrieveGlobals9.dll from Partner Source at this URL.

Contact your partner to download this for you if you don't have access to Partner Source. Place the file into the Microsoft Dynamics GP folder in the Program Files directory. Since this was a 64-bit machine, the Microsoft Dynamics GP folder was in Program Files (x86).

Next, open a command prompt in elevated mode.

Finally, to register the .dll, type:

Regsvr32 "C:\Program Files (x86)\Microsoft Dynamics\GP\RetrieveGlobals9.dll"

You will need to include the quotes because the directories have spaces in them.

If everything worked properly, you'll receive a confirmation that the .dll was registered successfully.

After these steps were complete, we were able to open and use Microsoft Dynamics GP without receiving the initial VBA error.

Hope it helps!

Bryan Prince

Record Lock Trace v 2.0

2011-08-28T11:37:00.004-05:00

I've updated the Lock Trace Utility for Dynamics GP with 2 new features.

The first enhancement will replace the standard "This batch is being edited by another user" prompt when attempting to post or delete a SOP Batch with the name of the user that has the batch locked with an activity record in SY00800.

The second enhancement will clear all records that are locked by users that are not actively logged in to GP. This will be executed when attempting to access a transaction locked by a user that is not actively logged in. After, the record will be available and the following prompt will be presented.

The basic delete statements executed by this feature are listed below:

delete tempdb.dbo.dex_lock where session_id not in (select SQLSESID from dynamics.dbo.activity)

delete tempdb.dbo.dex_session where session_id not in (select SQLSESID from dynamics.dbo.activity)

delete DYNAMICS.dbo.SY00800 where userid not in (select USERID from DYNAMICS.dbo.activity)

If you are not sure what version of the utility you have installed, since there isn't a GUI, I've updated the about message to include the version information.

Download Record Lock Trace for Dynamics GP here or view the product page for more information. Please leave a comment if there are other features or transaction types that you would like added to this utility.

Reconciling SOP Batches from WITHIN Dynamics GP

2011-08-09T21:53:00.005-05:00

An article I posted sometime ago with a SQL Script that would reconcile SOP Batch Totals has really developed legs recently. It's been downloaded several times a week for several months. As a result, I felt some enhancement was in order.

To that end, I've compiled the same logic, with few exceptions, into an assembly you can download here. Copy this to your Addins folder and then you can perform this same function from the Sales Batch Entry Additional Menu without having to run a SQL script or access the Reconcile Receivable Amounts Window in GP.

One exception to the logic in the script is that this will not delete empty batches. If you have an opinion as to how valuable that feature might be please leave a comment and I will evolve this accordingly. This utility will simply do the following:

Update batch totals for those that are NOT locked by a user with a Batch Activity Record (SY00800). This was not designed to reconcile a specific batch but rather all batches at once. So, don't select a batch in Sales Batch Entry when executing this routine unless you want the batch selected to be excluded from the logic.
Create missing Batch Header records for batches that do exist in SOP WORK (SOP10100) but not SY00500.

This will be especially useful if you want to enable GP users to resolve SOP Batch Total discrepancies on their own without having to grant them access to the Reconcile Receivables Amounts window.

This was developed on GP 2010 and tested on both GP 2010 and GP 10.

Resolving "Error occurred in deployment step 'Activate Features': Invalid file name"

2011-06-07T13:35:00.003-05:00

The other day I was working on a SharePoint project that required the deployment of a Content Type, a List Template, a couple of List Instances and a couple of Feature Receivers. Things were coming along well until I started to reorganize the project. I dragged the List Instances into the List Template folder and renamed several folders to better represent their purposes. When I went to deploy, I got the error: "Error occurred in deployment step 'Activate Features': Invalid file name".

I looked around the .spdata files, checked the Feature file and double-checked them all again. Everything looked right. The ULS Logger wasn't much help but it did give me the actual exception: "Exception: Microsoft.SharePoint.SPException: Invalid file name. The file name you specified could not be used. It may be the name of an existing file or directory, or you may not have permission to access the file"

Still not much help. I removed all the items from the feature and was able to deploy successfully. Unfortunately, a feature that doesn't do anything isn't much good so I started adding items and deploying one at a time. Feature Receivers: Check. Content Type: Check. List Template: Check. List Instance: Failed.

After looking at the List Instance for a while and not seeing the problem, I deleted the Instance from the project and recreated it from scratch. I added it to the feature, deployed and it failed again.

I went through the List Template again and saw the familiar warning:

<?xml version="1.0" encoding="utf-8"?>

<Elements xmlns="http://schemas.microsoft.com/sharepoint/">

<!-- Do not change the value of the Name attribute below.

If it does not match the folder name of the List Definition project item,

an error will occur when the project is run. -->

<ListTemplate

        Name="OldFolderName"

        Type="10001"

I had renamed the List Template folder from within Visual Studio but it did not update the ListTemplate Name element.

After correcting the Name to match the new folder name everything worked and the world was right again.

Add Solution:

Found 1 deployment conflict(s). Resolving conflicts ...

Deleted list instance 'Lists/SomeAwesomeList' from server.

Adding solution 'SomeAwesomeSolution.wsp'...

Deploying solution 'SomeAwesomeSolution.wsp'...

Activate Features:

Activating feature 'SomeAwesomeFeature' ...

Run Post-Deployment Command:

Skipping deployment step because a post-deployment command is not specified.

========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

The Dynamics GP Blogster: Microsoft Dynamics GP 2010 R2 is hot off the press...

2011-04-25T18:42:00.000-05:00

The Dynamics GP Blogster: Microsoft Dynamics GP 2010 R2 is hot off the press...: "Microsoft has made GP 2010 R2 available prior to the original launch date of May 1, 2011. The product can be downloaded from CustomerSource ..."

File not found: VBA6.DLL in Microsoft Dynamics GP 9 Visual Basic Editor

2011-04-15T20:16:00.004-05:00

Today I started a new project that had a requirement to create a new form in Microsoft Dynamics GP 9 to track additional item attributes.

On a clean install of Windows 7 32-bit, Office 2010, and Microsoft Dynamics GP 9, I received the file not found VBA6.dll error as soon as I'd made my first VBA modification:

I tried a few fixes including downloading and registering a new copy of the VBA6.DLL, but the error persisted.

Then I found a post by Beth Gardner describing the same problem. The resolution involved both a registry edit and the deletion of several files from the Dynamics GP folder.

The solution worked perfectly for me and I expect to see this problem more in the future as it seems to be related directly to an install of Office 2010.

I could not replicate the problem on a clean install of Windows Server 2008 R2 on which I had not installed Office 2010.

Microsoft Responds with Excellent Customer Service

2011-04-03T11:45:00.000-05:00

I discovered a problem in the way Dynamics GP Evergreen Contract Lines calculated revenue when 1) billing annually and 2) entering a quantity > 1 on the Contract Line. Quite simply, it just didn't calculate the Total Amount on the Contact Line or the Invoice Amounts accurately. This client is running GP 2010.

This was discovered late in the implementation finally being logged as a bug by Microsoft Support just days before data conversion was to be completed for the cutover to production. Yikes!! I was in quite a pinch without a good Plan B just days before go live. After discussing the situation with the Microsoft Field Service Development team they mobilized to produce a fix for this problem in just over 24 hours. Great response!

They really saved the day to ensure a smooth go live and successful Field Service Series implementation for my client. A hotfix for this problem is forthcoming from Microsoft. If you find yourself struggling with this issue when working with Evergreen Contracts in Dynamics GP Contract Administration shoot me a message and I'll help you get by in the meantime.

Voting for the Most Influential People in Microsoft Dynamics continues...

2011-03-28T08:52:00.000-05:00

Check out the Dynamics Community blog post on the voting for the most influential people in Microsoft Dynamics. They did a great job of highlighting the Dynamics GP contingent to make it easy for you to vote. They've moved me to Poll 5.

Dynamics World List of Influential People

2011-03-15T07:41:00.000-05:00

I'm not really sure how, and no I did not nominate myself, but I made it into the top 260 nominees for Dynamics World's Microsoft Dynamics Most Influential People. Thanks to who ever did nominate me. You can view the list of nominees here.

Several of my old friends are on the list; Mark Polino, Ross Carlson, Dwight Specht, Troy Ensor, Bob McAdam, and Shane Hall to a name a few. Plus some of my new friends like Mariano and Dave Musgrave will surely be moving up the list this year. People like Andy Hafer really deserve the recognition for their contributions to the community as do all of the Dynamics MVPs that we all hear from so often. Many of those who run the popular VAR and ISV organizations are on the list as well.

You can vote for those that you think deserve recognition for their contributions at http://www.dynamicsworld.co.uk/2011Voting.php. You will have to scroll through the polls to find individuals you might want to vote for. I'm in Poll 11 as are Dwight Specht (IBIS), Tony DiBenedetto (TriBridge), Andy Hafer (GPUG), and Bill Marshall (MC2).

Ooops! I marked a SOP Line as Drop Ship after I fulfilled it

2011-03-11T09:57:00.000-06:00

I recently received a request to lock down the Sales Transaction Entry Line Drop Ship checkbox when a line had been fulfilled. This purpose was to prevent users from mistakenly marking a fulfilled line as Drop Shipped which automatically removes all of the Serial Numbers from the line.

To do this just add the Sales Transaction Entry window to Visual Basic. Then add the Quantity Fulfilled field and Drop Ship checkbox to your project. Copy the code below and paste it behind the SalesTransactionEntryDetail(Grid):

Private Sub Grid_AfterLineGotFocus()

DropShipLock

End Sub

Private Sub QtyFulfilled_AfterUserChanged()

DropShipLock

End Sub

Private Sub DropShipLock()

If QtyFulfilled.Value <> 0 Then

Dropship.Enabled = False

Else

Dropship.Enabled = True

End If

End Sub

This will enable or disable the Drop Ship checkbox based on whether the Quantity Fulfilled is equal to 0 on both the Grid After Line Got focus and Quantity Fulfilled After User Changed events.

You can do this yourself as I describe above or download and import the package file with this code here.

GP Password Expired mid-day... while I was posting a Batch

2011-03-08T07:45:00.000-06:00

I was recently talking shop with another consultant that has a customer who's Dynamics GP password was valid when they logged in but expired after causing a batch posting interruption. Has this ever happened to you?

He submitted a support request to Microsoft to explain this issue and seek out a resolution. The response he received was:

As for ways to overcome the password becoming invalid mid-day, there are two options:

1. Keep the password synchronized (time-wise) with the windows password and use the windows password reminder as a GP password reminder
2. Use the GP Password Expiration Notification utility, freely available from the blog link below

http://mbsguru.blogspot.com/p/dynamics-gp-password-expiration-notice.html

Microsoft recommended the GP Password Expiration Notification utility with qualification, of course, that it is not something Microsoft created or provides support for. Even so, it's nice to see that this utility is serving the community well by helping to solve and prevent common problems.

If you haven't downloaded it already you should before you have to recover from a batch posting interruption because your password has expired.

Dynamics GP 2010 R2 Feature List

2011-03-04T10:57:00.003-06:00

Straight out of Tech Conference in Fargo this week, thanks to Dave Musgrave http://blogs.msdn.com/b/developingfordynamicsgp/archive/2011/03/04/microsoft-dynamics-gp-technical-conference-2011-day-1-morning.aspx, I came across this graphic that lists some of the exciting new features coming out with Dynamics GP 2010 R2:

Dave goes on to explain that not only is Dynamics GP not being laid to rest as some have speculated since Microsoft begin investing in Dynamics AX nearly a decade ago but rather in a future release they are planning to include a web based client for Dynamics GP. That's a great improvement that I think we can all look forward to but first let's look at what's coming now:

Field Service Series customers should be looking forward to some of the FSS enhancements on deck:

The Contract Line Hold feature is well overdue. I've had several clients that need this functionality.
Tech Stock Replenishment will be a much welcomed addition. I'm actively working on a project where we are having to work around this.
Contract Transfer Approval Workflow is something else our Contract Admin clients have been asking for. It's as though Microsoft is reading my mind.

The Reporting and BI enhancements for Dynamics GP seem endless:

More Excel Report functionality; Bulk Deployment, Group Excel Reports, Multi-Company Support, and Default Column Ordering will be great enhancements.
MORE Analytical SSRS Reports and Metrics. I'm not sure any of us can get enough of those!
New Business Analyzer! Mariano touches on that in his coverage of the conference here http://dynamicsgpblogster.blogspot.com/2011/03/microsoft-dynamics-gp-technical_02.html.
MORE Word Templates; SOP Returns and Word Template Generator. I was skeptical of the Word Templates after first seeing them at Tech Conference 2010 but I have to admit they're growing on me.

There are several other great features and enhancements to Extender, Deployment and Migration Tools, and Email Support among others. It's always good to see that Microsoft continues to invest in what is already such a fantastic product. Well done.

Join us at our Microsoft Dynamics CRM 2011 Launch Event

2011-03-03T11:41:00.000-06:00

Are you ready to renew your focus on business productivity? Then join us at our exclusive Microsoft Dynamics CRM 2011 launch event in Nashville where we will be exploring the power of the most highly anticipated CRM release in years.

This in-person event, hosted by Straight Arrow Consulting, Inc. CEO Tom Karpowich, will include a live demonstration from a true CRM expert - Ben Vollmer, as he shares with you ways he has helped other companies boost sales, improve marketing performance and enhance customer service through better use of their CRM technology. Ben is known for his impressive energy and deep technical expertise - You will enjoy this time with such a true CRM guru!

This event is geared towards a select group of business and technical professionals—allowing you to network with Microsoft professionals and other customers in your area in an open, round-table style discussion about the concept and place of CRM in modern business.

We’ll help you envision your organization reaching unparalleled levels of effectiveness with:

The fluent Microsoft Office user experience which saves employees time and increases adoption
More insightful and actionable dashboard data
Built in business connections, team management and process collaboration

The Microsoft Dynamics CRM 2011 Launch Event takes place on Wednesday, March 16th, 2011 at 8:15am. It could be that start you need to renew your focus on your own business’ productivity!

Visit http://www.straightarrowusa.com/microsoft-dynamics-crm/crm-2011 for more information or register now at https://www.clicktoattend.com/invitation.aspx?code=152507.

I hope to see you there!

Love, Hate and the ViewState

2011-03-02T07:47:00.006-06:00

I was recently tasked with creating a SharePoint interface to Microsoft Dynamics GP Item Maintenance. As the client's business had grown, inconsistencies in theirItem Master became apparent. When new items were needed, a similar existing item was copied and the details updated to match the new item. If there were no similar items, a new item was created.

The problem was, many of the existing items were not properly categorized. There were people in the organization who knew bits of information about items, but nobody had all the information to correctly set up an item. Setting up an item properly required a combination of phone calls, emails and a little bit of luck. It could sometimes take weeks to get the item set up. This held up BOMs, Routings and pretty much everything else dependent on the new item.

As usual for a new project, we started with a discovery phase. We tried to identify the groups that knew the necessary bits of information about items and proceeded to schedule interviews. During the interviews we gathered a lot of information to help us get started. We also found several points that were unclear: i.e. Accounting said Billing provides this. Billing said it was Sales, Sales thought it was Purchasing, Purchasing pointed to Engineering and Engineering said it was Accounting. After a few round trips, we were able to pin most things down but in the end, there were still a few bits of information that nobody understood.

It was clear the Workflow would have to be very flexible. Adding to the complexity, some Items could bypass entire groups. For example, there's no need to set a price on an item if you don’t sell it. And why bother Purchasing if you are making this item in your own shop?

To solve this business problem, we needed to deliver a product with:

Flexible Workflow rules that could be adapted as the business changes (and as the users work with the system and discover steps that had been overlooked.)
Field-Level security to ensure each group can only edit their section.
The ability to assign some fields to multiple groups and users.
The ability to add new fields and groups as new requirements surface.
Audit Logging for accountability.
An intuitive User Interface.
The ability to open, edit and copy existing GP Items.
The ability to push the approved changes back to GP.

This was not a simple SharePoint WebPart with an ASP.NET Form you can throw on a Page and start using. The form had to be generated dynamically, with different sections editable by different users. Lookup Fields had to be populated dynamically. Some based on values stored in GP like Class Code, others with a Hard-Coded list of choices, like Item Type. Oh, and new lookup fields could be added at any time.

What's more, the fields and their rules were not known at design time. All the metadata describing the rules had to be parsed during form generation and postback.

While building this application I ran into a few challenges. One in particular had to do with updating posted values based on business rules and then rendering the fields, not as the user had posted, but as the rules dictated. Now that you know the background, how do we make this happen?

The ViewState is great! It makes your life as a web developer so much easier. Some action causes a postback and all the fields are repopulated with their values. You can handle events for a control without having to worry about the rest of the form. Controls get their IDs set automatically. What's not to love? Plenty.

The ViewState is transmitted and parsed with every page post/load cycle using bandwidth, memory and CPU cycles for the server and the client.
The ViewState, while encoded, is not encrypted. I've seen this argument and I don’t think it's really all that relevant. After all, you are sending the same information back and forth through the form fields. And if you need to keep a secret, use HTTPS.
The ViewState is persistent insistent. If I take the users' submitted data and do some processing on the backend, I just might want to change some values on the form when it is sent back. Suppose they tried to order 100 widgets but you just sold some and you only have 75. Send the form back to the user with quantity set to 75 and display a nice alert telling them they are lucky to get that many. Thanks to ViewState, the quantity field gets set back to 100 automatically. I know, JavaScript could validate the page before the user submits. But what if the user is running without JavaScript? I know I do unless I'm on a trusted page. Add-ons like NoScript offer significant protection while surfing and after a whitelisting your common sites, they pretty much stay out of the way. But even with JavaScript, if I'm dealing with Dynamic Data, (what other kind is there?) the validation rules may have changed since the page was loaded. I suppose I could build some AJAXy validation JavaScript, but again, you can't count on JavaScript being there and you should never trust anything a user submits, even if you think your JavaScript has sanitized it.

So, lets say we want to prevent ViewState from running. Easy enough, just set the EnableViewState property of the control to false:

         TextBox t = new TextBox();
         t.EnableViewState = false;
         t.Text = this.ToString();
         return t;

And what if you want to disable ViewState on the whole page?

      private void Page_Init(object sender, System.EventArgs e)
      {
         this.EnableViewState = false;
         //do some other interesting stuff 

      }

So, you’ve defeated the ViewState on a couple of projects and now it's time to build a SharePoint Web Part. Create the Web Part, add some controls, disable ViewState, deploy the Web Part and life is good! And we still have time to make happy hour (the first one!)

Wait a minute. Didn't you disable ViewState? Why aren't your backend changes sticking? Because SharePoint Web Parts love ViewState so much, they insist on using it. Disable it on the control? Doesn't matter. It happens anyway. Control.ClearChildViewState() doesn't even help. How about firing up SharePoint Designer and disabling it for the entire Page that hosts the Web Part? Congratulations, you've done it, and you’ve disabled pretty much all the SharePoint functionality too. No, there has to be way around this.

Actually, there are two:

Create a LiteralControl and build it's Text property with the HTML you need to create your form field. Now you can render the LiteralControl and IIS will never even see the field. Remember, you will have to inspect the Post Variables yourself to find out how many widgets you just sold. Unfortunately, I tend to make the occasional mistake dynamically building HTML from within a C Sharp app. Leave off a quote or miss a closing tag and your form starts acting really strange. That brings us to the other option.

Create your controls like normal and add them to parent controls if you like. No need to worry about the HTML, IIS will get it right. But instead of adding the controls to the page (or a page element) render them into the Text property of our friend the LiteralControl. Using a StringBuilder, a StringWriter and an HTMLTextWriter, it all falls into place

         //...Build your textbox as you like. 
         //Don't forget a unique ID. 
         TextBox tbQuantity = new TextBox();
         LiteralControl LControl = new LiteralControl();
         LControl.Text = this.RenderControlToString(tbQuantity);
         this.Controls.Add(LControl);

      public string RenderControlToString(WebControl Control)
      {
         StringBuilder sb = new StringBuilder();
         using (StringWriter sw = new StringWriter(sb))
         {
            using (HtmlTextWriter textWriter = new HtmlTextWriter(sw))
            {
               Control.RenderControl(textWriter);
            }
         } return sb.ToString();
      }

Using the HtmlTextWriter, we have well-formed HTML without having to worry about ViewState moving our cheese. Even in a SharePoint Web Part. And as a bonus, the ID you assigned to the Control is the ID that will be returned. No prepending all the parent control IDs by IIS. This, too helps to reduce the page size and load time.

Microsoft Dynamics ERP Push to the Cloud

2011-02-25T07:59:00.000-06:00

The Software Advice Blog has a interview clip up with Guy Weismantel, Microsoft's Director of ERP Marketing, in which he discusses their strategy for moving Dynamics ERP to the cloud.

He makes a good point; many ERP components are already delivered as cloud based services and we continue to move in that direction. A complete, cloud based ERP won't be suitable for every Dynamics ERP customer but will prove to be a very good for many.

Check out the clip at http://www.softwareadvice.com/articles/enterprise/can-microsoft-dynamics-erp-make-it-to-the-cloud-microsoft-executive-interview-1022211/.

Updated: Lock Trace Utility for Dynamics GP - PO Support

2011-02-16T11:58:00.001-06:00

I received a request to extend the Lock Trace functionality to support locked Purchase Orders. It was a very simple change so I went ahead and implemented that functionality. You can download this new version here if you're interested in knowing who has a Purchase Order locked when you attempt to access it.

I started working on another feature for this utility but didn't want to hold back this new feature while working on that. I created a product page here where I'll document release notes as this utility evolves.

Keep the feedback coming. I have several other ideas for this utility and would like to hear yours.

Register Now to Save - Convergence 2011 in Atlanta!

2011-02-16T11:01:00.000-06:00

The Early Registration Deadline for Convergence 2011 expires on February 21 and hotels are filling up. That's just 5 days away!

Register now at http://www.microsoft.com/dynamics/convergence/atlanta11/registration.aspx to save $300 off the regular price that kicks in on the 21st.

Register 3+ people and receive another $100 off for the the 3rd + Attendee. Straight Arrow took advantage of this one with many of us invading Atlanta this year. Hope to see you there!

Record Lock Trace Addon for Dynamics GP

2011-02-13T15:51:00.001-06:00

Natively, Dynamics GP doesn't always do a good job of enabling users to resolve common issues on their own. A classic example of this is the prompt displayed when attempting to access a record that is locked by another user. By default, as a user you are presented with the very generic message:

This is the message presented when attempting to access a document that is locked by another user in Sales Transaction Entry. Now, you can download the free Record Lock Tracing addon for Dynamics GP (Tested on v10 and GP2010) that will replace this generic message with a message that includes the specific user that has the record locked:

This has already solved some headaches for some of my clients eliminating the need to contact their internal IT department in order to determine which user has a record locked. This is particularly useful during periods with heightened urgency such as when processing shipments and invoices at month end.

This can be extended to include other transactions such as Purchase Orders. I'm happy to build on this further if there is demand for it. Please leave me a comment or send me an e-mail and let me know how I might extend this to add value for you.

Download Record Lock Tracing for Dynamics GP

Creating Customer Invoices using Sql Server Reporting Services 2008 R2

2011-02-04T11:07:00.005-06:00

Recently I was tasked with creating a customer facing invoice using Sql Server Reporting Services 2008 R2. This particular client had employed a technique using Page Headers and Footers that didn't upgrade properly once they moved to hosting their reports in SharePoint 2010 Integrated mode.

Because Page Headers and Page Footers are not allowed to have references to data fields, the main issue was with how to consistently place a totals section in a fixed position at the bottom of the page without using a Page Footer.

Making the task even more difficult was the fact that this report should generate all customer invoices at once. Page numbering had to be based on the individual invoice, not the total pages in the "report". Although SSRS 2008 R2 has introduced a new method to reset page numbers on a group, explained by Chris Hays here, I choose to generate my page numbers in SQL rather than in SSRS.

Each one of these techniques deserves a deep dive, but in general this report was accomplished by:

Using SQL Server window aggregate functions to both limit the number of invoice lines which should be presented on each page and to serve up the page numbers to be grouped upon and used in the display. Read this for more information: Aggregate Functions
Using one large cell of a table containing rectangles and a nested table for the order lines.
Fixing the report totals section at the proper location at the bottom of the report.

My idea was that I would limit the number of invoice line items to around 10 per page with my Sql generated page numbers. The report would be grouped by invoice number and page number. I would leave enough whitespace for those 10 lines to grow into therefore the information at the bottom of the report could be fixed just like a page footer.

I was having a difficult time initially getting this to work properly because the "footer" was getting pushed to the second page when the number of lines would grow. After some research, I discovered that the behavior for consuming whitespace has been changed in SSRS 2008 R2. See this article for details: Behavior Changes in Sql Server Reporting Services 2008 R2

Here is the important part:

Preserving White Space in a Report Body or Rectangle Container
Extra white space is no longer removed by default. When you render a report that had extra white space on the report body when viewed on the report design surface, the trailing white space after the last report item on the page is preserved. This may result in more pages for an existing report. To remove the white space, set the report property ConsumeContainerWhitespace to true.

Once I changed the ConsumerContainerWhitespace property to true, the report worked as I expected.

Dynamics GP Custom Links - Shipment Tracking

2011-01-30T12:09:00.001-06:00

I was recently reminded how useful Custom Links are in Dynamics GP. This feature is very powerful for streamlining your processes but often goes underutilized.

You can access the Custom Link Setup window in the Company section of the Administration page in Dynamics GP 2010 or at Tools>>Setup>>Company>>Custom Link. Here is a quick example of how you might leverage Custom Links to streamline your processes in Dynamics GP to drill back directly to shipment tracking information from Sales User-Defined Field Entry:

To setup this up for Federal Express, add a new Custom Link for your FedEx Shipping Method as follows:

You could setup a Tracking Number Custom Link for all Shipping Methods but each carrier has a unique page for tracking shipments. After setting up the Custom Link, in Sales User-Defined Field Entry, for documents with the Shipping Method for which you setup the Custom Link assigned; when clicking on the Tracking Numbers hyperlink you will be presented with the prompt entered in the Create/Modify Custom Link window.

When selected, the shipment tracking information for the tracking number you chose will be displayed in the web browser:

In this example, I don't have an actual shipment to track but notice how it did pull my tracking number on to the shipment tracking web page from Dynamics GP.

Some shipment tracking urls for common carriers are listed below:

DHL: http://track.dhl-usa.com/atrknav.asp?ShipmentNumber=%1

FedEx: http://www.fedex.com/Tracking?action=track&language=english&cntry_code=us&initial=x&mps=y&tracknumbers=%1
UPS: http://wwwapps.ups.com/etracking/tracking.cgi?submit=Track&InquiryNumber1=%1&TypeOfInquiryNumber=T
U.S. Postal Service: http://trkcnfrm1.smi.usps.com/PTSInternetWeb/InterLabelInquiry.do?origTrackNum=%1

There are many other great uses for Custom Links. I plan to explore some of those in future posts.

Dynamics CRM is holding its own with Salesforce.com

2011-01-17T13:53:00.001-06:00

Dynamics CRM is holding its own in the LinkedIn Poll "What is your primary Client Relationship Management (CRM) Software that you use?" running a very close second behind Salesforce.com.

Get out and vote!

Microsoft is finding success in the Enterprise Software Market

2011-01-17T12:55:00.000-06:00

ERP Software Blog has a nice post up describing how Microsoft is finding success in the Enterprise Software space due in part to their shift to Azure cloud capabilities.

The Microsoft Dynamics stack isn't just for small and medium sized business. We're seeing GP fit better and better in the enterprise space. Don't rule it out when considering your next ERP.

5 Reasons to Re-Implement your Dynamics ERP

2011-01-13T09:50:00.000-06:00

Are you frustrated with your Dynamics ERP system and starting to consider other options? Maybe you feel like you have outgrown your Dynamics ERP system and need to move on to something like SAP. Maybe it's just time to take a fresh look at your ERP strategy to take it to the next level.

Over the past few years we have completed several re-implementations of Dynamics GP for our clients while several others are considering the same in favor or moving on to something else. Here are some reasons why they are choosing to re-implement.

1. Enable new revenue streams and growth strategies:

After completing a Dynamics GP implementation for a client in the early 2000s they recently changed their distribution strategy to include direct sale retail stores and have expanded the number of customer service centers. During the initial implementation, neither was part of their strategy or a design consideration. Their Dynamics GP workflow and integration customizations were not designed specifically to handle some of these new requirements.

We are now in the process of planning the re-design and implementation of some of their customizations. With this new design, as they continue to expand their retail and service operations, on boarding these entities will be as simple as populating some setup screens on which their workflow, subledger to general ledger integration, and other system features will rely. Long-term, as they continue their expansion, this will save them consulting services costs and make them more nimble.

2. Automate standardized business processes:

We spent a significant portion of last year re-implementing Dynamics GP for a client that was originally implemented in the early 2000s. Since, they had further evolved and standardized their business processes as business process improvement and SOX compliance projects had been executed. They evaluated their options and considered replacing Dynamics GP with a "Tier 1" system. Through that evaluation they found that Dynamics GP could handle their requirements just as well for a fraction of the cost.

The result was a complete re-implementation of an integrated Dynamics CRM/GP system with a new custom application in between to handle product configuration. The project was completed for considerably less than the estimate to do the same with a common "Tier 1" system with the same or better results. They now operate their business free of disparate Excel spreadsheets, countless hours of redundant and unnecessary data entry, or years worth of hard coding implemented to quickly deliver on new or changing requirements.

3. Hodge Podge Syndrome:

ERP systems are "evolutionary" not "revolutionary". They grow, mature, and evolve over time as existing requirements are refined or more clearly understood and new requirements become known. As this happens new functionality in the form of 3rd Party ISV Products, new modules or integrated technology, and custom developed solutions is implemented. This is a common best practice that most often produces very good results.

Over time, if continuously reacting to an ever changing environment by implementing the first solution that appears to plug a GAP as quickly as possible, at the lowest possible cost you may end up with a variety of heterogeneous systems that don't integrate well or truly fit your requirements. Common results of this approach include business rules hard coded into queries serving reports and a myriad of patches that have turned your off the shelf solution into a custom solution.

Taking the time to carefully and proactively consider new requirements and match them to the best available solution with consideration for how it interacts with the existing environment will prevent this problem. If you don't, you may be sacrificing long-term continuity for short-term results.

4. The "vanilla", "out-of-the-box" approach left you with an incomplete system:

While "vanilla", "out-of-the-box" implementations are less costly and completed more rapidly than full scale deployments they are often incomplete. This is more commonly true for larger organizations. Typically, customers assume that they can simply follow-up Phase I with future phases to layer on additional functionality. This is not only true but is considered a best practice approach. However, if you don't plan for future phases while designing your initial implementation you could be left with many GAPS to fill later.

If those GAPS are not few and far between you might be left with a square peg/round hole scenario in which you are consistently working around design decisions made without consideration of the future functionality required. This is another situation where the software, if designed and configured around all of your business processes and requirements, will likely fit well. If you do take this rapid approach with your implementation make sure to consider any known future requirements in your design to ease the implementation of that functionality later.

5. Your initial implementation just wasn't successful:

There are many reasons why ERP system implementations fail. Inadequate requirements definition, inadequate customer and/or consulting resources, and unrealistic timelines or expectations are among some of the common reasons. Typically, the primary driver of an unsuccessful implementation is not the software itself.

Often, unsuccessful implementations can be salvaged. However, if the foundation is bad then you might be better served starting over. Of course, now you would have the luxury of hindsight to determine why the initial implementation failed and correct that before starting over. I would recommend you start by discussing this with your partner who has obtained the institutional knowledge to best ensure your success. Of course, if they were the problem you might consider a change.

In summary, don't assume that changing ERP systems will solve your problems or get you where you need to go next. You could very well have already purchased, many years ago, the ERP system that is best suited to run your business for many years to come. Whether you are experiencing some sort of pain or simply have a new ERP strategy to implement I recommend you consider all of the factors involved in delivering a world class ERP solution and take the steps necessary to prevent the problems you might have to react to later.

Happy Holidays!

2010-12-25T10:14:00.000-06:00

Happy Holidays everyone!

2010 has been great year for all of us at MBS Guru and Straight Arrow Consulting. We recently wrapped up an integrated Dynamics CRM/GP implementation further evolving our Equipment Rentals Solution for Dynamics GP, are still working to help a client open a new Retail Store with an integrated Dynamics RMS solution, and are excited about all of the great projects, clients, and new products we are working with in 2011.

Check back for more posts from me, Bryan, and others after the first of the year. As usual, we hope to grow to be more active in the community next year and expect to introduce a new MBS Guru contributor with a core competency around SharePoint soon. We have a lot going on and are excited to share.

Cheers!

Really???? Dynamics GP Report Writer is the Best Report Writer in the World?

2010-12-06T09:05:00.003-06:00

Wow! I never thought I'd have an opportunity to jump between Polino and Musgrave in a squabble but I just can't resist any longer. Maybe they'll break out the sumo suits at Tech Conference this year and they can settle this debate once and for all.

I think Dave is just trying to get a rise out of Mark but I'll chime in anyway. Like most, I lean towards the school of thought that Dynamics GP RW should NOT even be up for consideration as a the best in the world. BUT, that's not to say it can't be great.

You can pretty much do anything you want with GP Report Writer as along as you have 1) intimate knowledge of the inner workings of GP and 2) know how to customize reports using VBA. Dave demonstrates some of the advanced capabilities in his latest post on how Dynamics GP Report Writer is the greatest Report Writer in the World. The problem is, there aren't many that would have come up with such a solution.

One cannot be a great Report Writer these days unless just about anyone can write reports with it. Just about everyone is willing to stipulate that you cannot expect canned reports from any ERP system to meet all of your company's reporting and analytics requirements. That said, the first question asked by most GP prospects about reporting capabilities is; "How difficult is it for ME to write new reports to my specifications?". If not for SQL Server Reporting Services, and even Crystal Reports, this would be a much harder sell relying on Dyanmics GP RW alone. Additionally, existing reports often rely heavily on temporary Dexterity tables that can make even seemingly simple changes to existing reports a challenge.

Finally, Dynamics GP has evolved so that RW is meant to be only one of many report writers available to GP customers. GP does ship with a vast library of canned RW reports that most find to be extremely valuable in addition to a growing library of SSRS Reports. Some companies hardly have to customize reports at all or develop new ones from scratch. But, when you need to you can do so with RW or you can look to other widely available and commonly known tools such as SSRS and/or Crystal among others.

You can voice your opinion on the subject in Mark's latest Facebook poll.

We should celebrate the fact that we deliver and work with a system in Dynamics GP that ships with world class reports "out of the box" and a variety of common tools that enable you to customize or develop new reports from scratch on your own.

Register today for the 2011 Microsoft Dynamics GP Tech Conference

2010-12-06T08:30:00.000-06:00

The 2011 Microsoft Dynamics GP Technical Conference is open for registration. This Partner event is in Fargo again this year; March 1-3. They haven't posted the agenda, speakers, or sessions just yet but I'm sure we'll see Dave, Mariano, and some other familiar faces with some new information for us.

Support Debugging Tool Build 14 released

2010-12-01T19:47:00.000-06:00

The latest build of the Support Debugging Tool has been released by THE David Musgrave. Check out the fixes, enhancements, and new features here http://blogs.msdn.com/b/developingfordynamicsgp/archive/2010/12/02/support-debugging-tool-build-14-released.aspx.

Ask your partner to download it for you now.

Thanks Dave. Keep it coming!

Microsoft Dynamics 10 eConnect C# 4.0 .NET Serialization In Memory

2010-12-01T19:30:00.012-06:00

I was recently tasked with creating an eConnect integration between Microsoft Dynamics GP 10 and a custom SharePoint 2010 Portal designed to replace the GP Item Maintenance process with workflow and departmental routing.

Most of the eConnect examples I see online serialize the eConnect object to a file before loading it into an XmlDocument. This can cause unnecessary permissions concerns, and worse, if your application runs under the context of IIS or SharePoint you may not have a file system available to you at all. This was the case in my scenario. A much better approach is to serialize the object in memory.

Below is an example of eConnect serialization in memory using Microsoft C# .NET 4.0.

Assuming you have already installed the eConnect SDK 10, reference the following assemblies:

Microsoft.Dynamics.GP.eConnect.dll

Microsoft.Dynamics.GP.eConnect.MiscRoutines.dll

Microsoft.Dynamics.GP.eConnect.Serialization.dll

System.EnterpriseServices.dll

And add the following using statements:

using System.IO;
using System.Xml;
using System.Xml.Serialization;
using Microsoft.Dynamics.GP.eConnect;
using Microsoft.Dynamics.GP.eConnect.Serialization;

Here is the code to serialize the eConnectType object in memory and load it into an XmlDocument which is then passed to the eConnect_EntryPoint method:

string connectionString = string.Empty; // Set up a valid connection string.

eConnectMethods econnectMethods = new eConnectMethods();
eConnectType eConnectType = new eConnectType();

// Instantiate and populate the specific eConnect types needed.
// Don't forget to add them to eConnectType once they are set up.

// We're done building the object model, now serialize it in memory.
MemoryStream memoryStream = new MemoryStream();
XmlSerializer xmlSerializer = new XmlSerializer(eConnectType.GetType());
xmlSerializer.Serialize(memoryStream, eConnectType);
memoryStream.Position = 0;

// Create a new XmlDocument from the in memory serialized object model.
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.Load(memoryStream);
memoryStream.Close();

econnectMethods.eConnect_EntryPoint(connectionString, EnumTypes.ConnectionStringType.SqlClient, xmlDocument.OuterXml, EnumTypes.SchemaValidationType.None, string.Empty);

Hope it helps!

Sign up for Convergence 2011 Registration Alerts

2010-11-09T11:50:00.000-06:00

If you haven't already done so stop by the Microsoft Convergence 2011 site at http://www.microsoft.com/dynamics/convergence/atlanta11/about_overview.aspx and sign up for Registration Alerts. You don't want to miss out on the early bird discounts.

See you in Atlanta!

Business Process Improvement with Conditional Process Holds in Dynamics GP Sales Order Processing

2010-11-04T20:05:00.000-05:00

I was presented with a business process problem by a client related to managing Credit Limit overrides in Dynamics GP Sales Transaction Entry. This company does not supply Order Entry users with the Credit Limit override password. It's the Credit Manager's responsibility to decide whether or not to allow that override.

This client has been working for some time to generally route information to the right person that needs to make the decision and eliminate waste in their business processes. During the normal course of business when users are presented with the "Please enter the credit limit override password:" prompt the Credit Manager is called to the user's workstation to make the decision on the fly.

This is a very inefficient process. What a great opportunity for improvement!

To resolve this issue and get the information to the person responsible for making the decision while maintaining the system controls required we got creative with Process Holds, eConnect, and VBA with ADO. Here's what we did:

First, we removed the "Exceed Credit Limit" password from Receivables Management Setup:

Now, users would have been presented with the more friendly prompt that would allow them to proceed without a password.

I don't claim to be a SOX expert but I'm guessing that wouldn't pass a SOX audit. So, we added some VBA Code to the BeforeModalDialog Event behind the Sales Transaction Entry Window. Of course, to make this work you would need to add that window plus the DocumentNo and the SOPTypeDatabase fields to your project.

The code calls the taSopUpdateCreateProcessHold eConnect stored procedure and adds the "CREDIT" Process Hold to the Document when the credit limit prompt appears and closes the dialog box by answering Continue to the prompt automatically.

Now, instead of stopping the user in their tracks to make this decision on the fly the Credit Manager can manage this Process Hold separate from the Order Entry Process. To do this, we used SmartList Builder to build a SmartList that displayed SOP Documents On Hold. Then, we added a Favorite with a Reminder to the SmartList to show only SOP Documents with the "CREDIT" Process Hold applied.

Now, when the Credit Manager logs into GP and periodically throughout the day he can quickly and easily analyze the Sales Orders for Customers that had exceeded their Credit Limit and manage them accordingly.

Get the right information into the hands of the person who is responsible for making the decision and you too can improve your business processes and efficiency. It doesn't take much work or creativity to increase the ROI on your GP implementation using techniques such as this.

If you like this let me know. If enough people do, maybe we'll produce a new freebie for Dynamics GP!

Don't accept what you're given, make it into what you need it to be.

Dexterity isn't going anywhere... anytime soon.

2010-11-03T16:18:00.000-05:00

The topic of whether to execute a project by developing in Dexterity came up in a requirements discussion I had this week after The Dynamics GP Blogster and then Mark Polino opined on the subject. I have spent some time thinking about this myself as we're also working on plans for 2011. I don't think Dexterity is going anywhere as long as GP is around although it will continue to get depreciated over time.

When Microsoft purchased GP and consolidated ERP solutions under the Dynamics brand all the buzz was about both consolidating those solutions into 1 best of breed solution and/or replacing Dexterity with (insert your favorite .Net language here). I don't even remember how long ago that was. We're not really any closer to realizing either vision even though it's clear that .Net is the future for GP.

With the introduction of the Visual Studio Toolkit we're able to finally do some GP Development work in Visual Studio. That's great but we still can't create Alternate Dynamics Windows or really modify existing GP windows in Visual Studio. Dexterity still provides the tightest integration. For some development projects, you just can't get around that. I can't really speculate on how reasonable it is to expect that to change anytime soon.

Mariano makes great points in his IMHO post that it is basically (my opinion, not his) just not reasonable to expect Dexterity to be replaced by .Net unless GP is abandoned as a viable ERP solution. The last I saw the Dynamics GP roadmap extended beyond v14 (2016). When it comes to selecting a development tool for GP it's inevitable that Dexterity will rise to the surface as the best tool for some projects. It's not always my first choice but sometime it is the best choice. I don't foresee that changing anytime soon.

There's a good white paper on choosing the right development tool for your Microsoft Dynamics GP 10 Development Project. http://www.microsoft.com/downloads/en/details.aspx?FamilyID=a5b6c523-0add-48fd-9deb-2c0ef39b5673

Decisions 2010 - Extending Your GP Distribution Solution with the Field Service Series Session Materials Available for Download

2010-11-02T11:36:00.000-05:00

I have posted both a .pdf version and a .ppsx version of the Decisions 2010 - Extending Your GP Distribution Solution with the Field Service Series presentation for download.

The Emerging Partner in the Microsoft Dynamics Partner Channel

2010-11-02T10:35:00.000-05:00

NavigateIntoSuccess.com has a nice write up on the direction Microsoft is taking with small Dynamics partners based some insight from a blog post by Alex Chow. http://navigateintosuccess.com/blog/is-microsoft-killing-the-small-partners.

It is a tough environment and getting tougher for some but I agree with Vjekoslav that they're not trying to kill us off. I say "us" because my company could be considered a "small" partner but we don't fall into any of the 3 categories of small partners that Alex describes in his post:

1. Small newcomers that oversell and don't execute.
2. Internal companies that arbitrage the partner program to get free licensing.
3. Lifestyle partners that don't sell and therefore don't send any revenue Microsoft's way.

There's another category he didn't account for; 4. Emerging Partners - broadly experienced partners that are growing organically at their own pace.

There are many Emerging Partners in the channel that are focused on profitable growth at a pace that is manageable for their business. Often, Emerging Partners are a better fit for even the larger Dynamics customers. They specialize more and don't try to be everything to everyone which is a plus for customers that typically don't implement more than one ERP system anyway.

So, what is it that Dynamics customers are looking for in a Partner? A few of the primary characteristics of a good partner include:

1. A conscientious business partner that delivers value in excess of its cost with the highest level of integrity and professionalism.
2. Timely access to expert product consultants that can deliver the technology solutions and services required as well as other services the customer may not have anticipated needing.
3. Proactive communication through e-mail, phone calls, newsletters, webinars, blogs, etc. to deliver the latest Microsoft Promotions, Tips and Tricks, and Product Releases.

Emerging Partners engage on projects on which they know they can be highly successful and employ senior level consultants and developers. They are establishing themselves in the channel through superior delivery of services not another metric that does not benefit the customer such as top line software licensing revenue that drives margins for the partner. Margins take a backseat to customer satisfaction but overhead is lower for them. They won't win worldwide partner of the year but that's not their goal.

With a core team of the right people with the right mix of skills and experience an Emerging Partner can deliver the Microsoft Business Solutions stack extremely effectively; Dynamics ERP/CRM, SharePoint, SSRS/Business Intelligence, and Custom .Net solutions and services. Sure, there are projects on which more and/or other resources are required but Emerging Partners have their own networks of experienced professionals to fill those gaps when needed. The larger partners all face the same challenges as they push their own consultants to triple digit utilization while managing a healthy sales pipeline.

Emerging Partners have fewer full-time sales people which can be a benefit to the customer. They typically engage senior level consulting resources, project managers, or firm owners as account managers who can address customer needs directly; engaging sales resources when necessary. They maintain a more intimate relationship with their customer base. They often turn down opportunities to engage with new customers if those engagements/customers aren't a good fit with their strategy; 100% customer satisfaction and retention.

Emerging Partners can afford to grow more slowly, be selective on the work that they do, and focus on customers first and prospects second. There's a lot of value in that strategy for those customers that are lucky enough to work with an Emerging Partner.

Decisions 2010 Program Schedule is available

2010-10-20T11:23:00.000-05:00

With Decisions 2010 just 2 weeks away MSDynamicsWorld has published the Program Schedule online so you can plan your conference activities.

Check out the GP Day Program at http://decisions.msdynamicsworld.com/content/dynamics-gp-day-program.

See you there!

Decisions 2010 - November 2, 2010

2010-10-14T12:14:00.000-05:00

I have recently wrapped up the presentation on the Dynamics GP Field Service Series that I will be delivering at MS Dynamics World Decisions 2010 on November 2nd. I'm excited to be presenting this year, for my first time, so please register and join in. Too bad it's not being delivered live; no heckling.

There's a great group of presenters this year including popular bloggers and MVPs Mark Polino, Jivtesh Singh, and Leslie Vail among others. Visit http://decisions.msdynamicsworld.com/ to register today.

Posting Dynamics GP Transactions Automatically "Autopost.DLL"

2010-08-27T13:42:00.000-05:00

I ran across this post from Mohammad Daoud recently. I get requests all the time from clients needing to have Dynamics GP transactions posted automatically. Mohammad did some digging and found a tool from Microsoft developed using the Continuum API to post GP Transactions. GP must be opened and a user must be logged in but that's generally not too much to ask.

You can use it to post GL, Inventory, Cash Receipt, Sales Order Processing, and Receiving transactions. It's not free but most great things aren't. Check out Mohammad's post for more details http://mohdaoud.blogspot.com/2010/07/posting-microsoft-dynamics-gp.html.

Disable DELETE PO in Dynamics GP Purchase Order Entry

2010-08-27T13:06:00.001-05:00

The question; "Is there a way to turn off the DELETE option for POs?" came up in the newsgroups recently. If you need to, here's a pretty simple way to do so.

Add the Purchase Order Entry Window to Visual Basic.
Add the following code to the Window_BeforeModalDialog event:

If PromptString = "Are you sure you want to delete this record?" Then

Answer = dcButton2

Response = MsgBox("You are not allowed to Delete Purchase Orders.", vbOKOnly, "Microsoft Dynamics GP")

End If

Now, instead of prompting to confirm that you want to Delete a PO after selecting Delete from the Actions Button the system will prompt you that you cannot and cancel that action.

Using GP Security to Control for which users VBA code runs

2010-08-25T12:53:00.000-05:00

If you want to control for which users your VBA Code runs... you can... in some situations.

VBA Code can run behind a Modified or Original Window in GP, or not at all, based on the event mode you select in the window properties in the VBA Editor:

You could modify a window mildly and place your code behind the modified version just so that you can control for which users the VBA code runs by only granting access to the modified window to certain users. This won't work in every situation such as those in which you need to modify the window for all users but only want VBA code to run for some.

eConnect Nugget: Recalculating SOP Document Totals

2010-08-25T11:05:00.000-05:00

I didn't run across any documentation on an eConnect proc that I found very useful while working with some eConnect integrations recently. I needed to update, not insert, a SOP Header record after adjusting some line item prices through code.

I found that using taSopHdrRecalc worked great for this. The schema is basically the same as the taSopHdrIvcInsert so you can consult that schema reference in the eConnect Help when working with this proc.

If you weren't already aware, Steve Gray has posted the eConnect Help Online here.

The Top 10 Dynamics Freebies

2010-08-25T10:26:00.000-05:00

The Dynamics GP Blogster has published his list of the Top 10 Dynamics GP Freebies.

I'm excited that the 2 I have produced made his list:

GP Password Expiration Notice made number 3.
PO Purchases Account Required made number 8.

Check them out if you haven't already.

Default Process Holds for Dynamics GP SOP Doc IDs

2010-08-23T19:57:00.001-05:00

You can assign default SOP Process Holds to Doc IDs so that when Documents are entered Process Holds that could control the flow of work in your processes are automatically applied.

You might use default holds if, for example, every Order entered needs to go through a manual Credit Check OR all Backorders needs to be manually allocated. Then, you could use SmartList to view documents on holds that are important to you.

To assign a Default Hold to a Doc ID:

Navigate to Sales Document Setup through Sales Order Processing Setup. Depending on which type of Document you select the name of the Doc ID Setup window will vary.
In the Doc ID setup window click the Holds button on the bottom right hand corner of that window to open the Sales Process Holds Assignments window.
In that window, you can specify which Process Holds will be automatically applied to a Document when entered into GP.

Cancelling Dynamics GP Contract Lines

2010-08-23T19:36:00.000-05:00

If you ever need to cancel Contract Lines (Field Service Contract Administration Module Contract Lines that is) outside of GP try this:

DECLARE @RC int
DECLARE @CONSTS smallint
DECLARE @CONTNBR char(11)
DECLARE @LNSEQNBR numeric(19,5)
DECLARE @CANCELDATE datetime

-- TODO: Set parameter values here.

EXECUTE @RC = [dbo].[SVC_Cancel_Contract_Line]
   @CONSTS
  ,@CONTNBR
  ,@LNSEQNBR
  ,@CANCELDATE
GO

The team that developed the Field Service Series made it quite easy to leverage some of the same stored procedures called by GP to perform various functions such as this.

Disallow adding Serial Numbers on the fly when fulfilling Orders in Dynamics GP

2010-08-23T14:51:00.002-05:00

If you want to prevent users from adding new Serial Numbers on the fly when fulfilling Sales Documents in Dynamics GP try this:

Add the Sales Serial Number Entry window to VBA
On the Window_BeforeModalDialog event paste in this code:

If PromptString = "Do you want to add this serial number?" Then

Answer = dcButton2

MsgBox "You must select a serial number from the list of available serial numbers."

End If

Now, when a user attempts to add a serial number on the fly when fulfilling a Sales Document they will be presented with a message instructing them to pick one from the list instead.

Row by row analysis and execution with Cursors

2010-08-22T13:41:00.000-05:00

Being able to do row by row data analysis and manipulation with SQL could be something that's missing from your toolbox. Whether you're converting data or developing a customization, cursors can prove to be quite useful. I use this template often. Loops are generally preferred to cursors for better performance but I still go to cursors often.

This example will pass all of the ACTINDX and ACTNUMST values from the GL00105 table into a cursor called curCursorName and select the variable values, displaying them in the SMS query results pane, before going to the next.

All you have to do is 1) declare a variable for each value you need to select into your cursor, 2) replace the select statement with the data set on which you need to do row by row analysis or manipulation, 3) write you own logic for the EXECUTE THE LOGIC section, and 4) replace the variables in the FETCH sections with your own. Remember, the values must be selected into the cursor in the same order the variables are fetched.

--DECLARE THE VARIABLES

DECLARE @ACTINDX INT,

@ACTNUMST VARCHAR(50)

--DECLARE THE CURSOR

DECLARE curCursorName cursor fast_forward for

-- SELECT THE VARIABLES INTO THE CURSOR

SELECT ACTINDX, ACTNUMST

FROM GL00105 T with (nolock)

--OPEN THE CURSOR

OPEN curCursorName

--FETCH A RECORD FROM THE CURSOR

FETCH next from curCursorName

into @ACTINDX, @ACTNUMST

WHILE (@@FETCH_STATUS = 0)

BEGIN

--EXECUTE THE LOGIC

select @ACTINDX, @ACTNUMST

--GET THE NEXT RECORD

FETCH next from curCursorName

into @ACTINDX, @ACTNUMST

END

--CLOSE AND DEALLOCATE THE CURSOR

CLOSE curCursorName

DEALLOCATE curCursorName

Filtering Grids in Dynamics GP with Modifier/VBA

2010-08-22T12:20:00.002-05:00

It is pretty simple to apply custom filters to grids in Dynamics GP to control the data that loads into Lookups and Transaction Entry Windows.

In this example, my client wanted to be able to filter the Contract Maintenance - Lines window by Item Number and/or Serial Number. Their contracts can be quite lengthy. Research and contract maintenance would be cumbersome and inefficient without the ability to filter the records in this window.

To do this, use Modifier to add 2 new Text Boxes to the Contract Maintenance - Lines window in which to capture the filter criteria. I placed them directly above the grid in the window.

Next, add the Contract Maintenance - Lines window and the following fields to your VBA Project:

Item Number and Serial Number in the Grid
Item Number and Serial Number fields you added with Modifier

Paste the following code in the ContractMaintenanceLDetail Grid BeforeLinePopulate event:

Now, just click the Redisplay button to apply the filter.

This code will evaluate records as they are populated in the grid and reject those that don't match the filter criteria. In this case, I used the Like operator so that any records that did not contain the filter criteria would be rejected.

Dynamics GP Password Expiration Notice

2010-08-20T11:39:00.001-05:00

Last year, I posted a VST customization I thought I would sell to a client that would give users advanced notice that their GP Password was about to expire. That version was locked to only work in Fabrikam. Since that sale never materialized, I have unlocked it and am distributing it for free, "AS IS" with no warranties. You can download the unlocked version here! Just drop it in the GP\Addins directory. I have only tested this on version 10 of Dynamics GP.

The Dynamics GP Blogster has talked about why system administrators are not taking advantage of the password policy enforcement feature in Dynamics GP. If you or your customers are growing frustrated that your user's passwords are expiring without notice and IT is having to reset them frequently you might like this mod. It works like this...

For compatibility with SQL2005 I decided to store a Maximum Password Age with the Number of Days before expiration to begin notifying users that their passwords would expire. I could have integrated it with windows security policy or active directory to get these values directly but I didn't do that here. With SQL2008 you would only need to store the Number of Days before expiration to start notifying because you can query the DaysUntilExpiration LoginProperty in SQL2008. I wrote this work both ways depending on which version of SQL was running.

To capture the fields I needed I created a new Dynamics GP form in Visual Studio Tools that could be accessed from the Additionals Menu in Company Setup. I chose to store this data in the DUOS so that I didn't have to deal with creating any new tables. Therefore, you can setup each company to behave differently which could be a blessing or a curse.

Next, I added a NewHandler in VST on the SwitchCompanyCloseAfterOriginal event to compare the age of the users password to the maximum password age and days before notification captured in the PWExpirationNotice VST Form. If the user is due to be notified that their password would expire soon I present them with a message box...

... and then the User Password Setup window in which they could change their password on their way into the GP Company.

Reconciling SOP Batches in Dynamics GP

2010-08-18T11:57:00.002-05:00

Yesterday I posted a SQL proc that would reconcile SOP Batches and delete any that were empty. I realized that was somewhat incomplete after learning about a problem another client was having with missing batch headers. That also is not for everyone since there was not an option to NOT delete empty batches.

To that end, I have posted a new SQL proc that will reconcile SOP Batch Totals, optionally delete any empty batches, and add any missing batch headers. If this sounds like something that would be useful to you, feel free to download the proc here, load it on your GP company database, and then simply run the following to execute it:

EXEC [dbo].[SACi_sp_GP_SOP_Batch_Reconcile] 'Sales Entry', 1

The second parameter will drive whether or not empty batches are deleted. Pass a 1 to delete empty batches and a 0 to not delete empty batches.

This blog is provided "AS IS" with no warranties, and confers no rights.

Deleting Empty SOP Batches Made Easy

2010-08-16T13:03:00.002-05:00

I took a support call recently in which a client explained that they had accumulated hundreds of empty SOP Batches that they wanted to delete. In response, I produced a simple proc that will scroll through the "Sales Entry" Batches and, like Check Links on Sales Work, will update the Number of Transactions and Batch Total on each. This proc goes one step further and deletes any Batches for which the Number of Transactions is 0. Of course, there are many reasons why you might want to keep some of your Batches even though they are empty so this might not be for everyone.

If this sounds like something that would be useful to you, feel free to download the proc here, load it on your GP company database, and then simply run the following to execute it:

EXEC [dbo].[SACi_sp_GP_SOP_Batch_Cleanup] 'Sales Entry'

You could schedule a SQL job and run this to clean up your SOP Batches periodically or add a push button to a form in GP for ad-hoc execution from within the application.

This blog is provided "AS IS" with no warranties, and confers no rights.

More companies are leasing their Equipment in this economy

2010-08-06T18:20:00.000-05:00

I read an article in the Nashville Business Journal with survey results that more companies are spending but more are opting to lease their equipment instead of buying. I too am seeing the same out in the field.

If you're already using Dynamics GP to manage your sales operations you're just a few clicks away from using it for managing your leases.

http://mbsguru.blogspot.com/2009/04/dynamics-gp-for-equipment-rentals.html

Dynamics GP Conference Session Brainstorming

2010-07-19T11:34:00.000-05:00

I've been approached recently by a couple of organizations to speak at Dynamics Conference/Learning events this fall. I'd like nothing more than to give back to the community by offering up my time and experience to share with those that might be interested to hear what I have to say.

I'd like to hear from the community on this if you would be kind enough to spare a moment. What kind of topics, content, etc. do you feel are lacking at these types of events?

My technical expertise spans general GP functionality, SQL Server (Administration, SSIS, SSRS, T-SQL Development, etc.), and GP Development/Integration Tools (Modifier/VBA, Visual Studio Tools, Integration Manager, eConnect, etc.) among other topics.

You can browse some of my blog posts and LinkedIn Profile to learn pretty quickly what I am good/passionate about if you don't already know. I'd love to hear from you perspective how I might be able to best fill a gap at one of these events. Please share your thoughts with me by commenting on this post or shoot me an e-mail.

Thanks!

Advanced RMA Entry R2 - Available for Download/Evaluation

2010-07-12T12:09:00.001-05:00

Straight Arrow Consulting, Inc. has made Advanced RMA Entry R2 available for download and evaluation.

The Advanced RMA (aRMA) Add-on for Dynamics GP will help you to maximize the value of the investment you made in your Dynamics GP solution in a number of ways. This add-on is particularly well suited for manufacturers/distributors and equipment rental companies but has a place in any solution that leverages the Dynamics GP RMA module to service customers. aRMA extends the Dynamics GP RMA Module to accomplish the following goals:

1. Allow lines on a single RMA originating from many SOP Invoices.
2. Allow users to append RMA Lines from varied SOP Invoices to Existing RMAs.
3. Streamline RMA Entry to improve customer service levels & satisfaction.
4. Eliminate waste and redundancy in the RMA Entry & Management processes.

I published a brief article about Advanced RMA Entry when we first start working on this product late last year. Since, we have released the product and are working to make it more widely available. You can download a fact sheet with the most current functional overview along with the AdvancedRMA.dll file that you will need to install and evaluate the product.

All feedback, recommendations, requests, and inquiries are welcome.

Take a LOOK INSIDE! The Microsoft Dynamics GP 2010 Cookbook at Amazon

2010-07-08T13:56:00.001-05:00

I highly recommend that you take some time to browse the content in Mark Polino's recently released "Microsoft Dynamics GP 2010 Cookbook" at Amazon.com. You will be very impressed with the content.

This book is a must have for everyone. There are many, not very well known nuggets of information documented in the book that will help everyone from users just beginning to learn Dynamics GP, to Dynamics GP Administrators, to the most experienced field consultants.

We just don't get the opportunity to apply these concepts everyday. Having them at your finger tips like this is great. I'd like a pocket sized edition but it's nice to be able to download it to my Kindle.

Thanks Mark! Great work!

Is “Horizontal” Accounting Software Dead?

2010-06-30T10:47:00.001-05:00

Accounting Software blogger Chris Thorman has a new article up exploring trends towards a decline in demand for horizontal accounting software. There's little doubt that medium sized and large enterprises are looking for integrated solutions (often industry specific vertical solutions) and that the days of selling standalone, horizontal accounting solutions are winding down.

Dynamics GP does a great job of filling most of the needs of medium sized and large enterprises across most industries.

GP delivers functionality to create an integrated, enterprise wide footprint across you organization; Accounting, Distribution, Manufacturing, Project Accounting, Retail Management, and Field Service Management are just a few of the integrated solutions available from Dynamics GP.

It is easily designed, tailored. and/or customized through the implementation process to meet the specific needs of individual companies, functional departments, and even users. This value added service does not have to break the budget for an implementation. Given the pricing model for GP and the relative ease of implementation the ROI for a Dynamics GP implementation is very attractive to executives and stakeholders.

Microsoft is pushing partners to focus on developing industry specific, vertical expertise more and more and rewarding them for it. The ISV channel is producing great vertical solutions for GP at a rapid pace. I don't think there's any doubt that horizontal accounting software is on the decline. We're not selling or implementing accounting software anymore. For many of us, it's dead already.

Dexterity and IntelliSense: Who says nothing is impossible?

2010-06-28T19:22:00.001-05:00

The Dynamics GP Blogster is passionately taking on the challenge to get IntelliSense implemented in Dexterity. When I made the statement to him that the likelihood that this would ever happen is ridiculously low his response to me was inspiring; "Good inventions were never right the first time around, they took several tries and awareness".

I voted for this feature. If you want to get everything you can get out of GP you should vote for it too. Go vote today at http://connect.microsoft.com/!

Dynamics GP 2010 is available for download... now!

2010-05-03T17:03:00.000-05:00

Dynamics GP 2010 is available for download on PartnerSource and CustomerSource:

PartnerSource Download
CustomerSource Download

Microsoft isn't sending out media automatically opting for a green distribution strategy. Go download your copy and get started today!

"Microsoft Dynamics GP 2010 Cookbook" available for preorder now!

2010-05-02T12:50:00.005-05:00

Dynamics GP MVP and consultant extraordinaire Mark Polino of DynamicAccounting.net has been working hard on a new book titled "Microsoft Dynamics GP 2010 Cookbook". It's now available for preorder at https://www.packtpub.com/microsoft-dynamics-gp-2010-cookbook/book and scheduled for release in the August/September time frame.

If you know Mark Polino, have attended any of his Dynamics GP 50 Tips Sessions at Convergence, and/or have been following his blog over the years you know his overall knowledge of all things Dynamics GP is unmatched. If you are brand new to the world of Dynamics GP I have no doubt that you will find this tips style book, with content applicable to versions 8, 9, 10, and 2010, to be your most valued reference to expand and deepen your knowledge of Dynamics GP. It's excited to see his book being published. This without a doubt will have something for everyone.

The Best of Convergence 2010... from a Consultant's perspective.

2010-04-28T13:05:00.001-05:00

I had a great time at Convergence this week. With ~8,500 people in attendance this was quite an event that Microsoft pulled off remarkably. Here's are some of my takes on the conference and some reasons why you should consider attending next year:

1. Learning Opportunities galore! The opportunity for education and learning for everyone at Convergence is at an all time high.

I don't have to administer GP systems on a day to day basis in my role BUT getting the opportunity to sit through another Mariano and Dave show on the Support Debugging Tool reminded me how much work that can be. I had a client at the conference ask me why I hadn't turn him on to this tool before after he had attended the session. Ooops! Visit the Support Debugging Tool Portal for more information http://blogs.msdn.com/developingfordynamicsgp/pages/support-debugging-tool.aspx

I finally got to see a Polino 50 Tips session. You can download the slide deck here http://msdynamicsgp.blogspot.com/2010/04/convergence-2010-50-more-tips-in-50.html. I've been a GP consultant since 1997 and started working with Mark in 1999. Yet, I still learned a lot in this session. Even if you're the most seasoned consultant, GP administrator, or power user you need to check this out. Plus, if you sit up front you are better looking for it.

The interactive sessions were great. GPUG sponsored a session in which we discussed various solutions, problems, and uses applicable to SmartList Builder for Dynamics GP. Participants had an open forum with industry experts & Microsoft Support Engineers to voice their requirements or problems and get immediate feedback, suggestions, and in many cases solutions on the spot. That type of response alone was worth the price of admission.

2. Networking, networking, networking... Together.

Catching up with old friends like Bob McAdam at Tribridge, Dwight Specht at IBIS, Marc Blumenthal at Intelladon, Susan Looby at ACE, and my partner Tom Karpowich at Straight Arrow Consulting was great fun but reconnecting with current and former clients, renewing relationships, and making new connections really made this worthwhile for me. I was able to help a few people out with some problems they were having, a few others helped me find some solutions to some of my clients problems, and I was able to pick up a few new engagements to boot.

3. The Expo was terrific.

The ISV network for Dynamics GP is fantastic. The Closer was quite a popular attraction, I was able to help a client review some retail management solutions with Professional Advantage and Nodus Technologies, and some of the Atlanta Falcon's Cheerleaders dropped by. There's virtually a solution for every problem and requirement at the Expo. It was worth the price of admission just to be able to talk to all of the ISVs and take some of the products for a test drive.

4. Reviewing new and existing but under utilized functionality has better equipped me to serve my clients Today and Tomorrow.

Today: Everyone should spend some time with Excel Report Builder for Dynamics GP. It's extremely simple to use and administer. Admittedly, I had not used this feature of Dynamics GP. But, after sitting through a very well delivered session on the subject I now feel like an expert on the tool. It's that simple and will allow you to very quickly and easily create Excel Reports hooked directly to your GP data that can be accessed by anyone in your organization WITHOUT logging into GP. Saves time and money. Who doesn't like that?

Tomorrow: As a Field Service Consultant, among many other things, I was excited to see some new enhancements coming out to that suite of modules with GP 2010. Consolidated Contract Invoicing, support for Kits and Non Inventory Items, and multi-level escalations are just a few of the great new features coming out next month. Everyone is excited about GP 2010.

5. Dynamics CRM continues to impress on several levels.

I've been working on a CRM Implementation this year and am very familiar with the core functionality and customization tools so I didn't attend sessions on those topics. There were just too many other sessions I felt compelled to attend. But, it was obvious to me that the CRM footprint continues grow within the Dynamics family of products.

I have followed the evolution of the integration between Dynamics CRM and Dynamics GP and was able to attend the session on that. There are many integration options and they continue to grow rapidly. You can learn more about that here http://crm.dynamics.com/en-us/technology/integration.aspx. They demonstrated how non developers can quickly create unique synchronizations between the systems leveraging a simple mapping UI. Of course, there is advanced functionality that does require some developer skills, such as hooking up services to external applications, that will allow you to create more complex integrations.

In summary, I had a great time. The food and drink were abundant and varied. The accommodations were excellent and convenient. The location in the heart of Atlanta was fun and exciting. The people were engaging. The content was of exceptional value. I can't enough about how much I got out of this conference.

Other than the 3 mile hikes between sessions in the Georgia World Congress Center and some bottlenecks in the flow at escalators I just can't find anything to complain about. Of course, with over 8,000 people in attendance you have to expect some logistical challenges. I have to applaud everyone who made the event what it was. Well done! Thank you!

Start making plans for next year!

My Article was published on MSDynamicsWorld.com

2010-02-09T10:15:00.000-06:00

Check out my article on publishing Salesperson Specific Data using Dyanmics GP, Business Portal, and SQL Server Reporting Services out at MSDynamicsWorld.com

Generating charts in SQL Server Reporting Services

2010-02-01T10:39:00.000-06:00

SQLServerCentral.com has a nice post up with step-by-step instructions on generating charts in SQL Reporting Services. With Dynamics GP moving more and more towards SQL Reporting Services as the standard reporting tool this is worth a look.

Hybrid Development: Using VST to overcome the Cyclic reference of projects constraint in VBA

2010-01-19T12:04:00.008-06:00

When customizing GP with Modifier/VBA you may run into situations where you need to create cyclic references between .vba Projects. In this example, I needed to allow the assignment a Contract Type to each Item in Item Maintenance to meet a Customer's requirement. Contract Type Lookup is in the Field Service dictionary and Item Maintenance is in the Dynamics dictionary.

I first tried using VBA to 1) Open the Contract Lookup after clicking on a lookup push button I added to Item Maintenance and then 2) Update a new string field I added to capture and display the Contact Type assigned to each item in Item Maintenance. To do this with VBA I had to open Contract Lookup from Item Maintenance in the Dynamics dictionary and then set the value in my custom string field on Item Maintenance on the Contract Type Lookup Select After User Changed Event. To do this, I would have to set a reference from Dynamics to FieldService AND from FieldService to Dynamics. You'll quickly discover that this is not permitted in VBA.

Before the introduction of VST you would have had to create your own lookup window. A workable solution, but not optimal. Now, with the VST for GP the solution is simple. You can use the Dictionary Assembly Generator (Dag.exe) to create an application assembly for your modified forms. This is required to access local fields added to windows in GP using Modifier. Over at The Dynamics GP Blogster, Mariano does a good job describing how to do use the Dag.exe in one of his posts on hybrid development techniques. Mariano explains:

Use the Dictionary Assembly Generator (DAG.EXE) tool provided with Visual Studio Tools to generate the Application.Dynamics.ModifiedForms.dll application assembly for the forms dictionary. Since DAG.EXE is a command line utility, go to the command prompt then go to the Visual Studio Tools SDK folder (typically under Program Files\Microsoft Dynamics\GP10 VS Tools SDK) to execute it, as follows:

dag.exe 0 "C:\Program Files\Microsoft Dynamics\GP\Dynamics.set" /F /N:Dynamics

After adding a reference to the Application.Dynamics.ModifiedForms.dll that to your VST project, you are able to use VST to launch Contract Type Lookup from Item Maintenance and set the value in Item Maintenance on the Contract Type Lookup Select After User Changed Event without issue. The GPAddin.vb code may look like this:

The end result is the native Field Service Contract Type Lookup window nicely bound to the Dynamics Item Maintenance Window:

Next, you would need to save the Contract Type associated with the Item Number to the database. You could do that using either VST or VBA. That's beyond the scope of what I was trying to communicate here but not a bad subject for a future post. Let me know what you think.

Publishing User Specific Data from Dynamics GP using SSRS

2009-12-29T15:50:00.008-06:00

We recently finished a SQL Server Reporting Services engagement for a client requiring that the Sales Order Processing data displayed in reports be filtered by Salesperson. That alone is simple enough; you could simply add the Salesperson or Territory as a parameter on the SSRS Report, right? The challenge here was that reps could never be permitted to view another Salesperson's data. So, it couldn't be quite that simple. This was compounded only slightly by the requirement that, logically, sales managers needed to see all of the data for their Territories. So, depending on whether a Salesperson was a Rep or a Territory Manager the results would vary.

This client had attempted several different ways to meet their requirements only to find that the simplest method turned out to be the most effective both in terms of delivering results and controlling costs.

"Make everything as simple as possible, but not simpler." - Albert Einstein

To do this, start by simply assigning a network login to each corresponding Salesperson in Salesperson Maintenance:

Now, we wrote stored procedures that would accept the network login as a parameter, evaluate whether the user was a Sales Rep or a Territory Manager, and then return the required data to which the user was assigned. In this example, the stored procedure will return the Top 10 Customers based on Invoice Document Amounts. You can download the sample stored procedure here to see exactly how we did that and use as a template to create your own.

Next, configure your dataset in Visual Studio to simply call the stored procedure using parameters from the report.

Next, after building the SSRS Report in Visual Studio, configure the Report Parameters...

... to make the @WINLOGIN parameter Hidden, so users cannot change this value when running the report, and Default the value to the Global User!UserID.

Now, when running the report Users will be prompted to select or enter a Cut Off Date but will not be given the option to select the Salesperson or User for which they want the report to display data. That value is automatically passed to the stored procedure to deliver user specific results back to the report.

Password Expiration Prompt - Using VST to Notify Users before their Password Expires in Dynamics GP

2009-12-22T12:03:00.009-06:00

I was recently asked by another consultant to quote a modification that would give users advanced notice before their GP Password was going to expire. This customer was growing frustrated that their user's passwords were expiring without notice and IT was having to reset them frequently. The typical work around is to change your GP Password when you change your network password but that wasn't good enough in this instance. The customer ended up choosing to go with one of the ISV solutions instead of a custom solution for additional features but I went ahead and finished this in my "spare time" anyway.

To make this work with SQL2005 I had to store a Maximum Password Age and the Number of Days before expiration to begin notifying users that their passwords would expire. I'm sure you could integrate with windows security policy or active directory to get these values directly but I didn't do that here. With SQL2008 you would only need to store the Number of Days before expiration to start notifying because you can query the DaysUntilExpiration LoginProperty in SQL2008. I wrote this work both ways depending on which version of SQL was running.

To capture the fields I needed I created a new Dynamics GP form in Visual Studio Tools that could be accessed from the Additionals Menu in Company Setup. I chose to store this data in the DUOS so that I didn't have to deal with creating any new tables. Therefore, you can setup each company to behave differently which could be a blessing or a curse. Of course, you could change this to store the data in a custom table but it would also be nice if GP had a installation specific DUOS in the DYNAMICS database for this purpose. Hint, hint.

... and then the User Password Setup window in which they could change their password on their way into the GP Company.

The prompt and User Password Setup window are only presented on the first login for a session. So, the users won't be nagged every time they change companies without shutting down GP first. They also aren't forced to change their password but rather given the option to do so easily at a convenient time so that they don't get locked out later.

I chose VST over Modifier/VBA to ease deployment as much as anything. You can download the .dll for this mod if you want to try it out here. Just drop it in your Addins directory and it will work in Fabrikam.

The VBA Source for making the Purchases Account Required in PO Entry

2009-12-02T20:02:00.000-06:00

I did a blog post a while back where I made available the functionality to force users to enter a Purchases Account on PO Lines when one didn't automatically default in. That was compiled with the VST for GP. Since, I've received several requests for the VBA source code to do the same. Finally, I've gotten around to posting that for you. You can download the .package file here.

I know there's a better way to do this other than using ADO to check for the presence of the account number but someone much smarter than I once said "if it works, it's the right way". I'm sticking with that here but would like to hear about the techniques other are using to do the same or similar customizations. Please comment and share.

This will only work on GP 10 and importing the package file will overwrite any existing VBA code behind POP Entry and POP Item Detail Entry. Thus, the reason why I prefer using the VST version in this situation.

Avoid some GP Form Modifications with VSTools

2009-09-30T21:49:00.008-05:00

I've been doing a lot of work with Visual Studio Tools lately and find myself migrating more towards that from VBA. VSTools is not a replacement for VBA but in some cases can prove to be a more effective tool.

Modifying a Form in GP is often a simple thing to do with few repercussions if planned and executed properly. One drawback to a form modification is that you have to upgrade the modified forms even when applying service packs. This makes the service pack application and upgrade processes slightly more cumbersome. Not a big deal but another step in the process that in some instances you might be able to avoid. Some of my existing clients are starting to request VSTools customizations over VBA partly for this reason.

While this isn't always applicable, you can't modify a GP Form with VSTools after all, you can sometimes leverage the VSTools .AddMenuHandler method in place of modifying a form in GP. You can find more information about the .AddMenuHandler on page 71 of the VSTDGPProgrammersGuide.pdf.

Here's an example of how to use the .AddMenuHandler method instead of modifying a GP Form:

Below is the code to add an Additional Menu Handler that when executed calls a function that will retrieve the next numeric Fixed Asset ID and populate the Asset ID in the Asset General Information Window.

Dim FANextNumericID As System.EventHandler

Sub Initialize() Implements IDexterityAddIn.Initialize

FANextNumericID = New System.EventHandler(AddressOf FAGetNextNumericID)
FixedAssets.Forms.FaGeneralMaintenance.AddMenuHandler(FANextNumericID, "Next Asset ID", "N")

End Sub

Now, in GP you can simply use the Ctrl-N hotkey or select Next Asset ID from the Additional Menu…

… to assign the Next Numeric Fixed Asset ID to new Fixed Asset Record.

Now, not only do you not have to deal with merging Form Modifications and/or VBA code with existing Form Modifications or VBA projects but if there weren't any to begin with you don't have to deal with upgrading them when applying services packs or upgrading.

PO Purchases Account Required during PO Entry

2009-09-28T21:26:00.003-05:00

After collaborating with another consultant to develop a solution to make the PO Line Purchase Account required during Purchases Order Entry I decide to make this available for general use "AS IS" with no warranties. You can download the .dll here. This has only been tested with GP v10.

This wasn't as simple as just making the field required with Modifier since the Purchasing Item Detail Entry window, on which the field is located, does not have to be loaded during PO Entry. We accomplished the same thing with VBA but decided that the Visual Studio Toolkit for GP was a better option. If you want the VBA code just shoot me an e-mail and I'll send that to you.

The customization works like this:

During PO Entry, before you lose focus from a PO Line for which a Purchases Account was not entered or for which there was not a default value the system will notify you accordingly and stop you from continuing.

This customization will open Purchasing Item Detail Entry and then prevent the window from being closed or the record from being saved until the Purchases Account field is populated.

It's pretty simple but there were a few challenges we had to overcome. Now, you don't have to! If you do use it or just check it out leave me a comment and let me know what you think.

Advanced RMA for GP is here!

2009-09-23T19:54:00.008-05:00

The Advanced RMA (aRMA) Add-on for Dynamics GP will help you to maximize the value of the investment you made in your Dynamics GP solution in a number of ways. This add-on is particularly well suited for manufacturers/distributors and equipment rental companies but has a place in any solution that leverages the Dynamics GP RMA module to service customers. aRMA extends the Dynamics GP RMA Module to accomplish the following goals:

1. Allow lines on a single RMA originating from many SOP Invoices.

a. Tie the credit amount on each line back to the originating SOP Invoice.

b. Reduce the number of RMAs issued to customers and that they have to manage.

2. Allow users to Append RMA Lines from varied SOP Invoices to Existing RMAs.

3. Streamline RMA Entry to improve customer service levels.

4. Eliminate waste and redundancy in the RMA entry process.

Installation

The Advanced RMA Entry Addon is installed by loading the AdvancedRMA.dll in the GP\Addins directory on each Dynamics GP Client. That's it! One file on each workstation. The .dll contains the logic to complete the one-time server install. So, you don't have to run any SQL scripts, create any SQL Logins, etc. All you have to do is log into GP as 'sa' and after selecting "Advanced RMA Entry" from the Additional Menu in RMA Entry Update when prompted with ...

... say "Yes". This will load the one table (DYNAMICS.dbo.mjRegistration) required for this addon. This table tracks registration information only. Otherwise, the add-on leverages Dynamics GP security, standard database objects, and standard business logic for processing.

User Instructions

1. From the "Additional" menu in RMA Entry/Update select "Advanced RMA Entry".

2. In Advanced RMA Entry, enter values into the filter fields; CustomerID, From Item Number, To Item Number, From Date (Invoice Document Date), and To Date (Invoice Document Date).

3. Click "Apply Filter" to display the list of SOP Invoice lines that meet the criteria you've entered. Any Serialized Lines that are already in inventory or on unreceived RMAs will automatically be filtered out of the results.

4. Now, you can sort the results set in the grid then Mark the SOP Line(s) that the customer wants to return.

5. The RMA Qty will default to the Invoice Qty but can be edited for each line.

6. If a line had been previously partially or fully returned the user will be notified accordingly.

7. When the window is opened the RMA Type, Office ID, and Return Site ID are all populated from the RMA Setup Defaults. Before creating the new RMA you can modify those default values, select a Reason Code, and enter a Customer PO Number if necessary.

8. Finally, click "Create RMA" to create a new RMA using the RMA Interface values and SOP Lines selected in Advanced RMA Entry.

9. You will notice that you can drill back to the RMA in RMA Entry/Update from the RMA Number hyperlink. The RMA can be edited, lines added or removed, etc. in the RMA Entry/Update using standard Dynamics GP functionality.

10. After creating a new RMA, additional SOP Lines can be appended to it. Notice that the "Create RMA" label has changed to "Append RMA" and the Customer ID and RMA Interface Fields have all been locked. Modify the other filter options or re-sort the list of SOP Invoices then select additional lines to append to the RMA.

11. Drill back to the RMA again to see the new lines that were selected, modify the RMA as necessary, and even enter additional lines that didn't originate from a SOP Invoice.

It's very simple and efficient!

aRMA was built using the Visual Studio Toolkit for GP. I'll gladly share it with you if you want to try it out. Just e-mail your request to MichaelJ2@gmail.com.

GP Technical Conference - early registration ends TODAY!

2009-09-18T15:10:00.000-05:00

I know that I'm looking forward to this conference. It's been too long since I've made a trek through Fargo. I know Mariano and David will knock it out of the park. Plus, there's the Green Mill!

Message from the Microsoft Dynamics GP Team:

Registration after today is $595. Save $100 by registering today for the Microsoft Dynamics® GP Technical Conference 2009.

It is THE place to learn about “all things” Microsoft Dynamics GP – the product itself, innovative development tools, best practices and more. Additionally, attendees will gain technical readiness for Microsoft Dynamics GP 11 and will learn about new features in the upcoming release. There will be numerous opportunities to network and visit with the Microsoft Dynamics GP development, support and leadership teams as well as with other partners.

Highlights include:

· Interactive concurrent sessions – learn more about the solution from the Microsoft Dynamics GP team

· Roundtable discussions – visit with a small group over lunch on a variety of topics

· ISV Expo and reception – learn about the latest add-on solutions

· Study halls – review specific areas of the solution with experts

· Post-conference training – gain knowledge on Microsoft Dynamics CRM and xRM

The conference, scheduled for November 9 – 11 in Fargo, North Dakota, will provide insight, skills and knowledge that will help move your business forward. Simply put, the conference is THE place to learn all things Microsoft Dynamics GP. How can you afford not to attend?

Visit www.microsoft.com/dynamics/fargodeveloperconference for session descriptions, additional details and registration info. And hurry: the discounted registration rate ends on Friday, September 18!

See you in November at THE place for all things Microsoft Dynamics GP!

The Microsoft Dynamics GP Team

P.S. Plan to join us on Thursday, November 12 for a morning of complimentary post-conference training. These concurrent session will focus on Microsoft Dynamics CRM and xRM. Check the conference website for details. You won’t want to miss this … and it’s free!

Visual Studio Tools Integration with GP Security

2009-09-18T14:44:00.000-05:00

After reading many blog posts, TK articles, forum threads, and the like I pieced together from many sources how to build Visual Studio Toolkit forms that integrate with Dynamics GP security so you don't have to hard code a connection string or any environment specific settings in your application or .config file.

You'll need to get the GP Connection Library (GPConn.dll) from GP Support. You have to license the GP Connection object and obtain the keys. This will enable you to get around the VS Toolkit only exposing the unencrypted password. Once you have that you can simply use the code snippet below in your project to dynamically connect to the Dynamics GP database using the GP user credentials.

Dim resp As Integer

Microsoft.Dexterity.GPConnection.Startup()

' Create the connection object
GPConnObj = New Microsoft.Dexterity.GPConnection()

' Initialize
resp = GPConnObj.Init("key1", "'key2")

CompanyDB.ConnectionString = "DATABASE= " & Dynamics.Globals.IntercompanyId.Value
GPConnObj.Connect(CompanyDB, Dynamics.Globals.SqlDataSourceName.Value, Dynamics.Globals.UserId.Value,
Dynamics.Globals.SqlPassword.Value)

This is mostly straight from the documentation that accompanies the GP Connection Library from GP Support. From that, it was easy and convenient to build the connection string using Dynamics.Globals.IntercompanyID for the database and SqlDataSourceName for the SQL Server Instance in the connection string. The UserID and SQL Password need no explanation. Now, you can compile your .dll and deploy it to any GP installation without having to worry about any environment specific settings.

I'm sure there are more elegant ways of doing this and this may been clearly documented elsewhere but I didn't find it teed up for me this way. That would have saved me a little time. I hope this helps you!

Using Visual Studio Tools to Streamline Call Center Processes in Dynamics GP

2009-08-02T21:30:00.011-05:00

I am excited to be deploying a customization I recently wrote leveraging the Visual Studio Toolkit for GP and eConnect to production this week. This purpose of publishing this real world example is to demonstrate the value of extending your GP solution with the Visual Studio Toolkit and provide a basis for future posts on the subject.

The purpose of this application was to consolidate a customer’s Call Center process that required a dozen+ keystrokes and mouse clicks to enter data into 6+ different GP windows. Dynamics GP does a good job of handling most business processes but not always as efficiently as you might like. In this case, the Call Center regularly enters & receives new GP Field Service Series RMAs, creates Depot Management Workorders, and generates SOP Quotes to record transactions for after sales services offered to their customers. A key pain point for them was their time per call which impacts customer satisfaction and the ability of the organization to scale with call volume.

While working through their upgrade to GP 10 this customer made the decision to build new screens and integrations that will streamline and automate the data entry process to record a new RMA, create a Workorder, and ultimately quote the service for the customer. We chose to use the Visual Studio Toolkit to build the new user interfaces (Screen shot below). A key result is that the Call Center now only enters data into 1 window, down from 6+, and as few as 5 fields, down from a dozen+. The time required to process this data is down to only a few seconds. Now, customers will be happier with their increased service levels and the organization can scale with existing headcount as call volume rises; both critical during these tough economic times.

With their new After Sales Service Entry window the Call Center is able to leverage native GP lookup windows for Equipment, RMA Reason Codes, Items, and Customers and standard drill back functionality to view/edit data in GP such as Customers, Addresses, and Workorders. Information from various screens and data that was not previously as readily accessible (Workorder History) is now consolidated in a single user interface. With only 3 fields for which data is required to be entered after selecting the Equipment Record for which the RMA/Workorder needs to be generated new RMAs and Workorders are generated with a single click of a button.

Mariano has been and will likely continue to write about many of the concepts I applied to build this form. He’s just getting started and I plan to learn much more from him. I’ll continue to follow his series and hope to complement him with supplementary commentary, sample code, and such using this project as the business case.

Thanks to Steve Gray at 4Penny.net and his Visual Studio Tools Forum for helping through a couple of hurdles and Shane Hall at Axiom Business Software Solutions for the design work.

Removing a Reference to a Field or Object in VBA

2009-06-22T15:15:00.004-05:00

While upgrading some customizations to GP 10 I encountered a situation in which I needed to remove references to 4 fields out of 60+ on Sales Transaction Entry that had been added to VBA. Many of the valid references were to invisible fields. For a couple of reasons the standard method of copying the code to .txt, noting all of the fields that needed to be added back, removing the window from VBA, adding the window back along with all of the valid fields, and then pasting the code back in seemed very laborious.

It made sense in this instance to spend a few minutes trying to remove the invalid references without having to click so many buttons.

The steps to remove a field reference from a VBA Project are:

1. Export your Form with VBA to a .package file (Tools>>Customize>>Customization Maintenance).
2. Open the .package file with notepad and find the references to the field you need to remove.
3. Remove any references to your field. Most importantly, remove the WindowField reference. This is how VBA knows about the field. It will look like this:

WindowField "Transfer Button"
{
ArrayIndex "0"
DisplayName "Transfer"
Local "false"
ProgrammerName "Transfer Button"
}

4. Save your .package file and import the modified version. (Tools>>Customize>>Customization Maintenance).

NOTE: The technique described here directly modifies the contents of a customization package with Notepad. This technique is not supported by Microsoft Dynamics GP support.

Creating Drop Down Lists using Modifier with VBA

2009-06-17T18:56:00.017-05:00

Inspired by a recent post on the GP Newgroup, I've taken the time to document a simple customization I recently did for a client. This is a very common type of request that is relatively easy to do and adds a lot of value. You can greatly improve the quality and consistency of your data by using custom drop down lists instead of settling with free form text fields.

Some background on this example; The client needs to track an invoice format for each Customer. They have 3 different invoice formats and it is critical that each customer is assigned a valid format. This client uses Contract Administration for Billing. Another customization is reliant on this field to assign the Contract Type to new Contracts that corresponds to the customer's invoice format so that invoices are batched properly when Contracts are billed. Standardization and consistency are critical.

First, open the window, Customer Maintenance in this example, on which you want to add the drop down list and bounce into Modifier (CTRL+F10 or Tools>>Customize>>Modify Current Window).

In modifier, select the Drop-down List control from the Toolbox and add one to the Window. At this point, any free peice of real estate on the window will do. We'll move it into the right position later. I've added the (L) DDLM79 field you see just to the right of the City field in the screen shot below.

Next, change a few of the properties of the new Drop-down List (DDL) field. Click on the DDL and find the Properties window. It might be hidden behind another window. To activate it you could select Properties from the Layout Menu or enter CTRL+M.

On the Object Tab double click the Data Type entry to open the Local Field Definition window. I like to change the name to something logical; in this example, "ddlInvoiceFormat".

Then click the Static Value elipse to access the Static Text Values Window. Enter the values you want to appear in your drop down list and click Insert.

You're done with Modifier for now. Click Ok to close the Static Text Values and Local Field Definition Windows. Then select Microsoft Dynamics GP from the File menu to return to GP. When prompted, save the changes made to the window layout. Once back in GP, grant security to the modified window.

Now, you have a Drop-down List on your window with your static values. That's nice but only half the battle. Next, we need to add the VBA code to store the data. In this example, I chose to store the data in the User Defined 2 field on the Customer Maintenance window. You could have elected to store this in your own table (using ADO) or the DUOS.

To do this, open the Customer Maintenance window and add the window to your VBA project (CTRL+F11 or Tools>>Customize>>Add Current Window to Visual Basic). Then, add the fields you need on the window to your VBA project (Shift+F11 or Tools>>Customize>>Add Fields to Visual Basic). In this example, add the Customer ID, User Defined 2, and the new Drop-down List (ddlInvoiceFormat). Finally, open the VBA Editor (Alt+F11 or Tools>>Customize>>Visual Basic Editor) to add the code.

There is code on on 3 events:

Declare a variable to track whether or not a user has changed the Drop-down List (DDL) value.

Dim bolUserChanged As Boolean

On the CustomerID_Changed event set that variable to false.

Private Sub CustomerID_Changed()

bolUserChanged = False

End Sub

On the ddlInvoiceFormat_AfterUserChanged event set the UserDefined2 value equal to the Drop-down List value and the variable to True only if the CustomerID is empty. There's no point in setting the value otherwise.

Private Sub ddlInvoiceFormat_AfterUserChanged()

If CustomerID.Empty = False Then

bolUserChanged = True

UserDefined2.Value = ddlInvoiceFormat.Value

Else

Changed = False

bolUserChanged = False

End If

End Sub

Only if a User did not change the ddlInvoiceFormat value (bolUserChanged = False) set the ddlInvoiceFormat equal to the UserDefined2 value. This is how the value in the Drop-down List gets updated as you scroll through or select different records in the window. Of course, you wouldn't want to update the DDL if the user just changed it.

Private Sub UserDefined2_Changed()

If bolUserChanged = False Then

ddlInvoiceFormat.Value = UserDefined2.Value

End If

End Sub

The reason for putting the new Drop-down List field on the window anywhere space was available was to allow for testing and validation before moving it to a permanent place. You'll notice that the value stored in the User Defined field is an integer value that corresponds to each static text value. You could translate that with VBA and store the static text value or translate it when necessary later in a query or report. Once the code is in place and tested, go back into Modifier (Tools>>Customize>>Modify Current Window) to move it to a permanent position and in this case make the User Defined 2 field invisible.

Click on the User Defined 2 field and change the Visible Property to False. The new Drop-down List will replace that field on the window for the user.

Finally, move the new Drop-down List on top of the User Defined 2 field then change the tab sequence and size/align the field accordingly. The end result is a User Defined field that is no longer a free form text but rather a Drop-down List.

Scheduling Jobs in SQL 2005

2009-05-01T06:39:00.011-05:00

After posting Automatic Aging for GP a request to explain how to schedule such a script to run unassisted was posed. Leveraging SQL Server Agent Jobs to schedule tasks that can execute a myriad of different types of events is a powerful way to improve productivity and automate processes among other things. On top of that, it's incredibly simple!

In this example, I'm going to use SQL 2005. The process would vary depending on which version of SQL Server you are running. In SQL Management Studio expand the SQL Server Agent and right click on Jobs to create a new Job.

In the New Job Window, General Tab enter a name for your job, specify the owner and category, and enter a description if you'd like. I don't recommend using sa or individual user accounts as job owners. It's best to setup service accounts specifically for this purpose.

Next, Select the Steps Page and click the New button to create a new step. You can setup jobs with many steps of varied types. In this example, we'll create only one that will execute the rmAgeCustomer stored procedure. If you have multiple GP company databases you can setup a separate job to run RM Aging in each. Give the step a name, select Transact-SQL script (T-SQL) as the type, and specify the GP company database aginst which the SQL statement will run; in this case TWO.

Next, select the Schedule Page to specify when the job will run. In this example, the job will run every morning at 3 AM. This way, receivables are aged each morning before the business day begins.

Finally, setup alerts to write to the event log on success and notify your GP Administrator or DBA when the job fails.

This is a very simplistic example of using the SQL Server Job Agent. There are many best practices on managing jobs you should consider. The purpose of this post isn't to cover all of those but rather help introduce those new to GP & SQL Server to the concepts.

Automatic Aging for GP

2009-04-29T07:39:00.004-05:00

The question came up on the Public GP Newsgroup about automating customer aging in GP. I recalled doing this for some customers in the past and found this method works well. GP calls a stored procedure called rmAgeCustomer to execute the Receivables Aging Process. You can do the same and schedule this to run periodically without user intervention. Run a DEXSQL.log when running that routine to trap this yourself.

Below is the SQL that will age all customers, all statement cycles, and all balance types as of the current date. I recommend you test this before deploying in a production environment.

DECLARE @O_iErrorState int, @I_dAgingDate datetime

select @I_dAgingDate = convert(varchar(10), GetDate(), 102)

EXEC dbo.rmAgeCustomer 0, '', 'þþþþþþþþþþþþþþþ', @I_dAgingDate, 127, 0, 0, '', @O_iErrorState OUT

SELECT @O_iErrorState

Table Index Optimization to Improve Dynamics GP Performance

2009-04-15T19:15:00.011-05:00

I recently touched up some code I wrote for a client back in 2003. For them, Bob and I integrated GP Depot Management Work Order Entry/Update with Sales Order Processing (Document Number = Work Order Number) for quoting and invoicing repair work and after sales service. Part of this customization enabled them to inquire on their quotes and orders directly from Work Order Entry/Update.

The code simply queries the Sales Order Processing Work (SOP10100) and Sales Order Processing History (SOP30200) tables where the ORIGNUMB = Work Order Number and then opens the Sales Order Processing Document Inquiry, sets the From and To Document Number, and sets the Unposted or History radio button accordingly. This saves the service center personnel significant time when researching work order detail for customers which increases customer satisfaction.

After 6+ years they've accumulated a little history in SOP. The code behind the inquiry buttons placed on Work Order Entry/Update window was taking too long to run. With customer service agents on the phone with customers, waiting more than a few seconds for the system to return data was too long. It didn't take long to realize that there wasn't an index on the SOP30200.ORIGNUMB column. The impact of adding that index was phenomenal reducing wait time from 10+ seconds to what seems like just milliseconds.

To create a new index in SQL Server Management Studio:

1. Right Click on the table and click Design from the menu.
2. From the Table Design menu option select Indexes/Keys.
3. Click Add to create a new index.

4. Select the column(s) on which you want to create the index.

5. Name your indexes consistently so that you can query all them out of sysobjects later when you need to.

6. Set other properties as needed.

There is a lot of great information widely available about when and how to create new indexes. I strongly recommend you educate yourself and engage an expert to optimize your indexes.

Of course, you can use T-SQL to load new indexes on your tables. Here's a sample:

CREATE NONCLUSTERED INDEX IX_SOP30200_ORIGNUMB ON dbo.SOP30200
(
ORIGNUMB
)

WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Remember that when you upgrade GP, it's likely that you will need to reload your indexes as the upgrade process will drop the old tables and therefore your indexes. Plan your upgrades accordingly and make sure you script your indexes before you do. Otherwise, GP will generally react positively to the new indexes.

Dynamics GP for Equipment Rentals

2009-04-11T09:22:00.031-05:00

I recently wrapped up an implementation in the role of solution architect for a multi-location Equipment Rental company. I discussed our design with some of the best GP consultants I know who expressed interest in hearing more. I hope you find this useful.

We chose to lead with the GP Field Service Series; Contract Administration, Returns Management, & Depot Management Module along with the Sales Order Processing and Inventory Control modules to handle Equipment Rental processes and requirements. I continue to be impressed by the variety of applications I have found for this suite of GP modules.

In this scenario, the process begins with the contract. A customer phones the inbound call center and requests equipment for a specified period of time. The call center agent enters the contract terms into Contract Entry/Update. We mapped Contract Type to the customer's Location/Warehouse and customized the way GL distributions were populated (More on that to come).

The next step is to use a push button we added to the Contract Entry/Update window to automate the generation of a Sales Order that the warehouse operations can fulfill to record the shipment of the equipment to the customer. This was a better fit than leveraging native functionality to create the Contract from SOP as the contract needed to be initiated before the SOP Invoice was posted. We setup the equipment as serialized inventory items without cost so that we could track shipments to customers and availability of equipment for rental.

The Create Order push button simply creates a new sales order header using default from SOP Setup and values from Contract Entry/Update and forces a relationship between the Sales Order (GL Reference) and the Contract (Job). The user then only has to enter the order lines and set the requested ship date then let the standard order fulfillment process run its course.

If you have worked with the Contract Administration Module you would realize at this point that we didn't assign any equipment to the Contract. The business process in this scenario is to assign the equipment to the Contract only after the equipment has shipped to the customer. It's only at that point that you would know which serialized piece of equipment was shipped.

We chose to automatically insert the equipment record on the Contract after the order was fulfilled, invoiced, and posted. We placed a trigger on SOP10201 to fire off a stored procedure that assigns the shipped serialized equipment records (SVC00300/SOP10201) to the Contract using the GL Reference:Job relationship. This is also where we assign the GL Account to the contract line based on the Location (SVC00600.CNTTYPE), Type of Customer (RM00101.USERDEF1), and Item Type (IV00101.USCATVLS_1) per the customer requirements.

At this point, the equipment has been shipped to the customer and the clock is ticking on the contract. You may have noticed that we defaulted the Hold flag on Contract Entry/Update to true (Simply done with VBA). This prevents the contract from being invoiced before the equipment is returned and inspected signaling the completion of the contract. This is where we'll pick up the process next.

We implemented the RMA module to track the returns of the equipment from the customer, send the equipment through the depot for inspection, and place the equipment back into stock to be available for future rental. The RMA and Depot Management modules were a great fit for this scenario.

We setup the RMA with a return path of none and to post the received inventory to an In Service Inventory Location. There's no need to credit or bill the customer for anything related to the RMA. We'll handle all of the charges when we bill the Contract later. We linked the RMA to the Invoice that we posted to record the shipment of the equipment to the customer.

Next, follow the standard process for receive the RMA. This will adjust the returned equipment inventory into the designated In Service Location.

Once received, use the RMA Line Process Return window to create Depot Management Workorders for each item so that it can be inspected and returned to stock.

Post Labor and Parts to the Work Order(s) to track repair costs that might be billed back to the customer later. In this scenario, these charges would not be invoiced separately from the Contract Invoice. Rather, the labor and parts posted against the Work Order(s) will be used later in the process to apply additional repair charges to the Contract before it is billed.

After the parts and labor are posted and Work Order is complete we finished the inspection process by transferring the equipment from the In Service location back to general stock to be available for a future rental.

Linking the RMA to the invoice on which the equipment was shipped to the customer and the RMA to the Work Order against which inspection labor and parts are posted allows the backoffice to have visibility to returns of equipment and costs of inspection on unbilled contracts. There's a relationship between the RMA and Work Order all the way back to the Contract through the GL Reference:Job relationship created when the Sales Order was initially created from the Contract.

The backoffice now knows that the equipment has been returned, received, and inspected. They can query the costs of repair/inspection and prepare the contract for invoicing. In this scenario they chose to add new contract lines for additional charges such as Freight and Repair.

The final step in the process is to Bill the Contract to create the SOP Invoice to send to the customer for the rental, freight, and repair/inspection charges.

Now, you can enter and track all of your transactions, costs, inventory movements, etc. associated with managing your equipment rental business using the Dynamics GP Field Service Suite which comes packaged with your BRL. There's no need for spreadsheets or disparate systems to manage these processes. We customized this implementation to streamline data processing, ensure consistency, and enforce data integrity. However, you could opt to implement this process without any customization at all.

Using this process also allows you to query all of your equipment; in your own inventory (IV00102 and/or IV00200) as well as the equipment that is out on a rental contract (SVC00300 and SVC00601). You can use this information for Available to Promise Reports.

You may have noticed the Create Project Button on the Contract Entry/Update window. We also integrated the FSS Contract with Project Accounting so that we could leverage PAS to track T&M activity related to the equipment rental. PAS was a better fit than Service Call management in this case and maintaining the relationship between the CA Contract and the PA Contract enabled us to report on the profitability of the relationship with the customer by combining both T&M project costs and billing with rental contract transactions.

Quick Lookups with Modifier & VBA

2009-03-15T08:47:00.009-05:00

It seems that the last few times I have implemented Contract Administration I've had to work around the inherent restrictions on the Contract Number field by using another field, in this case User Defined 1, to track an internal Customer Contract Number; in this example "Job Number". Maybe I'm missing something here but this is how I was able to work around this.

It's nice that you can move the User Defined 1 field onto the Contract Entry/Update window and add it to the Contract Lookup with Modifier in just seconds. This isn't as simple on some other windows in GP:

The problem was that Contracts needed to be retrieved using this Job Number value. Depending on how you address that, it could have a much more significant impact on the project budget. In this case, the customer was just as happy entering the Job Number to retrieve the Contract Record as they would be if the field were added to the Find Function in the Contract Lookup window.

It's important to note in this example that Job Number uniqueness is forced across Contracts.

To do this in GP 10; first, create some Sub Procedures to make and close a connection to the database:

Option Explicit

'Declare variables for the ADO Connection
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

Public Sub Get_ADOConnection()

'Get_NewConnection
Set cn = UserInfoGet.CreateADOConnection
cn.DefaultDatabase = UserInfoGet.IntercompanyID
cmd.ActiveConnection = cn

End Sub

Public Sub Close_ADOConnection()

cmd.ActiveConnection.Close

End Sub

Then, add some code behind UserDefined1 on the Before User Changed event to force uniqueness of Job Number across Contracts:

Private Sub UserDefined1_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)

On Error GoTo Proc_Error:

If ContractNumber.Empty = False Then

'Get_NewConnection
Get_ADOConnection

'Make sure the job number is not already assigned to a contract

cmd.CommandText = "select top 1 CONTNBR from SVC00600 where USERDEF1 = '" & Me.UserDefined1 & "'"
Set rst = cmd.Execute

'If a contact exists for this job notify the user and force them to enter a unique job number
If rst.EOF = False Then
MsgBox ("This Job Number has already been assigned to contract " & RTrim(rst.Fields("CONTNBR")) & ".")
CancelLogic = True
KeepFocus = True
End If

Close_ADOConnection

End If

GoTo Procedure_Exit:

Proc_Error:

MsgBox Error$ & " " & Error, vbOKOnly, "UserDefined1_BeforeUserChanged"

Procedure_Exit:

End Sub

Finally, add some code behind User Defined 1 on the After User Changed Event to query the Contract Master Table and retrieve the Contract to which the Job Number has previously been assigned:

Private Sub UserDefined1_AfterUserChanged()

On Error GoTo Proc_Error:

If ContractNumber.Empty = True Then

'Get_NewConnection
Get_ADOConnection

'Lookup the Contract for this Job
cmd.CommandText = "select top 1 CONTNBR from SVC00600 where USERDEF1 = '" & Me.UserDefined1 & "'"
Set rst = cmd.Execute

If rst.EOF = False Then
Clear = 1
ContractNumber = rst.Fields("CONTNBR")
Else
MsgBox ("This Job Number has not been assigned to a contract.")
End If

Close_ADOConnection

End If

GoTo Procedure_Exit:

Proc_Error:

MsgBox Error$ & " " & Error, vbOKOnly, "UserDefined1_BeforeUserChanged"

Procedure_Exit:

End Sub

The end result of this not only can you easily assign the Job Number (User Defined 1) to the Contract and view the Job Number in the Contract Lookup Window but, most importantly, you can pull the Contract into the Contract Entry/Update Window by keying into the Job Number field.

Simple, quick, cost effective solution that could be applied to a variety of windows in GP.

Get the Next NOTEINDX

2009-03-07T11:19:00.006-06:00

This came up for me 2x this week alone on 2 different projects. Just about every time you have to insert a record into a GP Table you have to get the next note index. While it's a simple thing, it may not be obvious exactly how to do it.

Run this against your COMPANY database to increment and return the next note index from the DYNAMICS.dbo.SY01500 table:

--Declare some variables
DECLARE @I_sCompanyID smallint,
@O_mNoteIndex numeric(19,5),
@O_iErrorState int

--Get the CompanyID
select @I_sCompanyID = CMPANYID
from DYNAMICS..SY01500
where INTERID = DB_Name()

--Get and increment the next note index
exec DYNAMICS.[dbo].[smGetNextNoteIndex] @I_sCompanyID, 1, @O_mNoteIndex output, @O_iErrorState output

--Print the Next Note Index
print(@O_mNoteIndex)
--Print the Error State
print(@O_iErrorState)

Forming, Storming, Norming, Performing Group Development Theory

2009-03-03T09:42:00.003-06:00

This is off topic but relevant for many, if not all of us, as we are integrating into or managing new teams. As a consultant, this is very common.

New teams generally form and evolve through a distinct set of stages; Forming, Storming, Norming, and Performing. The goal is to move to the Performing stage as quickly as possible. I'm currently tasked with managing a very diverse team that is not accustomed to working together. As a result, I'm spending a lot of time working to help them move along the path to high performance as quickly as possible.

Mind Tools has a good article posted that describes these stages with some tips on how to lead a team through them effectively to high performance. If you find yourself struggling to integrate with a new team or building a high performance team of your own you might find this useful.

GP Self Service - Locked Transactions

2009-03-01T14:06:00.011-06:00

I neglected to mention another potential good reason to Customize GP; control your support costs, increase your IT resource capacity, and increase user satisfaction. There are routine problems that are easily resolved but often require the assistance of a system administrator. For example, records locked by other users, orphaned TempDB.dbo.DEX_LOCK records, can be a nuisance. This typically grows more common as the concurrent user count increases in Citrix environments. These records are necessary to prevent multiple users from accessing the same transactions at the same time. However, when they are not properly cleared they prevent users from accessing transactions even though another user may not be.

In this example, I added a Push Button to Sales Transaction Entry using Modifier.

Then, I created 2 stored procedures; one to return the userid that has locked the transaction so the user can attempt to resolve this problem on their own ...

... and the other to remove the locking record.

Next, add the Sales Transaction Entry Window, the Document No. field, and the Unlock Trx button to your VBA Project.

Open the VBA Editor to set a Reference in the Microsoft_Dynamics_GP Project to the Microsoft Active X Data Objects 2.x Library. This is required to access the database directly through VBA.

Finally, paste the following code behind the Sales Transaction Entry Window. If some pieces of this are foreign to you try to take advantage of Mariano's VBA workshop this week to learn more and check back as I take a deeper dive in the future into some of these concepts. My apologies for the formatting of this code. I'll work on that.

Private Sub UnlockTrx_AfterUserChanged()

'Declare the variables for the message boxes
Dim Msg, Style, Title, Help, Ctxt, Response, MyString, Default

'Declare the variables to make the connection to the database
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

On Error GoTo Proc_Error:

'Prompt the user for the Document Number they want to unlock
Msg = "Enter the Document Number you want unlocked." ' Set prompt.
Title = "Unlock SOP Trx" ' Set title.
MyString = InputBox(Msg, Title, Default)

'Set the variables required to make the connection
Set cn = UserInfoGet.CreateADOConnection
cn.DefaultDatabase = UserInfoGet.IntercompanyID
cmd.ActiveConnection = cn

'Query the database to get user that has the document locked
cmd.CommandText = "exec uSp_SELLockedDocUser '" & Microsoft_Dynamics_GP.SalesTransactionEntry.DocumentNo & _
"', '" & cn.DefaultDatabase & "', 'SOP10100'"
Set rst = cmd.Execute
If rst.EOF = False Then
'Inform the user and give them the option to unlock the document or cancel
Msg = "This document is locked by " & (RTrim(rst.Fields("userid"))) & ". Continue Unlocking?"
Style = vbOKCancel + vbCritical ' Define buttons.
Title = "Dynamics GP" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
'Unlock the document if requested by the user
If Response = vbOK Then
cmd.CommandText = "exec uSp_DELClearLockedDoc '" & Microsoft_Dynamics_GP.SalesTransactionEntry.DocumentNo & "', '" & _
cn.DefaultDatabase & "', 'SOP10100'"
Set rst = cmd.Execute
End If
Else
'Inform the user that the record is not locked
Msg = "This document is not currently locked by another user."
Style = vbOKOnly + vbInformation ' Define buttons.
Title = "Dynamics GP" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
End If

GoTo Procedure_Exit:

Proc_Error:

MsgBox Error$ & " " & Error, vbOKOnly, "UnlockTrx_AfterUserChanged"

Procedure_Exit:

End Sub

Simple Table Backups with T-SQL

2009-03-01T09:16:00.003-06:00

I was working on a project recently with a very experienced and respected GP consultant. He has taught me many things but I was able to show him something very simple that made his life much easier. Simple table level backups with T-SQL.

The following will select all of the data in SOP10100 into a new SOP10100_Bkup_03012009 table:

select *
into SOP10100_Bkup_03012009
from SOP10100

Remember that inserts, updates, and deletes often fire off events that could alter data in other tables. This won't backup those dependent tables so be mindful of the potential that a simple update to one table could affect data on many others.

Good Reasons to Customize GP

2009-02-22T11:58:00.012-06:00

While customization, when not properly planned and justified, can be problematic there are good reasons to customize your system to create long-term, sustainable competitive advantage and build discipline in your organization. I will follow this up with some specific examples of customizations I have built for these reasons and others.

1. Leverage your people for more value added activities. I'm not opposed to relying on people for some things but I don't see the value in having them enter and process data that the system could process unassisted. I've seen too many Controllers that function more like glorified GL Clerks. They spend more time entering and managing data than analyzing it to make decisions. Customizing your system to automate non value added tasks and deliver high quality information could enable you to convert your high paid data entry clerks back into analysts and controllers which in turn would help you identify cost saving and revenue generating opportunities.

2. Eliminate waste and redundancy to make your company scalable. Growth companies are always dealing with too few people doing too many tasks. Everyone is overworked and the pipeline of new work seems endless. As sales and production volume increase so does the demand for back office and shop floor capacity. Given the current economic climate this may not be an issue again for a while. Increasing headcount in these situations is often not at the top of management's list of solutions. Automating redundant and repeatable tasks could enable your organization to increase sales and production volume without having to add more people to manage the increased load. That's not bad for your margins.

3. Instill discipline in your organization. I was the CIO at a company with many geographically dispersed SBUs. They were all mandated to work consistently and without exception under a set of corporate business rules. In spite of that, the employees often chose not to follow those rules favoring their better judgement. In place of spending considerable time and money policing people to ensure business rules were being followed we programmed those rules into the system and took the binary decisions for which rules were defined away from the employees. This forced alignment of the operations with the corporate strategy. The people didn't like it much but the company grew by more than 50% in less than 5 years.

4. Save money AND have it your way. The GP channel is full of terrific 3rd party ISVs that seemingly have a solution for everything. Dynamics GP also comes with some terrific tools such as Modifier with VBA that you can use to tailor or customize the system according to your company's specific business needs. When making the build vs. buy decision compare the cost of having your system customized with the cost AND the compromises you might have to make with the ISV solutions available. Sometimes, you will find that you get a better, more cost effective solution by building your own solution from scratch.

5. I'm leaving this one open for comment. It would be nice to hear why some of the MVPs and others in the community would choose to customize a GP installation. I can think of a lot of reasons and find that the ease of customization is a great selling point for GP that is under valued. What do you think?

In closing, I second Mariano's warning at the end of his article. You should plan any customizations carefully and consult with experienced professionals. However, even heavily customized GP systems can be upgraded without considerable extra effort. I don't agree with those who warn against any customization in fear of upgrade complications. Most GP projects I have worked on have been heavily customized and those that have upgraded have done so without experiencing noticeable extra pain or incurring material additional costs.

How to make PO field required for specific customer

2009-02-21T10:55:00.005-06:00

This requirement came up last week and after Mariano explained how to do it, I went ahead and did it... for fun. Sick, I know.

I posted an example of using the DUOS a couple of year ago and used that, with very few tweaks, after using Modifier to add a Check Box control to the Customer Maintenance window to store the RequiredPO flag in the DUOS (SY90000) as Mariano suggested. If you have problems converting that example to this scenario let me know and I will e-mail you the code.

After that, all you have to do is add the Sales Transaction Entry Window and the Customer ID and Customer PO Fields on that window to your Visual Basic Project. Open the VB Editor and add this code to the SalesTransactionEntry (Window) to make the Customer PO field required for selected customers:

Option Explicit
Dim Msg, Style, Title, Help, Ctxt, Response, MyString, Default
Dim ItemCollection As DUOSObjects
Dim ItemObject As DUOSObject

Private Sub CustomerID_Changed()

On Error GoTo Proc_Error:

Set ItemCollection = DUOSObjectsGet("Customer")

If CustomerID.Empty = False Then
Set ItemObject = ItemCollection(CustomerID)
If ItemObject.Properties("RequirePO") = "1" Then
CustomerPONumber.Required = True
Else
CustomerPONumber.Required = False
End If
End If

Exit Sub

Proc_Error: MsgBox Error$ & " " & Error, vbOKOnly, "CustomerID_Changed"

End Sub

Custom Business Alerts

2009-02-19T10:33:00.003-06:00

GP does a good job of giving you the ability to create business alerts to keep you informed of events that have or may occur based on conditions in your database. Sometimes, the need to create an alert outside of the functionality in GP does, amazingly, come up. Just today a post at http://groups.google.com/group/microsoft.public.greatplains/topics and a response by Polino @ DynamicsAccounting.net drove me to create a sample business alert using only T-SQL that you could use as a starting point to developing your own.

This alert, if scheduled to run periodically, will e-mail a list of users that have been logged into GP for longer than 12.5 hours or 750 minutes. It's pretty simple!

IF EXISTS
(
select datediff(mi,logindat+logintim, getdate()) as DURATION,--convert(datetime, convert(varchar(15), GetDate(), 114), 114) - LOGINTIM as DURATION,
USERID,
CMPNYNAM,
LOGINDAT,
LOGINTIM
from DYNAMICS.dbo.ACTIVITY
where datediff(mi,logindat+logintim, getdate()) > 750
)
BEGIN

DECLARE @SQL varchar(8000)

SET @SQL = 'select datediff(mi,logindat+logintim, getdate()) as DURATION,
USERID,
CMPNYNAM,
LOGINDAT,
LOGINTIM
from DYNAMICS.dbo.ACTIVITY
where datediff(mi,logindat+logintim, getdate()) > 750'

print @SQL

EXEC master.dbo.xp_sendmail @recipients = 'youralias@yourcompany.com',
@subject = 'Users Logged in beyond limit',
@message = 'Attached is a list of users that have been logged in beyond the limit',
@query = @SQL,
@attach_results = 'TRUE',
@width = 250
END

Restricting SOP Lookups with VBA

2009-01-05T09:03:00.003-06:00

A request to restrict SOP Lookup Widnow to only return Documents for which the User is responsible came up yesterday on the http://groups.google.com/group/microsoft.public.greatplains/topics. This is a somewhat common request that is easily addressed using VBA. This example only works for v10 but you could easily convert it to previous version by following the steps in http://mbsguru.blogspot.com/2006/04/ado-connections-through-vba-in.html.

This example assumes that the UserIDs are stored in the Sales Person ID field in Customer Maintenance and that relationship is used to determine Customer Ownership during order entry.

1. Add "Sales Document Numbers" window to Visual Basic.
2. Add the "Document Number" field to the project.
3. Open the SmartList Project in the Visual Basic Editor.
4. Set a Reference in the SmartList Project to "Microsoft ActiveX Data Objects 2.x Library"
5. Copy and paste the following code behind the SaleDocumentNumbersDetail (Grid) window in the SmartList Project:

Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

Private Sub Grid_BeforeLinePopulate(RejectLine As Boolean)

On Error GoTo Proc_Error:

'Get_NewConnection
Set cn = UserInfoGet.CreateADOConnection
cn.DefaultDatabase = UserInfoGet.IntercompanyID
cmd.ActiveConnection = cn

'Query the database to get check the document
cmd.CommandText = "select h.CUSTNMBR from SOP10100 h inner join
RM00101 c on h.CUSTNMBR = c.CUSTNMBR where c.SLPRSNID = '" &
UserInfoGet.UserID & "' and SOPNUMBE = '" & SOPNumber & "'"
Set rst = cmd.Execute

If rst.EOF = True Then
RejectLine = True
End If

GoTo Procedure_Exit:

Proc_Error:

MsgBox Error$ & " " & Error, vbOKOnly, "Grid_BeforeLinePopulate"

Procedure_Exit:

cmd.ActiveConnection.Close

End Sub

Who has that record locked?

2007-12-04T16:32:00.004-06:00

I love the GP Newsgroup. Helping others with their problems helps me learn more about GP. Here's what I taught myself today in response to a newsgroup post.

Run this against your company database to select the users which users have SOP Documents locked:

select s.SOPNUMBE, a.USERID

from tempdb.dbo.DEX_LOCK l

inner join DYNAMICS.dbo.ACTIVITY a

on l.session_id = a.SQLSESID

inner join SOP10100 s

on l.row_id = s.DEX_ROW_ID

and l.table_path_name = DB_NAME() + '.dbo.SOP10100'

Find tables, with data, that have a specific column

2007-12-03T17:28:00.001-06:00

We're going to change some Item Numbers in GP. I know, I can use PS Tools to do this but to use that you have to turn off your replication. Long story short, I don't wanna.

Anyway, I had to figure out which tables to update so I wrote a query that would return to me all of the tables, with data, that have an ITEMNMBR column:

select distinct o.Name
from SysColumns c
inner join SysObjects o
on c.id = o.id
inner join SysIndexes i
on c.id = i.id
where c.name = 'ITEMNMBR'
and o.xtype = 'u'
and rowcnt <> 0

I figured that some other SQL Hacks out there might find this useful.

Deleting Empty Batches in SOP

2007-11-27T16:29:00.001-06:00

Schedule this script to run periodically against your company database to delete empty SOP batches automatically. It will check to verify that there aren't any transactions in the batch and that there is not a batch activity record first.

DECLARE @INTERID varchar(10),
@CMPNYNAM varchar(31)

SET @INTERID = DB_Name()
SELECT @CMPNYNAM = CMPNYNAM from DYNAMICS.dbo.SY01500 where INTERID = @INTERID

DELETE SY00500
where BCHSOURC = 'Sales Entry'
and BACHNUMB not in (select BACHNUMB from SOP10100)
and BACHNUMB not in (select BACHNUMB from DYNAMICS.dbo.SY00800 where CMPNYNAM = @CMPNYNAM and TRXSOURC = 'Sales Transaction Entry')

Using VBA to Mark Inventory Batches to Post to GL

2007-11-27T12:53:00.002-06:00

I think this is pretty much common knowledge but it is a simple change you can make with VBA to ensure your inventory adjustments post to the GL. Paste this code below behind the Inventory Batch Entry window to mark the checkbox to post to the GL by Default and then prompt the user before posting without posting to the GL. This demonstrates a couple of handy VBA techniques you can use to alter and control the behavior of GP (forgive the formatting).

Option Explicit
Dim Msg, Style, Title, Help, Ctxt, Response, MyString, Default

Private Sub Comment_AfterGotFocus()

'Mark the Post to GL Checkbox after Comment Got Focus if not already marked

If PosttoGeneralLedger = 0 Then
PosttoGeneralLedger = 1
Changed = False
End If

End Sub

Private Sub Origin_AfterUserChanged()

'Mark the Post to GL Checkbox after changing the origin if not already marked

If PosttoGeneralLedger = 0 Then

PosttoGeneralLedger = 1
Changed = False

End If

End Sub

Private Sub Post_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)

If BatchID.Empty = False Then

If InventoryBatchEntry.PosttoGeneralLedger.Value = 0 Then

Msg = "Do you want to post this batch without posting to GL?" ' Define message.
Style = vbYesNoCancel + vbCritical ' Define buttons.
Title = "Great Plains" ' Define title.
Help = "DEMO.HLP\" ' Define Help file.
Ctxt = 1000 ' Define topic
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbNo Then

InventoryBatchEntry.PosttoGeneralLedger.Value = 1

Else

If Response = vbCancel Then

CancelLogic = True

End If
End If
End If
End If
End Sub

VBA Tip of the ?????

2007-11-26T14:11:00.001-06:00

I'm not real committed to this blog as you can tell. But, I do have something to offer. Here's the first of some simple tips I might share ;)

Displaying a message box to a user and controlling their actions might be the easiest way to change the functionality of GP. You display the message box so they aren't confused when you alter the standard functionality of GP. This came up recently in a GP newsgroup post. Below is an example of forcing the user to enter a tracking number before clicking the Ready To Close buttin on RMA Entry Update. The CancelLogic = True prevents the Ready To Close logic from running if the TrackingNumber field is empty. You can put this on other BeforeUserChanged events in GP to alter the logic elsewhere.

Option Explicit
Dim Msg, Style, Title, Help, Ctxt, Response, MyString, Default

Private Sub SVCReadyToClose_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
If TrackingNumber.Empty = True Then
Msg = "The Tracking Number is required before a RMA Line can be marked to close."
Style = vbOKOnly + vbCritical ' Define buttons.
Title = "Great Plains" ' Define title.
Help = "DEMO.HLP\" ' Define Help file.
Ctxt = 1000 ' Define topic
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
CancelLogic = True
KeepFocus = True
End If
End Sub

Dynamic Accounting.net: Weekly Dynamic: Dynamics GP 9 Payables

2006-04-27T16:05:00.002-05:00

Dynamic Accounting.net: Weekly Dynamic: Dynamics GP 9 Payables

Real Example of using the DUOS

2006-04-27T13:24:00.001-05:00

While I am not a fan of using the DUOS with the same methods described in the manual, using ADO with RetrieveGlobals is better and easier in my opinion, I did do so in one instance to store an EDI Account Number for each customer. I added a field to Customer Maintenance and named it "strEDIAccount". Then I added the code below to my VBA project to store the date in and retrieve it from the DUOS (SY90000) table. This isn't much different than the sample code in the manual but it is a real world example. You should be able make some tweaks to this code and use the information in the manual to make this work in your environment. Good luck!

Option Explicit

Dim Msg, Style, Title, Help, Ctxt, Response, MyString, Default
Dim ItemCollection As DUOSObjects
Dim ItemObject As DUOSObject
Dim WanttoDelete As Boolean
Dim ItemDelete As String
Public OkDelete As Boolean

Private Sub Delete_AfterUserChanged()

On Error GoTo Proc_Error:

If WanttoDelete And OkDelete Then
ItemCollection.Remove (ItemDelete)
End If

Exit Sub

Proc_Error: MsgBox Error$ & " " & Error, vbOKOnly, "Delete_AfterUserChanged"

End Sub

Private Sub CustomerID_Changed()

On Error GoTo Proc_Error:

Set ItemCollection = DUOSObjectsGet("Customer")

If CustomerID.Empty = False Then
Set ItemObject = ItemCollection(CustomerID)
strEDIAccount = ItemObject.Properties("EDIAccount")
ItemDelete = CustomerID
OkDelete = True
WanttoDelete = False
End If

Exit Sub

Proc_Error: MsgBox Error$ & " " & Error, vbOKOnly, "CustomerID_Changed"

End Sub

Private Sub Save_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)

On Error GoTo Proc_Error:

ItemObject.Properties("EDIAccount") = strEDIAccount

Exit Sub

Proc_Error: MsgBox Error$ & " " & Error, vbOKOnly, "Save_BeforeUserChanged"

End Sub

Private Sub strEDIAccount_AfterUserChanged()

On Error GoTo Proc_Error:

ItemObject.Properties("EDIAccount") = strEDIAccount

Exit Sub

Proc_Error: MsgBox Error$ & " " & Error, vbOKOnly, "strEDIAccount_AfterUserChanged"

End Sub

Private Sub Window_AfterModalDialog(ByVal DlgType As DialogType, PromptString As String, Control1String As String, Control2String As String, Control3String As String, Answer As DialogCtrl)

On Error GoTo Proc_Error:

If PromptString = "Are you sure you want to delete this customer record?" Then
If Answer = dcButton1 Then
WanttoDelete = True
End If
End If

Exit Sub

Proc_Error: MsgBox Error$ & " " & Error, vbOKOnly, "Window_AfterModalDialog"

End Sub

SQL Nugget - Shortcut to create INSERT into

2006-04-26T12:46:00.001-05:00

Here's a SQL nugget I love to use when writing stored procs, triggers, and the like. Copy and paste this into query analyzer and replace %TableName% with the name of the Table in which you want to insert records. Execute it to return the INSERT into statement complete with default values for every field in the table. Paste the results into your object and populate the fields with your values. This can be a real time save for developers and when doing data conversions.

A slick cat we'll call Mo gave this to me. I can't take credit for it.

set nocount on
DECLARE @sTableName varchar(128)set @sTableName = '%TableName%'
select @sTableName as sTableName into #tmpTableName
DECLARE @lTableID intset @lTableID = NullSELECT @lTableID = [ID] from sysobjects where (objectproperty(id, N'IsTable') = 1) and (id = object_id(@sTableName))
if (@lTableID is Null)begin print 'Table not found! Aborting.' returnend
SELECT [name], xtype, prec, scale, colorder, isnullable into #tmpColumns from syscolumns where ([id] = @lTableID) and (colstat = 0) order by colorder
alter table #tmpColumns ADD lRowID int not null identity, -- add an identity column to the temp table sDefault varchar(40) -- add a column to store the default that we want to enter for new rowsGO
UPDATE #tmpColumns set sDefault = case xtype when 34 then ''' ''' -- image when 35 then ''' ''' -- text when 36 then Null -- unique identifier when 48 then '0' -- tinyint when 52 then '0' -- smallint when 56 then '0' -- int when 58 then '''1/1/1900''' -- smalldatetime when 59 then '0.0' -- real when 60 then '0' -- money when 61 then '''1/1/1900''' -- datetime when 62 then '0.0' -- float when 99 then ''' ''' -- ntext when 104 then '0' -- bit when 106 then '.00' -- decimal when 108 then '0.0' -- numeric when 122 then '0.0' -- smallmoney when 165 then '0' --'convert(varbinary, '' '')' -- varbinary when 167 then ''' ''' -- varchar when 173 then '0' --'convert(binary, '' '')' -- binary when 175 then ''' ''' -- char when 189 then Null -- timestamp when 231 then ''' ''' -- nvarchar when 239 then ''' ''' -- nchar endDELETE #tmpColumns where sDefault is Null--select sDefault, xtype, [name] from #tmpColumns order by colorder, lRowID

DECLARE cur insensitive scroll cursor for select sDefault, [name] from #tmpColumns order by colorder, lRowIDOPEN cur
declare @sDefault varchar(40), @sName varchar(128)declare @sWork varchar(200)declare @sWork2 varchar(100)
select top 1 @sWork2 = sTableName from #tmpTableNameprint 'INSERT into ' + @sWork2print ' ('
FETCH first from cur into @sDefault, @sNameWHILE ( @@fetch_status = 0 )begin set @sWork = char(9) + @sName
FETCH next from cur into @sDefault, @sName -- if the fetch is good, add a ',' to the end if ( @@fetch_status = 0 ) set @sWork = @sWork + ','
print @sWorkendprint ' )'
print 'select'
FETCH first from cur into @sDefault, @sNameWHILE ( @@fetch_status = 0 )begin set @sWork = char(9) + @sDefault set @sWork2 = @sName
-- get the next record from the cursor FETCH next from cur into @sDefault, @sName
-- if the fetch is good, add a ',' to the end if ( @@fetch_status = 0 ) set @sWork = @sWork + ','
set @sWork = @sWork + char(9) + '-- ' + @sWork2 print @sWorkend
CLOSE curDEALLOCATE CUR
DROP table #tmpColumnsDROP table #tmpTableName

ADO Connections through VBA in Dynamics GP v9

2006-04-25T13:24:00.001-05:00

This has come up time and time again on message boards I frequent on the web. People are always asking how they can access SQL Tables and other objects through VBA to extend GP. It's pretty simple to add a field to a window with Modifier or create a user form in VBA but many seem to wonder how to access the database to really make things happen.

You can find all you need to know on Customer/PartnerSource. I'm only disclosing the same information here and a little of what I have learned to complement that information.

When Microsoft Dynamics GP 9.0 was released, a change was made in the password policy. This change required changes in how Microsoft Dynamics GP Dexterity works with passwords and in how the RetrieveGlobals.dll file works with passwords. The RetrieveGlobals.dll file was available for all earlier versions but does not work with Microsoft Dynamics GP 9.0. Therefore, you must replace the RetrieveGlobals.dll file with the new RetrieveGlobals9.dll file. The new RetrieveGlobals9.dll file is an ActiveX file. It returns the following information:

•The current user ID
•The current company to which you are logged in
•The current SQL data source
•The current user date in Microsoft Dynamics GP 9.0

The RetrieveGlobals9.dll file also returns an ActiveX Data Objects (ADO) connection object that lets you connect to Microsoft Dynamics GP data. The RetrieveGlobals9.dll file works only with version 9.0 of Microsoft Dynamics GP. Additionally, this file works only if you have one session of Microsoft Dynamics GP running and if you are logged into this session. The RetrieveGlobals9.dll file is for use only in Modifier with VBA or in Integration Manager. Modifier with VBA and Integration Manager both require that Microsoft Dynamics GP is open and running. To download the RetrieveGlobals9.dll file together with its documentation, visit one of the Microsoft Web sites.

After upgrading all of our GP v7 VBA code to Dynamics GP v9 I found that I might have something valuable to share on this topic that you cannot readily find on CustomerSource. This is how I have organized my code to get best results from the RetrieveGlobals9.dll.

1. Create a Get_NewConnection SubRoutine behind each window for which ADO connections will be required:

Public Sub Get_NewConnection()
'This code clears all variables used and closes the connections.

If precordset.State = adStateOpen Then precordset.Close
If pconnection.State = adStateOpen Then pconnection.Close

'Initialize the connection string variables
Set userinfo = Nothing
Set userinfo = CreateObject("RetrieveGlobals9.retrieveuserinfo")
luserid = userinfo.retrieve_user()
lintercompanyid = userinfo.intercompany_id()
lsqldatasourcename = userinfo.sql_datasourcename()

'Use the connection property to get a connection object.
Set pconnection = userinfo.Connection

'Create an ADO command object.
Set cmd = CreateObject("ADODB.Command")

'set the database to the currently logged in db.
pconnection.DefaultDatabase = lintercompanyid

cmd.ActiveConnection = pconnection

'adCmdText.
cmd.CommandType = 1

End Sub

2. Create a CloseConnection SubRoutine behind each window you create a Get_NewConnection:

Public Sub CloseConnection()

'This code clears all variables used and closes the connections.
If precordset.State = adStateOpen Then precordset.Close
If pconnection.State = adStateOpen Then pconnection.Close
Set cmd = Nothing
Set pconnection = Nothing

End Sub

3. Call the Get_NewConnection to create new connections and CloseConnection to close them. I found that on some windows you can put these calls on the After Open and After Close events. On others you will have to reinitialize the connections and close them for every new call you make to the database.

4. Here's an example of how we populate a checkbox placed on EFT Customer Maintenance with Modifier based on whether or not the customer's pre-note has been confirmed which is stored in a table called "Dealer" outside of Dynamics GP.

Private Sub CustomerID_Changed()

On Error GoTo Proc_Error:

Get_NewConnection

cmd.CommandText = "SELECT PreNoteStatus FROM " & lintercompanyid & ".dbo.Dealer where AccountNumber = '" & CustomerID & "'"

Set precordset = cmd.Execute

If precordset.EOF = False Then
If RTrim(precordset.Fields("PreNoteStatus")) = 1 Then
PreNoteConfirmed = 1
Changed = False
Else
PreNoteConfirmed = 0
Changed = False
End If
Else
PreNoteConfirmed = 0
Changed = False
End If

GoTo Procedure_Exit:

Proc_Error:

MsgBox Error$ & " " & Error, vbOKOnly, "CustomerID_Changed"

Procedure_Exit:

CloseConnection

End Sub

Liaison Messenger

2006-04-24T10:16:00.001-05:00

Let's review a 3rd Party addon for Great Plains today. We recently purchased Liaison Messenger http://www.liaisonsc.com/liaisonmessenger.html to automate business form generation and distribution to customers and vendors. Specifically, our ROI will come from automating invoice printing and e-mailing to customers as we generate hundreds of invoices everyday. The cost of the time required to process those invoices and the postage/supplies necessary get them distributed justified the investment in such a system. In time we'll do the same for customer statements and vendor POs. I would recommend that everyone check out this product.

I'll give Messenger 4 out of 5 stars. First, the Liaison Team is outstanding. Every issue we've had or change we've requested has been handled diligently and effectively under our support agreement. Their customer support and service is outstanding and by far the best I've seen from a 3rd Party solution provider. Secondly, Messenger fills a significant gap in GP. Being able to automate invoice distribution helps us to controls costs and turnover assets both of which are critical priorities for our business. There's no doubt that this purchase was a good decision.

The Messenger system is by no means perfect but they know that and are working on improvements. First, it doesn't run as a service but rather as an application so you have to have the Messenger Server installed and running on a PC or Server that is logged into at all times. We are running it on a process server (Not GP Process Server) that performs a variety of functions such as polling new EDI files which also requires an application to be running which can make for a rather unstable or inconsistent environment. I understand that they are currently working with MSFT to build Messenger to run as a service which will eliminate this as an issue.

The second thing that make Messenger less than perfect is the performance. I'll qualify that by saying we are still on GP eEnterprise v7 and I am told that Messenger performs much better with Dynamics GP v9. We have millions of SOP HIST records and print invoices from the History tables so it was taking hours to process a days invoices. If we were using Messenger the way it was designed, against SOP WORK, this problem would not have persisted but we are seeing and working through similar issues with customer statements. To resolve this problem the Messenger support and development team was stellar. They implemented some changes to our system and now our invoices print in minutes. We are working to do the same for statements now. I'll let you know how that turns out.

In summary, regardless of what product you select to help solve your business problems you will run into issues or gaps in functionality. It's inevitable. If you have a need to automate business form generation and distribution I can't recommend Liaison Messenger more highly mostly because their team is dedicated to ensuring that your expectations are met and the product works well in your environment.

Who am I and why am I here?

2006-04-23T09:49:00.002-05:00

I've been a Dynamics GP resource since the late 90s. Before that, I was an accountant with a fortune 500 company running Great Plains Accounting Software. Now, I manage an IT Department for a global organization that has standardized on Dynamics GP.

I'm here in part out of inspriration by Doug's keynote at Convergence 2006 in Dallas to further contribute unselfishly to the Dynamics GP community. I'm not out to sell anything but rather simply share my knowledge of Dynamics GP and absorb knowledge from others doing the same. I'll give you the good, the bad, and the ugly from my perspective.

My knowledge spans almost all of the modules Dynamics GP has to offer including the core financial suite, distribution, manufacturing, service management, HR/Payroll, the customization and integration tool such as DIM and VBA, and many of the 3rd party products widely used in the channel. I've implemented each a handful of times and passed many of the exams although I wouldn't consider myself an expert on each. I consider myself an expert SQL Server programmer although I've never pursued a certification, am able to leverage VBA to extend GP, and feel that I can make a Crystal Report tell me anything I want. I know little of Dexterity and just enough about .Net to be dangerous. In short, in some instances I can be considered a developer.

What I hope to offer those who read my posts is some insight into Dynamics GP and the surrounding community that you can get mostly through experience. As I work through the upgrade for my company I'll post some tips and tricks that might not be otherwise documented and describe some of the changes that have occured that may be surprising to some such as the data from the ASILOC50 table (Item Customer Maintenance) has been moved to SOP60300. I'll also post some code SQL and VB code snipits that might help you to extend your Dynamics GP system on your own. I'll let you know what I know about the Dynamics GP Community like you must check out http://www.microsoft.com/dynamics/gp/community.mspx, I like to use http://groups.google.com/group/microsoft.public.greatplains to contribute to and consume knowledge from others, and DynamicAccounting.net is a good place to get valuable information from Polino.