Forum Discussion

AndreaF's avatar
AndreaF
Contributor III
2 years ago

SetDataCell only work on Origin=Forms where data on the Origin=Import member exist

Hi all,

the SetDataCell command I am trying to use is behaving in a strange way and I would like to check if that is a bug, if it is by design, or if I may be doing something wrong.

I am trying to use the SetDataCell command to clear data on the Origin=Forms. The command looks like this (it is part of a custom calc rule):

'Clear data on the Form member
api.Data.SetDataCell("Cb#Main:E#" & MainPovEntity & ":S#" & MainPovScenario & ":A#AC410000:U1#None:U2#None:U3#" & UD3Name & ":U4#None:U5#Local_GAAP:U6#None:U7#None:U8#None:O#Forms", 0, True)

Now, if the UD3 member which I am trying to clear has data on the Origin=Import member, the Forms member is correctly set to zero by the rule. However, if the UD3 member has no data on the Origin=Import member (on that specific Pov intersection defined by the rule above), the Forms member retains its value.

 

Anyone else experienced this behavior? Thank you

Finance
  • MikeG's avatar
    MikeG
    2 years ago

    Can you give this a try and check the Error Log?  This is to capture the before and after amount in the data cell you are trying to clear.  Paste this in to the IsBaseEntity and IsLocalCurrency IF THEN condition.

    Dim before As Decimal = api.Data.GetDataCell("Cb#Main:C#Local:V#YTD:I#None:F#NoneE#" & MainPovEntity & ":S#" & MainPovScenario & ":A#AC410000:U1#None:U2#None:U3#" & UD3Name & ":U4#None:U5#Local_GAAP:U6#None:U7#None:U8#None:O#Forms").CellAmount

    'SetDataCell Command here

    Dim after As Decimal = api.Data.GetDataCell("Cb#Main:C#Local:V#YTD:I#None:F#NoneE#" & MainPovEntity & ":S#" & MainPovScenario & ":A#AC410000:U1#None:U2#None:U3#" & UD3Name & ":U4#None:U5#Local_GAAP:U6#None:U7#None:U8#None:O#Forms").CellAmount
    BRapi.ErrorLog.LogMessage(si, "SetCell Before/After check: Et= " & MainPovEntity & ", BeforeAmt= " & before & ", AfterAmt= " & after)

  • MikeG's avatar
    MikeG
    Contributor III
    2 years ago

    Can you give this a try and check the Error Log?  This is to capture the before and after amount in the data cell you are trying to clear.  Paste this in to the IsBaseEntity and IsLocalCurrency IF THEN condition.

    Dim before As Decimal = api.Data.GetDataCell("Cb#Main:C#Local:V#YTD:I#None:F#NoneE#" & MainPovEntity & ":S#" & MainPovScenario & ":A#AC410000:U1#None:U2#None:U3#" & UD3Name & ":U4#None:U5#Local_GAAP:U6#None:U7#None:U8#None:O#Forms").CellAmount

    'SetDataCell Command here

    Dim after As Decimal = api.Data.GetDataCell("Cb#Main:C#Local:V#YTD:I#None:F#NoneE#" & MainPovEntity & ":S#" & MainPovScenario & ":A#AC410000:U1#None:U2#None:U3#" & UD3Name & ":U4#None:U5#Local_GAAP:U6#None:U7#None:U8#None:O#Forms").CellAmount
    BRapi.ErrorLog.LogMessage(si, "SetCell Before/After check: Et= " & MainPovEntity & ", BeforeAmt= " & before & ", AfterAmt= " & after)

    • AndreaF's avatar
      AndreaF
      Contributor III
      2 years ago

      Thank you, this really helped me a lot! The SetDataCell command was enclosed in a loop that was lopping a data buffer, like this:

      For Each sourceCell As DataBufferCell In targetDataBuffer.DataBufferCells.Values
         If Not sourceCell Is Nothing And sourceCell.CellAmount <> 0 Then
            'Clear data on the Form member
            api.Data.SetDataCell(...)
            'Calculate the Import member
            api.Data.Calculate(...)
         End If
      Next

      With your error logging code I have been able to verify the data buffer was not including the UD3 I was trying to clear.

  • MikeG's avatar
    MikeG
    Contributor III
    2 years ago

    I have not experienced this behavior and could try to replicate it for you in my environment.  As a best practice I would include the C#Local and either V#Periodic or V#YTD in the api.Data.SetDataCell line.  Maybe also add I#None so as to not assume it is grabbing the IC Dimension member I#Top from the session POV.

    • AndreaF's avatar
      AndreaF
      Contributor III
      2 years ago

      Thank you for your reply, I've added the Consolidation, View, Intercompany and Flow (C#Local:V#YTD:I#None:F#None) to the expression, but with the same outcome.

      (On a side note, the command is within the very common "If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyforEntity())) Then" condition, so the consolidation member in the expression should not be needed, but I added it just to be sure anyway for the test).

  • MikeG's avatar
    MikeG
    Contributor III
    2 years ago

    AndreaF Take off the IsLocalCurrency condition statement.  You can't use SetDataCell on C#Local member.  You may need a variable to get the local currency on the Entity, so the SetDataCell command for the Cons dimension will ultimately be C#USD (or likewise).