2 Replies Latest reply on Nov 14, 2013 1:48 PM by databuzz

    SQL for an existing layout

    lkeyes

      Is it possible to use ExecuteSQL to constrain the records shown on an exisiting layout, as a substitute for the Find dialog?

      So far I can't seem to make this work, if it is, and I'm still a little foggy about where the results of a SQL query will actually appear, (other than in a massive text variable). I'd like the results to be reflected on my existing layout, juast as the results of a FIND query are.

       

      Example... I have a layout that displays a normal set of contact fields, such as id, firstname, lastname, address1, address2, city, state, zip, and

      I want to have a query that finds one record:

      Select * from person where ID=1234

       

      I know this is a trivial idea which is easily done using the Find button, but it is preliminary to a more involved query that I don't think I can do a find, where I want to update a field, based on a nested SQL query something like...

       

      Update person set person.status=0 where person.id IN (SELECT person.id from table2)

       

      Thanks for any help.

       

      --- L

        • 1. Re: SQL for an existing layout
          BruceHerbach

          Hi,

           

          ExecuteSQL is independant of the current found set.  It pulls the information from the Table Occurence in the graph directly.  So for example if I have a TO called USZipCodes and the

           

          ExecuteSQL (SELECT a."City", a."ST", a."County"

          FROM "USZipCodes" a

          WHERE a."ZIP" = ? ; " " ; " "; "10803" )

           

          The query would do a search for zip codes that match 10803 and return the found set.  The layout you are working on can be based on any Table Occurence.  The ExecuteSQL will work as long as the table occurence it is based on is in the graph.

           

          You might take a look at Beverly Voth's ExecuteSQL the missing manual.  http://www.filemakerhacks.com/?p=6406

          I found this to be very helpful.  Also take a look at SeedCodes SQLExplorer.  Incredibly helpful when trying to develop a new query.

           

          HTH

          1 of 1 people found this helpful
          • 2. Re: SQL for an existing layout
            databuzz

            Hi,

             

            Just to clarify the ExecuteSQL function only supports the SELECT statement with FileMaker Pro v12, so you will not be able to use it to perform an UPDATE etc. The results can be returned to a field or a variable - so it's not directly comparable to doing a native FileMaker find operation.

             

            If you want to manipulate the found set of records on the current layout your script would need to then use the results of the ExecuteSQL statement to then perform a find, go to Related Records etc using normal FileMaker script steps.

             

            HTH,

            Andrew

             

            FileMaker 12/11/10/9/8 Certified Developer

            Databuzz

            - - - - - - - - - - - - - - - - -

            Phone: +61 2 9484 6565

            Mobile: +61 418 468 103

            Email: andrew@databuzz.com.au

            http://www.databuzz.com.au