1 2 Previous Next 19 Replies Latest reply on Sep 8, 2015 9:42 AM by okramis

    Getting ESQL to always return 2 decimal places (currency)

    justinc

      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.

        • 1. Re: Getting ESQL to always return 2 decimal places (currency)
          mikebeargie

          You need to use CAST

           

          something like:

          SELECT payment FROM table CAST(payment as decimal(19,2) )

          • 2. Re: Getting ESQL to always return 2 decimal places (currency)
            coherentkris

            Perhaps you could CAST as money if your using MS Sql Server as the ess type.

            guarantee/warranty not offered on this advice

            • 3. Re: Getting ESQL to always return 2 decimal places (currency)
              crw030

              @justinc.

               

                I see the same behavior you are seeing getting data from SQL 2008R2. My view definition for COST_AMT is numeric(18,6), and it displays properly querying via the ODBC DSN connection used by FM if I use standard ODBC Query tools.

               

              Results from SQL SSMS  /  WinSQL via ODBC:

              111.600000

              2240.250000

              1824.600000

               

                But I get the following in Filemaker (similar to what you are seeing):

              111.6

              2240.25

              1824.6

               

                So I thought I would check how Filemaker handles it's own numeric fields when ExecuteSQL is used and the behavior is the same, it eliminates everything after the last non-zero digit.  Incidentally if you have a number like 111.600001 it would probably work just fine.

               

                 I cannot find any way to force FM to display additional decimal places even when I query a local FM table using ExecuteSQL that doesn't involve adding 0's where they need to be using a custom function or similar.

               

                My only guess is this may normally be handled via UI formatting on the display controls most of the time.  I tried CAST( as numeric(18,6) in the executeSQL, and I also tried CAST(CAST( as numeric(18,6)) as varchar) to see if I could trick it into treating it as a string once I had all the digits I wanted.

              • 4. Re: Getting ESQL to always return 2 decimal places (currency)
                mikebeargie

                jrenfrew had tested CAST in ExecuteSQL() and found that it worked. Per The Missing FM 12 ExecuteSQL Reference | FileMakerHacks

                 

                Sorry, didn't see that your second chunk of code already contained a CAST.

                • 5. Re: Getting ESQL to always return 2 decimal places (currency)
                  justinc

                  Have tried using CAST() (see example) and it hasn't been working.  But I didn't use it in the place you showed, but up in the 'SELECT' area.  I will give it a shot after the 'FROM' clause.

                  • 6. Re: Getting ESQL to always return 2 decimal places (currency)
                    greglane

                    It's a bit tedious, but for non-negative values I usually use something like:


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

                    FROM myTable

                    • 7. Re: Getting ESQL to always return 2 decimal places (currency)
                      justinc

                      Tedious is right!  I love it...and hate it. 

                       

                      I will have to give that one a shot.  That might make doing it in FileMaker a viable option. 

                      • 8. Re: Getting ESQL to always return 2 decimal places (currency)
                        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.

                         

                        Because this isn't working, I use a set of CFs to post process the ESQL-result - i.e. use it like this:

                         

                        Let ( [

                        _delimiter = " | "

                        ; _valuelist = ExecuteSQL ( "

                         

                        SELECT Type, ID, SUM(Field1), SUM(Field2)

                        FROM myTable

                        WHERE Condition=?

                        GROUP BY Type, ID

                         

                        " ; _delimiter ; "" ; "myCondition" )

                         

                        ] ;


                        SetLeadingZero (

                        RoundValuesMultiColumn ( _valuelist ; "3¶4" ; 2 ; _delimiter )

                        ; _delimiter )

                         

                        )

                         

                        =============================================

                        returns rounded and position fixed values of selcted column

                        =============================================

                        /*

                        RoundValues ( _valuelist; _column; _precision; _delimiter )

                         

                        by Otmar Kramis, Hochschule Luzern

                         

                        Params

                        _valuelist: list returned by the List ( ) - function or EcecuteSQL ( ) with the default line delimiter

                        _column: the column to round

                        _precision: number of post-decimal positions

                        _delimiter: column delimiter of the list

                        */

                         

                        Let ( [

                         

                        n = ValueCount ( _valuelist ) ;

                        row = Substitute ( GetValue ( _valuelist ; 1 ); _delimiter ; "¶" ) ;

                        nrow = ValueCount ( row ) ;

                        value = GetValue ( row ; _column ) ;

                        newvalue = If ( value = "?" ; 0 ; Round ( value * ( 10 ^ _precision ) ; 0 ) ) ;

                        neg = If ( newvalue < 0 ; "-" ; "" ) ;

                        newvalue = Abs ( newvalue ) ;

                        newvalue = If ( newvalue = 0 ; "" ;  neg  & Left ( newvalue ; Length ( newvalue ) - _precision ) & "." & Right ( Left ( "0000000000000000" ; _precision - 1 ) & newvalue ; _precision ) ) ;

                        newrow = LeftValues ( row ; _column - 1 ) & newvalue & ¶ &  RightValues ( row ; nrow - _column ) ;

                        newrow = Left ( newrow ; Length ( newrow ) - 1 )


                        ] ;

                         

                        Substitute ( newrow ; ["¶" ; _delimiter] )

                        &

                        Case ( n > 1 ; ¶ & RoundValues ( RightValues ( _valuelist ; n - 1 ) ; _column ; _precision ; _delimiter ) )

                         

                        )

                         

                        ==============================

                        calls RoundValues() for a list of columns

                        ==============================

                        /*

                        RoundValuesMultiColumn ( _valuelist; _columnlist; _precision; _delimiter )

                         

                        by Otmar Kramis, Hochschule Luzern

                         

                        dependency: RoundValues ( _valuelist; _column; _precision; _delimiter )

                         

                        Params

                        _valuelist: list returned by the List ( ) - function or EcecuteSQL ( ) with the default line delimiter

                        _columnlist: the columns to round, i.e. "2¶4¶5"

                        _precision: number of post-decimal positions

                        _delimiter: column delimiter of the list

                         

                        */

                         

                        Let ( [

                        n = ValueCount ( _columnlist ) ;

                        column = GetValue ( _columnlist ; 1 ) ;

                        valuelist = RoundValues ( _valuelist ; column ; _precision ; _delimiter )

                        ] ;

                         

                        Case ( n > 1 ; RoundValuesMultiColumn ( valuelist ; RightValues ( _columnlist ; n - 1 ) ; _precision ; _delimiter ) ; valuelist )

                         

                        )

                         

                         

                        ==================================

                        fixes the missing leading zero .5 --> 0.5

                        ==================================

                         

                        /*

                        SetLeadingZero ( _valuelist; _delimiter )

                         

                        by Otmar Kramis, Hochschule Luzern

                         

                        Params

                        _valuelist: list returned by the List ( ) - function or EcecuteSQL ( ) with the default line delimiter

                        _delimiter: column delimiter of the list

                         

                        */

                         

                        Let ( [

                         

                        list = Substitute ( _valuelist ; ["-." ; "-0."] ; [_delimiter & "." ; _delimiter & "0."] ; ["¶" & "." ; "¶" & "0."] )

                         

                        ] ;

                         

                        Substitute ( Left ( list ; 1 ) ; "." ; "0." ) & Right ( list ; Length ( list ) - 1 )

                         

                        )

                        • 9. Re: Getting ESQL to always return 2 decimal places (currency)
                          wimdecorte

                          Any significant difference in ExecuteSQL() execution speed of this query vs the plain SELECT?

                           

                          Wondering at what point it would make sense to post-process by looping over the result from the plain vanilla SELECT...

                          • 10. Re: Getting ESQL to always return 2 decimal places (currency)
                            okramis

                            I use it only on relatively small data sets < 100 rows for reports over virtual list, mostly < 5 rows for dashboard presentation. There I didn't see a remarkable lag. As my sql-queries are allready quite complicated I prefere the post-processing over the not so easy readable sql string operations.

                            But performance tests would have to be done yet...

                            • 11. Re: Getting ESQL to always return 2 decimal places (currency)
                              user19752

                              If the sql result is break into fields (in virtual list), you also can use field formatting on layout. Root quester use it in merge variable. If the number of result list is fixed or at least limited, using variables to apply number format to merge variable, but...

                              • 12. Re: Getting ESQL to always return 2 decimal places (currency)
                                greglane

                                While I haven't tested performance objectively, my experience has been that complex column expressions like this are not too bad with stored values. It could be much more expensive when used with unstored values or in a logical expression in a WHERE or HAVING clause.

                                 

                                Having said that, I've argued for a long time that if performance is the question, the ExecuteSQL() function is probably not the answer.

                                 

                                Here's another approach that I showed at DevCon a couple of years ago. The result of a properly constructed ExecuteSQL() function can be passed to Evaluate(). This effectively allows you to use any FileMaker function (including custom functions and plugins) within a column expression in an ExecuteSQL() SELECT.

                                 

                                Evaluate(ExecuteSQL(

                                 

                                "SELECT '\"<tr><td>' || title, '\" & AddDollarFormat(' || CAST(avgGrossPay AS varchar) || ') & \"</td></tr>\" &'

                                FROM job_stats WHERE avgGrossPayRank <=10

                                ORDER BY avgGrossPayRank";

                                 

                                "</td><td>"; ¶) & "\"\"")

                                 

                                In this example, the result of the ExecuteSQL() function before the Evaluate would look like:

                                 

                                "<tr><td>MEDICAL DIR</td><td>" & AddDollarFormat(155013) & "</td></tr>" &

                                "<tr><td>COMMISSIONER</td><td>" & AddDollarFormat(144040) & "</td></tr>" &

                                "<tr><td>DIR, DEPT OF TRANSPORTATION</td><td>" & AddDollarFormat(124853) & "</td></tr>" &

                                "<tr><td>COUNSEL TO THE GOVERNOR</td><td>" & AddDollarFormat(122810) & "</td></tr>" &

                                "<tr><td>STAFF PHYSICIAN SPECIALIST</td><td>" & AddDollarFormat(121496) & "</td></tr>" &

                                "<tr><td>SR PSYCHIATRIST</td><td>" & AddDollarFormat(119790) & "</td></tr>" &

                                "<tr><td>CRRD COUNSEL</td><td>" & AddDollarFormat(119271) & "</td></tr>" &

                                "<tr><td>APPELLATE JUDGE</td><td>" & AddDollarFormat(117647) & "</td></tr>" &

                                "<tr><td>CLINICAL DIRECTOR II PSY</td><td>" & AddDollarFormat(116315) & "</td></tr>" &

                                "<tr><td>MEDICAL SPEC II</td><td>" & AddDollarFormat(114772) & "</td></tr>" &""

                                 

                                ...and the final result would be:

                                 

                                <tr><td>MEDICAL DIR</td><td>$155,013.00</td></tr>

                                <tr><td>COMMISSIONER</td><td>$144,040.00</td></tr>

                                <tr><td>DIR, DEPT OF TRANSPORTATION</td><td>$124,853.00</td></tr>

                                <tr><td>COUNSEL TO THE GOVERNOR</td><td>$122,810.00</td></tr>

                                <tr><td>STAFF PHYSICIAN SPECIALIST</td><td>$121,496.00</td></tr>

                                <tr><td>SR PSYCHIATRIST</td><td>$119,790.00</td></tr>

                                <tr><td>CRRD COUNSEL</td><td>$119,271.00</td></tr>

                                <tr><td>APPELLATE JUDGE</td><td>$117,647.00</td></tr>

                                <tr><td>CLINICAL DIRECTOR II PSY</td><td>$116,315.00</td></tr>

                                <tr><td>MEDICAL SPEC II</td><td>$114,772.00</td></tr>

                                • 13. Re: Getting ESQL to always return 2 decimal places (currency)
                                  crw030

                                  If you decide to go the Filemaker CF route, one I have used (primarily in English databases) with good result is:  FileMaker Custom Function:FormatNumberAsText ( number ; currency ; separator ; decimal ; precision )  from briandunning.com

                                   

                                  Bear in mind that although you may be doing significant processing using a CF, it is being offloaded to the client, which in a multi-user environment may be desirable.  There may not be a silver bullet for every situation - find a couple options and test them all carefully for performance implications, preferably simulating near-real-world-as-possible configuration.

                                   

                                    I know (personally speaking) something that may be fast on local FM database might not perform as well for a hosted file serving a WAN user, and something that may work in testing with 3-4 users might suddenly perform poorly on a more loaded production environment serving 30, 50 or 100 clients.

                                   

                                    My personal experience with ExecuteSQL hasn't been that it improves performance so much as it can be used to greatly simplify the Relationship diagram or handle an infrequently needed complex relationship that would otherwise just be clutter on the graph.

                                  • 14. Re: Getting ESQL to always return 2 decimal places (currency)
                                    justinc

                                    I don't know about this specific situation and ESQL code, but I do know that I tested an ESQL-built string object vs. a FM built object and the ESQL was much faster.  I want to say...50%?  I couldn't find any actual data results.  And the problems could have been completely in how I wrote the FM code. 

                                     

                                    In that case, I was building a JSON object from a bunch of results.  In the FM-based code the ESQL just returned the list of element data I was looking to put into the object.  Then the structure of the JSON datatype was built using all FM text functions and filling in the data from the returned ESQL data set.

                                     

                                    In the ESQL version I built the JSON structure directly inside the ESQL call using concatentation of raw string elements with the data being retrieved.  I wasn't doing anything other than concatenation, so that might have an affect.

                                     

                                    Whatever it was, the ESQL was much faster at it.  But...that doesn't necessarily speak to this scenario.

                                    1 2 Previous Next