Jump to content
Sign in to follow this  
Justin Close

Get ESQL to return always 2 decimal places (currency)?

Recommended Posts

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.

Edited by Justin Close

Share this post


Link to post
Share on other sites

from Greg Lane (on another forum), I can confirm that this works as stated:

SELECT '$' || COALESCE(CAST(INT(numField) AS VARCHAR),'0') || '.' || SUBSTR(CAST(numField - INT(numField) AS VARCHAR) || '000',2,2)

FROM myTable

the "||" is a way to concatenate text output into one column. It may be beneficial to create a custom function to insert this long string (in the SELECT) into your queries. Just make the 'numfield' the parameter. I also may prepend the '$' or not.

beverly

Share this post


Link to post
Share on other sites

Any disadvantages/advantages to use STRVAL() instead of CAST() here? Would shorten the expression a bit.

otmar 

Share this post


Link to post
Share on other sites

Any disadvantages/advantages to use STRVAL() instead of CAST() here? Would shorten the expression a bit.

otmar 

re-write the query using STRVAL(), run some tests & report back. what I know, I know because I test. :)

beverly

Share this post


Link to post
Share on other sites

You're absolutely right.;) Performance wise there's no difference CAST as VARCHAR and STRVAL seem to do the same, but the SQL-way is 4 times as fast as post processing with a recursive CF.

otmar

  • Like 1

Share this post


Link to post
Share on other sites

for those who wondered, this is how I revised the CAST() to use STRVAL(). NOTE the placement, as it's important:

(yes, I broke the string out to make it easier to understand the 'parts' & you would supply the field not the static value for _num)

 

Let ( 

[ _pre = " '$' " // change as needed

; _num = round( 2345.017; 2) // change to actual field ref & round first

; _query = " SELECT "

& if ( not isempty(_pre); _pre & "||" )

& " STRVAL( COALESCE( INT( " & _num & "  ) , 0 ) ) "

& " || '.' "

& " || SUBSTR( STRVAL( " & _num & " - INT( " & _num & " ) ) || '000', 2, 2 ) "

& " FROM test "

; $result = ExecuteSQL ( _query ; "" ; "" )

]; $result

) // $2345.02

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By carlosnorvik
      HI;
       
      I created this module for currency conversion, variable "pair" = currencies in ISO format (example $ to Taiwan Dollar = "USDTWD"
      String url ="http://download.finance.yahoo.com/d/quotes.csv?s="+pair+"%3DX&f=l1n";
      return new URL(url).getText().trim();
       
      Returned result = 32.8200,"USD/TWD"
      Feel free to use it!
       
      Merry Christmas!
       
    • By llpeters
      Hi All!
      I work in a database that has several currency fields all set that way in US dollars. Maybe 1 out of 10 times I need to create a record where the currency would need to be displayed in euros, pounds, etc. Is there a way to make the fields editable so that I can ovverride the $ symbol only when I need to? Its not worth making the fields general since the majority of the time the value is $. Its also too clumsy to make a drop down box next to each field where you can select the currency symbol because there are MANY fields on the layout that display currency and its already a tightly structured layout. Any other ideas? Thanks! 
    • By db_tragic
      Hi folks,
       
      I have a field which is defined as a 'text' field as it will contain both numbers(currency) and text.
       
      The field is defined by the following calculation:
      Case(IsEmpty(_pk_InvoiceID); "(Not Invoiced)"; Paid - Subtotal) Because this field is defined as a text field (to allow insertion of "(Not Invoiced)" string), I can't use the currency formatter in the Inspector. 
       
      Any suggestions on how I can force a currency format on the numerical values in this field?
       
      TIA
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.