04-27-2023 12:01 AM - last edited on 05-02-2023 10:39 AM by JackLacava
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 possible to filter Buffer 1 and write corresponding buffer 2 value to result buffer?
Or the approach has to be completely changed for this requirement?
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
Dim resultCell As New DataBufferCell(newCell)
resultBuf.SetCell(si, resultCell, True)
End If
Next
api.Data.SetDataBuffer(resultBuf * -1, destInfo)
End If
Any Ideas on how I can further analyze this will be helpful.
Solved! Go to Solution.
04-28-2023 05:02 PM
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
04-27-2023 02:20 PM
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
04-28-2023 05:02 PM
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
04-29-2023 03:27 PM
You can definitely do it. Now the trick is to be inside the buffer loop check for your value and then if it meets the condition then query the next buffer keys with the ids from your current loop.
Doing api.data.calculate can cause performance degradation. Since you are doing it inside a loop and then also calling the data units into memory.