Tuesday, March 31, 2009

Dexterity to become a member of the Visual Studio family

Happy April Fools' Day!! I guess David got away with this one :-))

I cannot begin to express how excited I am to the news that Microsoft Dexterity will now become a standard part of the Microsoft Visual Studio family of development products. The news was published today over at Developing for Dynamics GP blog under strict permissions granted by the Microsoft Dynamics GP Product Management and Marketing team.



Since this was just a sneak peak, a lot of questions still remain, for example:



  • Will intellisense capabilities be granted to SanScript functions and statements? I for one appreciate the inline help provided for functions and statements when developing in C# and VB.NET


  • What versions of Visual Studio will be supported? I want to say, Visual Studio 2008, and Visual Studio 2010 are the most likely candidates.


  • Will developers now be able to perform backward compatible developments under Visual Studio? This is, will there be a need to continue using the old Dexterity interface for prior versions of customizations.


  • What about referencing assemblies, web services, and other interfaces from the new Visual Studio interface?
I am sure answers to these and many other questions will soon start to emerge from the Product Management and Marketing team. However, head over to the Developing for Dynamics GP blog to add your comments and ask everything you would like to know.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Monday, March 30, 2009

Developing Customizations with Dexterity's 3-Trigger Technique


One of the biggest challenges faced by third-party developers is the ability to establish the proper place to set a trigger. Remember, Dexterity developers don't usually work with source code. However, in the process of establishing the proper point for placing a trigger, we have help in the form of code traces. My friend David Musgrave is now determined to take the mistery (and guessing) by showing you a simple, but extremely useful concept that only he could coin a name for: the 3-Trigger Technique.

David's articles can be found here:


  • Using Dexterity 3-Trigger Technique - Part 1. Click here. Details the theory behind the implementation of this concept.
  • Using Dexterity 3-Trigger Technique - Part 2. Click here. Implements the solution and provides sample code for the implementation of the technique.

David also wants you to keep in mind the concepts that made the implementation of this technique possible:

  • Three Trigger Technique
  • Cross Dictionary Triggers
  • Capturing and Using References
Just stop over at his site and let him know if you find the information useful.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Mark Polino on Use Tax and Microsoft Dynamics GP


Use tax is one of those things that many companies using Microsoft Dynamics GP immediately resort to third-party solutions without really considering the systems out-of-the-box capabilities. The reasons may be various, among them, a lack of awareness of the GP's capabilities to begin with.

I had the opportunity to actively participate in the review of a document on Use Tax written by fellow Dynamics GP MVP, Mark Polino. In his document, Mark extensively covers the methods for configuring, tracking, and accounting for use tax in Microsoft Dynamics GP.

Please make sure you download and read Mark's document as he has dedicated a significant amount of time working through the techniques and methods with GP out of the box functionality.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Sunday, March 29, 2009

How to determine what company was selected from the Company drop-down on the Company Login window

Background

This question came up in a recent Dynamics GP newgroup post. A developer was trying to run some Visual Basic for Applications (VBA) code in response to the company selected by the user from the Company drop-down list, but was unsuccessful determining which company was selected by the user.



Challenges

From a customization and development perspective, the Company drop-down list presents the following challenges:

1) Different users may have access to different companies, therefore, the number of companies displayed by the drop-down may vary.

2) The company drop-down list does not store the company ID. It stores a positional value for the company record being displayed.

3) Companies are displayed in the drop-down by Company ID. However, due to challenge number 1, company IDs may present gaps in the sequence based on user access.

Solution

The following VBA code shows how to retrieve the company selected by the user based on the user access to the different company databases. The code executes a SQL statement against the system database when the DDL focus is lost. Since the user is already logged in into the system, it is possible to create an ADO connection with the CreateADOConnection() method.


' Created by Mariano Gomez, MVP
' Code is provided as is, with no warraties express or implied.
Private Sub Company_AfterLostFocus()
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

Dim objUsr As UserInfo
Dim userID As String

'Retrieve an ADO connection for the current user
Set cn = UserInfoGet.CreateADOConnection()

'Set the connection properties
cn.CursorLocation = adUseClient

'Set the current database, using the IntercompanyID property
cn.DefaultDatabase = "DYNAMICS"

'Create a command to select all customers
Set objUsr = VbaGlobal.UserInfoGet()
userID = objUsr.userID
With cmd
.ActiveConnection = cn
.CommandType = adCmdText
.CommandText = "SELECT B.CMPNYNAM FROM SY60100 A INNER JOIN SY01500 B ON A.CMPANYID = B.CMPANYID WHERE A.USERID = '" & userID & "' ORDER BY A.CMPANYID"
End With

Set rst = cmd.Execute

rst.MoveFirst
i = 1

While Not rst.EOF
If i = Company.Value Then
MsgBox "You've selected " & rst(0)
rst.MoveLast
End If
i = i + 1
rst.MoveNext
Wend

'Close the connection
cn.Close

End Sub


Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Life from the inside of the Fargo flooding - Part II

For those of you who have been asking about the flooding in Fargo, North Dakota, here is an update directly from my friend Tom Irsfeld. Amids the situation, Tom has taken time out to take these pictures and deliver a complete update from his vantage point.

Thanks to all those who have asked about the flood. My neighborhood is in great shape, except for the yards that were trashed by heavy equipment.

