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 ...
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.