I like to think that I'm generally aware of new tools and technologies, but sometimes good technology slips by in areas where I'm not looking.
I've used WinZip for eons for situations where I have to e-mail files to people (yes, I even paid for a license!). And I have used WinRAR for years for larger files, like SQL Server backups and VHD backups. Between those two products, I thought I had the compression category pretty well covered.
Well, a few weeks ago I randomly stumbled across a reference to a free, open source compression software utility called 7-Zip. Apparently most of my friends and colleagues had heard of it, and one has been using it for quite a while.
Since I like WinZip's convenience and speed, and I'm a big fan of WinRAR's compression performance, I was skeptical that this free tool could compare with both WinZip and WinRAR, but I decided to give it a try.
I had a 1.67 gigabyte Outlook PST backup file, so I decided to start with that. Not only was 7zip faster, but it's native 7z compression was better than WinRAR.
7-Zip shrunk the file down to 443MB in 9 minutes 23 seconds, while WinRAR got down to 485MB in 10 minutes 45 seconds. Wow.
Impressed, I then tried a 29.2 gigabyte VHD file. WinRAR brought the file down to 10.4 gigabytes, while 7zip shrunk it to 8.9 gigabytes. Both took roughly two hours, so I didn't compare their actual times, but given the improved compression, I'd gladly use 7zip even if it took a few minutes longer.
I'm now a 7zip convert when it comes to compressing large files. I haven't yet tried unzipping my 7z files to ensure that they don't have any errors, but I'll gladly spend some time testing it if it will save me 10-15% space on my backup and archive disks.
The one obvious drawback to the 7z file format used by 7-Zip is that it isn't as well known or universally supported as the zip file format. However, I have noticed that a lot more people are now e-mailing RAR files out (and Microsoft is distributing VHDs in RAR format) without any problems or confusion, so maybe 7-Zip won't be such a leap for people. And since it is free, it's easy for anyone to download and use, and doesn't have any trialware nag windows to deal with.
The other observation I made is that using 7-Zip to create a zip file is slower and produces a zip file that is much larger than what WinZip creates (at least using whatever settings I used). So for now, it seems that WinZip still has a role.
Given the performance I've observed with the 7z format, I'm hoping it becomes widely adopted. Since it is free and open source, it would be nice to have on client's servers instead of the ubiquitous WinZip "Trial Mode - Purchase Now" message that I see constantly.
Give it a try and let me know what you think, or if you are already a 7-Zip user, let me know if you have found any other pros or cons.
Thursday, April 29, 2010
Wednesday, April 28, 2010
Displaying Estimated Total Order Weight on the SOP Transaction Entry Window
By Steve Endow
Because of the size and weight of the products, most orders required them to get a shipping estimate from a freight provider prior to finalizing the order. Dynamics GP doesn't display the total weight of an order during order entry, so the client asked me to create a custom field that would calculate the order weight and display it on the Sales Transaction Entry window.
Naturally, you need to have the Shipping Weight field populated for all of your inventory items in order for the order weight to be accurate. And of course this total order weight doesn't necessarily equate to the weight value that you will actually end up with when shipping, but it can be a decent estimate.
I wrote this several years ago, but since a similar request came up in the Dynamics GP newsgroup, I thought I would share the code.
First, you use Modifier to add a custom field to the Sales Transaction Entry window. (I'll assume that you know how to do that, or can find a tutorial on that elsewhere)
You'll then add some VBA to the sales window. I came up with the following code. Note that you may need to adjust this query to meet your specific needs.
Public Sub UpdateOrderWeight()
If Trim("" & SalesTransactionEntry.DocumentNo.Value) <> "" Then
Dim cn As New ADODB.connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Set cn = UserInfoGet.CreateADOConnection
cn.DefaultDatabase = UserInfoGet.IntercompanyID
cmd.ActiveConnection = cn
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "SELECT ISNULL(SUM(ROUND(l.QUANTITY * (i.ITEMSHWT / 100.00), 2)), 0.00) AS ORDERWEIGHT " & _
"FROM SOP10200 l JOIN IV00101 i ON i.ITEMNMBR = l.ITEMNMBR " & _
"WHERE l.SOPNUMBE = '" & Trim(DocumentNo.Value) & "'"
Set rst = cmd.Execute
'7/26/2013: S. Endow: There is a bug / quirk in GP VBA that sometimes causes a field value assignment
'to generate an "Error 1007 Unsafe Operation" error.
'http://support.microsoft.com/kb/856199
'Instead of setting focus, then assigning a value, or just assigning a value, you have to
'set the Focus and assign the value in the same step.
'TotalOrderWeight.Value = FormatNumber(rst.Fields("ORDERWEIGHT").Value, 2, vbTrue, vbFalse)
TotalOrderWeight.Focus (FormatNumber(rst.Fields("ORDERWEIGHT").Value, 2, vbTrue, vbFalse))
rst.Close
cn.Close
End If
End Sub
I then added a call to that sub from the Document Number field and the line grid so that the number is refreshed if a new document is opened, or if lines are added or removed from the grid.
Private Sub DocumentNo_Changed()
Call UpdateOrderWeight
End Sub
Private Sub Grid_AfterLineGotFocus()
Call SalesTransactionEntry.UpdateOrderWeight
End Sub
And this is the result:
Steve Endow is a Dynamics GP Certified Trainer and Dynamics
GP Certified IT Professional in Los Angeles. He is also the owner of
Precipio Services, which provides Dynamics GP integrations, customizations, and
automation solutions.
Think about the client or end user
Sometimes we become so accustomed to things in "GP Land" that we forget that we are living in our own alternate universe with its own principles and laws and mechanics. When we forget this, it causes us to forget that our clients and end users don't necessarily live in this Dynamics GP universe. This means that they often have different concerns than what we have become accustomed to.
I recently developed an integration that would export Purchase Orders from Dynamics GP and send them to 3rd party warehouses for receiving inventory.
The files needed to go to different companies in either CSV or XML format, via e-mail and via FTP. I felt that I was very thorough in my development, following the warehouse specifications closely and making sure to be flexible and thorough in my development. The integration worked great, and I felt pretty good about the solution.
The client went live with the export today, and not long after the first CSV file was automatically e-mailed across the country, I received a concerned e-mail from the client asking "What is the strange line number in the PO file?".
The XML file specifications had simply requested a "unique" line number for each PO line, so I just (absentmindedly) defaulted to the standard GP line sequence number which is multiples of 16,384. The CSV file format wasn't specific, so I used the line sequence for it as well, assuming it would be imported into another system.
Apparently there is a person in the 3rd party warehouse that opens the CSV file and reviews it manually, and the first question was "What's wrong with your line numbers???". The client then e-mailed me, concerned about this bizarre series of numbers where a "PO line number" should be located.
I guess that I have become so used to the magic 16384 line sequence number that I no longer even notice it, like the folks in The Matrix effortlessly reading the green blips raining down their monitors.
But for the folks in the warehouse, 114688 is meaningless. They care about line 1, line 2, line 3, etc.
It only took me a few minutes to modify the exports to use the LineNumber field in the PO10110 table, so it was an easy fix, but it reminded me that I sometimes forget that the strange rules and logic of GP Land don't always apply to the rest of the world.
For more exciting reading about the adored Dynamics GP Line Sequence Number, check out Mariano's great series of posts on the subject.
Microsoft Dynamics GP Scrolling Windows and Line Sequence Numbers
Is there a maximum number of lines that can be inserted in any given scrolling window?
How to find the line number of an item on a Microsoft Dynamics GP document - Part 1
How to find the line number of an item on a Microsoft Dynamics GP document - Part 2
I recently developed an integration that would export Purchase Orders from Dynamics GP and send them to 3rd party warehouses for receiving inventory.
The files needed to go to different companies in either CSV or XML format, via e-mail and via FTP. I felt that I was very thorough in my development, following the warehouse specifications closely and making sure to be flexible and thorough in my development. The integration worked great, and I felt pretty good about the solution.
The client went live with the export today, and not long after the first CSV file was automatically e-mailed across the country, I received a concerned e-mail from the client asking "What is the strange line number in the PO file?".
The XML file specifications had simply requested a "unique" line number for each PO line, so I just (absentmindedly) defaulted to the standard GP line sequence number which is multiples of 16,384. The CSV file format wasn't specific, so I used the line sequence for it as well, assuming it would be imported into another system.
Apparently there is a person in the 3rd party warehouse that opens the CSV file and reviews it manually, and the first question was "What's wrong with your line numbers???". The client then e-mailed me, concerned about this bizarre series of numbers where a "PO line number" should be located.
I guess that I have become so used to the magic 16384 line sequence number that I no longer even notice it, like the folks in The Matrix effortlessly reading the green blips raining down their monitors.
(Can't you see Neo and Trinity jumping out of the window? Or is that a spoon?)
But for the folks in the warehouse, 114688 is meaningless. They care about line 1, line 2, line 3, etc.
It only took me a few minutes to modify the exports to use the LineNumber field in the PO10110 table, so it was an easy fix, but it reminded me that I sometimes forget that the strange rules and logic of GP Land don't always apply to the rest of the world.
For more exciting reading about the adored Dynamics GP Line Sequence Number, check out Mariano's great series of posts on the subject.
Microsoft Dynamics GP Scrolling Windows and Line Sequence Numbers
Is there a maximum number of lines that can be inserted in any given scrolling window?
How to find the line number of an item on a Microsoft Dynamics GP document - Part 1
How to find the line number of an item on a Microsoft Dynamics GP document - Part 2
Using eConnect to import Inventory Adjustments with Multi-Bin
I recently fielded a question on the GP Developer newsgroup about an eConnect error that occurred when trying to import inventory adjustments with multi-bin.
The developer was able to import positive inventory adjustments, but negative adjustments would fail with this error:
The QTY entered has to be > 0
After poking around in the GP Item Transaction Entry window, I found the inconsistency that was causing the problem.
The Item Transaction Entry window is one of the few places in GP where you can enter a negative quantity. I admit that I truly enjoy being able to type a negative number in the Quantity field, since it's such a rare treat in GP.
The negative quantity is convenient and logical for decreasing inventory quantities; however, it is not necessarily consistent with the rest of GP, and when it comes to the Bin Quantity Entry window, GP suddenly reverts to the typical positive-only behavior.
In the Bin Quantity Entry window, you must enter a positive value in the Quantity Selected field, even though the transaction is a negative inventory adjustment. Yet there isn't any field or option on the Bin Quantity Entry to specify increase or decrease.
Once you insert the bin and quantity selection, note that the Extended Quantity is negative, but the Selected Quantity is positive.
This is a pretty poor, inconsistent design that can cause confusion in both the GP user interface, as well as the eConnect API.
If you look at the eConnect documentation for the taIVTransactionMultiBinInsert node, you'll see a node called ADJTYPE, which can be 0=Increase or 1=Decrease. Why this exists in eConnect but not in the GP user interface, well, I'll leave that for others to ponder.
So to summarize, when you import a negative inventory adjustment via eConnect using multi-bin, you have to first create a negative inventory adjustment, then you have to create a positive multi-bin record, and then you also have to specify that the multi-bin record is a Decrease adjustment. Obvious, right?
Why the Bin quantity entry window (and taIVTransactionMultiBinInsert) accepts only positive values for a negative inventory adjustment is yet another historical GP mystery that I'm sure has some highly rationalized explanation, but for now I'm going to chalk it up to them wanting to keep us on our toes.
The developer was able to import positive inventory adjustments, but negative adjustments would fail with this error:
The QTY entered has to be > 0
After poking around in the GP Item Transaction Entry window, I found the inconsistency that was causing the problem.
The Item Transaction Entry window is one of the few places in GP where you can enter a negative quantity. I admit that I truly enjoy being able to type a negative number in the Quantity field, since it's such a rare treat in GP.
The negative quantity is convenient and logical for decreasing inventory quantities; however, it is not necessarily consistent with the rest of GP, and when it comes to the Bin Quantity Entry window, GP suddenly reverts to the typical positive-only behavior.
In the Bin Quantity Entry window, you must enter a positive value in the Quantity Selected field, even though the transaction is a negative inventory adjustment. Yet there isn't any field or option on the Bin Quantity Entry to specify increase or decrease.
Once you insert the bin and quantity selection, note that the Extended Quantity is negative, but the Selected Quantity is positive.
This is a pretty poor, inconsistent design that can cause confusion in both the GP user interface, as well as the eConnect API.
If you look at the eConnect documentation for the taIVTransactionMultiBinInsert node, you'll see a node called ADJTYPE, which can be 0=Increase or 1=Decrease. Why this exists in eConnect but not in the GP user interface, well, I'll leave that for others to ponder.
So to summarize, when you import a negative inventory adjustment via eConnect using multi-bin, you have to first create a negative inventory adjustment, then you have to create a positive multi-bin record, and then you also have to specify that the multi-bin record is a Decrease adjustment. Obvious, right?
Why the Bin quantity entry window (and taIVTransactionMultiBinInsert) accepts only positive values for a negative inventory adjustment is yet another historical GP mystery that I'm sure has some highly rationalized explanation, but for now I'm going to chalk it up to them wanting to keep us on our toes.
Monday, April 19, 2010
Selecting Distinct Node Values from an eConnect XML Document
I'm still working on a long project that involves using eConnect to import millions of transactions into Dynamics GP. Yes, millions. Over 7 million transactions so far, and many more to go before I sleep.
In this case, it was easier for the client to provide the data in XML format, so I'm importing the transactions by sending the XML directly to eConnect. This has had some benefits, but also posed several challenges.
One challenge is that the client is even including the Batch Number in the XML data. Normally this wouldn't be an issue, but as part of another requirement, I need to know every batch number that was created by each XML file.
To do this, I would like to use XPath to query the XML file and provide me with a distinct list of batch numbers. There are other techniques, but at the moment I believe that XPath is the simplest approach.
This was a bit trickier and more obscure than I realized, and although I am a big fan of XPath, I'm a bit rusty with complex queries, so this was definitely outside of my comfort zone. The tricky part is that there are many different ways to get a distinct list of nodes or node values, and your XML structure and the specific distinct values you need will dictate which approach and syntax you use.
I happen to be working on the POP Receipt import at the moment, so I'll use it's XML as an example. I basically need distinct values for the following node:
eConnect/POPReceivingsType/taPopRcptHdrInsert/BACHNUMB
Or:
//taPopRcptHdrInsert/BACHNUMB
To figure out how to do this, I dug through several similar requests on Experts-Exchange.com and discovered an answer that led me to create the following XPath query:
//taPopRcptHdrInsert[not(BACHNUMB = preceding::taPopRcptHdrInsert/BACHNUMB)]/BACHNUMB
This compact little gem scans the entire XML file (which in my case has thousands of transactions) and returns a tidy node list of distinct batch numbers.
To use it, I just use a SelectNodes statement:
batchNodes = popXML.SelectNodes("//taPopRcptHdrInsert[not(BACHNUMB = preceding::taPopRcptHdrInsert/BACHNUMB)]/BACHNUMB")
Is that cool, or what? (the inner geek in you screams "YES!")
You could use a similar XPath query to find a unique list of customer IDs, vendor IDs, transaction numbers, item numbers, you name it.
Full credit goes to the geniuses that invented XPath, and the clever folks on Experts Exchange that showed me how to use it.
In this case, it was easier for the client to provide the data in XML format, so I'm importing the transactions by sending the XML directly to eConnect. This has had some benefits, but also posed several challenges.
One challenge is that the client is even including the Batch Number in the XML data. Normally this wouldn't be an issue, but as part of another requirement, I need to know every batch number that was created by each XML file.
To do this, I would like to use XPath to query the XML file and provide me with a distinct list of batch numbers. There are other techniques, but at the moment I believe that XPath is the simplest approach.
This was a bit trickier and more obscure than I realized, and although I am a big fan of XPath, I'm a bit rusty with complex queries, so this was definitely outside of my comfort zone. The tricky part is that there are many different ways to get a distinct list of nodes or node values, and your XML structure and the specific distinct values you need will dictate which approach and syntax you use.
I happen to be working on the POP Receipt import at the moment, so I'll use it's XML as an example. I basically need distinct values for the following node:
eConnect/POPReceivingsType/taPopRcptHdrInsert/BACHNUMB
Or:
//taPopRcptHdrInsert/BACHNUMB
To figure out how to do this, I dug through several similar requests on Experts-Exchange.com and discovered an answer that led me to create the following XPath query:
//taPopRcptHdrInsert[no
This compact little gem scans the entire XML file (which in my case has thousands of transactions) and returns a tidy node list of distinct batch numbers.
To use it, I just use a SelectNodes statement:
batchNodes = popXML.SelectNodes("//taPopRcptHdrInsert[not(BACHNUMB = preceding::taPopRcptHdrInsert/BACHNUMB)]/BACHNUMB")
Is that cool, or what? (the inner geek in you screams "YES!")
You could use a similar XPath query to find a unique list of customer IDs, vendor IDs, transaction numbers, item numbers, you name it.
Full credit goes to the geniuses that invented XPath, and the clever folks on Experts Exchange that showed me how to use it.
Wednesday, April 14, 2010
VS 2008 .NET 3.5 Integration Error: Application has failed to start because the application configuration is incorrect
I just deployed a new .NET integration for Dynamics GP, and when I tried to launch the application on the client's server, I received this message:
"This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem."
These errors appeared in the Event log:
"Syntax error in manifest or policy file"
"The application failed to launch because of an invalid manifest."
Since I have successfully deployed other .NET integrations on this server, I realized that this one was a little different. My prior integrations were developed with Visual Studio 2005 and .NET 2.0. Because this new integration utilizes the .NET LINQ extensions, and I used Visual Studio 2008 and .NET 3.5.
Based on a search of the error, it seems that it can be caused by several completely different reasons. Sometimes it is due to missing C++ service packs, sometimes it is due to an issue with the XML configuration file.
In my case, the server is a Windows Server 2003 machine, and it does have .NET 3.5 SP1 installed, so it didn't seem that the server resources were the issue. After further digging, I came across a blog post that discussed the error.
It turns out that the error is caused by the Manifest setting in Visual Studio 2008. When it embeds the manifest in the application, this appears to sometimes cause issues on older (non-UAC) operating systems.
Changing the manifest setting to "Create application without a manifest" resolved the issue for me.
MSDN provides the following information about the Manifest setting. When they say "earlier applications", I'm assuming that also means "earlier operating systems".
http://msdn.microsoft.com/en-us/library/ms247046(VS.90).aspx
Manifest
Selects a manifest generation option when the application runs on Windows Vista under User Account Control (UAC). This option can have the following values:
Embed manifest with default settings. Supports the typical manner in which Visual Studio operates on Windows Vista, which is to embed security information in the application's executable file, specifying that requested ExecutionLevel be AsInvoker. This is the default option.
Create application without a manifest. This method is known as virtualization.Use this option for compatibility with earlier applications.
"This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem."
These errors appeared in the Event log:
"Syntax error in manifest or policy file"
"The application failed to launch because of an invalid manifest."
Since I have successfully deployed other .NET integrations on this server, I realized that this one was a little different. My prior integrations were developed with Visual Studio 2005 and .NET 2.0. Because this new integration utilizes the .NET LINQ extensions, and I used Visual Studio 2008 and .NET 3.5.
Based on a search of the error, it seems that it can be caused by several completely different reasons. Sometimes it is due to missing C++ service packs, sometimes it is due to an issue with the XML configuration file.
In my case, the server is a Windows Server 2003 machine, and it does have .NET 3.5 SP1 installed, so it didn't seem that the server resources were the issue. After further digging, I came across a blog post that discussed the error.
It turns out that the error is caused by the Manifest setting in Visual Studio 2008. When it embeds the manifest in the application, this appears to sometimes cause issues on older (non-UAC) operating systems.
Changing the manifest setting to "Create application without a manifest" resolved the issue for me.
MSDN provides the following information about the Manifest setting. When they say "earlier applications", I'm assuming that also means "earlier operating systems".
http://msdn.microsoft.com/en-us/library/ms247046(VS.90).aspx
Manifest
Selects a manifest generation option when the application runs on Windows Vista under User Account Control (UAC). This option can have the following values:
Embed manifest with default settings. Supports the typical manner in which Visual Studio operates on Windows Vista, which is to embed security information in the application's executable file, specifying that requested ExecutionLevel be AsInvoker. This is the default option.
Create application without a manifest. This method is known as virtualization.Use this option for compatibility with earlier applications.
Friday, April 9, 2010
Invalid object name 'DYNAMICS..taErrorCode'
Full moon? Sun spots?
Tonight I was installing a new eConnect integration on a client's development server.
When I ran the integration, I received the following SQL Exception error:
Invalid object name 'DYNAMICS..taErrorCode'
I've developed and deployed countless eConnect integrations, and have never seen this before. A search for this message via Google and PartnerSource produced no meaningful results.
When I checked the Dynamics database, sure enough, there was no taErrorCode table. eConnect 10 was installed and configured on the server, and the Release Info tool even reported that it was installed on all databases, but for some reason the taErrorCode table did not exist.
I didn't install GP on the server, and I can't explain how or why the table would be missing, or what else may be missing. I also don't know of a "proper" way to recreate and populate the table.
I ended up scripting the table on my development server and manually recreating it on the client's server.
For the over 7,000 records in the table, I then tried to use the SQL Import Export Wizard to export the data from my machine to a file that I could then import on the client's machine. But after numerous cryptic errors that prevented the file from importing, I had to give up on that option.
I eventually created a new database on my server, copied the table and its contents to that empty database, backed up the database, restored it on the client's server, and then used the Import Wizard to transfer the data from that database to DYNAMICS.
That seemed to resolve the issue, and now the integration seems to work.
Bizarre.
Tonight I was installing a new eConnect integration on a client's development server.
When I ran the integration, I received the following SQL Exception error:
Invalid object name 'DYNAMICS..taErrorCode'
I've developed and deployed countless eConnect integrations, and have never seen this before. A search for this message via Google and PartnerSource produced no meaningful results.
When I checked the Dynamics database, sure enough, there was no taErrorCode table. eConnect 10 was installed and configured on the server, and the Release Info tool even reported that it was installed on all databases, but for some reason the taErrorCode table did not exist.
I didn't install GP on the server, and I can't explain how or why the table would be missing, or what else may be missing. I also don't know of a "proper" way to recreate and populate the table.
I ended up scripting the table on my development server and manually recreating it on the client's server.
For the over 7,000 records in the table, I then tried to use the SQL Import Export Wizard to export the data from my machine to a file that I could then import on the client's machine. But after numerous cryptic errors that prevented the file from importing, I had to give up on that option.
I eventually created a new database on my server, copied the table and its contents to that empty database, backed up the database, restored it on the client's server, and then used the Import Wizard to transfer the data from that database to DYNAMICS.
That seemed to resolve the issue, and now the integration seems to work.
Bizarre.
Wednesday, April 7, 2010
Making the Dynamics GP User ID field blank on Terminal Servers
UPDATE: It has been several years since I had to deal with this, so it seems that at least one of my proposed solutions are outdated. David Musgrave has weighed in with his feedback in the reader comments.
This is an oldie, but a goodie.
Dynamics GP was designed with certain assumptions in mind. For instance, as a client application, GP assumes that a user would like to save certain settings and preferences so that they are retained for the user the next time they launch GP.
This typically works great on a single client PC, but when you install Dynamics GP on a Terminal Server, sometimes this can be annoying.
One of these saved settings is the user ID of the last user that logged into GP. On a Terminal Server with numerous users logging in and out of GP throughout the day, the last User ID is generally a random user ID. When a user logs in, they have to substitute their user ID.
Some GP customers don't even notice this "feature", but other customers consider this behavior very annoying or a security issue, and demand that it be changed.
Fortunately, there is a solution. Two actually.
Option 1 is to use a VBA script to blank out the User ID field when the GP login window is displayed. This solution is discussed in KB Article 859129, "Blank User ID in Great Plains". It is simple to implement, and in my experience, works well.
UPDATED: Ignore this second suggestion! See reader comments for more information.
Another solution that I learned somewhere is to make the Dex.ini file read only. Since the Dex.ini contains the SQLLastUser setting that GP uses to populate the User ID field on startup, if you make this setting blank, and then make the Dex.ini file read only, GP will always display a blank User ID value.
Although this technically works, there are some possible drawbacks to this approach. As I mentioned, GP saves several settings and preferences to make users' lives easier, so by making the Dex.ini file read only, you are not only disabling the last user ID feature, you are preventing GP from saving dozens of other preferences, some of which may cause new annoyances for users, or even problems for GP.
I was unable to find the Dex.ini solution in the Knowledge Base (it may be in there, but my searches didn't locate it), so because of the potential side effects, I suspect that it may not be a supported solution.
But just thought I would share both since I've seen both of them implemented.
This is an oldie, but a goodie.
Dynamics GP was designed with certain assumptions in mind. For instance, as a client application, GP assumes that a user would like to save certain settings and preferences so that they are retained for the user the next time they launch GP.
This typically works great on a single client PC, but when you install Dynamics GP on a Terminal Server, sometimes this can be annoying.
One of these saved settings is the user ID of the last user that logged into GP. On a Terminal Server with numerous users logging in and out of GP throughout the day, the last User ID is generally a random user ID. When a user logs in, they have to substitute their user ID.
Some GP customers don't even notice this "feature", but other customers consider this behavior very annoying or a security issue, and demand that it be changed.
Fortunately, there is a solution. Two actually.
Option 1 is to use a VBA script to blank out the User ID field when the GP login window is displayed. This solution is discussed in KB Article 859129, "Blank User ID in Great Plains". It is simple to implement, and in my experience, works well.
UPDATED: Ignore this second suggestion! See reader comments for more information.
Another solution that I learned somewhere is to make the Dex.ini file read only. Since the Dex.ini contains the SQLLastUser setting that GP uses to populate the User ID field on startup, if you make this setting blank, and then make the Dex.ini file read only, GP will always display a blank User ID value.
Although this technically works, there are some possible drawbacks to this approach. As I mentioned, GP saves several settings and preferences to make users' lives easier, so by making the Dex.ini file read only, you are not only disabling the last user ID feature, you are preventing GP from saving dozens of other preferences, some of which may cause new annoyances for users, or even problems for GP.
I was unable to find the Dex.ini solution in the Knowledge Base (it may be in there, but my searches didn't locate it), so because of the potential side effects, I suspect that it may not be a supported solution.
But just thought I would share both since I've seen both of them implemented.
Thursday, April 1, 2010
Dynamics GP Activity Tracking
My blog has moved! Please visit the new blog at: https://blog.steveendow.com/
I will no longer be posting to Dynamics GP Land, and all new posts will be at https://blog.steveendow.com
Thanks!
By Steve Endow
Several times I've received an inquiry along the lines of:
"Can you tell me how to view the Dynamics GP security and access logs? We need to know who logged on and when they logged on."
When clients ask this question, it is typically because they want to review the historical logs to research an issue. Unfortunately, by default, GP does not log security events or user activity, so the answer is usually that such data does not exist.
However, for those clients (and diligent consultants) who are aware of the possible need for such logs, now is the time to consider the Dynamics GP Activity Tracking feature to determine if it should be enabled.
Dynamics GP Activity Tracking has three windows:
1) Activity Tracking Setup
2) Activity Tracking Inquiry
3) Remove Activity Tracking Detail
Before I discuss the details, let's discuss what Dynamics GP Activity Tracking can, and cannot, provide.
Activity Tracking can provide basic logging of basic Dynamics GP security events. A user logged in successfully, or unsuccessfully. A user opened a window. A user logged off.
It can also track when a window was successfully opened, or when a user failed to open a window. And also when a user creates, modifies, or deletes a master record or transaction, or even when a user prints a report. And finally, the tracking can tell you when a user posted a specific type of batch.
On the surface, this sounds pretty nice, and sounds fairly comprehensive. However, before you go and turn on all sorts of Activity Tracking, you will want to ask yourself a few questions.
1) Why do you want to turn on activity tracking? Do you have a specific reason or scenario that the tracking will help address?
2) If you enable tracking, does the data collected specifically answer the questions that you will have?
3) How much effort are you willing to dedicate to managing and analyzing the tracking information?
I mention these because of a client request that I received several years ago. "Steve, we want to know when Sally came into work, how long she worked, what she did, and whether she was goofing off at work."
Dynamics GP will NOT answer any of these questions. If Sally came into work at 8am but did not login to GP until 10am, the login event won't necessarily be meaningful. If she entered some transactions but then had phone calls and meetings and paperwork outside of GP for several hours, the lack of activity in GP isn't meaningful either. And finally, even if you could see a log of everything she did in GP during the day, are you really going to sift through hundreds or thousands of records to understand what work or how much work she did during the day?
My recommendation is that you should use the GP Activity Tracking feature as a supplementary security audit log, or potentially as a diagnostic tool. The Tracking feature is not a replacement for a supervisor or manager that manages and monitors employee activity throughout the day, and in my experience, it is not very useful for HR related issues.
So with that long winded aside complete, let's look at the Dynamics GP Activity Tracking windows.
The Activity Tracking Setup window (Tools -> Setup -> System -> Activity Tracking) has four components.
It allows you to configure the types of activity that you would like logged, the users you would like to track, and the company databases where this tracking should be enabled.
The Activity Type drop down box provides a list of 5 different categories of activities that can be tracked. Once you select an activity type, the Activity list will be updated with specific Dynamics GP events that can be tracked.
After you choose the specific activities, you will select a user that you wish to track. Once the user is selected, the Company list will be updated with companies that the user can access. You can then select the specific companies where you want that user's activity tracked.
In the screen shot above, I chose to track all login events for the sa user in the Fabrikam company.
And here I am tracking when the sa user creates, updates, or deletes master files and transactions.
Now that we have enabled some tracking, let's take a look at the data provided by the events. The Activity Tracking Inquiry window (Inquiry -> System -> Activity Tracking) allows you to review all of the activity records, and provides basic filtering and sorting.
You can filter by company and user, as well as the activity type and specific activity.
If the inquiry window doesn't meet your analysis needs, you can access the activity data directly by querying or exporting the SY05000 table in the Dynamics database. You can then filter by date and time, and even search for specific data in the event description.
Last, but by no means the least, is the Remove Activity Tracking Detail. Once you enable tracking, you will likely notice that the number of records in the SY05000 table can grow very quickly. If you are tracking alot of events for all of your users (which I generally don't recommend), you can have tens of thousands of records build up.
To help manage this data, the Remove Activity Tracking Detail lets you selectively delete the activity records. You can remove specific types of activity records, for specific companies, users, and date ranges.
Given all of this functionality, here are my recommendations for using Activity Tracking.
1) Before enabling tracking, write up a few paragraphs about what events you think you would like to track, and why you want to track those events. This will help you determine how you will configure and use the tracking feature.
2) Enable as few events as necessary. Many people are inclined to track everything, but that produces alot of useless records, making it difficult to find meaningful events in the data and causing the logging table to grow unnecessarily. For example, instead of tracking inserts, updates, and deletes, you can use a SQL log viewer to review transaction logs if necessary. If you are already using Full Recovery Model for your GP databases and performing transaction log backups, rely on those for detailed data tracking instead.
3) After enabling tracking, try testing the data to confirm that it meets your requirements. Are you able to see the events that you needed? Does the data provide you with the detail required to answer the questions you had in mind when you wrote your requirements?
4) Don't be surprised if the tracking data does not answer some questions if an issue arises. Even though you enable tracking, there may be events that occur that raise questions that the logging data simply can't answer. As I mentioned previously, these are often HR related questions, and not necessarily security questions.
Enjoy!
I will no longer be posting to Dynamics GP Land, and all new posts will be at https://blog.steveendow.com
Thanks!
By Steve Endow
Several times I've received an inquiry along the lines of:
"Can you tell me how to view the Dynamics GP security and access logs? We need to know who logged on and when they logged on."
When clients ask this question, it is typically because they want to review the historical logs to research an issue. Unfortunately, by default, GP does not log security events or user activity, so the answer is usually that such data does not exist.
However, for those clients (and diligent consultants) who are aware of the possible need for such logs, now is the time to consider the Dynamics GP Activity Tracking feature to determine if it should be enabled.
Dynamics GP Activity Tracking has three windows:
1) Activity Tracking Setup
2) Activity Tracking Inquiry
3) Remove Activity Tracking Detail
Before I discuss the details, let's discuss what Dynamics GP Activity Tracking can, and cannot, provide.
Activity Tracking can provide basic logging of basic Dynamics GP security events. A user logged in successfully, or unsuccessfully. A user opened a window. A user logged off.
It can also track when a window was successfully opened, or when a user failed to open a window. And also when a user creates, modifies, or deletes a master record or transaction, or even when a user prints a report. And finally, the tracking can tell you when a user posted a specific type of batch.
On the surface, this sounds pretty nice, and sounds fairly comprehensive. However, before you go and turn on all sorts of Activity Tracking, you will want to ask yourself a few questions.
1) Why do you want to turn on activity tracking? Do you have a specific reason or scenario that the tracking will help address?
2) If you enable tracking, does the data collected specifically answer the questions that you will have?
3) How much effort are you willing to dedicate to managing and analyzing the tracking information?
I mention these because of a client request that I received several years ago. "Steve, we want to know when Sally came into work, how long she worked, what she did, and whether she was goofing off at work."
Dynamics GP will NOT answer any of these questions. If Sally came into work at 8am but did not login to GP until 10am, the login event won't necessarily be meaningful. If she entered some transactions but then had phone calls and meetings and paperwork outside of GP for several hours, the lack of activity in GP isn't meaningful either. And finally, even if you could see a log of everything she did in GP during the day, are you really going to sift through hundreds or thousands of records to understand what work or how much work she did during the day?
My recommendation is that you should use the GP Activity Tracking feature as a supplementary security audit log, or potentially as a diagnostic tool. The Tracking feature is not a replacement for a supervisor or manager that manages and monitors employee activity throughout the day, and in my experience, it is not very useful for HR related issues.
So with that long winded aside complete, let's look at the Dynamics GP Activity Tracking windows.
The Activity Tracking Setup window (Tools -> Setup -> System -> Activity Tracking) has four components.
It allows you to configure the types of activity that you would like logged, the users you would like to track, and the company databases where this tracking should be enabled.
The Activity Type drop down box provides a list of 5 different categories of activities that can be tracked. Once you select an activity type, the Activity list will be updated with specific Dynamics GP events that can be tracked.
After you choose the specific activities, you will select a user that you wish to track. Once the user is selected, the Company list will be updated with companies that the user can access. You can then select the specific companies where you want that user's activity tracked.
In the screen shot above, I chose to track all login events for the sa user in the Fabrikam company.
And here I am tracking when the sa user creates, updates, or deletes master files and transactions.
Now that we have enabled some tracking, let's take a look at the data provided by the events. The Activity Tracking Inquiry window (Inquiry -> System -> Activity Tracking) allows you to review all of the activity records, and provides basic filtering and sorting.
You can filter by company and user, as well as the activity type and specific activity.
If the inquiry window doesn't meet your analysis needs, you can access the activity data directly by querying or exporting the SY05000 table in the Dynamics database. You can then filter by date and time, and even search for specific data in the event description.
Last, but by no means the least, is the Remove Activity Tracking Detail. Once you enable tracking, you will likely notice that the number of records in the SY05000 table can grow very quickly. If you are tracking alot of events for all of your users (which I generally don't recommend), you can have tens of thousands of records build up.
To help manage this data, the Remove Activity Tracking Detail lets you selectively delete the activity records. You can remove specific types of activity records, for specific companies, users, and date ranges.
Given all of this functionality, here are my recommendations for using Activity Tracking.
1) Before enabling tracking, write up a few paragraphs about what events you think you would like to track, and why you want to track those events. This will help you determine how you will configure and use the tracking feature.
2) Enable as few events as necessary. Many people are inclined to track everything, but that produces alot of useless records, making it difficult to find meaningful events in the data and causing the logging table to grow unnecessarily. For example, instead of tracking inserts, updates, and deletes, you can use a SQL log viewer to review transaction logs if necessary. If you are already using Full Recovery Model for your GP databases and performing transaction log backups, rely on those for detailed data tracking instead.
3) After enabling tracking, try testing the data to confirm that it meets your requirements. Are you able to see the events that you needed? Does the data provide you with the detail required to answer the questions you had in mind when you wrote your requirements?
4) Don't be surprised if the tracking data does not answer some questions if an issue arises. Even though you enable tracking, there may be events that occur that raise questions that the logging data simply can't answer. As I mentioned previously, these are often HR related questions, and not necessarily security questions.
Enjoy!
Steve Endow is a Dynamics GP Certified Trainer and Dynamics
GP Certified IT Professional in Los Angeles. He is also the owner of
Precipio Services, which provides Dynamics GP integrations, customizations, and
automation solutions.