7 Replies Latest reply on Feb 13, 2015 7:29 AM by erolst

    Execute SQL in Repeating Fields

    jgomes384

      Hello -

       

      I am attempting to use Execute SQL in a repeating field in the following way:

       

      In Table1 I have a field (Days365) with 366 repetitions - representing every date including Feb 29th

       

      In Table2 I have a number of records with date fields that then allows me to calculate the repetition number the date corresponds with.

       

      I have set-up the following ExecuteSQL in my repeating field:

       

      ExecuteSQL ( "SELECT Sum(Hours) FROM Table2 WHERE  EmpID = ?  AND RepNr = ? AND RecYear = ?" ; "" ; ", " ; Table1::E_ID ; Get ( CalculationRepetitionNumber ) ; Table1::Att_Year )

       

      I have also tried Get ( ActiveRepetitionNumber ) in place of Get ( CalculationRepetitionNumber )

       

      I'm not getting any "?'s" as a result so I'm not sure if this is a limitation of FM or is there another way to get the repetition number?

       

      Thanks,

      Jason

        • 1. Re: Execute SQL in Repeating Fields
          TimDietrich

          Jason --

           

          By default, ExecuteSQL assumes that you're referring to repetition 1 of a field.

           

          However, you can refer to a specific repetition by placing the repetition number in brackets. For example:


          SELECT First_Name, Last_Name FROM Surveys WHERE Category[3] = 'FileMaker Developer'

           

          If you're interested, I blogged about how to use ExecuteSQL with repeating fields here.

           

          I hope this helps. Good luck!

           

          -- Tim

          • 2. Re: Execute SQL in Repeating Fields
            erolst

            jgomes384 wote:

            In Table2 I have a number of records with date fields that then allows me to calculate the repetition number the date corresponds with.

             

            This seems a rather roundabout method; instead, calculate the date the repetition corresponds to, and use that in the query:

             

            ExecuteSQL ( "

              SELECT Sum ( Hours )

              FROM Table2

              WHERE

                EmpID = ? AND

                theDateField = ?

              " ; "" ; ", " ;

              Table1::E_ID ;

              Date ( 12 ; 31 ; Table1:: Att_Year - 1 ) + Get ( CalculationRepetitionNumber )

            )

            • 3. Re: Execute SQL in Repeating Fields
              jgomes384

              Thanks for the reply Tim!

              Unfortunately, I am actually trying to set different repetitions of a field based on the date in the other table ( I.e. repetition 1 = 1/1/year etc. ) rather than trying to grab information from a repeating field.

              • 4. Re: Execute SQL in Repeating Fields
                jgomes384

                Erolst-

                This worked perfectly for setting the first repetition however, all other reps remain blank

                • 5. Re: Execute SQL in Repeating Fields
                  erolst

                  Use Extend():

                   

                  ExecuteSQL ( "

                    SELECT Sum ( Hours )

                    FROM Table2

                    WHERE

                      EmpID = ? AND

                      theDateField = ?

                    " ; "" ; ", " ;

                    Extend ( Table1::E_ID ) ;

                    Date ( 12 ; 31 ; Extend ( Table1:: Att_Year ) - 1 ) + Get ( CalculationRepetitionNumber )

                  )

                  • 6. Re: Execute SQL in Repeating Fields
                    jgomes384

                    That worked amazingly! Thank you Erolst.

                     

                    As I am still new to this, could you explain why the extend works?

                    • 7. Re: Execute SQL in Repeating Fields
                      erolst

                      When a repeating calculation references other fields, by default it tries to reference the same repetition cell in these other fields; if that repetition doesn't exist (because these field aren't repeating fields themselves, or have less repetitions), there is nothing to be found.

                       

                      Extend() lets you access the field value as if it were stored within the corresponding repetition. (IOW, it turns the target field into a virtual repeating field for the purpose of the calculation.)