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

    SQL Queries

    andrewb

      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

       

      Regards

      Andrew B

       

      Screen Shot 2012-11-12 at 20.33.47.png

        • 1. Re: SQL Queries
          thomas_staehli

          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
            andrewb

            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

            Andrew

            • 3. Re: SQL Queries
              thomas_staehli

              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
                beverly

                To clarify:

                 

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

                <http://www.filemaker.com/12help/html/scripts_ref2.37.69.html#1029790>

                 

                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.

                <http://www.filemaker.com/12help/html/func_ref3.33.6.html#1062707>

                 

                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.

                <http://www.filemaker.com/12help/html/odbc_ess.20.1.html#1027502>

                 

                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.

                 

                Beverly

                • 5. Re: SQL Queries
                  andrewb

                  Guys

                   

                  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

                   

                  AndrewB

                  • 6. Re: SQL Queries
                    andrewb

                    Yes

                    • 7. Re: SQL Queries
                      thomas_staehli

                      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
                        andrewb

                        Great I will try this next

                        Thanks

                        Andrew

                        • 9. Re: SQL Queries
                          andrewb

                          Hi Thomas

                           

                          Just thought about what you said in last post

                          Question

                          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

                           

                          Andrew

                          • 10. Re: SQL Queries
                            thomas_staehli

                            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
                              andrewb

                              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

                              Andrew