Forum Discussion

Gidon_Albert's avatar
Gidon_Albert
Contributor II
2 years ago

Data Buffer FilterMembers() with Empty Cells

 

Does the api.Data.GetDataBufferUsingFormula("FilterMembers(...)") only get cells that have values? 

Is there a way to create a data buffer with empty cells? 

Use case:  We need to to cycle through an account hierarchy and populate each base account with data from an intersection of a specific Account, UD2, UD3, and UD4 that are defined in the text fields of the particular account that is being populated.

The other dimensions of the data buffer are limited to a single member (i.e.":V#YTD:F#End_Bal:O#Import:U2#000:U3#0000:U4#0:U5#None:U6#None:U7#None:U8#None")

 

Finance
  • Omkareshwar's avatar
    Omkareshwar
    Contributor II
    2 years ago

    You can use this 

    Dim Dstpath as string will have your intersections for destination without data units.

    Dim SrcBuffer as string will use this info to create a data buffer "V#YTD:F#End_Bal:O#Import:U2#000:U3#0000:U4#0:U5#None:U6#None:U7#None:U8#None"

    api.Data.Calculate(DstPath+" = Eval($SrcBuffer)",,,,,,,,,,,,,AddressOf OnEvalDataBuffer,,True)

     

    Create a function with this signature. Inside this function you can access the databuffer using eventargs.databuffer1 

    this works in the exact same way as a DBCL rest you can manipulate as per your requirement.

    Private Sub OnEvalDataBuffer(ByVal api As FinanceRulesApi, ByVal evalName As String, ByVal eventArgs As EvalDataBufferEventArgs)

         eventArgs.DataBuffer1

    End sub

     

    • Gidon_Albert's avatar
      Gidon_Albert
      Contributor II
      2 years ago

      Right.... Using Eval(), AddressOf OnEvalDataBuffer generates an unknown error. we're trying to understand why.

  • ckattookaran's avatar
    ckattookaran
    VIP
    2 years ago

    Yes and no, you can create a buffer with existing data only. Now once you have that anchor you can loop through those cells and get stuff from the members that are there in the buffer. You can then create a new buffer and while looping through your anchor you can change the account and other members of the result databuffer cell and save it.

    Not sure if that is what you are looking for.

    • Gidon_Albert's avatar
      Gidon_Albert
      Contributor II
      2 years ago

      Right. So the problem is that the initial data buffer does not generate any cells because there are no cells with data. In other words, the anchor is empty so there is nothing to loop through.

      • JackLacava's avatar
        JackLacava
        Honored Contributor
        2 years ago

        Yes. A different approach, starting from scratch, should work but it's very low level and somewhat tedious. This is a rought start that should work (not tested...

        'create a new PK for each cell, describing the intersection
Dim newPk As New DataBufferCellPk()
newPk.AccountId = api.Members.GetMemberId(dimtype.Account.id, "myacc")
newPk.FlowId = api.Members.GetMemberId(dimtype.Flow.Id, "myflow")
'... set all dimensions ...
' create a new cell with the pk. There are other constructors too.
Dim newCell As New DataBufferCell(newPk, 10.0, DataCellStatus.CreateDataCellStatus(False, False))
' create the buffer, put cell(s) in it
Dim newBuffer As New DataBuffer()
newBuffer.SetCell(si, newCell)
' trigger saving to database
Dim dest As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("")
api.Data.SetDataBuffer(newBuffer, dest)

         

  • ckattookaran's avatar
    ckattookaran
    VIP
    2 years ago

    You need to have an anchor to start. Else you'll have to use MemberScriptAndValue and create data. Keep in mind, you need to take extra steps to make sure that you are not generating unwanted data (watch out for those loops). Another one is you can only write to Forms.