Forum Discussion

Johnny's avatar
Johnny
New Contributor II
3 years ago

Location of ConsId field Name / Description Table in Database

I am building a data adapter utilizing the SQL command type. In my query, I reference the ConsId field. What database table can I join to for the Name/Description fields for the ConsId field on my primary table?  For example, I can join to the [Member] table on the [DimTypeId] and [MemberId] fields to return Name/Description fields of an entity or account, ect.

I'm also looking for currency ID Name/Description, if that is held in a different location than ConsId.

Example of my query:

SELECT

JLT.[ConsId]

FROM [JournalLineItem] as JLT

LEFT JOIN JLT.[ConsId] = [Tbl_Cons_DimensionData???]

WHERE JLT.JournalID = 'ABC'

Methodology
  • NicoleBruno's avatar
    NicoleBruno
    Valued Contributor
    3 years ago

    Hello! 

    We have a journal entry report built that doesn't reference a table for the cons but lists this instead. Hopefully it's useful: 

    (CASE
    When jl.ConsID = 176 Then 'USD'
    When jl.ConsID = 57 Then 'EUR'
    When jl.ConsID = 62 Then 'GBP'
    When jl.ConsID = 27 Then 'CAD'
    When jl.ConsID = 81 Then 'INR'
    When jl.ConsID = 8 Then 'AUD'
    When jl.ConsID = 121 Then 'MXN'
    When jl.ConsID = 21 Then 'BRL'
    When jl.ConsID = 196 Then 'ZAR'
    Else 'Unknown'
    End) as 'Currency',

    • Johnny's avatar
      Johnny
      New Contributor II
      3 years ago

      Thank you, Nicole! This was my backup plan. Thankfully the currency dimensions will not change.

  • ckattookaran's avatar
    ckattookaran
    VIP
    3 years ago

    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.

    • Johnny's avatar
      Johnny
      New Contributor II
      3 years ago

      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.

      • ckattookaran's avatar
        ckattookaran
        VIP
        3 years ago

        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 Function

        Use this in a Dashboard Data Set rule and you can use a data adapter to create the report.