How to launch a URL from VBA in Microsoft Dynamics GP

So you have created this great customization and part of it is to launch a URL and pass in some parameters via http. For example, a customization on the customer master that will open the customer's invoice entry portal where you can type in the invoice information.

The following code can be placed in the (General) section of your VBA customization and be called from any other event, for example the BeforeUserChanged event.

Public Function OpenBrowser(ByVal URL As String) As Boolean
Dim res As Long

' it is mandatory that the URL is prefixed with http:// or https://

If InStr(1, URL, "http", vbTextCompare) <> 1 Then
  URL = "http://" & URL
End If

result = ShellExecute(0, "open", URL, vbNullString, vbNullString, vbNormalFocus) OpenBrowser = (result > 32)

End Function

You can call the ShellExecute() Windows API function from a Visual Basic for Applications in Microsoft Dynamics GP to start another program under Microsoft Windows. Use ShellExecute() instead of Shell (a Visual Basic statement) or WinExec() (a Windows API function) to work around the following limitation of the latter commands: With Shell and WinExec(), you cannot start an application by specifying a file name only. ShellExecute() uses the default programs associated to each file type. In the case of the above example, ShellExecute() automatically recognizes the prefix http in the URL and launches Internet Explorer (or the default browser application).

Other Resources

KB Article 224816 - Use ShellExecute to Launch the default browser, by Microsoft Support.

Until next post!

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

Microsoft Dynamics GP Support Debugging Tool

You may have heard about the Support Debugging Tool for Microsoft Dynamics GP previewed a few months ago at the Microsoft Dynamics GP Technical Airlift 2008 in May with good reception among the attending crowd.

What is the Support Debugging Tool?

The Support Debugging Tool is a Dexterity application which contains a collection of utilities and tools to make the task of supporting, developing and debugging Microsoft Dynamics GP easier and faster.

David Musgrave, author of the tool says "I originally developed the tool when our Microsoft Dynamics Support in the Asia Pacific region was assisting a customer with a situation that produced invalid data in a table, but no cause could be replicated. Looking at the customer’s data it was verified that there was an incorrect value in the table. No one was able to identify when the previously correct value in the table was being changed to the incorrect value."

Using Dexterity triggers and the ability to programmatically control Dexterity and SQL logging, the Support Debugging Tool was built and used to monitor the table field in question and log the steps which led up to the field changing to the incorrect value. It was able to identify the situation and provide the exact scripts being executed up to the point the error occurred. This information allowed the code issue to be identified and fixed.

Since then the features of the tool have been expanded and will continue to expand as feedback and needs demand.

For more information on Support Debugging Tool please see David Musgrave's article on the topic.

Until next post!

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

Microsoft Dynamics GP's History... from a Logo Perspective

I am not sure if my friend David Musgrave has too much time on his hands, but one thing is for sure: the guy does have some of the coolest articles around the GP blogosphere. This time, David surprises us with a time progression of Dynamics GP logos. If the product history could be documented in pictures, this would be it.

Let's see, I started working with Dynamics GP when the wheat and red sun logo was around back in 1996. Back then I took R3.15 to Latin American Spanish... not how I want to be reminded of my age.

Until next post!

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

Create your own taGetNextNumber stored procedure

I have heard this one very often on developers and users newsgroups alike. In past releases of eConnect (8.0 and before) it was not possible to retrieve the next document number for a few existing transactions in the system via eConnect's COM interface, case in point the Purchasing Receipt Transaction. However, the problem is not limited to eConnect. Many developers have tried to retrieve transactions next document numbers via Integration Manager and have faced the same issue.

The following sample code segment creates a wrapper around the little known dbo.ivNumber_Inc_Dec stored procedure, present within the company database.

create procedure sptaGetNextNumber 
  @poprctnm char(17) output, 
  @errorstate int output
as
declare @nextrctnm char(17)

select @poprctnm = poprctnm, @nextrctnm = poprctnm from pop40100
-- call Inc_Dec procedure

exec @errorstate = dbo.ivNumber_Inc_Dec 1, @nextrctnm output, @errorstate output

if @errorstate = 0
  update pop40100 set poprctnm = @newrctnm

return 
go

grant execute on sp_taGetNextNumber to DYNGRP

