Best Practices for Storing High Value Numeric Fields (Billions+) in Appian Records

Team, good morning!

I’m facing a situation similar to some previous discussions here in the community about how to handle large numbers in Appian.

I’ve seen topics mentioning Appian’s integer limit, where values may end up being converted to infinity, as well as recommendations to store certain values as text. However, my scenario is different.

I’m not dealing with values that will never be used for calculations (such as phone numbers, ID numbers, zip codes, etc.). In my case, I need to store values that may potentially be used in calculations in the future — I don’t know for sure yet (the client hasn’t provided this information), but the possibility exists.

This application will be used by wholesale clients who deal with very large revenue amounts (in the billions+). Because of that, I want to handle this situation as conservatively as possible.

I’m using Oracle, and I defined these fields as NUMBER(15, 2), in appian it gets converted to number (decimal) in the record type.

The issue is:

 If I store the values as text, I lose the ability to perform calculations later.

If I store them as decimal, the Appian record loses precision.

Given this, I would like to know: how would you approach this case?

What would be the best strategy to ensure numeric precision while still allowing for potential future calculations?

Ex1:. Formatting decimal value input below (Loses precision in some cases)

a!localVariables(
  local!label: a!defaultValue(value: ri!label, default: ""),
  local!placeHolder: a!defaultValue(value: ri!placeHolder, default: ""),
  local!readOnly: a!defaultValue(value: ri!readOnly, default: false),
  local!showWhen: a!defaultValue(value: ri!showWhen, default: true),
  local!required: a!defaultValue(value: ri!required, default: false),
  local!novoNumero,
  /*conversão*/
  local!valorMoeda: if(
    a!isNotNullOrEmpty(ri!valor, ),
    a!currency(
      isoCode: "BRL",
      value: ri!valor,
      decimalPlaces: 2,
      showSeparators: true()
    ),
    if(
      a!isNotNullOrEmpty(local!novoNumero),
      a!currency(
        isoCode: "BRL",
        value: ri!valor,
        decimalPlaces: 2,
        showSeparators: true()
      ),
      {}
    )
  ),
  /*validações*/
 
  /*pega o tamanho quando vira expoente*/
  local!tamanhoExpoente:if(
    or(
      a!isNullOrEmpty(ri!valor),
      fn!len(ri!valor) <= 11
      ),
      ri!valor,
      a!localVariables(
        local!base: todecimal(index(split(ri!valor, "e"), 1)),
        local!expoente: tointeger(index(split(ri!valor, "e+"), 2)),
        local!resultado: local!base * power(10, local!expoente),
        local!calc: stripwith(fixed(local!resultado,0), ",-~"),
        local!calc
      )
  ),
  local!tamanho: fn!len(local!tamanhoExpoente) < ri!tamanhoValidation,
  local!onlyNumber: if(
    a!isNullOrEmpty(todecimal(ri!valor)),
    false,
    like(todecimal(ri!valor), "*[0-9]"),
   
  ),
  local!validaTudo: if(
    and(local!tamanho, local!onlyNumber),
    true,
    false,
   
  ),
 
  a!textField(
    label: local!label,
    placeholder: local!placeHolder,
    labelPosition: "ABOVE",
    refreshAfter: "UNFOCUS",
    value: if(
      a!isNullOrEmpty(ri!valor),
      "",
      substitute(local!valorMoeda, "~", "")
    ),
    saveInto: {
      if(
        a!isNullOrEmpty(ri!valor),
        a!save(
          ri!valor,
          if(
            a!isNullOrEmpty(save!value),
            "",
           
            /*ISSO JA ESTA CORRETO*/
            if(
              a!isInText(save!value, ","),
              substitute(substitute(save!value, ".", ""), ",", "."),
              save!value
            ),
          )
        ),
        {
          a!save(local!novoNumero, 1),
          a!save(
            ri!valor,
            if(
              a!isNullOrEmpty(save!value),
              "",
              stripwith(substitute(substitute(save!value, ".", ""), ",", "."), "R$-"),
            )
          )
        }
      ),
     
    },
    validations: if(
      local!validaTudo,
      "",
      a!defaultValue(
        value: ri!errorMessage,
        default: "Só é permitido numeros"
      )
    ),
    required: local!required,
    showWhen: local!showWhen,
    readOnly: local!readOnly
  )
)

