Question

How to format long numbers when it comes from an integration

Forum|Forum|4 years ago
December 10, 2021
5 replies
0 views

I'm getting this kind of numbers when i get data from an integration i want to convert it into numbers to make an excel file and be able of edit the numbers

when i try converting todecimal() or round(val,2) any of this conversion methods work what can i do in this case ?

stefanhelzle0001

Brainy

What is the code you use and what is the output you are seeing?

felixrAuthor

*that is the code i use *

if(
or( isnull(ri!month), isnull(ri!month))
,

{},

a!localVariables(

local!data: a!fromJson(xmltojson( rule!BBO_quickSystemGet(
querys: "select ExchangeRate,CurrencyRef, BillAddr,DocNumber,TxnDate,Balance,DueDate,TotalAmt,TxnTaxDetail,Line from Invoice where TxnDate >= '"&text(date(ri!year, ri!month, 1),"yyyy-MM-dd")&"'
and TxnDate <= '" & text(date(ri!year, ri!month, daysinmonth( ri!month,ri!year)),"yyyy-MM-dd")&"' order by TxnDate asc",
url: "/v3/company/"&cons!BBO_QUICK_COMPANY_ID&"/query?&minorversion=61").result.body)).IntuitResponse.QueryResponse

,

if(or(count(local!data)<1,local!data = ""),

{}
,

a!forEach(items: local!data.Invoice,

expression:

a!localVariables(
local!montoTotal:
if( index(index(fv!item,"CurrencyRef",null()),"content",null())= "USD",
(

( todecimal(reduce(fn!sum, 0,
{
a!forEach( items:index(fv!item, "Line", null()) ,
expression:

if(index(fv!item,"DetailType","") ="SalesItemLineDetail",
if(todecimal(index(fv!item,"Amount",0))<0,
{}
,
sum(todecimal(index(fv!item,"Amount",0)) )
)

,
{}
)

)})))* todecimal(index(fv!item,"ExchangeRate",1)) )
,
todecimal(reduce(fn!sum, 0,
{
a!forEach( items:index(fv!item, "Line", null()) ,
expression:

if(index(fv!item,"DetailType","") ="SalesItemLineDetail",
if(todecimal(index(fv!item,"Amount",0))<0,
{}
,
sum(todecimal(index(fv!item,"Amount",0)) )
)
,
{}
)

)}))

)
,
local!balance: if( index(index(fv!item,"CurrencyRef",null()),"content",null())= "USD"
,( todecimal(index(fv!item,"Balance",0))* todecimal(index(fv!item,"ExchangeRate",1))


)
,

todecimal(index( fv!item ,"Balance",0))

)

,

local!itebisTotal:
if( index(index(fv!item,"CurrencyRef",null()),"content",null())= "USD",

todecimal(index(index(fv!item,"TxnTaxDetail",null()),"TotalTax",0))* todecimal(index(fv!item,"ExchangeRate",1)) ,
todecimal(index(index(fv!item,"TxnTaxDetail",null()),"TotalTax",0))
)
,
local!itebisretenido:

if( index(index(fv!item,"CurrencyRef",null()),"content",null())= "USD",

( todecimal(abs( index(reject(rule!BBO_isMinorThenZero,index( index(index(fv!item,"Line",null()),"SalesItemLineDetail",null()),"UnitPrice",0) ),1,0)))
*todecimal(index(fv!item,"ExchangeRate",1)) )


,

abs( index(reject(rule!BBO_isMinorThenZero,index( index(index(fv!item,"Line",null()),"SalesItemLineDetail",null()),"UnitPrice",0) ),1,0))



)

,
cast('type!{urn:com:appian:types}BBO_invoicesReportdatatypes',
{


RNC_o_cedula:
if(isnull(index(index(fv!item,"BillAddr",null()),"Line2",null())),
null()
,
substitute(substitute(substitute(index(index(fv!item,"BillAddr",null()),"Line2",null()),"RNC",""),"CED",""),":","")
),
Tipo_Id:
if( isnull(index(index(fv!item,"BillAddr",null()),"Line2",null())),
3
,

if(
left(index(index(fv!item,"BillAddr",null()),"Line2",null()),3)= "RNC",

1,

if(left(index(index(fv!item,"BillAddr",null()),"Line2",null()),3)= "CED",

2,3)
)

)

, NCF: index(fv!item,"DocNumber",null()),
NCF_Modificado:null(),
Tipo_Ingresos:1,
Fecha_Comprobante: index(fv!item,"TxnDate",null()),
Fecha_Retencion:index(fv!item,"DueDate",null()),

Monto_Facturado: todecimal(dollar(local!montoTotal) )

,
ITBIS_Facturado:todecimal(round(dollar(local!itebisTotal),2) ) ,
ITBIS_Retenido:local!itebisretenido
,
Cheque_Transferencia_Deposito: sum(local!montoTotal , local!itebisTotal)-if(local!balance=0,
local!balance
,
local!balance +local!itebisretenido
) ,
Venta_a_Credito:
if(local!balance=0,
local!balance
,
local!balance + local!itebisretenido
)


}) )

))

))

*this is the output*

mikes0011

Brainy

Just as an aside, your code will look a lot better if you use an "Insert Code" box - font will be monospaced, indentation will be preserved, and it won't take up several pages. Plus I've found selecting the "Java" language option from the drop-down selections applies somewhat-suitable context highlighting for SAIL/Appian code.

e.g.