The dbo.ivNumber_Inc_Dec stored procedure takes a document number string and increments or decrements the numeric portion based on a flag. The stored procedure uses the following parameters:

[dbo].[ivNumber_Inc_Dec]
  @I_tInc_Dec tinyint = NULL, 
  @IO_vDocNumber varchar(21) = NULL output, 
  @O_iErrorState int = NULL output

Until next post!

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

Microsoft Dynamics GP 10.0 Service Pack 2 Login Issue Fixed

Mark has interesting information posted on his blog on a security fix addressed in Microsoft Dynamics GP 10.0 Service Pack 2 (10.00.1061). The original issue had to do with turning on password control with password expiration in GP, which is in turn address by Active Directory. Go on to read Mark's post on the issue to get the full details.

Until next post!

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

Cross Dictionary Development with Microsoft Dexterity

When I first started working in 1996 as a Software Development Engineer on the Dynamics GP Spanish release for Latin America project in Colombia for my former company Nucleus S.A. -- a Microsoft regional partner at the time -- I was challenged by a number of puzzling coding issues that seem to have no solution. Our inability to access third party dictionary information interactively with Dynamics GP was the absolutely most disappointing issue we faced since we had great plans for integrating Fixed Assets (back then a Forestar product) with GP for some Latin American localizations needed at the time. Those issues propagated over a period of 2 years, and during the time our only possibilities were database triggers and stored procedures after the fact. I must say that I worked hand to hand with some of the most brilliant people on the International Team at the former Great Plains Software in getting the product off the ground from a development perspective: Tom Irsfeld, Dave Gaboury, Tadeuz Komorovski and Papa Ndoye.

It was not until 1998 when I traveled to Fargo, ND that I met David Musgrave for the first time. I attended his Integrating with Third Party Dictionaries session, becoming familiar with some not too ortodox, but highly effective methods (at the time) he engineered for accessing data on foreign third party forms and tables from Dynamics GP using Dexterity 4.0. His materials became the bible I (and many Dexterity programmers around the world) followed to train new developers and forward my career in the company as a Senior Software Architect. I also attended his conference in Summer of 2000 where he unleashed other cross dictionary techniques that until today are still considered the absolute best practices.

I followed up with his Pushing the Limits with Dexterity which he co-hosted with Mark Rockwell from Rockton Software in 2002. By then, Dexterity had gained new functionalities from a scripting perspective and it was showing its maturity.

David and Mark have always made these conference materials available from each of their sites, but you can now download directly from David's blog.

Until next post!

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

Microsoft Dynamics GP Scrolling Windows and Line Sequence Numbers

I thought I would write about something meaningless that no one really thinks about. However, I realized that this is a compelling topic since it makes for some interesting discussion around the ability to insert line items withinin most transaction scrolling windows.

Let’s take some time to understand where Line Sequence Numbers come from. A Line Sequence Number is auto generated by Dynamics GP and uses a seed value of 16,384 as a basis for the first line. Each subsequent line in the scrolling window is incremented by this number, so line number 2 would have a Line Sequence Number value of 32,768, line number 3 would have Line Sequence Number Value of 49,152, and so on.

Since most GP transaction entry scrolling windows allow inserting rows in between line items, the newly inserted Line Sequence Number value is calculated as an average between the previous and the next line item sequences, i.e., if you were to insert an item in between row 1 and row 2 in the scrolling window -- keep in mind the values 16,384 and 32,768 respectively -- the Line Sequence Number would then be calculated as:

Line Sequence Number = (16,384 + 32,768)/2 = 24,576.

To visually represent this:

row1.... 16,384
row2.... 24,576 -- newly inserted row
row3.... 32,768

Now, if you were to insert a new row in between 1 and your previously inserted row 2, the new Line Sequence Number is now calculated as follows:

Line Sequence Number = (16,384 + 24,576)/2 = 20,480.

Once again, to visually represent this:

row1.... 16,384
row2.... 20,480 -- newly inserted row
row3.... 24,576 -- previously inserted row 2 is now row 3
row4.... 32,768

At this point, you may be asking, how is this nonsense even relevant? Just imagine for one instance that you attempt to insert line items at the same spot in the scrolling window, say for example at row 2, what would happen? You receive a "You cannot insert a row" error!




