- OneStream Community
- Forums
- Rules
- How to store Destination cell amount to temp variable to append with source cell amount
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
How to store Destination cell amount to temp variable to append with source cell amount
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-16-2023
06:08 AM
- last edited on
05-16-2023
09:55 AM
by
JackLacava
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.- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-16-2023 06:16 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-16-2023 06:47 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-16-2023 07:05 AM
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-16-2023 07:36 AM - edited 05-16-2023 08:01 AM
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.