if(
  or(isnull(ri!month), isnull(ri!month)),
  {},
  a!localVariables(
    local!data: a!fromJson(
      xmltojson(
        rule!BBO_quickSystemGet(
          querys: "select ExchangeRate,CurrencyRef, BillAddr,DocNumber,TxnDate,Balance,DueDate,TotalAmt,TxnTaxDetail,Line from Invoice where TxnDate >= '" & text(date(ri!year, ri!month, 1), "yyyy-MM-dd") & "'
and TxnDate <= '" & text(
            date(
              ri!year,
              ri!month,
              daysinmonth(ri!month, ri!year)
            ),
            "yyyy-MM-dd"
          ) & "' order by TxnDate asc",
          url: "/v3/company/" & cons!BBO_QUICK_COMPANY_ID & "/query?&minorversion=61"
        ).result.body
      )
    ).IntuitResponse.QueryResponse,
    if(
      or(count(local!data) < 1, local!data = ""),
      {},
      a!forEach(
        items: local!data.Invoice,
        expression: a!localVariables(
          local!montoTotal: if(
            index(
              index(fv!item, "CurrencyRef", null()),
              "content",
              null()
            ) = "USD",
            (
              (
                todecimal(
                  reduce(
                    fn!sum,
                    0,
                    {
                      a!forEach(
                        items: index(fv!item, "Line", null()),
                        expression: if(
                          index(fv!item, "DetailType", "") = "SalesItemLineDetail",
                          if(
                            todecimal(index(fv!item, "Amount", 0)) < 0,
                            {},
                            sum(todecimal(index(fv!item, "Amount", 0)))
                          ),
                          {}
                        )
                      )
                    }
                  )
                )
              ) * todecimal(index(fv!item, "ExchangeRate", 1))
            ),
            todecimal(
              reduce(
                fn!sum,
                0,
                {
                  a!forEach(
                    items: index(fv!item, "Line", null()),
                    expression: if(
                      index(fv!item, "DetailType", "") = "SalesItemLineDetail",
                      if(
                        todecimal(index(fv!item, "Amount", 0)) < 0,
                        {},
                        sum(todecimal(index(fv!item, "Amount", 0)))
                      ),
                      {}
                    )
                  )
                }
              )
            )
          ),
          
          local!balance: if(
            index(
              index(fv!item, "CurrencyRef", null()),
              "content",
              null()
            ) = "USD",
            (
              todecimal(index(fv!item, "Balance", 0)) * todecimal(index(fv!item, "ExchangeRate", 1))
            ),
            todecimal(index(fv!item, "Balance", 0))
          ),
          local!itebisTotal: if(
            index(
              index(fv!item, "CurrencyRef", null()),
              "content",
              null()
            ) = "USD",
            todecimal(
              index(
                index(fv!item, "TxnTaxDetail", null()),
                "TotalTax",
                0
              )
            ) * todecimal(index(fv!item, "ExchangeRate", 1)),
            todecimal(
              index(
                index(fv!item, "TxnTaxDetail", null()),
                "TotalTax",
                0
              )
            )
          ),
          local!itebisretenido: if(
            index(
              index(fv!item, "CurrencyRef", null()),
              "content",
              null()
            ) = "USD",
            (
              todecimal(
                abs(
                  index(
                    reject(
                      rule!BBO_isMinorThenZero,
                      index(
                        index(
                          index(fv!item, "Line", null()),
                          "SalesItemLineDetail",
                          null()
                        ),
                        "UnitPrice",
                        0
                      )
                    ),
                    1,
                    0
                  )
                )
              ) * todecimal(index(fv!item, "ExchangeRate", 1))
            ),
            abs(
              index(
                reject(
                  rule!BBO_isMinorThenZero,
                  index(
                    index(
                      index(fv!item, "Line", null()),
                      "SalesItemLineDetail",
                      null()
                    ),
                    "UnitPrice",
                    0
                  )
                ),
                1,
                0
              )
            )
          ),
          
          
          cast(
            'type!{urn:com:appian:types}BBO_invoicesReportdatatypes',
            {
              RNC_o_cedula: if(
                isnull(
                  index(
                    index(fv!item, "BillAddr", null()),
                    "Line2",
                    null()
                  )
                ),
                null(),
                substitute(
                  substitute(
                    substitute(
                      index(
                        index(fv!item, "BillAddr", null()),
                        "Line2",
                        null()
                      ),
                      "RNC",
                      ""
                    ),
                    "CED",
                    ""
                  ),
                  ":",
                  ""
                )
              ),
              Tipo_Id: if(
                isnull(
                  index(
                    index(fv!item, "BillAddr", null()),
                    "Line2",
                    null()
                  )
                ),
                3,
                if(
                  left(
                    index(
                      index(fv!item, "BillAddr", null()),
                      "Line2",
                      null()
                    ),
                    3
                  ) = "RNC",
                  1,
                  if(
                    left(
                      index(
                        index(fv!item, "BillAddr", null()),
                        "Line2",
                        null()
                      ),
                      3
                    ) = "CED",
                    2,
                    3
                  )
                )
              ),
              NCF: index(fv!item, "DocNumber", null()),
              NCF_Modificado: null(),
              Tipo_Ingresos: 1,
              Fecha_Comprobante: index(fv!item, "TxnDate", null()),
              Fecha_Retencion: index(fv!item, "DueDate", null()),
              Monto_Facturado: todecimal(dollar(local!montoTotal)),
              ITBIS_Facturado: todecimal(round(dollar(local!itebisTotal), 2)),
              ITBIS_Retenido: local!itebisretenido,
              Cheque_Transferencia_Deposito: sum(local!montoTotal, local!itebisTotal) - if(
                local!balance = 0,
                local!balance,
                local!balance + local!itebisretenido
              ),
              Venta_a_Credito: if(
                local!balance = 0,
                local!balance,
                local!balance + local!itebisretenido
              )
            }
          )
        )
      )
    )
  )
)

felixrAuthor

Thanks Sr.

Sign up

Login with SSO

Login to the community

Login with SSO