For those that have been following the Fargo flood, here is a little update. On Saturday, the river appears to have crested at 40.82 feet (previous record was 40.1 in 1897) & is now at 40.15 & trending downward. It was originally expected to get to 42 feet, so it is great that it didn’t go that high (although all city dikes were built to 43 feet). Barring something unforeseen, such as a major rainstorm or rapid melting, neither of which is predicted, we should be in the clear, unless one of the dikes gets a hole in it.

Now all we have to do is watch the pumps from behind the dikes & watch for any major holes. Once the river gets down to about 39 feet (expected by Thursday or Friday), that will be below the dikes in my neighborhood & so we should be in the clear. In my neighborhood, the Rose Creek Coulee, which flows into the Red River in about 1 mile, is mostly now just backup water, so there isn’t any current that can eat away at the ground beneath the dikes. Last night in another part of town, a dike was undermined by the current, sprung a leak & caused a school to be flooded. Fortunately the city has a backup clay dike on a street near the school that protected the rest of the city.

I don’t have a dike in my backyard (my next-door neighbor does), but those of us in the neighborhood all have shifts monitoring the pumps behind the houses that do. This is pretty boring work, a cross between ice fishing & sentry duty. The most excitement I’ve had in one of my shifts is when someone accidentally unplugged one of the pumps.

Flood2.jpg has the layout of my neighborhood.



The treeline on the right-hand side is the Rose Creek Coulee, which in the summer you can jump across. The dikes were made up of a combination of 3 different styles of dikes.

1) Hesco – these are dikes used by the military & you can see them in the left-side of the picture. They are made of wire frame with a cloth covering, about 4 feet high, 3 feet wide & are filled with sand. Each section weighs 5300 pounds.

2) Standard sandbag dikes – no explanation needed

3) Clay dikes

The city today is closing off the contingency dike, which will protect the city in case my neighborhood’s dikes fail. It is good for us in case the city’s other dike’s fail, as we would then be protected. I think we have enough frozen pizzas & beer to last us. Yesterday our phones went out, as Qwest service was interrupted (either a contractor building a dike cut the line or their relay station was flooded).

Here’s a couple of pictures I took from my yard & my next-door neighbor’s yard. Nothing dramatic like a rooftop rescue.

IMG_1594 – position A. On the left, you can see the Hesco dike, on the right, you can see the sandbags. You can also see the pump hose which is used to pump the water from the seepage buckets back into the river.



IMG_1588 – position B. On Friday, volunteers filled sandbags which were used to create a backup dike in one yard Friday, after one of the Hesco’s started to tilt towards the water. It took 15,000 sandbags to create a backup dike behind the tippage.



IMG_1600 – this is my garage, where they have been storing sandbags overnight, to keep them warm (frozen sandbags don’t stack very well). Since our diking is done, these will be used in case of a hole in the dike. Almost every house in our neighborhood has their garage full of sandbags. Our other cars were parked on the West side of the contingency dike so that if we need to evacuate, we have a vehicle on the other side.



IMG_1596 – position C. This is the clay dike in back of my house.



IMG_1592 – in the event the dike breaks, the water would be above the fenceline on the left & we’d take the canoe to the contingency dike.



The local Microsoft office is closed next Monday & Tuesday (there is only one road open to get to it) & possibly longer. Most employers have been urged to close down to keep people off the roads. Most schools are cancelled for next week

Each morning, we watch the City meeting on TV where the mayor, governor & other city people give their updates. Congressman Earl Pomeroy has been sporting his flannel shirt, which usually only comes out during election years. From one camera angle, I actually saw the back of Senator Byron Dorgan's head, indeed he is bald (no crime in my eyes), his comb-over rivals Donald Trump's. For those of you Non-North Dakotans, Byron has been a congressman or senator for about 30 years & I have never before seen the back of his head

Since the water appears to be receding, I probably won't send out further updates unless things deteriorate.

Tom

So far, nature has been on the side of North Dakotans and we can only expect the outcome to be positive under the current circumstances. We wish everyone a safe return to normalcy.

Related Articles

Life from the inside of the Fargo flooding - Part I. Click here.

Until next post!

MG.-
Mariano Gomez, MIS
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Friday, March 27, 2009

Life from the inside of the Fargo flooding

I thought I would share how residents in Fargo and sorrounding communities are dealing with the flooding situation by posting the exchanges I have had with my dear friend of 13 years, Tom Irsfeld. Tom was a mentor and instrument to the success I have had in my career. In the mids of keeping an eye on his family and helping the community to safeguard their lives and properties he still finds time to have some humor and an upbeat spirit. This is true for all the people of North Dakota.

03/25/2009
Hey Mariano,

You had asked about the flooding (I missed your call, I was out sandbagging yesterday). It’s easier to show via picture (using this great Microsoft product). So far we are okay. The red square is our house, Microsoft campus is about a mile to the left. The empty space behind the houses normally fills in with water only when the river level gets to about 30 feet or if we have a sudden heavy rain.

The treeline on the right is the Rose Creek Coulee, it flows to the Red River in about a mile. Normally, it has only a foot of water in it, if that, but now is filled & overflowing its banks..

Initially the city’s plan was to do a sandbag dike through our yard (the green broken line), but the city wisely decided to build a clay dike instead across, which saved thousands of sandbags & lots of work. And saved my lawn J

The blue lines are some kind of modular walls that are filled with sand, they were provided by the National Guard & are much easier than sandbags. But they can only be used on flat land (which in Fargo, there is plenty of) or where a Bobcat can get in to fill them with sand. The pink lines are sandbag dikes to connect the 2. There are 3 more cul-de-sacs that have the same thing happening.

