Side note,
Doing it this way will give you a total average = average of all years, which technically is different than the total average of the individual grades. Shout if this is a problem.

p.s. another type of average is if you want it weighted by the number of credits of each courses 

Yes, that is true.  I need it to be weighted according to credits.  But hold off on that for now, I'll try doing what you've suggested so far, and I may be able to figure the rest out.  If not, i'll ask.  This program is simply too cool.

By the way, I was thinking about something.  Pretty soon, I'm going to have dozens of grids in my SQLNotes because I'm excited about sticking all of my life's information into it.  Are there plans to change the grid chooser in the future.  The toolbar with the grid names on it works for a few grids, but what if you have 50 grids?  It might be nice to have another pane (with the option to auto-hide) that has all the grids in there.  The grid Manager is nice, but I was thinking of something dockable and without all the extra information in it, basically, a grid chooser.

Good example of some advanced features
I suppose that the grade will be letters (B, B+, etc). To calculate the GPA (which is a number) simply create a user-defined function:
Function CalcGPA(Grade)
dim t
select case Grade
case "B": t=3.5
case "A+": t=5
case [repeat for every possibility]
end select
CalcGPA=t
End Function

If the choices are too many, you can also using string functions to extract the letter and the +/- modifier and "calculate" a value: something like: 69-asc(letter) + modifier

And in the GPA field, set the equation to =CalcGPA(Grade)
Also in the GPA field set the Hierarchy equation: Parent=AVG(children)

That should do it! 

Quick question, where do I create a user-defined function?  Is it in the Option box inside the field properties?

OK, I need more help with this.  I've created 5 custom fields for my transcript grid; course department, course number, grade, number of units, and GPA.

Now this automatic calculation of the GPA is difficult.  I made the function like you said:

Function CalcGPA(Grade)
dim t
select case Grade
case "A": t=4.0
case "A-": t=3.7
case "B+": t=3.3
case "B": t=3.0
case "B-": t=2.7
case "C+": t=2.3
case "C": t=2.0
case "C-": t=1.7
case "D+": t=1.3
case "D": t=1.0
case "D-": t=0.7
case "F": t=0.0
end select
CalcGPA=t
End Function

I put CalcGPA into the field (named the same as the function).  However, I need the GPA weighted by units.  Also, the outline is organized by all 4 years, year, semester, individual classes.  For each higher level, it should give the cumulative GPA of the children items.  It's not simply an average because it has to be weighted by GPA.  So how can I do this?  Do I have to make another function?

I think that if you
1- Add a column for NbCredit and fill it. Set column calc to Parent=sum(children)
2- In the CalcGPA add a parameter to now read Function CalcGPA(Grade,NbCredit). Set column calc to Parent=sum(children)
3- Add a column AVGGPA and set the equation to GPA / NbCredit

I think this should do it (but I haven't tested it).

For the NbCredit field, isn't that the same as my Unit field?  I think it is.  By the way, I realize you're using the conventional naming methods with "Nb" for number and so on.  Should I be doing this also?

I was trying to do it on my own... but ran into a problem 

Basically, CourseAVG had both an equation and a level calc. There was a conflict between the two.

I've made a modification to enable correct computation.

[By the way, Pierre, this is really great!  Can't say enough about the power of this program.]

CourseUnits hierarchy operations: Parent=sum(children)

My hierarchy operations are not working.  in the box, I'm only supposed to input "sum" right?  because the "((children))" automatically appears on the right when I choose "parent" from the drop-down.  Similarly, none of my equations for fields are calculating.  They are all blank in the grid, even after I close/reopen the grids.  How do I "activate" these calculations?

Also, for the last line of your code, I modified it to be:

if isnull(Grade) or grade="" or grade="P" then CalcGPA=null

so it can ignore classes with P for passing.


Another point I wanted to make is about sorting, I think I've mentioned this before.  Sometimes, especially in cases like this transcript, things are ordered and sorted in a random way (ie not alphabetically or numerically).  If I accidentally click on one of the columns and it adjusts the sorting, I lose the sorting I had before.  How can I save a random sort to retrieve it later?

4- sorting: right-click on the item and select sort ascending or descending. You can also manually drag-drop items. The column you right-click on is used to perform the sort. So to sort by CourseUnits, right-click that cell of the semester item. Doing it this way will save the sort order (make sure Grid>Sort criteria applies to sub-items is not checked).

I understand that.  This works in most cases.  However, there are times when I will sort manually by dragging-dropping in a totally random way (it's not ascending/descending by any column).  Once I have painstakingly sorted something manually, sometimes I accidentally will click on the column and it will change the sorting according to that column.  how can I save the manually sorted grid?

1- After entering an equation, click on Update if you want to apply it to existing items

I still can't get any of my equations to display anything in the grid.  I've taken that CalcGPA code, entered it into the user code area, validated and saved it.  I entered those equations in the fields like you said, and I clicked the update button.  Still, nothing appears in the grid.

Have you entered a grade and a courseunit for the courses ?

Yes, I had those in before I added the equations and function.  Here's a screenshot:

SQLNotes can easily produce you a Journal list of the information you entered. This is just another way to find stuff. You say, "I know I entered that info last week, but where did I put it...".

Simply activate the DateFilter toolbar and set the filter field to ItemCreated (or ItemModified), select the time span (1 day, 1 week, 1 month) and all items entered that day/week/month will be displayed. Other software journal feature will seem primitive compared to this!

[attach]

Looks good, Pierre! 

(I'm not talking too much these days... That's just because I'm busy... But I want to spend some more time with SQLNotes soon, maybe this weekend!)

You are quite right that the user-code needs work:
1- Divide the code between:

• a) system code (part of SQLNotes, updated)
• b) user-code
• c) database-specific code

2- Better code editor with syntax highlighting

This will be done before release 1.0. Idealy I'd like to do it as MS apps with a VBA editor, but it is likely an overkill and MS stopped licencing it (Thanks Bill  )

There are plans to include the LUA language into SQLNotes which would allow more powerful code (VBScript is an out-of-process calculation engine and does not have access to the SQLNotes data, the LUA engine would be fully integrated) Version 2.0 perhaps.

Well, the transcript grid is working properly now.  I used the method where I have a Pass/No Pass column because I wanted to keep track of how many units were Pass anyway.  So, now there is a template available for people who want to enter their transcripts into SQLNotes, which I think is useful.

I mean, do you guys see how powerful this program is?  It's like I'm creating my own mini-programs here.  I bet there is someone out there selling some kind of transcript software.