Solved

Create a note field for the Item Price List  Maintenance

Posted on 2011-02-23
21
681 Views
Last Modified: 2012-05-11
I am using GP 2010 and would like to create a note field for the Window Item Price List Maintenance. how can I accomplish that.?
Thanks
0
Comment
Question by:taverny
  • 13
  • 8
21 Comments
 
LVL 18

Assisted Solution

by:Steve Endow
Steve Endow earned 500 total points
ID: 34967197
Hi,

Just to confirm, you want to be able to enter a note that is associated with a specific item number on the Item Price List Maintenance window?

If so, I believe that will require a little bit of work.  Because the IV00108 (Price List) table does not have a Note Index field, you will need to store the note index in a custom table.

Here is what I am thinking:

1) Create a new note button on the window using Modifier

2) Create a new custom note window in VBA (with save and cancel button)

3) Add VBA to open the window when the user clicks the button

4) Create a new custom database table to store the custom item and note index association.  For instance:

Table:  IV00108_CustomNote

Fields:
      ITEMNMBR
      NOTEINDX
      DEX_ROW_ID
      
5) Add VBA to the Save button to get the next note index using the following SQL calls:

      a) Get the company ID from SY01500 (CMPANYID field)
      b) Get your SQL session ID:  SELECT @@SPID
      b) Call the DYNAMICS..smGetNextNoteIndex procedure.  Here is how it is called manually.  It's trickier to do this in VBA, as I think you will need to use SQL parameter objects.
      
            DECLARE @I_sCompanyID smallint = -1  (use your company ID)
            DECLARE @I_iSQLSessionID int = 60  (use your session ID)
            DECLARE @O_mNoteIndex numeric(19,5) = 0.00
            DECLARE @O_iErrorState int = 0
            
            EXEC DYNAMICS..smGetNextNoteIndex @I_sCompanyID, @I_iSQLSessionID, @O_mNoteIndex OUTPUT, @O_iErrorState OUTPUT
            
            SELECT @O_mNoteIndex, @O_iErrorState

6) Create VBA to save the note text to the SY03900 table.

      INSERT INTO TWO.dbo.SY03900 (NOTEINDX, DATE1, TIME1, TXTFIELD) VALUES (1234.00000, '2011.02.23', '20:33:25', 'Note text')

7) Save the Item Number and Note Index to your custom table.

      INSERT INTO IV00108_CustomNote (ITEMNMBR, NOTEINDX) VALUES ('100XLG', 1234.00000)

8) Add VBA code to your window Open event that checks to see if a note exists for the item, and if so, retrieves and displays the note text.  You can write your own code, or use the GP stored procedure:

      EXEC TWO.dbo.zDP_SY03900SS_1 1234.00000


Let me know if that is in the ball park.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional
0
 

Author Comment

by:taverny
ID: 34971513
Thanks for your response
I think this is exactly what I want to do . I will try to follow your explanation.
I am on step 2 , when creating the custom note window will it  look the same as the dynamics note , or is it just a form that I create in VBA and put a TEXT BOX in it?
0
 
LVL 18

Expert Comment

by:Steve Endow
ID: 34972757
Hi,

Your note window will be a custom VBA window that you create--it can look however you want.  You can try and get it to look similar to a GP note window, but it will likely look noticeably different.

One thing to check--in the past I have run into field length limitations with a standard VB6 text box, so  started to use the Rich Text Box control instead.  When you create your custom window, check to see if you will run into a length limitation on the text box.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional
0
 

Author Comment

by:taverny
ID: 34972825
Thank you Steve for the clarification, I am gonna start working on it now , and come back with some feedback.
My first question is regarding the Rich Text Box , I do I put it in my window it doesn't show in my toolbox in VBA

Thanks,
David
0
 
LVL 18

Expert Comment

by:Steve Endow
ID: 34975999
Hi,

Unfortunately it looks like the Text Box is your only option in VBA.

From what I can tell, the limit is roughly 2048 characters.  If that is enough for your users, you could write code to ensure that they don't exceed that limit.  If you need more, you could use two of the text box controls.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional
0
 

Author Comment

by:taverny
ID: 34982415
Well I am working on it.

I created the form and 2 buttons and a field to display/write the note.
This is what I currently have in my code, which is very basic.