So for now we are okay, the crest is expected Sunday (40 feet). Yesterday the sandbagging was nice (50 degrees & sunny), but overnight we got 3-4 inches of snow with another 3-4 inches expected to come (you know it’s bad when the shopping mall even closes).

So far, so good, as long as the weather cooperates & doesn’t dump too much rain all at once, or if it gets too warm too fast & all the snow melts at the same time.





Tom also provided an update 2 days later as follows:

03/27/2009
All,

If I were in the 21st century, I would have a Facebook page to share info (or I guess MySpace), but anyway, here's the latest Fargo flood update.

The river now is at 40.33 feet (the old record of 40.1 was in 1897 ---- in 1997, the year of the last flood, it was 39.5). The expected crest is to hit on Monday & last for 7 days, the NWS says it will crest at 43 feet. Unfortunately, our dikes are at 43 feet, so we now have to put another 1 foot on top of them (which means 2 more days of sandbagging starting today). So for the next 10 days, in my area, we will have to watch the dikes for leaks (unless of course it flows over on Monday).

We have moved most important things from the basement to the main floor & much of the main floor valuables (our Picassos & Faberge eggs) up to the 2nd floor. Some neighborhoods in Fargo & Moorhead have had some precauationary evaucuations due to some leaking in the dikes, but the city engineers are working to fix that.

The good news is that the Microsoft campus is closed, so we don't have to work (even if it were open, travel is prohibited anyway). :)

Later,
Tom I.


Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Fargo flooding to impact Dynamics GP technical support response

Due to the current flooding events in Fargo, North Dakota, home of the Microsoft Business Solutions campus, you will experience delays in the response to any support cases and newsgroup questions. For news updates on the flooding click here.

We all develop a relationship with some of the individuals behind the phone at some point in time. Please keep the tech support team and the people in North Dakota living through this difficult times in your thoughts and prayers.

I am also posting this Out of Office reply from my friend Tom Irsfeld for those of you who have development cases pending with the Global Development Team. It provides intructions for contacting individuals.

"As many of you have heard, Fargo is encountering a flood at this time, with the expected crest on Monday. The Microsoft campus is currently closed & may incur flooding as well. As a result, I will be out of the office for sandbagging, indefinitely. I may be able to respond to emails from home, but you should not rely on it.

If you have a Development or Build question, please contact Manoj Swaminathan.

If you have a Test question, please contact Patrick Spaeth."

Until next post!

MG.-
Mariano Gomez, MIS
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Wednesday, March 25, 2009

Mariano, "How do you find time to post on the Dynamics GP Newsgroup?"

Folks, I cannot begin to tell you how many times I have been asked this question. Despite working full time, having a family, a few children to take care of, one of the most successful blogs in the Dynamics GP community to keep up with, and a weekend full of activities, most of you may think that my life revolves around answering questions in the newsgroup -- well if you ask my wife, you may get a definitive YES to that statement.

I do enjoy reading the Dynamics GP Newsgroup, the Dynamics GP Developer Newsgroup and the SQL Server Programming Newsgroup. These outlets are perhaps the best places [of my own interest] to pick up some of the everyday challenges facing users and developers from all around the world. In addition, I believe newsgroup participation is perhaps the best method -- bar training -- to maintain your competitive edge in this business.

So here is the secret! From Monday to Friday, between the hours of 8:00 AM and 5:00 PM, I review the newsgroups 4 times a day -- the one(s) of my interest for the day. During the review period, I read the new posts, plus previous posts I am keeping an eye on. If the answer is immediate or if there is a blog article, KB article, or other resource I can refer the poster to, I immediately reply with an answer. Some posts demand some research, for example, those requesting a code snippet or those for which the answer is not as apparent. This is the real gain for me! I love playing with code and I love testing stuff out to respond to a post.

NOTE: It's important to always clarify what version and build number or service pack of the product you are using. 9 times out of 10, you will receive a faster response and the response may make more sense within the context of the product you are using.

Once I figure out the answer to a problem, in my next cycle I proceed to respond to those posts and review the new posts. But life is not always perfect, time slips and sometimes I am buried in the tasks at hand -- after all, the customer signs the check. This is where the night time owl comes into play.

My evenings are centered around homework with the kids, some family time, and... no sleep! That's right! Like many individuals around the world, sleeping is not one of my virtues. I find the evenings to be very conducing to writing -- newsgroup posts, blog articles, articles for MSDynamicsWorld, among others.

Now that you know my secret, I encourage you to participate actively in the Dynamics GP newsgroup or the newsgroup of your choice, but more importantly, to reward the person(s) who answered your question with a simple "Yes" if the post answered your question* and/or "Yes" if the post was helpful to you*.

NOTE: * These options are available with the web reader.

Please check the links to the newsgroups on the right frame of the blog and get involved with the Dynamics GP community.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Tuesday, March 24, 2009

What is the Dynamic User Object Store (DUOS)?

I have written a number of articles on my blog showcasing storing data in the DUOS, you may have read a number of postings on Developing for Dynamics GP on the subject, but many of you have asked what exactly is the DUOS and how to store and retrieve the data in the DUOS.

For all its fancy name, simply put the DUOS is a SQL Server table - dbo.SY90000 - a part of your company database. However, there is more to it than meet the eyes. But first, lets take a look at the table definition:



