Forum Discussion

AndreaF's avatar
AndreaF
Contributor III
2 years ago

Using a variable in the calculate string formula

Hi, I am in a custom business rule.   I have a calculate formula like the following one, which works fine: api.Data.Calculate("A#Sales1 = A#Sales2 + 50”)   Now let say I want to declare a varia...
Finance
  • JackLacava's avatar
    JackLacava
    2 years ago

    The safest approach is this:

    Api.Data.FormulaVariables.SetDecimalVariable("Growth", 50)

    API.data.Calculate("A#Sales1 = A#Sales2 + $Growth”)

    This approach avoids possible issues with converting numbers to string - when the user executing it has a different Culture, or when decimals get truncated (hence losing precision). There is a blog post discussing it as part of internationalising an application.

DanielWillis's avatar
DanielWillis
Valued Contributor

Hey Jack, just wanted to understand how this issue occurs a little more.

Are you saying

- BRs get executed in the OneStream culture setting of the user

- So when Growth gets converted to a string as in Robb's example then it will convert a decimal to a string in the format of the user, so if user is French and Growth is 50.123/50,123 it will result in something like API.data.Calculate("A#Sales1 = A#Sales2 + 50,123”)? Which wouldn't work because OneStream would be expecting API.data.Calculate("A#Sales1 = A#Sales2 + 50.123”)?

JackLacava's avatar
JackLacava
Honored Contributor
2 years ago

Formulas expect numbers formatted in Invariant Culture (basically US), but in some contexts the process is actually executed under the user's own Culture, resulting in the behaviour you describe.

In addition, even if you convert from number to string in Invariant, there is a significant chance that numbers will be truncated, losing precision.

So the best approach is to never convert Decimals to String at all, and to assign them to a Formula Variable instead.

  • ckattookaran's avatar
    ckattookaran
    VIP
    2 years ago

    I thought there were a few methods that does this in culture invariant way, the XFToString ones? 

    • JackLacava's avatar
      JackLacava
      Honored Contributor
      2 years ago

      There are, but they might lose precision on long numbers, as digits could be truncated (or so I'm told).