7 Replies Latest reply on Aug 12, 2017 5:23 AM by fmpdude

    How to set up executesql to bring back nice neat columns


      I have several queries that where I am selecting fields and counting fields at the same time and would like a way to have the result end in a nice neat columns and rows.  Can this be done?

        • 1. Re: How to set up executesql to bring back nice neat columns

          If you're serious about SQL, I would recommend using a real SQL tool, not the "Data Viewer".


          Below, is a screenshot that I posted about a "JDBC Error", but you can still see that if you use a "REAL" SQL tool, you get distinct re-sizable columns (as well as SQL Type Assist, and lots of other options):

          You can add "CHAR(9)"s an other tricks in FMP's ExecuteSQL, but that won't always work. Real SQL tools give you distinct columns.


          One tool, RazorSQL, will also let you run RegEx on your results.


          Great stuff out there.


          HOPE THIS HELPS.

          • 2. Re: How to set up executesql to bring back nice neat columns

            Use the Tab - Char(9) as the column separator. Then in whatever field you push the results, the tab stops will (columnize). You may need to play with the position of the stops to make it the way that looks best.



            P.S. There are ways with padded results and mono-spaced fonts. Rather than bother with either use VL.

            Soliant has some articles. The same technique is in the FTS.

            Sent from miPhone

            1 of 1 people found this helpful
            • 4. Re: How to set up executesql to bring back nice neat columns

              IMHO, the "Data Viewer" is fine for a quick simple query, but considering the issues with "?" (non FMP 16), no SQL type assist, no resizable, re-order-able columns, having to use the awkward "ExecuteSQL" syntax to do any query, and other SQL deficiencies in FMP (no INSERT, UPDATE, or DELETE as obvious examples), I'd still argue for a real SQL tool for serious FMP SQL work (beyond a query like "SELECT * FROM CUSTOMER" -- wrapped in ExecuteSQL, naturally).


              In my quick tests, trying to pad output with CHAR(9) is problematic (and unnecessary with a real SQL tool, which just gives you actual columns).

              Although I'm sure there are "ways around" the issue shown in the first image below (requiring work/research on the user's part), that is, just having to "remember"....Hey, I need to use special formatting to get thing to line up. This formatting chore is yet another burden dumped in the user's lap.


              SQL, as it is, has been in FMP for five versions. IMHO, the SQL tools by this time, frankly, should just be (a lot) better.


              Below is a screenshot with a simple "two-column query" from the online consumer complaint database shown in FileMaker and a real SQL tool:






              REAL SQL TOOL (no CHAR(9), no "ExecuteSQL" syntax, just SQL - all with FileMaker):

              (Also, here, columns are resizable, re-order-able. Exporting, saving, and different views directly available right here.)

              Being able to export directly from the SQL results is a no-brainier. Who wouldn't want that?


              Here's the JSON equivalent simply exported from the SQL tool above:





              "product":"Payday loan, title loan, or personal loan",

              "issue":"Charged fees or interest you didn't expect"



              "product":"Money transfer, virtual currency, or money service",

              "issue":"Confusing or missing disclosures"



              "product":"Debt collection",

              "issue":"Took or threatened to take negative or legal action"



              "product":"Money transfer, virtual currency, or money service",

              "issue":"Fraud or scam"



              "product":"Money transfer, virtual currency, or money service",

              "issue":"Money was not available when promised"




              Other common formats like TEXT (TAB, SPACE, or user-selected delimiter), Excel, CSV, DBASE, etc., should also be supported.



              Whether a SQL tool is worth it to the FMP person is individual, of course. I live in a world where every database (other than FMP) understands and expects full SQL so for me the choice is clear.

              • 5. Re: How to set up executesql to bring back nice neat columns

                Link to the Product Ideas entry/entries about the specific feature to add, so that we can upvote it?