/****** Object: Table [dbo].[SY90000] Script Date: 03/23/2009 14:48:44 ******/

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[SY90000](
[ObjectType] [char](31) NOT NULL,
[ObjectID] [char](61) NOT NULL,
[PropertyName] [char](31) NOT NULL,
[PropertyValue] [char](133) NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PKSY90000] PRIMARY KEY NONCLUSTERED
(
[ObjectType] ASC,
[ObjectID] ASC,
[PropertyName] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO



The important aspect to highlight from this table definition is the primary key constraint as, before hand, you will not be able to import records with a duplicate Object Type, and Object ID, and PropertyName.

When do I use DUOS?

To understand when to use DUOS, I will first refer to the methods of accessing and storing data in the DUOS itself.

There are two methods for programming DUOS:

1) Using Dexterity
2) Using Modifier with Visual Basic for Applications.

While Dexterity offers a good support for accessing DUOS (after all, it's only another Dexterity table) with its standard get, save table, change, and range statements, DUOS was really implemented to be used in conjuction with Modifier and Visual Basic for Applications.

DUOS is primarily designed to support minor customizations (with Modifier and VBA) requiring limited data storage, this is, a few extra fields added here and there. All extra fields and their values are stored as strings in the physical DUOS table, hence the performance overhead that large data sets can bring as their values will require conversion to match proper datatypes.

NOTE: If you have a need to store large data sets it is recommended you explore other methods like independent SQL Server tables with their own data definitions. These tables can be created and accessed with Dexterity, or created in SQL Server and accessed with ADO.

The DUOS object model

The DUOS object model is a standard part of Microsoft Dynamics GP Visual Basic for Applications object library - the library resides in the DEXVBA.DLL assembly file and exposed to COM via an OLE type library file, DEXVBA.TLB.

The library exposes the DUOSObjects and DUOSProperties collections and two objects, the DUOSObject and DUOSProperty. The following image illustrates the object model with its methods and properties.





















DUOS Resources

Now that you have an understanding of the object model and how/when to utilize DUOS take a look at some of the coolest samples around the blogosphere.

DUOS examples - David Musgrave at Developing for Dynamics GP. Click here.
VBA Workshop series - Click here.

Until next post!

MG.-
Mariano Gomez, MIS, MCP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Monday, March 23, 2009

2009 Payroll Tax Update (Round 4) for Dynamics GP 8.0 Users

If you are using Great Plains 8.0 you can download the Payroll Tax Update (Round 4) file by clicking here. Keep in mind that this script was developed for version 10, but runs just fine on version 8.

I strongly encourage you to execute the script in a test environment to make sure the results are what you are aligned with your expectations. Once you've verified this, you can proceed to execute against your production environment.

NOTE: They were additional changes included in a separate installation file to update Missouri's tax code. This script does not include those changes. I am in the process of working on the new installation file and will post back with the updated tax rates for Missouri. In addition, and according to Microsoft, The msp file for I-9 and Cobra changes will be available on March 25.

Related Articles

2009 Payroll Tax Update for Microsoft Dynamics GP 8.0

Until next post!

MG.-
Mariano Gomez, MIS
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Monday, March 16, 2009

Dynamics GP Code Name "version 11"


I was reading carefully through The Official Blog of Microsoft Dynamics GP and could not help to notice the fine print on the next release of Microsoft Dynamics GP covered at Convergence 2009, among them, "version 11" is just the code name of the product and according to Microsoft's Product Management and Marketing team. In their own words "this is the internal name…we’ll likely ship with a different moniker for this release".

It is also said that code name "version 11" will ship with yet more integration capabilities to the Microsoft Office suite of applications, especially the all too neglected integration to Microsoft Word. The Dynamics GP home page is getting a face lift with tons of graphics and charts that will allow users to drill-down to the details behind those charts up to the point of reaching inquiry and transaction windows.

You can click here to read more information about code name "version 11".

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Asset-based lending and receivables factoring

Factoring receivable invoices is the sale of an asset - your company's invoices. The sale of your invoices to a third party - known as a Factor - eliminates the sale-to-collection business cycle of waiting for a customer payment. Usually, factoring companies will purchase your invoices for up to 90% of the total amount. You get your cash now and the factor takes on the risk of collecting the payments from your customers. The creditworthiness of your customers is very important if you want to get a good rate from a factor.

What are the specific advantages?


  • Immediate cash with no waiting and without incurring new debt: You receive quick payment following invoicing.

  • To factor, your business credit rating is not an issue because you are not borrowing money

  • Efficient handling of all your invoicing and data entry

  • Relief from the responsibility for collecting no-pay and slow-pay clients

  • You have expanded growth capacity through increased production and total sales

  • Ability to take advantage of vendor discounts

How to implement factoring in GP?

I have done a number of implementations where accounts receivable factoring was necessary, and one way -- read, they may be other methods, but this is what worked for my clients -- I have found is by using the National Accounts functionality in GP, where the factoring company becomes a national account and all customers are child records of the national account. Lets take a look:

1) Setup your factoring company as a customer tied to the checkbook ID where funds from the factoring company are deposited.


















*Click on image to enlarge

2) Setup your factoring company as a national account and add your customers as child records to the national account.



















*Click on image to enlarge

3) If you receive check or payments from your customers, just forward them to your factoring company. This is not a common occurrence in this scenario, but if you do receive checks from your customers, there is no need to record these in Dynamics GP.

4) When you receive the funds and payment report from the factoring company, enter a cash receipt for the amount reflected on the report. When you post the cash receipt, don't forget to do a bank deposit. This will reflect the funds in your account.













* Click on image to enlarge

5) Apply the cash receipt from the factoring company to the invoices reflected on the report. Because of factoring, invoices will be short. Writeoff the difference to the factoring expense account you have created.

















