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

Triggers in Dexterity; the most powerful and safest way of writing Cross Dictionary Customizations. Often, we get stuck in our Customization Development, not knowing how to intercept certain processes in GP (or any 3rd Party Product).

The Three Trigger Technique, by David Musgrave, explains the “Scope, Relevance & Preciseness” of placing a Trigger. Every Dexterity Developer knows how to write a trigger and on which object. But very less would know where to write it and how safely that can be placed for execution.

First article, Using the Dexterity Three Trigger Technique Part I, explains the theory with a real time example.

Second article, Using the Dexterity Three Trigger Technique Part II, explains the Three Trigger Technique with the support case that David addressed.

Per his bullet points at the end, concentrate on the following (instead of the actual scripts and the fix that he had provided):

– Three Trigger Technique
– Cross Dictionary Triggers
– Capturing and Using References

Must Read and for our Reference.

VAIDY

Another classic example of why we should read Newsgroup and Community posts, to learn more and extend the level of our Expertise.

I read this Dynamics GP Community post, SOP Transaction Issue, and there was a discussion on this issue. While this query is yet to be answered or provided a solution with, there are couple of things that we may learn.

1. David mentioned that the issue could be due to multiple users accessing the Next SOP Number. And the code might probably have dealt with Next SOP Number wrongly. I have been instructing this to anyone who asks me how to retrieve the Next Number (for any document that matters). As soon as you retrieve the Next Number from the Setup, you have to increment it by one and save the value in respective Setup Table. For instance, if I generate the SOP Number thru’ my code, I should first retrieve the Next SOP Number from SOP Setup table, then increment the SOP Number by one and finally update the incremented value on to SOP Setup table.

2. Ron Draganowski (of Olsen Theilen Technologies Ltd.) explained how SOP Entry form works when we enter a transaction. The issue of using GET TABLE statement on SOP_HDR_WORK table, was clearly explained. I shall brief you of what he explained: Once we start entering an SOP Document (precisely after tabbing out of SOP Number field), a dummy record is created with only SOP Type and SOP Number in SOP_HDR_WORK table and it is duly locked to store the rest of the values when the Save Record occurs. Now the point here is: Most of us would not know that while inside the SOP Entry process, using GET TABLE on SOP_HDR_WORK will silently release the locked record. And this will have a drastic effect in the later stage of the SOP document process.

Read this Community Post for more and complete details from David and Ron.

VAIDY