Skip to main content
M
mpavan
April 27, 2023
Solved

Filter Result DataBuffer based on value in secondary Source Buffer

  • April 27, 2023
  • 2 replies
  • 0 views

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. 

Best answer by mpavan

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

2 replies

C
chul
April 27, 2023

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

M
mpavanAuthorAnswer
April 28, 2023

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

C
ckattookaran
April 29, 2023

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. 

Terms & ConditionsAccessibility statement