4 Replies Latest reply on Sep 23, 2013 10:57 AM by greglane

    SQL Query: Tab delimited and dollar amounts

    Diver

      Playing with basic SQL in FileMaker, I have it return the data I want but not in the format I want.

       

      The first two lines in my script's "Set field" are:

       

      ExecuteSQL ("SELECT InvoiceNum, OrderDate, TotalAmount

      FROM TestTable

       

      This returns the proper data but formatted like:

       

      12345,2013-09-22,400.1

      12346,2013-09-22,299

      12347,2013-09-22,199.23

       

      I am not sure how to have it return a tab delimited format and use proper formatting for dollar amounts with 2 decimal places, as shown below:

       

      12345 2013-09-22 400.10

      12346 2013-09-22 299.00

      12347 2013-09-22 199.23

       

      I have experimented with || char(9) and ", " , " " and have had no success with the delimination. I have no clue on how to do the 2 decimal point accuracy.

       

      I welcome any direction or examples.

        • 1. Re: SQL Query: Tab delimited and dollar amounts
          taylorsharpe

          Try:

           

          ExecuteSQL ( "SELECT InvoiceNum, OrderDAte, TotalAmount FROM TestTable" ; " " ; "¶" )

           

           

          Note to add a tab in the field separate between the quotes, you have to hold down the option key while pressing tab on a Mac and probably the control key on Windows. 

          • 2. Re: SQL Query: Tab delimited and dollar amounts
            Diver

            Thanks Taylor.  I was trying to adjust the delimination in the first two lines of my code rather than toward the end, as you help point out.  I now have the delimination working properly, using the Option-Tab as you also mentioned.  Now I am hoping you or someone else has some direction on getting the dollar amounts to include 2 decimal numbers.

            • 3. Re: SQL Query: Tab delimited and dollar amounts
              taylorsharpe

              The CONVERT function doesn't seem to work with FileMaker's ExecuteSQL.  So the basic answer is no.  But some people have created report tables say with thousands of records and each field is a calculation that references a global array from ExecuteSQL to pull in the field values.  If you do this, then you can format the field in the FileMaker layout to a currency format. 

              • 4. Re: SQL Query: Tab delimited and dollar amounts
                greglane

                Hi Diver,

                 

                Here're a couple of ways to add formatting to number fields with ExecuteSQL. The first example works with values that are less than a million. The second example uses the Evaluate function to apply a FileMaker custom function to a single column in each row of the result.

                 

                These examples use HTML table tags as delimiters, but you can modify to remove the tags and use tab as a column delimiter as Taylor suggested.

                 

                Greg

                 

                 

                ExecuteSQL(

                 

                "SELECT '<tr><td>' || title, '$' || CASE WHEN INT(avgGrossPay) > 999 THEN CAST(INT(avgGrossPay/1000) AS varchar) || ',' || RIGHT('000' || MOD(INT(avgGrossPay),1000),3)  ELSE CAST(INT(avgGrossPay) AS varchar) END || LEFT(STRVAL(ABS(avgGrossPay) + .001 - INT(ABS(avgGrossPay))), 3) || '</td></tr>'

                FROM job_stats WHERE avgGrossPayRank <=10

                ORDER BY avgGrossPayRank";

                 

                "</td>¶  <td align=\"right\">"; ¶)

                 

                 

                 

                 

                Evaluate(ExecuteSQL(

                 

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

                FROM job_stats WHERE avgGrossPayRank <=10

                ORDER BY avgGrossPayRank";

                 

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