What happened? Let’s go back to our visual representation, this time with a real example:

ITEMNMBR                LNITMSEQ
--------                -----------
FOO1                         16,384
FO16                         16,385
FO15                         16,386
FO14                         16,388
FO13                         16,392
FO12                         16,400
FO11                         16,416
FO10                         16,448
FOO9                         16,512
FOO8                         16,640
FOO7                         16,896
FOO6                         17,408
FOO5                         18,432
FOO4                         20,480
FOO3                         24,576
FOO2                         32,768

The first time around, you will only be able to insert up to 14 rows in one location before experiencing the error mentioned previously. After the error, you will need to move one to a much more “suitable” location within the scrolling window to insert a new row, for example, row 4 (FO14) where only one item can be inserted due to the Line Sequence Number on FO15 and FO14.

Until next post!

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

New article on MSDynamicsWorld: Five Tips for Getting Your Microsoft Dynamics GP Implementation Right – The First Time!

Please stop by MSDynamicsWorld.com and read my new article "Five Tips for Getting Your Microsoft Dynamics GP Implementation Right – The First Time". In this article, I explore the reasons why most implementations go sour and provide some guiding principles for customers (and partners/consultants) to be successful.

Implementations are enterprise transformation projects and not just mere software installations.

Until next post!

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

Modifying reports that use Temp table and Accessing SQL data in Report Writer

David Musgrave demonstrates two Report Writer customization techniques. The first technique shows you how to access SQL data using VBA, which can be thought of as a way of working around those impossible table references, especially between GP and third-party products or viceversa. The second technique discusses how to customize reports that use temporary tables and considerations when attempting to create new relationships to other tables.

Enjoy these two Dynamics GP customization jewels and remember that you can take advantage of David's fully downloadable samples.

Until next post!

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

Suppressing "Credit Limit" Warning in Sales Order Processing

A few days aback, I was asked how to keep the "This transaction exceeds the customer's credit limit" message box from popping up when entering a Sales Order. At first I thought it was a strange request, since the warning serves its purpose, but then the user explained that they had their own credit management system which takes into consideration a lot more information that GP does not manage beyond the aging buckets and the limit itself. In addition, they already had a customization in place to check the customer's credit limit against their credit system and automatically set the order on hold if limit was exceeded. In summary, the message was an inconvenience since it appeared each time a line item was entered.
















To resolve this issue, I created a small VBA script that would automatically respond to the modal dialog without the user even noticing a blink on the screen, as follows:

1) Add the Sales Transaction Entry window to Visual Basic.

2) Open the Visual Basic Editor and locate the SalesTransactionEntry (Window) object. Double-click on it to bring up the editor.

3) Copy/paste the following script to the editor

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

  If PromptString = "This transaction exceeds the customer's credit limit. Do you want to continue?" Then

    'Click the first button, the Continue button
    Answer = dcButton1
  End If
End Sub

Until next post!

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

A First Look at GP's PerformancePoint Connector by Alan Whitehouse

Alan Whitehouse explores GP's intergration to PerformancePoint Server 2007 and shows details of his experience working with the Integration Wizard -- he also provides his honest opinions. As Alan kindly points out, "grab yourself an adult beverage" for this one as there is plenty of information to go through.

Until next post!

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

New Article on MSDynamicsWorld: XBRL and Microsoft Dynamics GP

Please stop by MSDynamicsWorld.com to read my new article addressing the Securities and Exchange Commission's (SEC) initiative to implement filings of financial results for publicly traded companies with XBRL and how two of Dynamics GP's tools can help you accomplish that goal: Analytical Accounting and FRx.

Until next post!

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

David Musgrave's Conditional Logos Implementation in Report Writer

How many of you have struggled and have ditched the idea of having a logo print on your reports based on say, for example, the company you are currently logged into. David really has a cool 1-2 punch example on how to implement conditional logos in Report Writer. The man is simply a genius and illustrates that sometimes the simplest solutions just happen to be the most powerful.

Until next post!

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

How to create a Go To link to the Apply Sales Document window in SmartList Builder

