8 Replies Latest reply on Jan 25, 2016 8:19 AM by Powerbook

    SQL with multiple conditions

    Powerbook

      I'm quite custom with SQL scripts, but I'm having some problems with filemaker syntax.

       

      up to now I was able to perform a sum with just one condition, in the following case I'm summarizing all values of "Entrate" from the Table "Movimenti" that have a correspondence value of "R1A" in the field "Causale 3"

       

      Sum(

      Substitute(

      ExecuteSQL  ("SELECT SUM (\"Entrate\" ) FROM \"Movimenti\" WHERE (\"Causale 3\") = ?" ; "" ; "" ; "R1A");".";",")

       

      (the Substitute is for compatibility with European decimal between Windows and Mac OS systems)

       

      what I would need now is to add more conditions to the extraction.

      Like a range of date that I have already stored in 2 Variables "$DataInizio" and "$DataFine" and also when the field "ID Tecnico" has a correspondence to the variable "$Tecnico"

       

       

      in few word I would need to extract from the table "Movimenti" and summarize the field "Entrate" in a range of date when the "ID Tecnico" is equal to $Tecnico.........

       

      How would be the correct syntax to use in a script for filemaker ?

       

      Thank you

        • 1. Re: SQL with multiple conditions
          coherentkris

          you can chain things together with AND / OR

          Where field = ? AND Where Field = ? OR WHERE Field2 <> ? is valid when you put in three arguments.

           

          What is the substitute and Sum for?

          You are asking SQL to return a sum already and the delimiters can be specified right in the ExecuteSQL ()

          ExecuteSQL  ("SELECT SUM (\"Entrate\" ) FROM \"Movimenti\" WHERE (\"Causale 3\") = ?" ; "." ; "," ; "R1A")


          The Missing FM 12 ExecuteSQL Reference | FileMakerHacks

          • 2. Re: SQL with multiple conditions
            Powerbook

            The Substitute is to summarize correctly field that has decimal as "," and as "." due to differences in settings you can fine between Macintosh and Windows Systems,

            The Summarize is because I do some other calculation with other Sql values in the same script.

             

            I know how to use AND OR in statements.

            Why I cannot understand is how to write it correctly under filemaker.

            That's why I'm asking to see the example in my case.

            • 3. Re: SQL with multiple conditions
              beverly

              I also don't understand the Sum() around everything else, but that doesn't matter here. If you are asking how to make the SQL statement include the other criteria:

               

              ExecuteSQL (

              " SELECT SUM(\"Entrate\")

              FROM \"Movimenti\"

              WHERE

                   ( \"Causale 3\" = ? )

                   AND

                   ( datefieldhere >= ? )

                   AND

                   ( datefieldhere <= ? )

                   AND

                   ( \"ID Tecnico\" = ? ) "

               

              ; "" ; ""

              ; "R1A" ; GetAsDate($DataInizio) ; GetAsDate($DataFine) ; GetAsNumber($Tecnico)

               

              )

               

              I think perhaps you were unclear about additional parameter attributes? Each "?" is listed in order (semi-colon separated) after the column delimiter and the row delimiter. In the above example 4 question marks have 4 values.

               

              HTH,

              beverly

              • 4. Re: SQL with multiple conditions
                Powerbook

                Thank you Beverly, you started to make some of the filemaker SQL syntax clouds disappearing......

                 

                But I still get an "empty" result.

                Before I was just getting the famous "?"

                It looks for sure better, but still with no result.

                This is what I used from you example:

                 

                ExecuteSQL (

                " SELECT SUM(\"Entrate\")

                FROM \"Movimenti\"

                WHERE

                     ( \"Causale 3\" = ? )

                     AND

                     ( \"Data Movimento\" >= ? )

                     AND

                     ( \"Data Movimento\"  <= ? )

                     AND

                     ( \"ID Tecnico\" = ? ) "

                 

                ; "" ; ""

                ; "R1A" ; GetAsDate($DataInizio) ; GetAsDate($DataFine) ; GetAsNumber($Tecnico)

                 

                )

                • 5. Re: SQL with multiple conditions
                  Powerbook

                  Ops, my mistake,

                  I was using a wrong field. Nos is working fine.

                   

                  Thank you Beverly, you really helped me.

                  • 6. Re: SQL with multiple conditions
                    beverly

                    What happens when you do a manual (or scripted) find within FileMaker?

                     

                    Given a layout with these:

                    (summary field) sum_Entrate (in the footer is ok after the find)

                    (number field) Entrate

                    (text field) Causale

                    (date field) Data Movemento

                    (number field) ID Tecnico

                     

                    Enter Find mode

                    Set field ( Causale 3 ; "R1A" )

                    Set field ( Data Movimento ; GetAsDate($DataInizio) & "..." & GetAsDate($DataFine) )

                    Set field ( ID Tecnico ; GetAsNumber($Tecnico) )

                    Perform Find

                    If ( Get ( FoundCount ) )

                    Sort

                    End if

                     

                    Do you  get records returned?

                    beverly

                    • 7. Re: SQL with multiple conditions
                      beverly

                      I hate when that happens.

                       

                      Thanks!

                      beverly

                      • 8. Re: SQL with multiple conditions
                        Powerbook

                        Is exactly what I tried to do, and when I did not get any record back I realized I was using a wrong condition value... once I changed all when't fine.

                         

                        I am using sql syntax because sometimes I find it quicker to summarize or extract datas.

                        In this particular case I want to summarize the Income field of a table with different conditions (date range, ID....).

                        Then the result generated I needed to use it inside  a report.

                         

                        I was really getting mad trying to understand the Filemaker script syntax in this case.

                        But you helped me fine.

                         

                        Thank you again.