4 Replies Latest reply on Oct 5, 2012 7:04 AM by Greg

    ExecuteSQL Results Capture

    Greg

      Good Day All,

       

      I have seen and read much on the new ExecuteSQL Function but I have a question concerning capturing / displaying the results of the Query.

       

      Some examples just seem to use a Text Field others appear to be much more complicated. I have seen examples that just say use a Varible or a Merge Varible.

       

      Where can I find instructions on using and or just displaying the results.

       

      I have a query that displays the results perfectly in the data viewer but no where else.

       

      greg

        • 1. Re: ExecuteSQL Results Capture
          beverly

          Greg. The results will be TEXT regardless of being stored in field or merge field or shown in web viewer. The separators, also play a big part in how the text gets displayed. If you use the TAB as the separator in the query, the "tab-stops" can be set on a field to make these appear as "columns". But you could have the separators as anything (including html table tags!), these show better in a web viewer as "table".

           

          I should think a bigger question: what do you want to do with the result (other than display)? This is where some parsing would be great!

           

          Here is the information on tab-stops, if that's what you wanted:

          http://www.filemaker.com/12help/html/edit_layout.10.44.html

          http://help.filemaker.com/app/answers/detail/a_id/3210/kw/tab%20stops

           

          Beverly

          1 of 1 people found this helpful
          • 2. Re: ExecuteSQL Results Capture
            Greg

            Beverly,

             

            Thanks as always...

             

            I like the idea of the "Tab-Stops" being set on a field.

             

            This particular Query is to find records that match the Where statements and then export them to Excel.

             

            greg

            • 3. Re: ExecuteSQL Results Capture
              beverly

              Ah! I did something similar with ExecuteSQL and put the "header row" in, too:

               

              Let (
                  [ _col_delim = char(34) & "," & char (34) // this could be: "\",\""
                  ; _row_delim = char(13) // this is the carriage return
                  // SET UP THE HEADER ROW HERE
                  ; $query_h = "\"Last" & _col_delim & "First" & _col_delim & "State" 
                      & _col_delim & "Zip\"" & _row_delim
                  ; $query = "SELECT '\"'+lastname, firstname, state,''''+zip+'\"' 
                      FROM names WHERE lastname LIKE ? ORDER BY lastname, firstname"
                  ; $result = $query_h & ExecuteSQL ( $query ; _col_delim ; _row_delim; "Ab%" )
                  ]; "$result
              )

              I get back something like this:

              "Last","First","State","Zip"
              "Abascal","Guadalupe","CA","'92807"
              "Abdo","Janette","CA","'94089"
              "Abdo","Vesta","AK","'99501"
              "Abeb","Chas","AZ","'85012"
              "Abedelah","Wendy","CA","'95824"
              "Abes","Edmond","CA","'91351"
              "Abes","Wyatt","CA","'90007"
              "Abke","Luciano","CA","'90731"
              "Abler","Chantel","AK","'99515"
              "Abney","Patti","AR","'72904"
              "Aboulahoud","Dexter","AZ","'85014"
              "Abousaleh","Lauri","CA","'95603"
              "Abramowski","Ann","CA","'90036"
              "Abramowski","Rose","AK","'99501"
              "Abrev","Son","CA","'90670"
              "Abubakr","Mable","AZ","'85251"

               

              What are you doing to "save" the results to a file for Excel, Greg?

              Beverly

              p.s. the extra single quote before the zip is my attempt to force Excel into keeping it as literal text, not a number.

              1 of 1 people found this helpful
              • 4. Re: ExecuteSQL Results Capture
                Greg

                Thanks again,

                 

                I have not reached the point where I am actually trying to save the results to Excel. My intent was to export the results to Excel.

                 

                I like what you have shown me here so I am going to modify my calculation and try to match what you are doing in your example.

                 

                I will let you know how it goes.

                 

                This really is truly amazing!

                 

                greg