* Click on image to enlarge

















* Click on image to enlarge

NOTE: If you have a high volume of transactions, you can request a file from the factoring company to be used with Integration Manager.

Overall, what I like about this approach is, your company still does not loose track of the invoces owed by your customers, you can still run receivable agings that will allow you to estimate how much your outstanding invoices are worth allowing greather visibility on cash flow -- remember, your fees will increase depending on the age of the invoices held by the factoring company.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Monday, March 9, 2009

Modifier with VBA 3-day workshop summary

Thanks for your participation in the workshop! I hope you enjoyed working on the project and understanding the techniques and ability to do custom complex customization with Modifier and VBA.

I did receive quite a bit of feedback, some via email, some via comment posts to the workshop articles, but more impotantly, thanks for the 300+ hits a day while the workshop was being conducted. 200+ of you downloaded the code from day 3 and a good number of you participated from some of the most remote locations on the planet. That just keeps me going! If you liked the format of the worshop, send me your emails, pick the topic and the time and you most likely will see it here on my blog.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Saturday, March 7, 2009

Workshop Day 3 - Adding the VBA code to the project

Welcome to the final installment of the Modifier with VBA workshop. Today we will work with lots of code, some of it very straight forward, some of it will show advanced techniques.

Topics to be covered:
  • UserInfoGet.CreateADOConnection() Method
  • Dynamics Continuum Integration Library and Pass-through Dexterity
  • Dynamic User Object Store (DUOS)

But first, some more fields for our project. Since we will need to have a key for the additional field (Regulatory Agency) being stored, we will use the Equipment Number as the key -- the assumtion is the Regulatory Agency is the same at the equipment level, regardless of the item assigned to the equipment.

1) Open the Equipment Maintenance window. Go to Cards > Field Service > Equipment
2) Press Shift + F11 on your keyboard to activate the Add Fields... option.
3) Click on the Equipment Number field.
4) Click on the Save button.
5) Click on the Delete button.

In addition, we will be introducing some professional grade code, so we will want to remove the exiting reference to the SmartList project and remove the LookupButton_BeforeUserChanged script created in our previous session.

1) To remove the reference to the SmartList project, open the VBA Editor - Press ALT + F11 on your keyboard.
2) Locate the FieldService project in the Project Explorer.
3) From the Tools menu, choose References...
4) Unmark the reference to SmartList and save
5) Highlight the LookupButton_BeforeUserChanged code in the VBA Editor and delete.

We now want to create our reference to the FieldService project in our SmartList project. We will expose some methods in our FieldService project to facilitate returning the Customer Number to the Regulatory Agency field.

1) In the Project Explorer, locate the SmartList project and highlight.
2) From the Microsoft Visual Basic editor's Tools menu, select References...
3) In the References window, locate FieldService, then add a checkmark to indicate that objects in this project will be referenced. Use the Priority buttons (Up) to give the FieldService objects a higher priority. This will help improve your customization's performance.

Good to go!

NOW THE CODE!

1) Open the VBA Editor - Press ALT + F11 on your keyboard.
2) Locate the FieldService project in the Project Explorer.
3) In the Code Editor, we will first start with some general definitions to support our project. Select (General) from the drop-down list, next to the Project Explorer.

(General)

'Enforce explicit variable declaration

Option Explicit
' Used to stored and retrieve data from DUOS

Dim EquipmentCollection As DUOSObjects
Dim EquipmentObject As DUOSObject
Dim WantToDelete As Boolean
Dim EquimentIDToDelete As String

Public OkToDelete As Boolean
Public Deleting As Boolean
Public LinkedCustomerLookup As Boolean


4) We will now add some code to the Equipment Number field. Each time this field changes, we need to check our DUOS repository to see if there is a value in store. If so, we need to display it. In addition, we will need to make sure that we setup our delete flags. These will let us know if it's ok to delete a record from the DUOS and if the user wants to actually delete the record.

EquipmentNumber_Changed

' Created by Mariano Gomez, MVP
Private Sub EquipmentNumber_Changed()
Set EquipmentCollection = DUOSObjectsGet("Equipment Information")
If Not EquipmentNumber.Empty Then
Set EquipmentObject = EquipmentCollection.Item(EquipmentNumber)
RegulatoryAgency = EquipmentObject.Properties("RegulatoryAgency")

' Lock the field if not empty
If Not RegulatoryAgency.Empty Then
RegulatoryAgency.Locked = True
Else
RegulatoryAgency.Locked = False
End If

Me.Changed = False
End If

' Setup up delete flags if we were able to retrieve something from DUOS

If Not EquipmentNumber.Empty Then
EquipmentIDToDelete = EquipmentNumber
OkToDelete = True
WantToDelete = False
End If
End Sub


5) Now, lets make sure everytime we save our equipment record, the value stored in our Regulatory Agency field gets flushed to the DUOS. We need to validate if all required fields have been entered.

Save_BeforeUserChanged

' Created by Mariano Gomez, MVP

Private Sub Save_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
' Save DUOS Data from Save Button
If Me.Required = True Then
EquipmentObject.Properties("RegulatoryAgency") = RegulatoryAgency
End If
End Sub


6) Deleting an object from DUOS is rather a 3-step process that will require capturing the user's intentions based on the reponse to the modal dialog question of whether they want to delete a record or not.

Delete_BeforeUserChanged

' Created by Mariano Gomez, MVP

Private Sub Delete_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
' Flag Deleting
Deleting = True
End Sub



Delete_AfterUserChanged

