AnsweredAssumed Answered

Getting ESQL to always return 2 decimal places (currency)

Question asked by justinc on Aug 17, 2015
Latest reply on Sep 8, 2015 by okramis

I'm having a bit of an issue with an ESQL query.  I would like it to always return 2 decimal places on the value retrieved.  This is a currency field, and "$45.5" looks weird.  I am generating a list of payments to displayed (via a global variable in the UI) to the user, so it should look money-like.  It's a simple 'here's what you currently have set up' kind of display.

 

So here's the basic query (I believe folks here are familiar with the GFN() and GTN() functions - they are just a way to robustify the query in FileMaker):

ExecuteSQL (
"SELECT '$' || " & GFN ( Payments::Amount ) &
" FROM " & GTN ( Payments::aaPaymentUUID ) &
" WHERE " & GFN ( Payments::aLineItemID_fk ) & " = ? "
; "" ; "" ; $ID )

 

I have also tried various versions of this without luck (using "numeric(10,2)", or "decimal(10,2)"):

ExecuteSQL ( 
"SELECT '$' || CAST ( " & GFN ( Payments::Amount ) & " as decimal (5,2) )" &
" FROM " & GTN ( Payments::aaPaymentUUID ) &
" WHERE " & GFN ( Payments::aLineItemID_fk ) & " = ? "
; "" ; "" ; $ID )

 

But I always get responses like (yes, they should be the same - it's an equal payment calculator, so the last one might be different):

$125.5 
$125.5 
$125.5

When I would like it to be:

$125.50 
$125.50 
$125.50

Anyone have a quick answer as to how to get each record value to have 2 decimal places after it?

 

I have shied away from doing in FileMaker string manipulation because I thought the ESQL fix would be easy; and it would save some steps in the script.  But maybe it's easier to do it in FM and just reprocess the whole list.

Outcomes