We use cookies to improve your experience on our website.

Andosi Blog

Exploring Great Design

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

Read more

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
Read more

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

Read more

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

Read more

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

Read more

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.
Read more

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

Read more

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
Read more

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

Read more

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

Read more

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
Read more

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
Read more

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 calledrmAgeCustomer 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

Read more

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

Read more

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,
Read more

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

Read more

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]

Read more

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.


Read more