' Created by Mariano Gomez, MVP

Private Sub Delete_AfterUserChanged()
' Delete DUOS if Delete was confirmed on dialog
Deleting = False
If WantToDelete And OkToDelete Then
EquipmentCollection.Remove (EquipmentIDToDelete)
End If
End Sub



Window_AfterModalDialog

' Created by Mariano Gomez, MVP

Private Sub Window_AfterModalDialog(ByVal DlgType As DialogType, PromptString As String, Control1String As String, Control2String As String, Control3String As String, Answer As DialogCtrl)
Select Case PromptString
' Save from Dialog (browse or close window)
Case "Do you want to save changes?"
If Answer = dcButton1 Then
If Me.Required = True Then
EquipmentObject.Properties("RegulatoryAgency") = RegulatoryAgency
End If
End If

' Confirm Delete
Case "Do you want to delete this record?"
If Answer = dcButton1 Then
WantToDelete = True
End If

Case Else
End Select
End Sub

NOTE: If you were following the Translating Dexterity Applications series by David Musgrave over at Developing for Dynamics GP, you may want to look at some considerations for your region when using VBA in the article "Handling Translation when Customizing with VBA". Especially, you can make your Window_AfterModalDialog code language independent by using the advanced technique outlined in the article.


OUR CUSTOM FIELDS ALSO DESERVE SOME CODE!

We can now begin to add code to our custom fields on the window, the Regulatory Agency field, the Zoom button and the Lookup button.

1) Since the Regulatory Agency field can be typed in by the user, we need to validate whether the customer entered exists or not. For this we will use the UserInfoGet.CreateADOConnection() method to open an ADO connection to the company database. You can also use the Dynamics Continuum Integration Library and pass-through Dexterity to do some more interesting things like positioning the first record in the scrolling window to the closest match of what the user typed in the field.

RegulatoryAgency_BeforeUserChanged

' Created by Mariano Gomez, MVP
Private Sub RegulatoryAgency_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
' If the user manually types in the customer number, we need to make
' sure it is a valid record.

Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRs As New ADODB.Recordset
Dim Exists As Boolean

On Error Resume Next

'Retrieve an ADO connection for the current user
Set objConn = UserInfoGet.CreateADOConnection()

'Set the connection properties
With objConn
.CursorLocation = adUseClient

'Set the current database, using the IntercompanyID property
.DefaultDatabase = UserInfoGet.IntercompanyID
End With

'Create a command to check for the customer record
With objCmd
.ActiveConnection = objConn
.CommandType = adCmdText
.CommandText = "SELECT CUSTNMBR FROM RM00101 WHERE CUSTNMBR = '" & Trim(RegulatoryAgency) & "'"
End With

Set objRs = objCmd.Execute
objRs.MoveFirst
If objRs(0) = "" Then
Exists = False
MsgBox "This customer does not exist."
RegulatoryAgency.Focus
Else
RegulatoryAgency.Locked = True
Exists = True
End If
End Sub


NOTE: In order for this script to execute successfully, you will need to add a reference to Microsoft ActiveX Data Objects (ADO).

2) Now, lets mix it up a bit. We will use Pass-through Dexterity with the Dynamics Continuum Integration Library to zoom into the Customer Maintenance window whenever a user clicks on the field's hyperlink.

ZoomButton_BeforeUserChanged

' Created by Mariano Gomez, MVP
Private Sub ZoomButton_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
'Dim CompilerApp As New Dynamics.Application
Dim CompilerApp As Object
Dim CompilerMessage As String
Dim CompilerError As Integer
Dim Commands As String

' Create link without having reference marked
Set CompilerApp = CreateObject("Dynamics.Application")

Commands = ""
Commands = Commands & "call OpenWindow of form RM_Customer_Maintenance, """ & _
Trim(RegulatoryAgency) & """;"

' Execute SanScript
CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
If CompilerError <> 0 Then
MsgBox CompilerMessage
End If
End Sub

NOTE: The above technique is not supported by Microsoft. However, it shows the endless power and flexibility of the VBA environment when combined with other tools like Dexterity and Continuum.

3) Our Lookup Button deserves some really strong code, so we will use pass-through Dexterity to call the SmartList lookup. This allow us to do some positioning of the record being displayed in the lookup window, replicating Dynamics GP's exact behaviour.

LookupButton_BeforeUserChanged

' Created by Mariano Gomez, MVP
Private Sub LookupButton_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
'Dim CompilerApp As New Dynamics.Application
Dim CompilerApp As Object
Dim CompilerMessage As String
Dim CompilerError As Integer
Dim Commands As String

LinkedCustomerLookup = True

' Create link without having reference marked
Set CompilerApp = CreateObject("Dynamics.Application")

Commands = ""
Commands = Commands & "open form Customer_Lookup {return to CUSTOMER_FIELD}; {Can't return to Modifier field}"
Commands = Commands & "call INITIALIZE of form Customer_Lookup, 1, 0, """ & Trim(RegulatoryAgency) & ""","""","""","""","""","""";"

' Execute SanScript
CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
If CompilerError <> 0 Then
MsgBox CompilerMessage
End If

LinkedCustomerLookup = False
End Sub


ALMOST DONE!

So, we are almost there.

1) Now our Customer Lookup window needs to be able to return data to our Regulatory Agency field. In order to do that, we will create a Public method in our FieldService project that will set the field value when returned from the by the lookup.

ReturnRegulatoryAgency()

