6 Replies Latest reply on Oct 25, 2012 2:27 PM by jbrown

    ExecuteSQL: Grouping

    jbrown

      Hey all,

      IS there a way to mimic a sub-summary report that can be displayed in a simple text field?

      When I run the function that looks like this:

       

      ExecuteSQL("
      SELECT Advisor, FirstLastName, ConferenceTime

      FROM StudentTable

      WHERE Grade = ?

      AND School =?

      AND ConferenceTime IS NOT NULL

      GROUP BY Adivsor, FirstLastName, ConferenceTime

      ORDER BY ConferenceTime";"";"";$$Grade, $$School)

       

      I get something like this:

       

      Andree, Barny Rubble, 7:00

      Andree, Fred Flintstone, 7:30

      Andree, Betty Rubble, 8:00

       

      -------

       

      Is it possible to make it look like this, using ExecuteSQL?

      Andree

      Barny Rubble, 7:00

      Fred Flintstone,7:30

      Betty Rubble, 8:00

       

      Instead of the advisor name getting pulled and listed for every record, can it be listed once, as a heading of sorts?

       

      Thanks

        • 1. Re: ExecuteSQL: Grouping
          BowdenData

          Jeremy,

           

          Try adding the DISTINCT option to your Select statement.

           

          SELECT DISTINCT Advisor, FirstLastName, ....

           

           

          See this web page for more info on DISTINCT.    http://www.w3schools.com/sql/sql_distinct.asp

           

           

          HTH.

           

          Doug

          • 2. Re: ExecuteSQL: Grouping
            jbrown

            Hi Doug,

            Thanks for the advice.

            Unfortuantely adding DISTINCT to the option in the exact place you suggest doesn't change the output. It sill gives me the advisor's name for every line.

             

            The distinct function pulls the distinct vaules from the list, as the w3schools says. It seems to me the data I'm pulling, including a different kid's name, and different conference time, is all unique.

             

            It doesn't seem like i can do this with a simple statement. I feel i have to set it up this way:

            Create a list of advisors for that grade, sorted by last name. This can be done with an ExecuteSQL statement.

             

            Pick the first name from the list and run an executeSQL statement lookin for those students.

            Combine the Advisor's name with the students underneath in the given text field.

            Loop through another advisor and add more to that text field.

             

            Thoughts?

            • 3. Re: ExecuteSQL: Grouping
              beverly

              Yes, or use standard FMP functions to get this into a variable (and eventually into a field). No need for ExecuteSQL,if you're going to jump through so many hoops.

               

              But if it works, go for it!

              Beverly

              • 4. Re: ExecuteSQL: Grouping
                TimAnderson

                Or check out my discussion from a couple of days ago!

                 

                https://fmdev.filemaker.com/message/98648#98648

                 

                I posted a solution to a very similar problem i had this morning

                 

                Tim

                • 5. Re: ExecuteSQL: Grouping
                  DavidJondreau

                  You could do an initial ExSQL for DISTINCT Advisors, then loop through that list and ExSQL on each advisor building your list that way.

                  • 6. Re: ExecuteSQL: Grouping
                    jbrown

                    My final script is:

                    Set Variable $Advisor:  ExecuteSQL ("SELECT DISTINCT Advisor FROM StudentTable WHERE Grade =? AND School = ? ORDER BY Advisor";"";""$$Grade, $$School)

                    Set Variable $i: 1

                    Loop

                    Set Variable $List (executeSQL ("SELECT FirstLastName, ConferenceTime FROM STudentTable WHERE Advisor =? AND ConferenceTime IS NOT NULL ORDER BY ConferenceTime";"";"";GetValue($Advisor;$i))

                     

                    SetField  StudentTable::g_TextField   A_KIPPsters::g_TestSubject  &¶ & TextStyleAdd(GetValue($Advisors;$i) ;Bold)  & ¶ &"       "&$List

                    SetVariable $i = $i + 1

                    ExitLoop IF $i > ValueCount($Advsior)

                     

                    This works fine. It may not be as fast as I'd like but it updates it pretty quickly.