Forum Discussion

BCG's avatar
BCG
New Contributor II
2 years ago

How to store Destination cell amount to temp variable to append with source cell amount

Hi Experts,

I would like to store existing cell destination value to temp variable so as to write back to destination by appending of source cell value using databuffer - with the following script i have written can only copy source cell to destination, but it does not append if the value is already exist in destination prior to write from source cell amounts.( I need because multiple source values are writing up to same destination then each time it is getting replaced already existing value- which is not correct as per the business case)

Dim ResulDataBuf As DataBuffer = New DataBuffer()

Dim DestinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("V#Periodic")
Dim DataBufFilter As DataBuffer = Api.Data.GetDataBufferUsingFormula("FilterMembers(V#Periodic),[A#[root].base.Where((Text1 <> "")And (Text2 <> ""))]")

DataBufFilter.LogDataBuffer(api, "records", 100)

For Each sourceCell As DataBuffercell In DataBufFilter.DataBufferCells.Values

    Dim cashflowCell As New DataBufferCell(sourceCell) 'Temp buffercell within the loop to copy my cashflow cell

    Dim sText1 As String = api.Account.Text(cashflowCell.DataBufferCellPk.AccountId, 1) 'Source Mvmt
    Dim sText2 As String = api.Account.Text(cashflowCell.DataBufferCellPk.AccountId, 2) 'Target Account:Mvmnt.



    If (Not sText1 = "") And (Not sText2 = "") AndAlso instr(sText2, ":") > 0 Then

        Dim vSrcUD2Split As String() = Split(Trim(sText2), ":")

        Dim GetUD1Ac As String = Trim(vSrcUD2Split(0)) : Dim GetUD1Mvt As String = Trim(vSrcUD2Split(1)) 'Spliting with : in Text2 value for Account:Flow 
        Dim GetUD1AcR As String = Right(GetUD1Ac, len(GetUD1Ac) - 2) : Dim GetUD1MvtR As String = Right(GetUD1Mvt, len(GetUD1Mvt) - 2) 'Removing A#/F# to get Member ID's

        Dim vTgtActID As Integer = api.Members.GetMember(DimType.Account.Id, GetUD1AcR).MemberId
        Dim vTgtMvmntID As Integer = api.Members.GetMember(DimType.Flow.Id, GetUD1MvtR).MemberId

        Dim GetExistDestCellValue As DataCell = api.Data.GetDataCell(sText2)
        api.LogMessage(sText2 & "=>" & GetExistDestCellValue.CellAmount)

        cashflowCell.DataBufferCellPk.AccountId = vTgtActID
        cashflowCell.DataBufferCellPk.FlowId = vTgtMvmntID

        Dim ResultCell As New DataBuffercell(cashflowCell)

        ResultCell.CellAmount = (ResultCell.CellAmount)

        If (Not sourceCell.CellStatus.IsNoData) Then
            ResulDataBuf.setcell(si, ResultCell, True)
        End If
    End If
