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

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/

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/

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/

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/

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/

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/