topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Friday March 29, 2024, 8:21 am
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Last post Author Topic: SQLNotes...what is it exactly?  (Read 834490 times)

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #350 on: January 04, 2008, 12:37 AM »
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  :huh:
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

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 702
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #351 on: January 04, 2008, 12:48 AM »
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)
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz
« Last Edit: January 04, 2008, 12:50 AM by PPLandry »

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #352 on: January 08, 2008, 01:13 PM »
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!  8)
Quick question, where do I create a user-defined function?  Is it in the Option box inside the field properties?

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 702
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #353 on: January 08, 2008, 01:16 PM »
View > User Code
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #354 on: January 08, 2008, 05:00 PM »
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.
Screenshot_20080108-144650.png
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

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 702
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #355 on: January 08, 2008, 06:00 PM »
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).
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz
« Last Edit: January 08, 2008, 06:02 PM by PPLandry »

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #356 on: January 08, 2008, 06:54 PM »
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?

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 702
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #357 on: January 08, 2008, 07:08 PM »
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
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 702
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #358 on: January 08, 2008, 08:12 PM »
I was trying to do it on my own... but ran into a problem  :huh:

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.
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz
« Last Edit: January 08, 2008, 08:14 PM by PPLandry »

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 702
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #359 on: January 08, 2008, 10:10 PM »
This is what I got:
TranscriptStats.jpgSQLNotes...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
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz
« Last Edit: January 08, 2008, 10:14 PM by PPLandry »

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #360 on: January 09, 2008, 10:57 AM »
[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?

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 702
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #361 on: January 09, 2008, 11:08 AM »
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.
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #362 on: January 09, 2008, 11:21 AM »
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

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 702
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #363 on: January 09, 2008, 11:27 AM »
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
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #364 on: January 09, 2008, 11:39 AM »
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

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 702
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #365 on: January 09, 2008, 11:42 AM »
Have you entered a grade and a courseunit for the courses ?
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #366 on: January 09, 2008, 11:58 AM »
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:
Screenshot_20080109-095859.pngSQLNotes...what is it exactly?
« Last Edit: January 09, 2008, 11:59 AM by superboyac »

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 702
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #367 on: January 09, 2008, 12:01 PM »
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
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 702
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #368 on: January 10, 2008, 02:27 PM »
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!

Journal.jpg
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz
« Last Edit: January 10, 2008, 02:33 PM by PPLandry »

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 702
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #369 on: January 10, 2008, 03:55 PM »
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
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz

Armando

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 2,727
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #370 on: January 10, 2008, 09:01 PM »
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

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #371 on: January 11, 2008, 12:25 PM »
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

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 702
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #372 on: January 11, 2008, 12:39 PM »
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  :down:)

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.
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz
« Last Edit: January 11, 2008, 12:42 PM by PPLandry »

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #373 on: January 11, 2008, 12:50 PM »
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  :down:)

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

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #374 on: January 11, 2008, 01:03 PM »
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.