SQLNotes...what is it exactly?

[superboyac]

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 

-PPLandry (January 03, 2008, 08:00 PM)

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.

[PPLandry]

The grid list is also shown in View>Grids, where you can see more. You can also choose to hide less used grids from the toolbar (grid>properties)

Keep in mind that grids are basically a definition of (1) item selection and (2) columns. So you can
1- re-use grids by simply changing the source
2- use the properties pane / forms to enter/view information

More to come of course. Suggestions are welcomed (grid chooser is a good idea)

[superboyac]

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! 

-PPLandry (January 03, 2008, 07:46 PM)

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

[PPLandry]

View > User Code

[superboyac]

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:

Code: Text [Select]

1. Function CalcGPA(Grade)
2. dim t
3. select case Grade
4. case "A": t=4.0
5. case "A-": t=3.7
6. case "B+": t=3.3
7. case "B": t=3.0
8. case "B-": t=2.7
9. case "C+": t=2.3
10. case "C": t=2.0
11. case "C-": t=1.7
12. case "D+": t=1.3
13. case "D": t=1.0
14. case "D-": t=0.7
15. case "F": t=0.0
16. end select
17. CalcGPA=t
18. 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?

[PPLandry]

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).

[superboyac]

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).

-PPLandry (January 08, 2008, 06:00 PM)

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?

[PPLandry]

yes NbCredit is the same as your Unit (I did not notice that field). Naming convention is your own decision. I tend to avoid spaces because one then need to wrap the field with in [  ]. Other than that, it is good to think locally and globally:
1- If the file is used for other purposes, make sure the name will not be confused to mean something else. Make the name just specific enough.
2- If the field could be used in other applications, make it's name generic enough. A generic Date field, along with a qualifying field (i.e.MusicLibrary) is sometimes preferable to MusicLibraryDate, especially if the database will be used for many uses, you probably don't want to end up with 10-20 date fields. The advanced filtering will allow you to get back only the dates specific to MusicLibrary

[PPLandry]

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.

[PPLandry]

This is what I got:
SQLNotes...what is it exactly?

CourseUnits hierarchy operations: Parent=sum(children)

CourseGPA equation: =CalcGPA(CourseGrade,CourseUnits)
CourseGPA hierarchy operations: Parent=sum(children)

CourseAVG equation: =CourseGPA/CourseUnits

And this user function:

Code: Visual Basic [Select]

1. FUNCTION CalcGPA(Grade, Units)
2. dim t
3. SELECT case Grade
4. case "A": t=4.0
5. case "A-": t=3.7
6. case "B+": t=3.3
7. case "B": t=3.0
8. case "B-": t=2.7
9. case "C+": t=2.3
10. case "C": t=2.0
11. case "C-": t=1.7
12. case "D+": t=1.3
13. case "D": t=1.0
14. case "D-": t=0.7
15. case "F": t=0.0
16. end SELECT
17. CalcGPA = t * Units
18. if isnull(Grade) or grade="" then CalcGPA=null
19. End FUNCTION

[superboyac]

[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:

[Select]

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?

[PPLandry]

A few points:
1- After entering an equation, click on Update if you want to apply it to existing items
2- There is a small bug in the hierarchy operations that it is only applied when data is changed. If you change any of the sub-item, the parent will recalculate. I'm fixing it right now and will update the daily build today.
3- re: sum: correct
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'll post a message here when the daily build has been updated.

[superboyac]

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?

[PPLandry]

Sorting is saved after a drag-drop or sort command operation. Right after clicking on the column header simply close the grid and re-open it. Sorting order should be correct

[superboyac]

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.

[PPLandry]

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

[superboyac]

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

-PPLandry (January 09, 2008, 11:42 AM)

Yes, I had those in before I added the equations and function.  Here's a screenshot:
SQLNotes...what is it exactly?

[PPLandry]

There is a space between GPA and Average, did you enclose the field between [  ] ? Send me the file if you want, I'll take a look

[PPLandry]

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!

[PPLandry]

Just a heads-up to say that the daily build has been updated. A new feature in this build, not discussed on this forum yet, is a first implementation of the GTD methodology using SQLNotes.
http://www.sqlnotes....e/posts/Default.aspx

[Armando]

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!)

[superboyac]

Pierre, it seems like that when I update SQL with the daily build, it replaces the user code.  Isn't this dangerous?  What if I add my own custom user codes?  They shouldn't be replaced by the daily build.  In this case, it's ok, because it's not much and you and I worked together on it (and you included it in the daily build).  Just for the future.  Also, in the future, do you plan to have the user code section more organized than just being one long text file?  Not only would it be easier to use, but less chance to accidentally alter the existing code.

[PPLandry]

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.

[superboyac]

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.

-PPLandry (January 11, 2008, 12:39 PM)

That would be really cool.

minor bug report:  When you click on an item in the grid so that the cursor starts blinking to start typing, sometimes (not always) the cursor will be blinking one space in.  That is, a space is added to the beginning of the text even though no typing has begun.  Also, I know you can start typing immediately without clicking once inside first, but if my caps-lock is on and I start typing suddenly, the first letter is lower-case and then everything is upper-case.

[superboyac]

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.