Ex2:. Formatting text value input below (Can't perform calculations in the future if I save as text in the DB)

a!localVariables(
  /* 0) Entrada original como texto */
  local!raw: substitute(
    ri!valueText,
    ".",
    ","
  ),
  /* 1) ÚLTIMO separador na string original (varre toda a string) */
  local!posLastComma: max(
    0,
    a!forEach(
      items: enumerate(len(local!raw)) + 1,
      /* len(): texto */
      expression: if(mid(local!raw, fv!item, 1) = ",", fv!item, 0)/* mid(): char 1-based */

    )
  ),
  local!posLastDot: max(
    0,
    a!forEach(
      items: enumerate(len(local!raw)) + 1,
      expression: if(mid(local!raw, fv!item, 1) = ".", fv!item, 0)
    )
  ),
  local!lastSepPos: max(local!posLastComma, local!posLastDot),
  local!hasSeparator: local!lastSepPos > 0,
  /* 2) Prefixo (antes do sep) e sufixo (depois do sep) em TEXTO */
  local!prefixText: if(
    local!hasSeparator,
    left(local!raw, local!lastSepPos - 1),
    local!raw
  ),
  local!suffixText: if(
    local!hasSeparator,
    right(
      local!raw,
      len(local!raw) - local!lastSepPos
    ),
    ""
  ),
  /* 3) Extrair SOMENTE DÍGITOS de prefixo/sufixo (100% nativo) */
  local!prefixDigits: joinarray(
    a!forEach(
      items: enumerate(len(local!prefixText)) + 1,
      expression: if(
        and(
          code(mid(local!prefixText, fv!item, 1)) >= 48,
          /* '0' */
          code(mid(local!prefixText, fv!item, 1)) <= 57/* '9' */

        ),
        mid(local!prefixText, fv!item, 1),
        {}
      )
    ),
    ""
  ),
  local!suffixDigits: joinarray(
    a!forEach(
      items: enumerate(len(local!suffixText)) + 1,
      expression: if(
        and(
          code(mid(local!suffixText, fv!item, 1)) >= 48,
          code(mid(local!suffixText, fv!item, 1)) <= 57
        ),
        mid(local!suffixText, fv!item, 1),
        {}
      )
    ),
    ""
  ),
  /* 4) Centavos (completa à direita / trunca) e Inteiro bruto */
  local!suffixLen: len(local!suffixDigits),
  local!decRaw: if(
    not(local!hasSeparator),
    "00",
    if(
      local!suffixLen = 0,
      "00",
      if(
        local!suffixLen = 1,
        substitute(padright(local!suffixDigits, 2), " ", "0"),
        /* padright 2 args; troca espaços por '0' */
        left(local!suffixDigits, 2)
      )
    )
  ),
  local!dec: substitute(local!decRaw, " ", "0"),
  /* Inteiro bruto (se não há sep: pegar dígitos do texto inteiro) */
  local!intRawDigits: if(
    not(local!hasSeparator),
    joinarray(
      a!forEach(
        items: enumerate(len(local!raw)) + 1,
        expression: if(
          and(
            code(mid(local!raw, fv!item, 1)) >= 48,
            code(mid(local!raw, fv!item, 1)) <= 57
          ),
          mid(local!raw, fv!item, 1),
          {}
        )
      ),
      ""
    ),
    local!prefixDigits
  ),
  /* 5) Remover ZEROS À ESQUERDA da parte inteira (se tudo zero -> "0") */
  local!intLenRaw: len(local!intRawDigits),
  local!intCharsRaw: a!forEach(
    items: enumerate(local!intLenRaw) + 1,
    expression: mid(local!intRawDigits, fv!item, 1)
  ),
  local!isNonZero: a!forEach(
    items: local!intCharsRaw,
    expression: fv!item <> "0"
  ),
  local!firstNonZeroIdx: index(where(local!isNonZero), 1, 0),
  local!intNormalized: if(
    or(
      local!firstNonZeroIdx = 0,
      local!intRawDigits = ""
    ),
    "0",
    right(
      local!intRawDigits,
      local!intLenRaw - local!firstNonZeroIdx + 1
    )
  ),
  /* 6) Agrupar milhares (sem reverse) */
  local!intLen: len(local!intNormalized),
  local!intGrouped: joinarray(
    a!forEach(
      items: enumerate(local!intLen) + 1,
      expression: if(
        and(
          fv!item > 1,
          mod(local!intLen - fv!item + 1, 3) = 0
        ),
        "." & mid(local!intNormalized, fv!item, 1),
        mid(local!intNormalized, fv!item, 1)
      )
    ),
    ""
  ),
  /* 7) Montagem final (texto) */
  if(
    ri!spaceAfterSymbol,
    "R$ " & local!intGrouped & "," & local!dec,
    "R$" & local!intGrouped & "," & local!dec
  )
)

Thank you in advance for your attention!