A few days aback, my buddy Jim Harris at TurboChef was having an issue with creating a Go To in SLB that would link open credits and return documents displayed in SmartList to the Apply Sales Documents window in GP where he could effectively apply documents that had not been applied to invoices, etc.

I thought, how complicated could that be? You open SLB, you setup your SmartList fields then, setup your restrictions for RM Document Type-All equal or greater than 7, and add the Go To by matching the Customer Number field on the window to the Customer Number in the RM Open Table, the Document Type on the window with the RM Document Type-All field in the table, and the Document Number in the window withe the Document Number in the table, a few clicks to close and save, build and... not so quick!

At first the Apply Sales Document window filled in the Customer Number and Document Number, but screamed at the Document Type field. I figured, my restriction was wrong, so I went back in and check on the restrictions, reviewed the SQL check on my Go To fields to make sure I was matching the values and running the field scripts, a few clicks to close and save, build and... not so quick!

I got the same result as before and was starting to wonder now if I had had a long day and was not thinking straight. So I went to the kitchen, got some coffee and went back to work on the problem.

After a few hit-and-miss attempts the light bulb went off! Drop-down lists are enumerations of the items listed in them! In the case of the Apply Sales Document window, 1 - Credit Memo, 2 - Returns, 3 - Payments, which do not correspond to the actual document types in RM (7 -- Credit Memo, 8 -- Returns and 9 -- Payments).

This meant that I would need to create a calculated field that would pickup the RM Document Type-All value from the RM Open table and translate it into the corresponding enumaration in the Drop-Down list in the Apply Sales Document window. The resulting calculation looked something like this:

case {RM Open File:RM Document Type-All} when 7 then 1 when 8 then 2 when 9 then 3 end

That's correct! It looks and behaves just like the CASE...END statement in SQL Server!

This now meant that in the Go To window, I would have to reference my calculated field instead of the RM Document Type-All field when matching the Document Type value.

From this point on a few clicks to close and save, build and... Voila!

Until next post!

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

David Musgrave's "Developing for Dynamics GP" Blog Moves to New URL

Dear readers, please update your Favorites link as David's blog site has moved to a new URL (http://blogs.msdn.com/DevelopingForDynamicsGP). The previous site now displays a link to his new site.

Great picture on the new site mate! For those of you not familiar with David, he is the dude on the right.

Until next post!

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

The Saga Continues: Microsoft SQL Server 2008 to be Shipped in August

Microsoft announced on Wednesday, July 9, 2008, at Worldwide Partner Conference that it will be shipping SQL Server 2008 in August with RTM set for sometime during Q3. I had the opportunity to preview SQL Server 2008 last April 29 in the Heroes Happen Here tour through Atlanta, held at the Convention Center Concourse and was very excited with the features and improvements in the product, however, other is got to be the story for customers and ISVs testing the product since last February, who have had to settle for the TCP and RC0 versions.

Until next post!

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

Bug: Correcting Journal Performance (or Lack Thereof)!

If you are experiencing slow performance issues while looking up a journal entry for correction, you are not alone! Make sure to stop by Jivtesh's blog site to get an insight on the issue. What I can't understand is why this problem has been dragging on since v9.0, and why the fix has not made it permanently to a service pack.

The good news is there is a fix available with KB article 925326 in the form of a SQL script.

Until next post!

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

To Check or Not to Check: Understanding Landed Cost's Invoice Match and Revalue Inventory Options in Microsoft Dynamics GP

Dynamics GP presents two selection options in the Landed Cost Maintenance screen: Invoice Match and Revalue Inventory. These two options have profound accounting effects in how inventory and purchases are tracked in GP. The following is provided as is and was posted by Marge Swanson, Senior Software Development Engineer at Microsoft in response to a user's question on the Dynamics GP community board, but I felt it was important to rescue as it contains valuable information not found in the manuals or elsewhere:

You should determine whether or not to mark the Invoice Match checkbox based on how you want your distributions to be tracked for the Landed Cost. If you do not mark Invoice Match, the distributions will be reversed from the accrued purchases account used on the Shipment – which defaults from the Landed Cost card. If you mark Invoice Match, the distributions will be created with the Purchase Price Variance account on the Landed Cost Maintenance window.