Public Sub ReturnRegulatoryAgency(sCustomer As String)
' Set the field value with the returned value from the lookup
' Since we cannot reference our field directly with pass-through Dex
' this method is called from the SmartList dictionary to return the
' value when the Select button is clicked.
RegulatoryAgency = sCustomer
End Sub

2) Locate the SmartList project in the Project Explorer, expand and double-click on the DebtorandProspects(Window) object to open the code editor. Add the following code.

Customer Lookup code

(General)

' Created by Mariano Gomez, MVP
Option Explicit
Dim LinkedCustomerLookup As Boolean


Window_BeforeOpen

' Created by Mariano Gomez, MVP
Private Sub Window_BeforeOpen(OpenVisible As Boolean)
LinkedCustomerLookup = FieldService.EquipmentMaintenance.LinkedCustomerLookup
End Sub


Select_AfterUserChanged

' Created by Mariano Gomez, MVP
Private Sub Select_AfterUserChanged()
If LinkedCustomerLookup Then
If FieldService.EquipmentMaintenance.IsLoaded Then
Call FieldService.EquipmentMaintenance.ReturnRegulatoryAgency(DebtorsandProspectsDetail.CustomerNumber)
End If
End If
LinkedCustomerLookup = False
End Sub

Note that the Select_AfterUserChanged method calls our ReturnRegulatoryAgency method in our FieldService project. This is a more natural way of setting our field value.

Downloads

WorkshopDay3References.package - Contains references to Microsoft ActiveX Data Objects 2.6 - Click here to download.

WorkshopDay3.package - Contains the actual code for the entire workshop - Click here to download.

I hope you enjoyed the workshop. Please provide your feedback to the topics covered throughout. Let me know if you would like me to address other subjects in this format as well and what difficulties or inconveniences you experienced.

Until next post!

MG.-
Mariano Gomez, MCP, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Friday, March 6, 2009

Dynamics MVPs Recognized at 2009 Global MVP Summit

Thanks to Dynamics GP MVP Monzer Osama for the clip live from the 2009 MVP Global Summit held in Seattle, Washington from March 1 to March 4. It's a shame we did not get to meet this year Monzer, but I am sure there will be plenty more of opportunities in the near feature.

video

Related Articles

Dynamics GP MVPs in the News - Click here

Until next post!

MG.-
Mariano Gomez, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Workshop Day 3 rescheduled for tomorrow

Hi and thanks for following the workshop. The last installment of the workshop is moving to tomorrow Saturday 8:00 AM EST (GMT - 5:00).

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Wednesday, March 4, 2009

Workshop Day 2 - Setting up the VBA project

Welcome to the second day of our Modifier with Visual Basic for Applications (VBA) workshop!

Today we will dive into adding objects (windows and fields) to VBA. This will set us one step closer to our final project. We will also be adding our first lines of code. Today's workshop will cover the following topics:

  • Adding windows and fields to VBA
  • Setting up cross-dictionary references with VBA
  • Adding code with the VBA code editor
Lets recap

In the previous session we added some extra controls (a field, a lookup button, and a zoom button) to the Equipment Maintenance window. We also explored linking the field to its prompt and the lookup to its field. This is especially important if we want to provide some extra capabilities to our project. After all, you never know who will customize our customization. For more information check my previous article "The importance of linking your fields to their prompts.

ADDING WINDOWS AND FIELDS TO VBA

For our project, we will need to add the Equipment Maintenance window to VBA, along with the controls we created. In addition, we will need to have our lookup button call the Customer Lookup window in the SmartList dictionary. This means that we will also need to add this window to the VBA project.

To add the Equipment Maintenance window to VBA follow these instructions:

1) Open the Equipment Maintenance window. Go to Cards > Field Service > Equipment
















* Click image to enlarge - Equipment Maintenance window

2) From the window Tools menu, select Customize > Add Current Window to Visual Basic. I am also a fan of shortcuts, so you could have also pressed CTRL + F11 on your keyboard. Now you have added your window to VBA... very simple!

3) To add the fields, we will press Shift + F11 on your keyboard. The system will change the mouse cursor to a visual cue that will indicate that the Add Fields mode is on. Click on the following controls:

a) The zoom button - Remember, this control is not visible, but will be added to VBA by clicking over the prompt.

b) The '(L) Regulatory Agency' field. This control is the textbox next to the prompt

c) The lookup button - The button with the binoculars.

Now, turn off the Add Fields visual cue by pressing Shift + F11 on your keyboard once more.

To verify you have completed this step successfully, you can open the Visual Basic Editor. Press ALT + F11 on your keyboard. Locate the FieldService project in the Project Explorer in the left pane of the window.






















* Click on the image to enlarge

Highlight the Equipment Maintenance object as indicated above, now click in the drop-down list contiguous to the Project Explorer to verify all your objects are present.









* Click on image to enlarge

Don't worry, if you are missing any of the fields, you can repeat step 3 until the results are the same as shown on the figure above.

NOTE: Dynamics GP is also open with the VBA Editor. You can close the editor to continue with the next steps.

4) Now we will add the Customer Lookup window. Click on the lookup button next to the Customer ID field in the Equipment Maintenance window.














5) Press CTRL + F11 on your keyboard to add the window to VBA

6) To add the fields, we will press Shift + F11 on your keyboard. The system will change the mouse cursor to the visual cue that will indicate that the Add Fields mode is on. Click on the following controls:

a) Select Button
b) Customer ID -- in the scrolling window

To verify you have completed this step successfully, you can open the Visual Basic Editor. Press ALT + F11 on your keyboard. Locate the SmartList project in the Project Explorer in the left pane of the window.






















