Forum Discussion

Krishna's avatar
Krishna
Valued Contributor
2 years ago

Data Buffer

Hi - I am trying to execute the below code but it is not taking the prior month actual data. Could you please let me know what am doing wrong? It is copying only the current month.      Dim S...
Finance
  • JackLacava's avatar
    JackLacava
    2 years ago

    That's better. Note that you don't even need to assign buffers to variables for simple math, you can just do:

     

    Dim buf1 as DataBuffer = api.data.GetDataBufferUsingFormula(...)
Dim buf2 as DataBuffer = api.data.GetDataBufferUsingFormula(...)

Dim result as DataBuffer = buf1 + buf2
api.Data.SetDataBuffer(si, result, targetDestExpressionInfo)

     

    As for the last bit: you know how, in your first code, you retrieved a buffer then looped through its cells? You can simply retrieve two buffers, then manipulate their cells directly. A buffer is effectively a dictionary, where keys are DataBufferCellPK and values are DataBufferCell; so you can get the PK of cells from buf1 and look up if there is an equivalent one with the same PK in buf2. Because this would be done in memory, rather than retrieving from db every time (which is what GetDataCell would do), it's still fast enough (even though working at the buffer level is likely to be faster).

JackLacava's avatar
JackLacava
Honored Contributor
2 years ago

You're filtering on Flow at the very beginning, which means the Flow member you find in cells is XFCommon. Then you use that to try and locate data elsewhere; because the cell definition is valid, there is no error, but the cell will always be empty because XFCommon is not a valid Flow. At the very minimum, you will have to specify a valid Flow member in that GetDataCell call.

However, I would add that this is not the best approach - you'll be forcing a lot of lookups and a lot of database calls, it's going to be super slow. You should simply use buffers: get a buffer for M11, get a buffer for M10, sum them up, save results to database. If you really want to go cell by cell, you should still pull the two buffers in memory and then work with cells from those objects.

  • Krishna's avatar
    Krishna
    Valued Contributor
    2 years ago

    Thanks Jack you are spot on the XFCommon because when I try to print the logbuffer it is has F#XFCommon.

    A#A1000:F#XFCommon:O#Import:I#None:U1#C100:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None, 3,000.00, IsRealData, Input
    A#A1000:F#XFCommon:O#Import:I#None:U1#C200:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None, 0.00, IsDerivedData, StoredButNoActivity
    A#A3000:F#XFCommon:O#Import:I#None:U1#C200:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None, 3,000.00, IsRealData, Input
    A#A1001:F#XFCommon:O#Import:I#None:U1#C100:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None, 0.00, IsDerivedData, StoredButNoActivity
    A#A1001:F#XFCommon:O#Import:I#None:U1#C200:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None, 3,000.00, IsRealData, Input

     

    1. Are you refering to use 2 databuffer for M11 & M10 ? ex below

    				Dim M1 As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(V#YTD:F#EndBalInput:S#Actual:T#2026M11),[A#[BalanceSheet].Base],[U1#[Total_CostCenter].Base]")
				api.Data.FormulaVariables.SetDataBufferVariable("M100",M1,False)
				
				Dim M2 As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(V#YTD:F#EndBalInput:S#Actual:T#2026M10),[A#[BalanceSheet].Base],[U1#[Total_CostCenter].Base]")
				api.Data.FormulaVariables.SetDataBufferVariable("M111",M2,False)
				
				api.Data.Calculate("F#EndBalInput= RemoveZeros($M100 + $M111)
				")

     

    Could you please elaborate on  " If you really want to go cell by cell, you should still pull the two buffers in memory and then work with cells from those objects."

     

     

    • JackLacava's avatar
      JackLacava
      Honored Contributor
      2 years ago

      That's better. Note that you don't even need to assign buffers to variables for simple math, you can just do:

       

      Dim buf1 as DataBuffer = api.data.GetDataBufferUsingFormula(...)
Dim buf2 as DataBuffer = api.data.GetDataBufferUsingFormula(...)

Dim result as DataBuffer = buf1 + buf2
api.Data.SetDataBuffer(si, result, targetDestExpressionInfo)

       

      As for the last bit: you know how, in your first code, you retrieved a buffer then looped through its cells? You can simply retrieve two buffers, then manipulate their cells directly. A buffer is effectively a dictionary, where keys are DataBufferCellPK and values are DataBufferCell; so you can get the PK of cells from buf1 and look up if there is an equivalent one with the same PK in buf2. Because this would be done in memory, rather than retrieving from db every time (which is what GetDataCell would do), it's still fast enough (even though working at the buffer level is likely to be faster).

      • Krishna's avatar
        Krishna
        Valued Contributor
        2 years ago

        Thanks, Jack, for the detail response and it make more sense. I have a complex math and I will be re-using the variables in my code. The code I uploaded was just to test the functionality.