-----------------------------------------------------------

Option Explicit
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim strsql As String

Private Sub ButSave_Click()

    'Retrieve an ADO Connection for the current user
    Set cn = UserInfoGet.CreateADOConnection
    'Set the current Database company
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn

End Sub

------------------------------------------------------

Now I am very not familiar with Stored procedure. So I tried to copy and paste your code in my code but it become red so I know something is wrong.
Can you tell me how and where to put those lines , thanks
0
 
LVL 18

Expert Comment

by:Steve Endow
ID: 34984893
Hi,

You're in luck--I happened to find an old VBA script that calls the get next node procedure.

It did several other things, so I have tried to carve out the code that gets the next index, and there may be a few syntax issues you may need to adjust.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional

Dim cn As New ADODB.connection
Dim cmd As New ADODB.Command
Dim strSQL As String

Dim intReturn As Integer

Set cn = UserInfoGet.CreateADOConnection
cn.DefaultDatabase = UserInfoGet.IntercompanyID
cmd.ActiveConnection = cn

'Get next note id and then insert batch record
strSQL = "DECLARE @I_sCompanyID smallint, @O_mNoteIndex numeric(19,5), @O_iErrorState int; " & _
         "select @I_sCompanyID = CMPANYID from DYNAMICS..SY01500 where INTERID = DB_Name() " & _
         "exec DYNAMICS.[dbo].[smGetNextNoteIndex] @I_sCompanyID, 1, @O_mNoteIndex output, @O_iErrorState output; SELECT @O_mNoteIndex"

cmd.CommandText = strSQL
cmd.Execute intReturn

Open in new window

0
 

Author Comment

by:taverny
ID: 34985654
thanks I am gonna try it
0
 

Author Comment

by:taverny
ID: 35009855
sorry for my long delay in responding to this one. well, I am back again on it and I am trying to understand your code and I sorry for my ignorance but can't figure it out some of the language/code:
and how do I use it.

those it retrieve the number or does it create a new number?
also the intreturn what is it for?

Thanks
David
0
 

Author Comment

by:taverny
ID: 35011286
I moving slowly and I found the Stored procedure in SQL and run it from there , and if I understood right this procedure looks for the next available noteindex.
now the parameter that needs to be pass is the companyid , which I think your code is passing it. but how do I get the notindex from this SQL statment?
when I look at the code in VBA the intreturn returns a 0 or a 1 , and I don't know how I can use that.
I guess I need your help for the next step.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 18

Expert Comment

by:Steve Endow
ID: 35013701
Hi,

The VBA should call the stored procedure and store the value of @O_mNoteIndex into intReturn (you can name that variable whatever you want).

That value will be your next note index.

Does that make sense?

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional
0
 

Author Comment

by:taverny
ID: 35021187
it does make sense , but for some reason when I run this code in my VBA the int return 1.
maybe I am missing something.
the INTERID is the database company that I am currently using , in my case it is 3 , but your code seems to pull the right one.

what is the 1 , in "@I_sCompanyID, 1" do i need to change the one to something?
0
 
LVL 18

Expert Comment

by:Steve Endow
ID: 35023445
Hi,

The "1" after the company ID parameter is the SQL session ID, which should theoretically be your PID, but I think can be just about any positive integer.

If you want to do it properly, you could use @@SPID, as mentioned in my first post.

I'll try the VBA code and see if I can get it to work or if it needs some tweaks.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional
0
 

Author Comment

by:taverny
ID: 35023626
Thank you
0
 

Author Comment

by:taverny
ID: 35059241
Hi Steve,
have you got a chance to try the code?
thanks
David
0
 
LVL 18

Accepted Solution

by:
Steve Endow earned 500 total points
ID: 35144829
Hi,

Well, it wasn't quite as simple as I thought.  My old code was doing something more involved than just grabbing the next note index, so I had to rewrite the routine using parameters to get the note index.

This code now works for me on GP 2010.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional

