You’re offline. This is a read only version of the page.
2,046
hi folks
I need to put the description of the currency ID onto my SOP blank invoice.
I can see the currency ID and currency index in report writer on Sales transaction work. I don't want to print Z - US$ however
I want to print the description of Z-US$
Trying to link out from the sales transaction work table report writer will take me to sales document setup, payment terms master and sales workflow set up.
Do I have a shot at getting to the currency set up master via report writer?
thanks!
*This post is locked for comments
Hi Folks,
For complex table relation ship, the report works in test mode while deploying the chunk it results Table Hierarchy Error. so the best way is the usage of RW function as Soma suggested. above scenario is straight, so you can follow any of the above suggested posts.
You can also achieve the same by creating a global Function in Dexterity with the name starting of "RW_" (use this function to get the currency index and return the description) and then create a calculated field with adding the global function in SOP blank invoice Report and use this calculated field to your report layout.
Note: If you following the above mentioned suggestion, this will helps to add the Currency Description to all the reports( which is containing the currency id) without adding the table relationship and VBA code to each of the reports.
Hope this helps!!!
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
Hi,
You can add 'Currency Setup' table (MC_Currency_SETP) as a relationship table to the Sales transaction work (SOP_HDR_WORK) .
In the report writer, follow below steps to add relationship between above mention tables.
1.Click Tables, and then click Tables.
2.Click SOP_HDR_WORK, and then click Open.
3.Click Relationships, and then click New.
4.Click the Secondary Table ellipsis button (...), click Currency Setup, and then click OK.
5.In the Secondary Table Key list, click MC_Currency_SETP_Key1.
6.In the Primary Table column list, click Currency ID to match to the Currency ID of secondary table.
8.Click OK.
9.Close the Table Relationship window.
10.In the Table Definition window, click OK, and then close the Tables window.
11. Now you need to open your report and click on 'Tables'. Select 'Sales Transaction Work' and click on New. You can see the 'Currency Setup' table available under related tables and you can select that and click on OK.
12. Open report layout and choose 'Currency Setup' in the Tool box to see Currency Description available to add to the report.
Hope this helps.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (