I <3 Execute SQL

Discussion created by jbrown Expert on Sep 13, 2012


If i picked up one thing from the developer conference this last year (my first) it was the Execute SQL power. As is typical, the information gathered on the edges of my understanding as it was presented in many different ways by experts in the grandiose and expensive hotel. Gradually, over the past 2 months, the information has oozed from the edges to the center of my brain, and I now have a strong(er) understanding of it. I'm amazed at the flexibility of it and what I can do with it.

Yesterday for the first time I used it in scripts to populate number fields, fields that would have been Calc fields reaching across my messy relationship graph for information. When 90 people are using the database at once, those calc fields get slow (thanks David for telling me that).


I was able to set up variables in the script step from the current record (Id number, testyear, etc) to reach into a test-data table and pull out the infomation, doing calculations in the script to return the right result. Wow.


I have finally mastered the trick of grabbing IDs of students via the Execute SQL when the student has an advisor (again set by a variable) and use that list created to move to another table to create attendance records or behavior records. That saves me the task of moving back and forth between the two tables and looping through the student table to grab the id, one record at a time.


I'm sure there are so much more uses. At one point i was able to generate a vitual list, but I've forgotten that. I still am trying to understand all that's happening in the Advanced Reporting Class and the example file that Bob Bowers demoed at DevCon, but i'm starting to get it.


Just had to say how much I enjoy the ability. I'm excited to know more about what it can do. I'm not an expert at it by any means, and it still takes me a lot to get the syntax exactly right, but i'm on the verge.