This helps you track the variances to a separate account if you want to. If you also mark Revalue IV when you mark Invoice Match, the distributions will assigned to the inventory account associated with the item the landed cost is applied to. The cost basis for the item will also be updated for any cost variance on the Landed Cost.

Example: Invoice matching and distributions for landed costs

Marking the Invoice Match option for a landed cost record will affect account distributions. For example, suppose that a shipment is recorded for 10 items at $1 each. The landed cost uses the Flat Amount cost calculation method, and the flat amount is $0.50.

The distributions for the shipment would look like this:

Inventory                                  $10.50
Accrued Purchases - Landed Cost            ($0.50)
Accrued Purchases – Inventory             ($10.00)

Suppose that when the invoice is received, the cost of the goods is unchanged, but the landed cost has increased to $0.75. If Invoice Match is not marked, the account distributions would be as follows.

Accrued Purchases - Landed Cost             $0.75
Accrued Purchases – Inventory              $10.00
Accounts Payable                          ($10.75)

If Invoice Match is marked and the Revalue Inventory option is marked for the cost variance, the account distributions would be as follows:

Accrued Purchases - Landed Cost             $0.50
Accrued Purchases - Inventory              $10.00
Inventory                                   $0.25
Accounts Payable                          ($10.75)

If Invoice Match is marked and Revalue Inventory option is not marked for the cost variance, the account distributions would be as follows:

Accrued Purchases - Landed Cost             $0.50
Accrued Purchases - Inventory              $10.00
Purchase Price Variance                     $0.25
Accounts Payable                          ($10.75)

Until next post!

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

Bug: SmartLists Builder not Formatting SQL Correctly in Microsoft Dynamics GP 10

It appears that SLB cannot format the SQL query required to extract data after adding a few restrictions. Specifically, any query requiring more than one restriction will ommit the AND operator. This has been documented under Problem report 47625: Restrictions don't work if have more than one on a SmartList which is currently scheduled to be resolved in v10 SP3.

"If you want it fixed earlier in a hotfix, I would suggest that you log a support case and reference the bug number. The more customers who request the fix, the higher the priority will be", said David Musgrave, Microsoft Escallation Engineer in a Microsoft Dynamics GP community post.

Until next post!

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

How to Automate a PDF File Output Name for a Microsoft Dynamics GP Posting Report

Many of you automating posting jobs have come across an issue with these schedulers: you are only allowed to output reports to the same physical file setup under your posting options. If you attempt to do this via Named Printers, the MDI writer will use a generic and all too cryptic 'NoteXXXX' file name that cannot be associated to anything coming from GP.

So, I figured you could probably use a SQL Server job that would run, say 20 minutes before your habitual posting schedule to change the file name. The following example will use the General Ledger Posting Journal report as an example, however, this can be accomplished for any report that need to be output to a file. In this example, it is all too important to keep in mind that the output of all transactions posting is stored in the 'Posting Journal Destinations' table (SY02200) in the company database.

1) Open Dynamics GP posting options. Go to Microsoft Dynamics GP > Tools > Setup > Posting > Posting. Choose Series:Financial, Origin:General Entry. Set the output of the report to file and enter a dummy file including the path as follows (use UNC for files that will be sent to a shared location on a server):



2) Setup a SQL Job that runs 20 minutes before your scheduled posting to execute the following statement:

declare @path varchar(255), @filename varchar(255)
set @path = '//ServerName/SharedFolder/'
set @filename = 'GLPJ' + convert(char(5), convert(int, getdate())) + '.PDF'

-- perform the update on the Posting Journals Destination table
update sy02200 set filexpnm = rtrim(@path) + rtrim(@filename) 
where series = 2 and trxsourc = 'General Entry'

NOTE: Replace //ServerName/ShareFolder/ with your path. If storing locally, the format is :drive_letter:RootFolder/Subfolder/ This simple example will allow you to automate a filename based on the date (which I converted to numeric for the convenience of the example, but you could extend according to your needs).

Until next post!

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

Restricting Access to Metrics on Microsoft Dynamics GP 10 Homepage

When it comes to securing metrics on GP 10's homepage, very little can be found outside of KB articles 918313 and 914898. But a few of us have decided to put this issue to rest -- I say a few of us because this 'how to' guide could not be possible without the assistance of FlieHigh and Tim Foster of Trudell Medical Limited in London, Ontario, Canada, both regular contributors in the Microsoft Dynamics GP community -- by combining our different approaches on the subject.

