It was certainly a learning curve for me to learn such interesting and new concepts. There are lots more to learn, but I have certainly taken a big step on the right direction.

I was able to clear all 10 challenges and have also got selected for a sweepstake. What’s more that I can ask for?

Now it’s time to share my learning and the knowledge that I have acquired based on the challenges. Please do visit this link to download the solutions: Office Palooza – Novice Challenges – Vaidy’s Approach. There can be (are) much better solutions to all these challenges, but by sharing my solutions, I could probably start a healthy discussion on my approach to each challenge and the respective solution. To more about each challenge and refer my solution, visit the site: Office Palooza Challenges List.

It may seem to be repetitive, but it’s worth to be. I am Thankful to David for his article and leading me (probably more GP consultants out there) to know a contest like this and learn new things.

VAIDY

To all FRx Consultants available, kindly read the below query from one of my colleagues and please send in your inputs as comments to this post. Any help is highly appreciated:

This one is another interesting query from a User on GP Community Forum (original link: Restrict Duplicate Non Inventory Items on SOP Invoice). The query is as follows:

Has anyone ever come across a good way to restrict dupliate non inventory items from being entered on a SOP Invoice? For example if I enter 12345 as the non inventory item number on an invoice and post that I don’t ever want to be able to enter 12345 again.

While couple of replies were indicating Dex involvement, I was thinking otherwise. Though, I am an ardent believer in Dexterity, this one does not need a separate chunk as such. The immediate alternate is of course VBA.

This time I am going to use VBA & ADO to incorporate this restriction on SOP Entry form. How? Read the below steps:

1. Open GP and start entering an SOP Document, in order to activate the SOP Line Grid (Scrolling Window) and its fields.

2. Add SOP Entry form to VBA Editor and add the field “Item Number” to the VBA Editor.

3. Open VBA Editor and copy paste the following code on to the “SalesTransactionEntryDetail” code editor (Forgive the format and alignment of the code piece.):

—
Private Sub ItemNumber_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
Dim sItemNo As String, sQuery As String
Dim adoConn As New ADODB.Connection, _
adoComm As New ADODB.Command, _
adoRecSet As New ADODB.Recordset

sItemNo = ItemNumber.Value
Set adoConn = UserInfoGet.CreateADOConnection
adoConn.CursorLocation = adUseClient
adoConn.CommandTimeout = 1000
adoConn.DefaultDatabase = UserInfoGet.IntercompanyID

adoComm.ActiveConnection = adoConn
adoComm.CommandType = adCmdText

sQuery = “SELECT COUNT(*) AS RECCOUNT FROM SOP30300 WHERE ITEMNMBR = ‘” & sItemNo & “‘ AND NONINVEN = 1”
adoComm.CommandText = sQuery
Set adoRecSet = adoComm.Execute
adoRecSet.MoveFirst
If adoRecSet.EOF True Then
If adoRecSet(0) > 0 Then
MsgBox “This Non-Inventory Item Number has already been entered in an SOP Document.”, vbCritical, “Microsoft Dynamics GP”
KeepFocus = True
CancelLogic = True
adoConn.Close
Exit Sub
End If
End If

adoRecSet.Close

sQuery = “SELECT COUNT(*) AS RECCOUNT FROM SOP10200 WHERE ITEMNMBR = ‘” & sItemNo & “‘ AND NONINVEN = 1”
adoComm.CommandText = sQuery
Set adoRecSet = adoComm.Execute
adoRecSet.MoveFirst
If adoRecSet.EOF True Then
If adoRecSet(0) > 0 Then
MsgBox “This Non-Inventory Item Number has already been entered in an SOP Document.”, vbCritical, “Microsoft Dynamics GP”
KeepFocus = True
CancelLogic = True
adoConn.Close
Exit Sub
End If
End If

adoConn.Close
End Sub
—

4. Compile your VBA Code and check this on SOP Entry form.

Now, what’s the logic? The code checks whether the entered Non-IV Item Number has already been entered on any of the Open Invoices (SOP10200) or on any of the History Invoices (SOP30300). If so, prompt the user with relevant message and keep the focus on the Item Number field, as long as the user either deletes that line or change the Item Number from that of already been entered.

You can download the package from this link: VBA & ADO – Retrict Duplicate Non-IV Entry on SOP Documents.

Note:

1. This package will work only on Dynamics GP 10.0.

2. Importing this package will override any existing VBA customizations done on SOP Entry form. Make sure that you take a backup of any such customizations.

VAIDY

This article from Mariano, explains the necessity of GP 3-Tier architecture and how it improves the performance.