* Click on image to enlarge

You can repeat the same procedure as above to verify the Select button has been added. For the CustomerNumber field, double-click on the Grid object in the project explorer then check the drop-down.

SETTING UP CROSS-DICTIONARY REFERENCES

Because the Customer and Prospects Lookup is located in the SmartList project, it is not accessible directly by the FieldService project. We will need to make this happen in order to be able to call the lookup window. In order to do so, we have to setup a reference to the SmartList project in the FieldService project.

1) In the Project Explorer, locate the FieldService project and highlight

2) From the Microsoft Visual Basic editor's Tools menu, select References...

3) In the References window, locate SmartList, then add a checkmark to indicate that objects in this project will be referenced. Use the Priority buttons (Up) to give the SmartList objects a higher priority. This will help improve your customization's performance.


















* Click on image to enlarge

Now you will see a References folder added to your FieldService project in the Project Explorer. This folder will contain a reference to the EXP1493.vba file.





















* Click on image to enlarge

Good work so far!


ADDING CODE WITH THE VBA EDITOR

We will add our first line of VBA code now. We will make the lookup button open the Customer Lookup window.

1) Click on the FieldService project in the Project Explorer window and highlight the EquipmentMaintenance(Window) object.

2) Click on the drop-down list next to the Project Explorer and select the LookupButton object. By default, VBA will create a BeforeUserChanged event subroutine template for our code. We will stick to this and add the following code:

LookupButton_BeforeUserChanged()

' Lookup button - BeforeUserChanged code
' Modifier with VBA Workshop - Day 2
Private Sub LookupButton_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
SmartList.DebtorsandProspects.Open
End Sub

3) Compile your code by clicking selecting Debug > Compile FieldService.

4) Save your code by clicking on the Save button on the toolbar menu.

NOTE: This workshop is not intended to go into code detail, but rather to demonstrate the techniques available to the user and developer communities.

Testing the code

1) Return to Dynamics GP and open the Equipment Maintenance window
2) Click the Lookup Button added, next to the Regulatory Agency field.

Our final installment on Friday will present lots and lots of code. We will explain some of it, you will research some of it and remember, you can always post back with your feedback.

Downloads

v10 - WorkshopDay2.package - Contains all topics covered during day 1 and day 2 of this workshop.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Monday, March 2, 2009

Workshop Day 1- Adding custom fields and lookups to a window

Overview

Welcome to the first installment of this 3-day workshop! The workshop will be conducted today Monday, Wednesday, and Friday. The format is simple: get your laptop or PC with Dynamics GP 10.0 and follow the instructions below. Try to complete each section of the workshop and post your questions. That's it!

Today's workshop will focus on the essentials of Modifier by working with the Equipment Maintenance window in the Field Service series. The topics to be covered are as follow:


  • Adding a window to Modifier
  • Adding extra fields and controls to a modified window
  • Working with field properties
  • Linking fields to prompts
  • Linking lookups to fields
  • Granting security to modified windows
Modifying the Equipment Maintenance Window

The Equipment Maitenance window will require a new field, the Regulatory Entity, and a new Lookup button that will call our Customer Lookup window.

1) Open the Equipment Maintenance window. Go to Cards > Field Service > Equipment.

2) From Tools menu, select Customize, then choose Modify Current Window to open Modifier

3) Click the Static Text tool on the Toolbox window to add the prompt Regulatory Agency. Change the properties for the static text as follows:






















4) Click the String tool on the Toolbox window to add a field place holder for the value returned by the lookup. Set the field properties as follows:






















5) Link the field to its prompt. Click on the Tools menu and select Link Prompt. When the visual cue is activated, click on the '(L) Regulatory Agency' field dragging to the prompt.

6) Add the lookup. Click on the Push Button control in the Toolbox window, then click next to the '(L) Regulatory Agency' field. Arrange the push button to be of the same size of other lookup windows on the screen.

7) Change the button properties as follows:

a) Double-click on the Field property under the Object tab in the Properties window. Change the field name to Lookup Button.





















b) Select Text-Native Picture from the Static Values drop-down list. Click on the ellipses button next to the DDL to open the Button Items window. Click on the ellipses button next to the Up property and select the VS_Binoculars image from the list.












NOTE: If you want the classic lookup image, you will need to add it to the Field Service modified forms picture library by copying and pasting an image from the Dynamics dictionary.

c) Click Ok as many times to return to the modified window layout screen.

8) Link the lookup to the field. Click on the Tools menu and choose Link Lookup. Click on the lookup button dragging to the '(L) Regulatory Agency' field. The final window result is as follows (Layout > Preview):



















9) Return to Microsoft Dynamics GP and grant security to the modified window. Save all changes to your modified window on the way out.


























10) Verify the window displays as intended. You can go back to Modifier to make any adjustments for field alignment or layout as needed. Our series continues Wednesday with adding all the objects and elements needed for our VBA project.

On your own

Add the Zoom Button.

1) Return to Modifier
2) Click to add a Push Button control.
3) Position the push button over the Regulatory Agency prompt
4) Change the push button properties as follow:

a) Visible = False
b) Zoom = True

5) Verify the size and length of the push button and alignment with the prompt
6) Return to Dynamics GP and test the form by going to Cards > Field Service > Equipment

Downloads

v10 - EquipmentMaintenance.package. Contains the changes made to the equipment maintenance window made with Modifier (does not contain zoom button, I will post a download for this tomorrow).

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/