11-08-2021 10:50 AM - last edited on 05-02-2023 10:12 AM by JackLacava
I want to round the values for the Revenue account for all UD2 members to whole numbers.
Do I have to get the values, round them and write the value, or is there a simpler method?
Solved! Go to Solution.
11-08-2021 10:58 AM
There is no explicit rounding function in api.data.calculate, but you can use math.round function together with the eval function, as explained here: Using-the-EVAL-function-to-loop...
It will look like this:
Here is the member formula part of it:
api.Data.Calculate("A#60000R = EVAL(A#60000)", AddressOf onEvalDataBuffer)
and here the helper function:
Private Sub OnEvalDataBuffer(ByVal api As FinanceRulesApi, ByVal evalName As String, ByVal eventArgs As EvalDataBufferEventArgs)
'Loop over cells
Dim resultCells As New Dictionary(Of DataBufferCellPk, DataBufferCell)
For Each sourceCell As DataBufferCell In eventArgs.DataBuffer1.DataBufferCells.Values
sourceCell.CellAmount = math.round(sourceCell.CellAmount)
Next
eventArgs.DataBufferResult.DataBufferCells = eventArgs.DataBuffer1.DataBufferCells
End Sub
The result will look like this:
11-08-2021 10:54 AM
Hi Christian, i don't think there is a 'checkbox' that does this. It depends most on where you want to use it (in formulas or e.g. in reporting). If in reporting you could use a dynamic UD8 member that rounds these specific Account:UD2 combinations. If you need them in the database i would build a custom finance function that gets the databuffer with the intersections that you need and writes them back rounded.
11-08-2021 11:00 AM
Thank you, I was a little too slow with my answer. Cheers
11-08-2021 10:58 AM
There is no explicit rounding function in api.data.calculate, but you can use math.round function together with the eval function, as explained here: Using-the-EVAL-function-to-loop...
It will look like this:
Here is the member formula part of it:
api.Data.Calculate("A#60000R = EVAL(A#60000)", AddressOf onEvalDataBuffer)
and here the helper function:
Private Sub OnEvalDataBuffer(ByVal api As FinanceRulesApi, ByVal evalName As String, ByVal eventArgs As EvalDataBufferEventArgs)
'Loop over cells
Dim resultCells As New Dictionary(Of DataBufferCellPk, DataBufferCell)
For Each sourceCell As DataBufferCell In eventArgs.DataBuffer1.DataBufferCells.Values
sourceCell.CellAmount = math.round(sourceCell.CellAmount)
Next
eventArgs.DataBufferResult.DataBufferCells = eventArgs.DataBuffer1.DataBufferCells
End Sub
The result will look like this:
06-19-2023 01:45 PM
I'm a novice with the eval and that did the rounding, but my problem is it rounded everything at the lowest level (I'm assuming), and when it adds all the rounded amounts together the rounded number is off one or two from if I just could round the total amount. At least this is what I'm thinking is happening.
03-15-2024 07:18 AM
Hi,
api.Data.Calculate("F#TOT = EVAL(F#CALC_END-F#CALC_Activity-F#CALC_BEG)", AddressOf onEvalDataBuffer)
Private Sub OnEvalDataBuffer(ByVal api As FinanceRulesApi, ByVal evalName As String, ByVal eventArgs As EvalDataBufferEventArgs)
'Loop over cells
Dim resultCells As New Dictionary(Of DataBufferCellPk, DataBufferCell)
For Each sourceCell As DataBufferCell In eventArgs.DataBuffer1.DataBufferCells.Values
sourceCell.CellAmount = math.round(sourceCell.CellAmount)
Next
eventArgs.DataBufferResult.DataBufferCells = eventArgs.DataBuffer1.DataBufferCells
End Sub
i have the this code and retrieving F#TOT from CV, but it's not working
Thanks
03-31-2024 07:06 AM
Hi Manunathag
Is F#Tot a base member?
Cheers
Christian