While not always this is required, especially for a relatively mid-size companies, this one is the best approach and the first one to look out, if you experience a sluggishness in your day-to-day GP transactional activities.

Must Read.

VAIDY

It’s my immense pleasure to inform you all that the Office Palooza Novice Challenges are conquered :-).

Check this link, Office Palooza – Novice Challenges Results, you will find me (VAIDY MOHAN) on the results table. The results are announced as on today. So there are many more entries to come and get validated.

More than the results and the happiness it gives, honestly, it’s the sense of satisfaction that I was able to learn something completely new and manage to clear the challenges. Of course, it was really difficult to solve each, but I must admit, I enjoyed it a lot. There so many things to learn and I am glad I learned some of the nicest concepts available.

I am planning to venture into the Experienced Challenges. It’s already late for couple of challenges, but as I said above, it’s not a bad idea to try, learn and solve all the challenges.

VAIDY

Note: David, thanks again for sending this link to our community. I would not have known about this wonderful contest and the concepts I learned.

Thanks David, for pointing us to this fun and informative Office/VBA Challenges, called Office Palooza.

And needless to say, I am participating on this event. But, as of now I am submitting the code only for the NOVICE Challenges. I will upload all my entries once the each challenge crosses it’s due date.

It’s all fun, informative, learning and well you would forget the time till you complete each challenge.

Take part and you will see by yourself.

VAIDY

One of the GP Consultants, explained the following and asked for a method to resolve it:

Issue: Employee Maintenance form contains SUTA State field, which is not available for other Region Installations (for instance, UK and Ireland Region). Since it is not available on the form, the users does not have any idea about this and don’t enter any value. By default, this will store a NULL string (EDIT [04/21/2009]: It’s not NULL String, it’s an EMPTY String denoted as “”. Read the comment from David for more details.). When we try to run Payroll Check, this field will be referred and an error message will be popped up. Now, the catch is: Even if we modify this form using Modifier and make this field visible, we still won’t be seeing this field on screen.

Reason: When the Employee Maintenance form is opened, either the Window PRE or Form PRE script (or BOTH) would be triggered. And from any one, a validation checking the Region would be conducted. Based on that, the respective fields will be either shown or hidden. Since this user’s GP installation is “UK and Ireland” and they don’t have SUTA State, this field would be hidden thru’ code. This will override our Modifier changes.

Solution: A typical and simple VBA / Continuum / Sanscript code would solve this.

1. Open the Employee Maintenance form and add this form to VBA Editor and open the VBA Editor.



2. From the left hand side tree view, select the “EmployeeMaintenance” from “Microsoft Dynamics GP”, if the form is of original GP. We have several products customizing this form, including HR and Project Accounting. Depending on that, we should select “EmployeeMaintenance” from under the respective product name.



3. Write the code shown below (click on the picture to see it on full zoom).



4. Save this VBA project and compile it to see any error message you get. Rectify, if any error found.

We are good to go now. Simple, but very efficient.

You can find a sample package of this customization from the link: VBA & Continuum – Making an Invisible Field Visible. This package will work only on Microsoft GP 10.0 and for Regions other than United States. Try this package on, for instance, United Kingdom & Ireland to see the results.

Do get back with your comments / feedback.

VAIDY

David had written an article on this and given a link, Red River Flooding, which contains some photos on ND Community fighting Red River Flood. And with that, there is a whole lot of comments (it’s nearly 1400 comments and still counting) on this flooding and the consequences.

While it is not possible to read all the comments, quite understandably, I did go through some comments (for nearly 300). And out of all, this one stands out:

Comment #47: (Excerpts) We will win this thing or die trying. – Don

What a level of commitment and attitude this guy has shown in his words! I am completely taken back by these words. While some of the comments were really cruel and harsh, some were really submissive to the situation, some were prayers, only some comments like this shown the real character of a Community which is relatively small but large at heart.

I salute this spirit, attitude and character of ND Community.

VAIDY

I did, today. Check the following message:

This warning message was displayed from a custom code. And the focus was on the picture (Exclamation) and not on the Button (the blue border on the OK Button indicates the default option on this message box). I am not quite sure how this could happen.

Does anyone have any idea about this?

VAIDY

Those who are interested and practicing in Project Management Principles, an article and the book that it talks about, will be certainly giving a lesson or two.

The article is: Hollywood Secrets Of Project Management Success – A Review Of A Sort. Written by Vjekoslav Babic on his blog. I read this article 4 days back and also had expressed my views on it. But took me sometime to write on my blog about this wonderful article.

Excellent one and it is a MUST READ.

VAIDY