4 Replies Latest reply on Sep 24, 2012 2:01 PM by philmodjunk

    Can you write SQL queries inside of filemaker pro 12 (example inside)?

    LukeWallace

      Title

      Can you write SQL queries inside of filemaker pro 12 (example inside)?

      Post

            

           Can FileMaker 12 Pro or 12 Advanced write SQL like shown below for querying tables, like Microsoft Access can?
           I have a very complex MS ACCESS SQL query (as you can see below) to filter data from a table with a great many amount of records and fields in a Microsoft access database already, and I'm wanting to make the shift to FileMaker for ease of use and better front end form design.

           The MS ACCESS database however wasn't designed by me (just purchased by me) and so I don't know much SQL, I just realize that I'm going to need it and it would be really nice if I could use the same SQL command with renamed variable/controls to do the same thing in FileMaker. Thanks for any technical insight on this question.

            

           To help understand what is going on below, the end user is prompted with 3 drop down boxes (combo boxes as they are called below) with several choices to filter data. Then they are prompted with two numerical fields (Edit2 and Edit2) which further narrow down the tables in the query, and finally 3 checkboxes which also filter the data even more if they're checked.

           Then the "WHERE" part seems to filter the data somehow (this is where my ability to see what's going on gets blurry)

           

                PARAMETERS COMBO1 Text ( 255 ), COMBO2 Text ( 255 ), COMBO3 Text ( 255 ), EDIT1 IEEEDouble, EDIT2 IEEEDouble, CHECK1 Text ( 255 ), CHECK2 Text ( 255 ), CHECK3 Text ( 255 );

           

                SELECT DISTINCT *

           

                FROM Table1 AS TBL1

           

                WHERE (EDIT1>EDIT2) And ((TBL1.System Like '% '+COMBO1) Or (TBL1.System Like COMBO1) Or (TBL1.System Is Null)) And ((TBL1.Class Like '% '+COMBO3) Or (TBL1.Class Like COMBO3) Or (TBL1.Class Is Null)) And ((TBL1.Location Like '% '+COMBO2) Or (TBL1.Location Like COMBO2) Or (TBL1.Location Is Null)) And ((TBL1.Hs_min<=EDIT2) Or (TBL1.Hs_min Is Null)) And ((TBL1.Hs_max>=EDIT2) Or (TBL1.Hs_max Is Null)) And ((TBL1.Hc_min<=EDIT0) Or (TBL1.Hc_min Is Null)) And ((TBL1.Hc_max>=EDIT0) Or (TBL1.Hc_max Is Null)) And ((TBL1.Pitch=CHECK1) Or (TBL1. Pitch Is Null)) And ((TBL1.Pockets=CHECK2) Or (TBL1.Pockets Is Null)) And ((TBL1. Spaces=CHECK3) Or (TBL1.Spaces Is Null));