11 Replies Latest reply on Nov 13, 2012 8:05 AM by andrewb

    SQL Queries


      Hi All


      I am new to running scripts within Filemaker 12

      I would like to run the SQL Query in the screen shot attached


      I don't want the result to go to XL unless I have to.

      I would rather stay within Filemaker if I can.


      Is this possible to do without the server version of Filemaker



      Andrew B


      Screen Shot 2012-11-12 at 20.33.47.png

        • 1. Re: SQL Queries

          Hi Andrew,


          I don't think you can actually do what you want by using the Execute SQL script step. This script step  (as far as I know), is used to be able to interact with other ODBC datasources, but not the current database itself.


          To be able to script a query, you need first to have a layout based on the table occurence you want to search on. Then in the script you do this (this is a really basic example):


          Go to Layout (choose the layout)

          Enter find mode

          set field (fieldname ; value you want to have)

          perform find


          The way the result will be displayed, really depends on how your layout is setup. If you want a list, you need the layout to be either in list view, or table view.



          Now if you really want to use SQL commands, you can use the ExecuteSQL function (not script step) which will return a result in text format. For more information concerning this function, I recommend to have a look on http://www.filemakerhacks.com

          They have a PDF reference document, and a lot of articles explaining what you can do with it

          • 2. Re: SQL Queries

            Hi Thomas


            What I'm actually tring to do is create a unique description based on anwering a list questions with differnet answer.


            So if I answer question 1 with 2nd option and answer question 2 with 4th option and answer question 3 with 9th option and so on


            I then end up with a unique ID for each combionation of answers ie   02-04-09 and so on.


            The answers drive a price calculation and then I want to automaticlly get either Filemaker or XL to create every unique ID with its price.


            I will then be importing the ID and Price into an online tool. This means that all the price are calculated outsife of the online tool and then imported in and then when the same question are answer in the online tool it can display the appropriate price.


            I can keep the rates used in the calculation in tables in Filemaker and kep them upto date


            Do you think there a better way of doing this


            • 3. Re: SQL Queries

              Okay, so if I understand you well, you already have a price list with an ID for each price, and then the user answers to a list of questions and depending on his answers, he gets a specific price?

              • 4. Re: SQL Queries

                To clarify:


                Execute SQL [] - script step is for interaction with an ODBC source for INSERT, UPDATE, DELETE queries. Results are IN the source.



                ExecutSQL() - function is for making queries to any valid Table Occurrence (as on the Relationship graph) with the SELECT statement. Results are returned to a TEXT field.



                To SELECT with an ODBC source, the Import[] script step is used. Although the SELECT may also be used within the INSERT statement.


                To NOT use any queries a valid ODBC source can be on the relationship graph and layouts based on the table(s) made. Standard FileMaker manual and scripted finds and other actions can be made. This is referred to as ESS.



                I agree that the script as written is probably not valid. We need to know if Andrew B is using FileMaker or FileMaker with an ODBC source.



                • 5. Re: SQL Queries



                  Thanks for all you help


                  I will put this info infront of my Colleage later and we will hopefully find the solution in all you have provided

                  If not I will be back tomorrow


                  Thanks again



                  • 6. Re: SQL Queries


                    • 7. Re: SQL Queries

                      In that case you don't even need to do a query. All you need is an auto-enter field or a calculated field that will generate an ID depending on the answers. If you then link that field (in the relationships) to the ID field of the price table (I assume you have one, and if not you definitely should create one).


                      Once this is done you can easily export the ID and the related price.


                      I attached a really basic example file

                      • 8. Re: SQL Queries

                        Great I will try this next



                        • 9. Re: SQL Queries

                          Hi Thomas


                          Just thought about what you said in last post


                          Does your answer mean that I will be able to create all the combinations of the ID's


                          Ulitmtley I want to run a script that will create all the ID's and calculate the prices

                          Then I will export ID and Price to online software



                          • 10. Re: SQL Queries

                            Hi Andrew,


                            Check my example file,


                            I have 3 tables


                            - Test ( Question 1, Question 2, ID )

                            - Prices ( ID, Amount )

                            - Answers (ID, Answer )


                            In the Test table, the ID field is automatically calculated depending on the answer you give. As the ID is linked to the price table, you can automatically see the price without having to run any scripts.

                            • 11. Re: SQL Queries

                              Hi Thomas


                              Just looked at your example FM file


                              I see how the ID is created by answering the questions

                              That is spot on


                              Now I just need to create all combination of ID's automatically for 20 to 40 questions

                              Some qustion might have more than 10 answers


                              We could end up with up to 1 million answers for some of my products calculations


                              This is brillinat

                              thanks for all your help