Dim cn As New ADODB.connection
    Dim cmd As New ADODB.Command
    Dim strSQL As String
    Dim rs As New ADODB.Recordset
    Dim companyID As Integer
    
    Dim intReturn As Integer
    
    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
    
    strSQL = "SELECT CMPANYID from DYNAMICS..SY01500 where INTERID = DB_Name()"
    cmd.CommandText = strSQL
    Set rs = cmd.Execute
    companyID = rs.Fields("CMPANYID").Value
    
    cmd.Parameters.Append cmd.CreateParameter("I_sCompanyID", adSmallInt, adParamInput, , companyID)
    cmd.Parameters.Append cmd.CreateParameter("I_iSQLSessionID", adInteger, adParamInput, , 1)
    cmd.Parameters.Append cmd.CreateParameter("O_mNoteIndex", adNumeric, adParamInputOutput, , 0)
    cmd.Parameters("O_mNoteIndex").Precision = 19
    cmd.Parameters("O_mNoteIndex").NumericScale = 5
    cmd.Parameters.Append cmd.CreateParameter("O_iErrorState", adInteger, adParamInputOutput, , 0)
    
    strSQL = "DYNAMICS.dbo.smGetNextNoteIndex"
    
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = strSQL
    cmd.Execute
    
    MsgBox "Note index: " & cmd.Parameters("O_mNoteIndex").Value

Open in new window

0
 

Author Comment

by:taverny
ID: 35149338
Thank you so much it does work now. I would have never been able to figure this one.
If I understand correctly this last code is to get the next available note index available from the table Sy01500 in the Dynamics database?

what I have so far , is a note field and 2 buttons , one that say save and another one cancel. I have no code besides the save button.
If I click once on the save button , it does work and give me the note index number , but if I click a second time It doesn't work . I need to close my form and reopen it in order to work.
Is that normal ?
attached is the error message I get when clicking the second time.


Capture.JPG
0
 
LVL 18

Expert Comment

by:Steve Endow
ID: 35149893
Hi,

That is a strange error.  I'm able to click on my button over and over and it generates the next number without issue.

Attached is a slight update to the code that explicitly closes the recordset and database connection, in case that is an issue on your machine for some reason.

Give the new version a try and let me know if that eliminates the error.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional

Dim cn As New ADODB.connection
    Dim cmd As New ADODB.Command
    Dim strSQL As String
    Dim rs As New ADODB.Recordset
    Dim companyID As Integer
    
    Dim intReturn As Integer
    
    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
    
    strSQL = "SELECT CMPANYID from DYNAMICS..SY01500 where INTERID = DB_Name()"
    cmd.CommandText = strSQL
    Set rs = cmd.Execute
    companyID = rs.Fields("CMPANYID").Value
    rs.Close
    Set rs = Nothing
    
    cmd.Parameters.Append cmd.CreateParameter("I_sCompanyID", adSmallInt, adParamInput, , companyID)
    cmd.Parameters.Append cmd.CreateParameter("I_iSQLSessionID", adInteger, adParamInput, , 1)
    cmd.Parameters.Append cmd.CreateParameter("O_mNoteIndex", adNumeric, adParamInputOutput, , 0)
    cmd.Parameters("O_mNoteIndex").Precision = 19
    cmd.Parameters("O_mNoteIndex").NumericScale = 5
    cmd.Parameters.Append cmd.CreateParameter("O_iErrorState", adInteger, adParamInputOutput, , 0)
    
    strSQL = "DYNAMICS.dbo.smGetNextNoteIndex"
    
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = strSQL
    cmd.Execute
    
    cn.Close
    Set cn = Nothing
    
    MsgBox "Note index: " & cmd.Parameters("O_mNoteIndex").Value

Open in new window

0
 

Author Comment

by:taverny
ID: 35150263
nop that doesn't work . I mean it works but can't keep cliking.
here is the code that I have . and also when I click debug it brings me to line
Set rs = cmd.Execute
Option Explicit
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim strsql As String
Dim companyID As Integer

