Forum Discussion

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

Filter Result DataBuffer based on value in secondary Source Buffer

Hello Experts,  I have a requirement of calculating Cash_ADJ based a sum value of 2 different buffers.  If the sum value is Positive I have to post a cash_adj with value from bufSrc2. Is it possi...
Finance
  • mpavan's avatar
    mpavan
    2 years ago

    Thanks' for the suggestion. I have tried but unable to make it work using member formula. Still a lot of learning  to do. In the meanwhile I had used api.calculate with in the loop and it had worked as expected, 

    If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyForEntity())) Then
    Dim destInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("A#Cash_ADJ")
    Dim bufSrc1 As DataBuffer = api.Data.GetDataBufferUsingFormula("A#Cash")
    Dim bufSrc2 As DataBuffer = api.Data.GetDataBufferUsingFormula("A#Loan")
    Dim bufSrc3 As DataBuffer = bufSrc1 - bufSrc2
    Dim resultBuf As New DataBuffer ()

    For Each newCell As DataBufferCell In bufSrc3.DataBufferCells.Values
    If Not NewCell.CellAmount > 0 Then
    api.data.calculate("A#Cash_ADJ:F#ENDBAL=A#Loan:F#ENDBAL * -1")
    End If
    Next
    api.Data.SetDataBuffer(resultBuf * -1, destInfo)
    End If

chul's avatar
chul
Contributor III
2 years ago

Write a member formula on A#Cash_ADJ. If the sum of A#Cash + A#Loan is greater than zero, then A#Cash_ADJ = A#Loan

mpavan's avatar
mpavan
New Contributor II
2 years ago

Thanks' for the suggestion. I have tried but unable to make it work using member formula. Still a lot of learning  to do. In the meanwhile I had used api.calculate with in the loop and it had worked as expected, 

If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyForEntity())) Then
Dim destInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("A#Cash_ADJ")
Dim bufSrc1 As DataBuffer = api.Data.GetDataBufferUsingFormula("A#Cash")
Dim bufSrc2 As DataBuffer = api.Data.GetDataBufferUsingFormula("A#Loan")
Dim bufSrc3 As DataBuffer = bufSrc1 - bufSrc2
Dim resultBuf As New DataBuffer ()

For Each newCell As DataBufferCell In bufSrc3.DataBufferCells.Values
If Not NewCell.CellAmount > 0 Then
api.data.calculate("A#Cash_ADJ:F#ENDBAL=A#Loan:F#ENDBAL * -1")
End If
Next
api.Data.SetDataBuffer(resultBuf * -1, destInfo)
End If