Hi folks,
Naga suggest you a precise step.
you can also achieve the above functionality through modifier + VBA
1) modify the current report
2) Add report to Visual Basic
3) create a calculated string field, add the field to your layout and click add fields to visual basic
2) click currnidx from the corresponding table (SOP_HDR_WORK) and click add fields to visual basic
3) you can start the VBA code
sample code:
Option Explicit
Dim userinfo
Dim luserid
Dim lintercompanyid
Dim lsqldatasourcename
Dim ldate
Private Sub Report_BeforePH(SuppressBand As Boolean)
'Create an object of RetrieveGlobals9.
Set userinfo = CreateObject("RetrieveGlobals9.retrieveuserinfo")
'Retrieve global fields from the current session of Microsoft Dynamics GP.
luserid = userinfo.retrieve_user()
lintercompanyid = userinfo.intercompany_id()
lsqldatasourcename = userinfo.sql_datasourcename()
ldate = userinfo.user_date()
If userinfo.status = 1 Then
Dim cn
Dim cmd
Dim rst
'Use the connection property to get a connection object.
Set cn = userinfo.connection
'Create an ADO command object.
Set cmd = CreateObject("ADODB.Command")
'Use a client-side cursor so that a recordset
'count can be obtained later.
cn.CursorLocation = 3
'set the database to the currently logged in db.
cn.DefaultDatabase = lintercompanyid
cmd.ActiveConnection = cn
'adCmdText.
cmd.CommandType = 1
cmd.CommandText = "select crncydsc from dynamics..MC40200 where currnidx = '" & currnidx.value & "'"
Set rst = cmd.Execute
If Not (rst.EOF And rst.BOF) Then
calculated string field.value = RTrim(rst(0))
End If
'Close the connection.
If cn.State = 1 Then
cn.Close
End If
'Cleanup.
Set cmd = Nothing
Set cn = Nothing
Else
'Handle a connection status other than 1.
MsgBox ("The following connection error occurred " & userinfo.status)
End If
Set userinfo = Nothing
End Sub