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

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.