Reconciling SOP Batches from WITHIN Dynamics GP

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:

1. 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.
2. 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.

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

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

Dynamics GP Password Expiration Notice

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.

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.

Advanced RMA Entry R2 - Available for Download/Evaluation

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.

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

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.

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

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.

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.

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

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

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

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!

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.

Visual Studio Tools Integration with GP Security

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

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.