Private Sub ButSave_Click()
Dim NoteIndex As Integer

    'Retrieve an ADO Connection for the current user
    Set cn = UserInfoGet.CreateADOConnection
    'Set the current Database company
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
    
    'this grab the companyID logged in
    strsql = "SELECT CMPANYID from DYNAMICS..SY01500 where INTERID = DB_Name()"
    cmd.CommandText = strsql
    Set rs = cmd.Execute
    companyID = rs.Fields("CMPANYID").Value
    rs.Close
    
    Set rs = Nothing
    cmd.Parameters.Append cmd.CreateParameter("I_sCompanyID", adSmallInt, adParamInput, , companyID)
    cmd.Parameters.Append cmd.CreateParameter("I_iSQLSessionID", adInteger, adParamInput, , 1)
    cmd.Parameters.Append cmd.CreateParameter("O_mNoteIndex", adNumeric, adParamInputOutput, , 0)
    cmd.Parameters("O_mNoteIndex").Precision = 19
    cmd.Parameters("O_mNoteIndex").NumericScale = 5
    cmd.Parameters.Append cmd.CreateParameter("O_iErrorState", adInteger, adParamInputOutput, , 0)
    
    strsql = "DYNAMICS.dbo.smGetNextNoteIndex"
    
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = strsql
    cmd.Execute
cn.Close
Set cn = Nothing
    MsgBox "Note index: " & cmd.Parameters("O_mNoteIndex").Value
    'NoteIndex = cmd.Parameters("O_mNoteIndex").Value
    
    'strsql = "insert INTO PSHITEMNOTE (ITEMNMBR, NOTEINDX)VALUES ('" & LblItem & "' , '" & NoteIndex & "') "
    'Set rs = cn.Execute(strsql)
    End Sub

Open in new window

0
 

Author Comment

by:taverny
ID: 35150296
I am not sure if I understand parameters correctly , but could it be that the paremeters are already saved and interfere with the code when running a second time?
0
 

Author Comment

by:taverny
ID: 35152337
well, I modified the code to work exactly the way I want it . it saves, update and read the note correctly.
attched is the full working code.
thank you for your help.
I am sure more questions will come up tomorrow.

Option Explicit
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim strSQL As String
Dim companyID As Integer
Dim NoteIndex As Integer
Dim binchanged As Boolean


'*********
'Sub called when the button save is clicked
'*********
Private Sub ButSave_Click()

    'Retrieve an ADO Connection for the current user
    Set cn = UserInfoGet.CreateADOConnection
    'Set the current Database company
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
    
    'Check if the note exists in the database
    strSQL = " Select count(*) from PSHITEMNOTE where ITEMNMBR ='" & LblItem & "'"
    Set rs = cn.Execute(strSQL)
    
    'if record exists in PSHITEMNOTE table look if the records has text in the SY03900 table
    If rs.Fields(0).Value = 1 Then
        strSQL = " Select NOTEINDX from PSHITEMNOTE where ITEMNMBR ='" & LblItem & "'"
        Set rs = cn.Execute(strSQL)
        'store the noteindex for the item selected
        NoteIndex = rs.Fields("NOTEINDX").Value
        
        'check if the noteindex exists in the SY03900
        strSQL = "select count(*) from SY03900 where NOTEINDX='" & NoteIndex & "' "
        Set rs = cn.Execute(strSQL)
        
        'if record exists do an update otherwise create it
        If rs.Fields(0).Value = 1 Then
            strSQL = "UPDATE SY03900 set TXTFIELD='" & txtnote & "' where NOTEINDX= '" & NoteIndex & "'  "
            Set rs = cn.Execute(strSQL)
        Else
            strSQL = "insert INTO SY03900 (NOTEINDX, TXTFIELD)VALUES ('" & NoteIndex & "' , '" & txtnote & "') "
            Set rs = cn.Execute(strSQL)
        End If
    'records is new so we need to find the next index
    Else
        'this grab the companyID where the user is logged in
        strSQL = "SELECT CMPANYID from DYNAMICS..SY01500 where INTERID = DB_Name()"
        cmd.CommandText = strSQL
        Set rs = cmd.Execute
        companyID = rs.Fields("CMPANYID").Value
        'close the connection
        rs.Close
        Set rs = Nothing
    
        'set the parameters
        cmd.Parameters.Append cmd.CreateParameter("I_sCompanyID", adSmallInt, adParamInput, , companyID)
        cmd.Parameters.Append cmd.CreateParameter("I_iSQLSessionID", adInteger, adParamInput, , 1)
        cmd.Parameters.Append cmd.CreateParameter("O_mNoteIndex", adNumeric, adParamInputOutput, , 0)
        cmd.Parameters("O_mNoteIndex").Precision = 19
        cmd.Parameters("O_mNoteIndex").NumericScale = 5
        cmd.Parameters.Append cmd.CreateParameter("O_iErrorState", adInteger, adParamInputOutput, , 0)
        'execute the Stored Procedure to get the next note index #
        strSQL = "DYNAMICS.dbo.smGetNextNoteIndex"
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = strSQL
        cmd.Execute
    
        'MsgBox "Note index: " & cmd.Parameters("O_mNoteIndex").Value
        'store the noteindex available to a string
        NoteIndex = cmd.Parameters("O_mNoteIndex").Value
        
        strSQL = "insert INTO PSHITEMNOTE (ITEMNMBR, NOTEINDX)VALUES ('" & LblItem & "' , '" & NoteIndex & "') "
        Set rs = cn.Execute(strSQL)
        
        strSQL = "insert INTO SY03900 (NOTEINDX, TXTFIELD)VALUES ('" & NoteIndex & "' , '" & txtnote & "') "
        Set rs = cn.Execute(strSQL)
    
    End If
    binchanged = False
