If you use more than one field for your relation calculating the sum, check on the order of the single field relations. The order can make a huge difference.
Use releations that should result with the smallest records set retuned first. On one of my statistics solution it made a diffence from 30 min to 10 sec!
Maybe this will help.
A server script is always a good way for caching data, if you only need a daily/hourly updates on your summary.If it takes minute on the client, the server should do it in a few secs.
ExecuteSQL and Virtual Lists! Or maybe just ExSQL and set the fields by script, rather than using Summaries.
Server side is a good choice too.
I'm working on the ExecuteSQL, but am having trouble with the syntax.
I need to:
Select all the classIDs from the Enrollment table, get their course name from the CourseName table, Sort the ClassIDs by period, and count each classID when sorted by period.
Here's what i have so far:
"SELECT MasterSchedule.CourseName, COUNT(HS_Enrollment.pk_ClassID)
FROM HS_Enrollment INNER JOIN MasterSchedule ON MasterSchedule.ClassID = HS_Enrollment.pk_ClassID
"|"; "¶" )
But it aint working. The count and sorting part is what i have to take care of.
Teacher / Tech Guy @ KSPA
The things that come to mind are:
1) Switch the join fields, and put the Enrollment Class ID first, the Schedule ID second.
2) Check the data types for the join fields and make sure they're the same
The coolest thing though is from our Belgian friends: You can find out what the actual SQL error is with this elegant function:
Thank you for posting this link. I had no idea this could be done.