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
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.
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!
You could do an initial ExSQL for DISTINCT Advisors, then loop through that list and ExSQL on each advisor building your list that way.
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
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.