End Sub

'*********
'Note text has changed
'*********
Private Sub txtnote_Change()
    binchanged = True
End Sub

'*********
'Load the note if one exists
'*********
Private Sub UserForm_Initialize()

    'retrieve the Item Number from the Item Maintenance
    LblItem = ItemPriceListMaintenance.ItemNumber.Value
    
    'Retrieve an ADO Connection for the current user
    Set cn = UserInfoGet.CreateADOConnection
    'Set the current Database company
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
    
    'Check in the database if the record exists in the database
    strSQL = " Select count(*) from PSHITEMNOTE where ITEMNMBR ='" & LblItem & "'"
    Set rs = cn.Execute(strSQL)
    
    'if record exists look if the records has text in the SY03900 table
    If rs.Fields(0).Value = 1 Then
        strSQL = " Select NOTEINDX from PSHITEMNOTE where ITEMNMBR ='" & LblItem & "'"
        Set rs = cn.Execute(strSQL)
        'store the noteindex for the item selected
        NoteIndex = rs.Fields("NOTEINDX").Value
        'check if the noteindex exists in the SY03900
        strSQL = "select count(*) from SY03900 where NOTEINDX='" & NoteIndex & "' "
        Set rs = cn.Execute(strSQL)
        If rs.Fields(0).Value = 1 Then
            strSQL = "select TXTFIELD from SY03900 where NOTEINDX= '" & NoteIndex & "' "
            Set rs = cn.Execute(strSQL)
            txtnote = rs.Fields("TXTFIELD").Value
        End If
    End If
    binchanged = False
    
End Sub


'*********
'The x button on top of the form is clicked to close the form
'*********
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    Dim AnswerFromBox As String
    AnswerFromBox = ""
    'if x is pressed(0) and the form has been modified then call the yesnomessage
    If CloseMode = 0 And binchanged = True Then
        ' changed has been made do we want to save the change?
        AnswerFromBox = YesNoMessageBox()
        If AnswerFromBox = "YesAndSaved" Then
            Cancel = 0
        ElseIf AnswerFromBox = "NoNeedSave" Then
            Cancel = 0
        ElseIf AnswerFromBox = "Cancel" Then
            Cancel = 1
        End If
    Else
        ' nothing changed in the form we can close safely
        Cancel = 0
    End If
    
End Sub


'*********
'Message Box to see if we want to save the modif
'*********
 Function YesNoMessageBox() As String
 
    Dim Answer As String
    Answer = MsgBox("Do you Want To save your changes?", vbQuestion + vbYesNoCancel, "Save?")
    If Answer = vbYes Then
        Call ButSave_Click
        YesNoMessageBox = "YesAndSaved"
    ElseIf Answer = vbCancel Then
        YesNoMessageBox = "Cancel"
    ElseIf Answer = vbNo Then
        YesNoMessageBox = "NoNeedSave"
    End If

End Function

Open in new window

0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This is a walkthrough guide I wrote whilst upgrading my on-premise MS Dynamics CRM 3.0 deployment to 4.0. This covers the actual installation of the product to a working level for my system, I ran into a lot of issues that the steps below fixed so h…
On Sep 22nd 2014 Microsoft released Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 and back in July Update Rollup 3 was released.  So we now have:   Update Rollup 1Update Rollup 2Update Rollup 3Service Pack 1Update Rollup 1 for S…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now