I was not able to figure that one out. It is there in the FXRate tables. However, there is no name and Id relation anywhere.
You could get the datatable and edit the Cons column and use the following to get the name.
Currency.GetItem(62).Name (this gives GBP)
Loop through the data table items, edit the row and set it to the name. One thing to keep in mind is either cast consid to varchar or add a blank varchar column in your SQL so that you can add the name there in it.
Thank you for the reply, ckattookaran. Your tip for casting the ID as an varchar was helpful. I was getting incompatible field type issues from the case statement when having the ELSE return the ID.
Here you.
Private Function GetJournals(ByVal si As SessionInfo) As DataTable
Dim dt As New DataTable
Dim sql As New Text.StringBuilder
sql.AppendLine("SELECT CAST(JLT.[ConsId] as varchar) as ConsId")
sql.AppendLine("From [JournalLineItem] As JLT")
sql.AppendLine("WHERE JLT.JournalID = 'ABC'")
Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
dt = BRApi.Database.ExecuteSqlUsingReader(dbConn, sql.ToString, True)
End Using
If dt.rows.Count > 0 Then
dt.Columns("ConsId").MaxLength = -1
dt.Columns("ConsId").ReadOnly = False
For Each dr As DataRow In dt.Rows
dr.BeginEdit
Dim intConsId As Integer = ConvertHelper.ToInt32(dr("ConsId"))
dr("ConsId") = Currency.GetItem(intConsId).Name
dr.EndEdit
Next
End If
Return dt
End FunctionUse this in a Dashboard Data Set rule and you can use a data adapter to create the report.