7 Replies Latest reply on Apr 18, 2017 8:12 AM by philmodjunk

    executesql pains

    cortiel

      ExecuteSQL( "

          SELECT sum (a.amount)

          FROM donations a  join donors b  on a.kf_donors_id=b.kp_donors_id

           where b.kp_donors_id = ?  ";

        "" ; "";

      "DON10000016452"  )

       

      how can i show this in a textbox?

      do I have to create a field for it??

        • 1. Re: executesql pains
          philmodjunk

          A text box requires a field in FileMsker. It could be a global field. It could be a calculation field or a field set to the query results by a set field script step.

           

          It it would not be a text box, but you could also set a variable to this value and put the variable on the layout as a merge variable.

          • 2. Re: executesql pains
            beverly

            This is a calculation that can:

            • be in a defined field (not my recommendation) of text type

            • be set to a Variable in a script step (which can be then put into a field)

            • be set to a Field (text) in a script step (even to a global field)

             

            Yes, the field must be defined to get the result. From there, you can use various means to 'extract' the results (if more than one value and/or column).

            The function does not create any records like an 'INSERT INTO SELECT', so you must do something with the result.

            A single field with many 'columns' can have tab stops set to make the columns appear as a 'table', if you use the TAB - Char(9) as the column separator, if you don't need to parse the results.

            beverly

            • 3. Re: executesql pains
              cortiel

              i have a form  with a total in the portal where I want to use the sql

              what do I have  to do?

              do I really need to add a field??

              if I create a variable where would I do that? is tehre a script trigger which kicks in when I move the TO record?

               

              P

              • 4. Re: executesql pains
                beverly

                I would NOT use eSQL for this. Just use a calculation field (unstored, in parent)

                Sum ( DONATIONS_donors::amount )

                it will UPDATE as the values in the portal rows change. This will be different for each parent record.

                WHY? because you must recreate the relationship in the eSQL that you already have in the RG (relationship graph).

                 

                OR you can trigger a Set Field whenever you deem needed. (just a number field)

                 

                and yes, I would add a field.

                beverly

                1 of 1 people found this helpful
                • 5. Re: executesql pains
                  cortiel

                  thanks i am using a summary filed in the child table (portal)

                  that works.

                   

                  but again: what is eSQL???

                  • 6. Re: executesql pains
                    beverly

                    eSQL = ExecuteSQL() - the function.

                    look this up in the forum (search with the magnifying glass icon in the upper right) and/or on Google. Just make sure the information is about the Function.

                    • 7. Re: executesql pains
                      philmodjunk

                      Note that the aggregate functions such as Sum ( ) update more smoothly than a summary field from the related table.

                      2 of 2 people found this helpful