Next
api.Data.setdatabuffer(ResulDataBuf, DestinationInfo) 'Save the data into destination.
  • BCG's avatar
    BCG
    New Contributor II
    2 years ago

    In addition to the above, i could see the data in the cube view for the destination intersection, but when i see the same in the logbuffer the value is 0.00 with no data status in the printscreen (source & destination sequently)- what could be the reason. this is why i could not get the data to temp variable when i try to store from destination intersection

  • ChristianW's avatar
    ChristianW
    Valued Contributor
    2 years ago

    you just need to prepopulate the result databuffer with the source data:

    instead of:

    Dim ResulDataBuf As DataBuffer = New DataBuffer()

    do this:

    Dim ResulDataBuf As DataBuffer = Api.Data.GetDataBufferUsingFormula("FilterMembers(V#Periodic),[A#[root].base.Where((Text1 <> "")And (Text2 <> ""))]")

    then

    ResulDataBuf.setcell(si, ResultCell, True)

    should do the job without any additional work.

    • BCG's avatar
      BCG
      New Contributor II
      2 years ago

      Hi ChristianW,

       

      as suggested, i have modified the script but not able to see the expected results, instead it is getting failed

      error message : Collection was modified; enumeration operation may not execute.

      modified full script:

      'Dim ResulDataBuf As DataBuffer = New DataBuffer()
       
      Dim DestinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("V#Periodic")
      Dim DataBufFilter As DataBuffer = Api.Data.GetDataBufferUsingFormula("FilterMembers(V#Periodic),[A#[root].base.Where((Text1 <> "")And (Text2 <> ""))]")
       
      DataBufFilter.LogDataBuffer(api,"records",100)
       
      For Each sourceCell As DataBuffercell In DataBufFilter.DataBufferCells.Values
       
      Dim cashflowCell As New DataBufferCell(sourceCell)'Temp buffercell within the loop to copy my cashflow cell
      Dim sText1 As String = api.Account.Text(cashflowCell.DataBufferCellPk.AccountId,1)'Source Mvmt
      Dim sText2 As String = api.Account.Text(cashflowCell.DataBufferCellPk.AccountId,2)'Target Account:Mvmnt.
       
      Dim GetExistDestCellValue As DataCell = api.Data.GetDataCell(sText2)
      api.LogMessage(sText2 & "=>" & GetExistDestCellValue.CellAmount)
       
      If (Not sText1 = "") And (Not sText2 = "") AndAlso instr(sText2,":")>0 Then
       
      Dim vSrcUD2Split As String() = Split(Trim(sText2),":")
       
      Dim GetUD1Ac As String = Trim(vSrcUD2Split(0)):Dim GetUD1Mvt As String = Trim(vSrcUD2Split(1))'Spliting with : in Text2 value for Account:Flow 
      Dim GetUD1AcR As String = Right(GetUD1Ac,len(GetUD1Ac)-2):Dim GetUD1MvtR As String = Right(GetUD1Mvt,len(GetUD1Mvt)-2)'Removing A#/F# to get Member ID's
       
      Dim vTgtActID As Integer = api.Members.GetMember(DimType.Account.Id, GetUD1AcR).MemberId
      Dim vTgtMvmntID As Integer = api.Members.GetMember(DimType.Flow.Id, GetUD1MvtR).MemberId
       
       
      cashflowCell.DataBufferCellPk.AccountId = vTgtActID
      cashflowCell.DataBufferCellPk.FlowId = vTgtMvmntID
       
      Dim ResultCell As New DataBuffercell(cashflowCell)
       
      ResultCell.CellAmount = (ResultCell.CellAmount)
       
      If (Not sourceCell.CellStatus.IsNoData) Then
      'ResulDataBuf.setcell(si,ResultCell,True)
      DataBufFilter.setcell(si,ResultCell,True)
      End If
      End If
      Next
      'api.Data.setdatabuffer(ResulDataBuf,DestinationInfo)'Save the data into destination.
      api.Data.setdatabuffer(DataBufFilter,DestinationInfo)'Save the data into destination.
       
      • ChristianW's avatar
        ChristianW
        Valued Contributor
        2 years ago

        I don't understand exactly what you are doing, but can you try this code:

        Dim ResulDataBuf As DataBuffer = Api.Data.GetDataBufferUsingFormula("A#all")
Dim DestinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("V#Periodic")
Dim DataBufFilter As DataBuffer = Api.Data.GetDataBufferUsingFormula("FilterMembers(V#Periodic,A#root.base.Where((Text1 startswith F) And (Text2 contains :)))")
 
DataBufFilter.LogDataBuffer(api,"records",100)
 
For Each sourceCell As DataBuffercell In DataBufFilter.DataBufferCells.Values
 
	Dim cashflowCell As New DataBufferCell(sourceCell)'Temp buffercell within the loop to copy my cashflow cell
	Dim sText1 As String = api.Account.Text(cashflowCell.DataBufferCellPk.AccountId,1)'Source Mvmt
	Dim sText2 As String = api.Account.Text(cashflowCell.DataBufferCellPk.AccountId,2)'Target Account:Mvmnt.
	 
	If (Not sText1 = "") And (Not sText2 = "") AndAlso instr(sText2,":")>0 Then
		If (Not sourceCell.CellStatus.IsNoData) Then
	 
			Dim script As New MemberScriptBuilder(Trim(sText2))
		
			Dim vTgtActID As Integer = api.Members.GetMemberId(DimType.Account.Id, script.Account)
			Dim vTgtMvmntID As Integer = api.Members.GetMemberId(DimType.Flow.Id, script.Flow)
			 
			cashflowCell.DataBufferCellPk.AccountId = vTgtActID
			cashflowCell.DataBufferCellPk.FlowId = vTgtMvmntID
			 
			ResulDataBuf.setcell(si,cashflowCell,True)
		End If
	End If
Next
api.Data.setdatabuffer(ResulDataBuf,DestinationInfo)'Save the data into destination.