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 ?
Discussion posts and replies are publicly visible
What is the code you use and what is the output you are seeing?
*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*
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 ) } ) ) ) ) ) )
Thanks Sr.
OK. That's a ton of hard to understand code ... can you give us a more manageable example?
This snippet is weird:
Monto_Facturado: todecimal(dollar(local!montoTotal)),
You take the value, turn it into a string adding a "$" character, just to parse it back into a decimal.
BTW, this creates a flawless decimal for me.
todecimal("1234.5678")