Forum Discussion

Kneelay's avatar
Kneelay
New Contributor II
2 years ago
Solved

SQL Table Insert Not adding all Records

HI All,  I'm running into an issue when running a Finance Business Rule, essentially this rule creates a data buffer and loops through where U8 member is not 0. From here we have a SQL statement wh...
Finance
  • RobbSalzmann's avatar
    RobbSalzmann
    2 years ago
    Dim valueList As List(Of String) = New List(Of String)
Dim x As Integer = 0

For Each Cell As DataBufferCell In SrcDataBuffer.DataBufferCells.Values
    ' Execute If Allocation percentage Is Not equal To 0
    If Cell.CellAmount <> 0 Then			            
        x += 1
        sAcct = Cell.DataBufferCellPk.GetAccountName(api)
        sUD1 = Cell.DataBufferCellPk.GetUD1Name(api)
        sFlow = Cell.DataBufferCellPk.GetFlowName(api)
        sUD2 = Cell.DataBufferCellPk.GetUD2Name(api)
        sUD4 = Cell.DataBufferCellPk.GetUD4Name(api)
		valueList.Add($"('{sqlentity}','{sEntitymember}', '{sUD4}', '{sFlow}', '{sUD2}', '{sUD1}', '{sAcct}', {Cell.CellAmount})")
    End If
Next

Dim insertValues = String.Join(", ", valueList)

Dim sql As String = $"INSERT INTO XFT_BSM_Mapping_Total$ 
                     ([BU], [Entity], [BSM_Account], [BSM_Measure], [Responsibility_Center], [Company_Code], [Account],[Allocation_Percentage])
                     VALUES {insertValues}"
brapi.ErrorLog.LogMessage(si,x)

Using dbConn As DBConnInfo = BRAPi.Database.CreateApplicationDBConnInfo(si)
	BRAPi.Database.ExecuteSql(dbConn , sql, True)
End Using

     

RobbSalzmann's avatar
RobbSalzmann
Valued Contributor II
2 years ago

Its a lot easier for me to edit,if you post the code as text vs a screen shot.

 

  • Kneelay's avatar
    Kneelay
    New Contributor II
    2 years ago
    'Create a databuffer for all combinations that would hold a value in U8#Alloc_Perc
Dim SrcDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(V#Periodic:I#None:O#Top:U5#None:U6#None:U7#None:U8#Allocation_perc:U3#Primary,[U1#CO_20.Base],[A#GMACM.Base],[F#Total_BSM_Flow.Base],[U2#Total_RC.Base],[U4#New_Supertable.Base])")
							
Dim sAcct As String = String.Empty
Dim sUD1 As String = String.Empty
Dim sflow As String = String.Empty
Dim sUD2 As String = String.Empty
Dim sUD4 As String = String.Empty
								
								
	Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
																	
		Dim sql As New Text.StringBuilder
		Dim x As Integer = 0
																		
		For Each Cell As DataBufferCell In SrcDataBuffer.DataBufferCells.Values
										
									
		'Execute If Allocation percentage Is Not equal To 0
		If Cell.CellAmount <> 0 Then 										
									
		'x = x + 1	
		'sEntity = Cell.DataBufferCellPk.GetEntityID(api)
		sAcct = Cell.DataBufferCellPk.GetAccountName(api)
		sUD1 = Cell.DataBufferCellPk.GetUD1Name(api)
		sflow = Cell.DataBufferCellPk.GetflowName(api)
		sUD2 = Cell.DataBufferCellPk.GetUD2Name(api)
		sUD4 = Cell.DataBufferCellPk.GetUD4Name(api)									
										
		'End If
									
'Next
																			
	sql.AppendLine($"INSERT Into XFT_BSM_Mapping_Total5")
	sql.Append($"([BU], [Entity], [BSM_Account], [BSM_Measure], [Responsibility_Center], [Company_Code], [Account],[Allocation_Percentage])")					
	sql.Append($"Values ('{sqlentity}','{sEntitymember}', '{sUD4}', '{sflow}', '{sUD2}', '{sUD1}', '{sAcct}', '{Cell2.CellAmount}')")
									
	End If
									
	brapi.ErrorLog.LogMessage(si,x)
									
	Next
									
	BRAPi.Database.ExecuteSql(dbConnApp,sql.ToString, True)
												
	End Using

     

    • RobbSalzmann's avatar
      RobbSalzmann
      Valued Contributor II
      2 years ago
      Dim valueList As List(Of String) = New List(Of String)
Dim x As Integer = 0

For Each Cell As DataBufferCell In SrcDataBuffer.DataBufferCells.Values
    ' Execute If Allocation percentage Is Not equal To 0
    If Cell.CellAmount <> 0 Then			            
        x += 1
        sAcct = Cell.DataBufferCellPk.GetAccountName(api)
        sUD1 = Cell.DataBufferCellPk.GetUD1Name(api)
        sFlow = Cell.DataBufferCellPk.GetFlowName(api)
        sUD2 = Cell.DataBufferCellPk.GetUD2Name(api)
        sUD4 = Cell.DataBufferCellPk.GetUD4Name(api)
		valueList.Add($"('{sqlentity}','{sEntitymember}', '{sUD4}', '{sFlow}', '{sUD2}', '{sUD1}', '{sAcct}', {Cell.CellAmount})")
    End If
Next

Dim insertValues = String.Join(", ", valueList)

Dim sql As String = $"INSERT INTO XFT_BSM_Mapping_Total$ 
                     ([BU], [Entity], [BSM_Account], [BSM_Measure], [Responsibility_Center], [Company_Code], [Account],[Allocation_Percentage])
                     VALUES {insertValues}"
brapi.ErrorLog.LogMessage(si,x)

Using dbConn As DBConnInfo = BRAPi.Database.CreateApplicationDBConnInfo(si)
	BRAPi.Database.ExecuteSql(dbConn , sql, True)
End Using