Lets get started! There are two approaches to lock down the metrics for a particular user in GP.

Method 1: T-SQL with SQL Security

1) Open Query Analyzer (SQL 2000) or SQL Server Management Studio (SQL 2005). Execute the following statement against the DYNAMICS database.

UPDATE SY08100 set Visible = 0 
WHERE(DictID = 0) and (SectionID = 3) and (UserID = 'userid')

The following is a list of Section ID values:

1 -- To Do's
2 -- Outlook
3 -- Metrics
4 -- My Reports
5 -- Quick Links

2) Open Enterprise Manager (SQL 2000) or SQL Server Management Studio (SQL 2005). Open the Tables folder of the DYNAMICS database and locate the SY08100 (syHomePageLayout) table. Click on Permissions.

3) In Enterprise Manager, hightlight the DYNGRP and click on the Columns button. Double-click on the Visible column under the Update heading to make sure that this option is set to Deny.

In SQL Server Management Studio, highlight DYNGRP in the Permissions window, select Update from the Explicit Permissions window, and click on the Column Permissions... button. Highlight the Visible field and change click on the checkmark under the Deny column

Method 2: SQL Server Trigger

Yes, folks! Thanks to Tim Foster you can now deny access to changing the visibility of the metrics section with a trigger as follows:

USE [DYNAMICS]
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Tim Foster
-- Create date: 2008-07-04
-- Description: Prevent users from adding Metrics content on the Home Page
-- =============================================
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[dbo.X_SY8100_Prevent_Metrics]'))
   DROP TRIGGER [dbo].[dbo.X_SY8100_Prevent_Metrics]
GO

CREATE TRIGGER [dbo.X_SY8100_Prevent_Metrics] ON dbo.SY08100 AFTER INSERT,UPDATE
AS
BEGIN

print 'X_SY8100_Prevent_Metrics trigger active'
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

IF USER_NAME()<>'dbo'
BEGIN
   DECLARE @SECTIONID INTEGER, @VISIBLE INTEGER

   SELECT @SECTIONID = SECTIONID, @VISIBLE = VISIBLE FROM INSERTED
   IF @SECTIONID = 3 AND UPDATE(VISIBLE)
   BEGIN
      UPDATE dbo.SY08100 SET dbo.SY08100.VISIBLE = 0
      FROM dbo.SY08100 INNER JOIN INSERTED ON dbo.SY08100.USERID = INSERTED.USERID
        AND dbo.SY08100.SECTIONID = INSERTED.SECTIONID 
        AND dbo.SY08100.DICTID = INSERTED.DICTID
      WHERE dbo.SY08100.USERID = INSERTED.USERID 
        AND dbo.SY08100.SECTIONID = INSERTED.SECTIONID 
        AND dbo.SY08100.DICTID = INSERTED.DICTID

      PRINT 'You are not allowed to turn that content on'
   END
END

PRINT 'X_SY8100_Prevent_Metrics trigger Complete'
END
GO

Maintaining metrics and other homepage elements safe is key to information security. Hope these two approaches ease the pain of dealing with metrics.

Until next post!

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

'Important' Fixes to Come in Microsoft's July Patch Cycle

It's this time of the year when Microsoft releases a set of patches for most of its server applications, and according to Redmond Magazine the patches have been deemed 'important'. The patches will address mostly exploit risks in products such as SQL Server (7.0 SP4, 2000 Itanium, and all versions of 2005 SP2), all versions of Windows (Vista, XP, Server 2003, and Server 2008), and Exchange Server. You can read the full article at Redmond Magazine.

Keep in mind that applying any of these patches may affect your Dynamics GP system and you need to consult with Microsoft before applying any of them.

Until next post!

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

GP_eEnterprise Yahoo! Group Moving to Google Groups

GP_eEnterprise... that tells you how long this group is been around, and for those of us who have seen it grown over the years, moving to our new home in Google Groups (http://groups.google.com/group/DynamicsGP) comes with a little bit of sadness and nostalgia, but yet with the excitement of leveraging some of the best tools available on the Internet to continue with our commitment of helping others.

The group will begin its activities on July 7, 2008 at 7:00 GMT. During the transition phase, all efforts were made to transfer all postings from the GP_eEnterprise group to DynamicsGP group as a way of maintaining the rich history accumulated over the years. Please visit our new home and register.

Until next post!

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

David Musgrave, "Father Dexterity" Himself!

Ok, not quite! But Microsoft Dexterity and the overall Dexterity development community owes a great deal of progress in the tool and development techniques to David Musgrave. His now famous "Pushing the Limits with Dexterity" series have been a staple to everyone for quite sometime now, but he is well known for fathering products such as Omni Tools and Omni Security.

David decided it wasn't enough and has ventured to join the increasing number of bloggers willing to share their knowledge with the rest of the community. You can now find David at his MSDN blog site. Good luck with this new venture.

Until next post!

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

Automating Buyer ID field entry with logged in User ID in the PO Entry window in Microsoft Dynamics GP

Here is another very common request: how can the Buyer ID field entry be automated with the user ID currently logged into Microsoft Dynamics GP? This is certainly an important control feature in environments where security is key to auditors and systems administrators alike.

A simple VBA script can take care of this issue, as follows:

1) Add the Purchase Order Entry screen and the Buyer ID field to Visual Basic
2) Open the Visual Basic Editor
3) Locate Microsoft_Dynamics_GP > Microsoft Dynamics GP Objects
4) Double-click on the PurchaseOrderEntry (Window) object
5) Add the following code in the editor

' Created by Mariano Gomez, MVP
' Code is provided "AS IS". No warranties express or implied
'
Private Sub BuyerID_BeforeGotFocus(CancelLogic As Boolean)
  Dim usrinfObj As UserInfo
  Dim usrID As String

  Set usrinfObj = VbaGlobal.UserInfoGet()
  usrID = usrinfObj.UserID

  BuyerID = usrID
End Sub

I know what you are thinking... this script could use some enhancements:

1) You can add additional code to check whether the buyer ID already exist in the database prior to assigning the usrinfObj.UserID content to the usrID variable

2) Of course, all the ramifications of the non-existance of the buyer ID that goes along with programming best practices and application security.

Until next post!

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

Microsoft Excel Spreadsheet as Source for Professional Services Tools Library's Account Modifier/Combiner

This Microsoft Dynamics GP nugget comes courtesy of Victoria Yudin, MVP and owner of Flexible Solutions. Account Modifier/Combiner, a tool part of the PSTL, allows to change previous account numbers into new account numbers or combine existing accounts. The tool can automatically identify which one of these activities you are trying to perform. However, if you have used the tool in the past, you are well familiar with the long waiting times until it finishes with one modification/combination. What if you need to combine or modify hundreds of accounts?

Account Modifier/Combiner allows an end-user to setup two columns in a spreadsheet representing the Account Number and the Convert To Account Number fields respectively. The detail steps to import the accounts to be combined or modified are outlined in the Professional Services Tools Library manual, but in summary, once the spreadsheet is created, you can choose to open and validate the accounts prior to any action being carried out by the tool.

Changes can also be previewed by running the following SQL statement in SQL Server Management Studio (SQL2005) or Query Analyzer (SQL2000):

SELECT * FROM TACHANGE

Once you are comfortable, click the Modify button on the same screen and go zip a grade mocha as this baby will take for a while.

Until next post!

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

Bug: Excel Budget Wizard Export/Import issues in Microsoft Dynamics GP 10

If you are an Excel Budget Wizard user, please check out Mark Polino's post on a bug found after exporting the budget to Excel and attempting to reimport the data back into GP. The bug is persistent through Service Pack 2, but a workaround is provided.

Until next post!

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

Mark Polino's Six Best Practices for Using Microsoft Dynamics GP to Improve Processes and Save Time in Closing the Books

Well, you have to love when darn good consultants also happen to be darn good writers! Please check Mark Polino's article - Six Best Practices for Using Microsoft Dynamics GP to Improve Processes and Save Time in Closing the Books - on MSDynamicsWorld.com. The article explores a series of fast closing principles applied to Microsoft Dynamics GP financial processes that will help controllers and accounting managers realize efficiencies and improve time during accounting book closings.

Until next post!

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