topbanner_forum
  *

avatar image

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

Login with username, password and session length
  • Wednesday December 4, 2024, 9:43 pm
  • 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 901266 times)

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 #325 on: December 28, 2007, 12:17 PM »
No probs. Give me a few days, since I'm busy changing web server today.
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 #326 on: December 29, 2007, 03:32 PM »
A little note to say that I'm changing web server. so www.sqlnotes.net will be down for a few days.

BTW. I do not recomment Brinkster as a hosting service. Bad system, bad service (and they closed by account 2 days before expiration  >:(

To contact me in the meantime, please use this temporary email: ppl32  at  hotmail
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz

Darwin

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,984
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #327 on: December 29, 2007, 03:56 PM »
Thanks for the heads up, Pierre - and good luck!

Armando

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 2,727
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #328 on: December 29, 2007, 03:57 PM »
Yes : bonne chance Pierre!!

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 #329 on: December 29, 2007, 11:52 PM »
Thanks!

Migration was successful. In waiting for DNS propagation, the new web site can be reached at http://98.130.34.115/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 #330 on: December 30, 2007, 11:54 AM »
Yes, it looks like it was a success. It's speedy and everything is at the right place...   :) :)

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #331 on: December 31, 2007, 12:41 AM »
OK, I'm trying something new now.  I want to keep a log of my jazz piano practices.  For each day, I want to track how long I spend on each item and maybe a comment in the HTML pane.  I'm thinking that the forms is the way to do this, here is what I want it to look like (with proper indentations):

12-30-2007
   practice item1 (15 minutes, comment)
   practice item2 (15 minutes, comment)
   practice item3 (25 minutes, comment)

For the form, it would be ideal if it could fill in the date to be whatever the date is when the form is invoked.  Also, if it could automatically set up the 4 lines in the grid with the proper indentation, that would be great.  Then I just fill in the time spent in a separate field, and comments in the html pane.  For each day, I would invoke the form and the basic outline would be setup for that day.  Is this possible?  Then I could print it out for my teacher.

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 #332 on: December 31, 2007, 11:59 AM »
OK, I'm trying something new now.  I want to keep a log of my jazz piano practices.  For each day, I want to track how long I spend on each item and maybe a comment in the HTML pane.  I'm thinking that the forms is the way to do this, here is what I want it to look like (with proper indentations):

12-30-2007
   practice item1 (15 minutes, comment)
   practice item2 (15 minutes, comment)
   practice item3 (25 minutes, comment)

For the form, it would be ideal if it could fill in the date to be whatever the date is when the form is invoked.  Also, if it could automatically set up the 4 lines in the grid with the proper indentation, that would be great.  Then I just fill in the time spent in a separate field, and comments in the html pane.  For each day, I would invoke the form and the basic outline would be setup for that day.  Is this possible?  Then I could print it out for my teacher.

Such a outlining structure is certainly feasible, but I think you should question whether it is the best. Let me explain.
For each application, one must examine the relative merits (advantages/disadvantages) of each outline structures. Yes SQLNotes can do outlines/calculations, but it can also do flatter structures. Each have relative merits. Flatter structures are sometimes superior to indented ones. Similarly, separate values can be saved in separate fields vs single field but multiple items, vs single text field. Ease of data entry, data analysis (immediate and eventual), information sharing are key considerations here.

Only you can answer this kind of information. It seems to me that the individual practice times are probably not relevant, total time in the day is what you may want to do some analysis. A single entry with the date, total practice time and in the HTML pane, comments on the individual practice sessions would be easier to enter and to report to your teacher. But if after reflexion, you find that the indented structure provides you with valuable information, I can help out setting it up.

Happy new year!
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 #333 on: December 31, 2007, 12:50 PM »
Good point Pierre.  In fact, I was going to ask your advice about that very same issue when i wrote the post last night.  The part I couldn't quite figure out was that each practice day, I would have 6-7 practice items, and I want to keep track of how many minutes I practice each item.  Now, I could keep track of it in the HTML pane with a long comment, but I wouldn't be able to sum the practice items individually.  Actually, I don't really care about the total time spent practicing, but the time spent on the individual items.

How about this: one line for each day (flat, like you said) and I'll create a field for each practice item where I can enter how many minutes I spent on it.  Then, if I want to elaborate on the details of the practice, I can include that in the HTML pane.  This way, each day's practice will be included on one line, and I can still sum up the individual items later if I need to.  Right?

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 #334 on: December 31, 2007, 12:55 PM »
I can still sum up the individual items later if I need to.  Right?

Yes, you can create a sum field (=practice1 + practice2 + ...) or use the pivot table or export to excel, etc

[edit]
BTW, if the comment on each practice is generally small, you can include it directly in the practice info. Here is how:
Instead of using a number field for the practice duration, use a text field. First enter the duration and then the comment. Set it to wordwrap if you want.

Now the neat thing, you can still do computations even though it is a text field! SQLNotes can evaluate text fields and treat them as number fields.
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz
« Last Edit: December 31, 2007, 01:00 PM by PPLandry »

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #335 on: December 31, 2007, 02:02 PM »
I can still sum up the individual items later if I need to.  Right?

Yes, you can create a sum field (=practice1 + practice2 + ...) or use the pivot table or export to excel, etc
Hmm...that sounds interesting, I'll have to give that a shot.  But I think my comments will be long enough to include them in the HTML pane.

[edit]
BTW, if the comment on each practice is generally small, you can include it directly in the practice info. Here is how:
Instead of using a number field for the practice duration, use a text field. First enter the duration and then the comment. Set it to wordwrap if you want.

Now the neat thing, you can still do computations even though it is a text field! SQLNotes can evaluate text fields and treat them as number fields.
That's interesting, I will check that out.
« Last Edit: December 31, 2007, 03:54 PM by superboyac »

Armando

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 2,727
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #336 on: December 31, 2007, 05:07 PM »
For each application, one must examine the relative merits (advantages/disadvantages) of each outline structures. Yes SQLNotes can do outlines/calculations, but it can also do flatter structures. Each have relative merits. Flatter structures are sometimes superior to indented ones. Similarly, separate values can be saved in separate fields vs single field but multiple items, vs single text field. Ease of data entry, data analysis (immediate and eventual), information sharing are key considerations here.

Good advice, Pierre.

"Ease of data entry, data analysis (immediate and eventual), information sharing"  :  These parameters are exactly the one I use when I have to structure data.

I must say that the parameter I think of most is "data analysis". It's a drag when you've entered all your data in a structure that's opaque, not differentiated enough and only later realize that you should of separate some info.

Is there a way to merge fields in SQLNotes? If yes, then creating a multitude of fields is not a problem if you can decide to merge them later on, or even just create a "merged view/print of all fields/columns".
--> Would that be possible (to create a "merged view/print of all fields/columns") ?

Similarly : even if a highly hierarchical structure is adopted, it's not really a problem if a flat view was also possible, momentaneously eliminating all hierarchies, with the possibility to sort through different parameters : date, name, etc.
--> Is that possible at the moment?


Some various comments :

--> One potentially useful feature would be to save certain tool bar arrangements and apply these "arrangements" to all grids.


Also, 2 things have been happening with v.0.9.21

-- When I open certain grids : "Error opening grid. Check grid source criteria."
What does that ?

-- Sometimes, under certain varying circumstances (like selecting some empty space-columns in an empty grid, or -- but that was a while ago -- I typed alt+2 by mistake within the find window) I'll get a super crash:

Run-time error '-2147467261 (80004003)': Method '~' of object '~' failed.

And then I loose the stuff I've been working on  :( .

The data is not completely gone of course but I have to repopulate the grid manually.


--> Is there a way to save the grids content and "format" automatically or even manually with a shortcut key ?


(BTW, what's the new link for the daily builds ?  :) )
« Last Edit: December 31, 2007, 05:09 PM by Armando »

Armando

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 2,727
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #337 on: December 31, 2007, 05:10 PM »
(I've quickly edited my previous post for better readability...  :-[)

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #338 on: January 02, 2008, 11:20 AM »
OK, here's what I settled on for the jazz practice sheet.  I'm going to use a flat structure, and I created two new fields, one for the practice description, and another for the time spent practicing.  So each line should look like this:
date, practice description, minutes, (and of course the html pane for comments)

For the practice description, I made a new field instead of using the "Item" field so that I can use dropdown lists to choose between a handful of prenamed descriptions.  I can post a screenshot of it later (it's at home).

The great thing about this method is that sometimes I will only practice one item a day, and other days I may practice multiple things, and this will be able to handle both easily because each line will have the date on it.  So if I practice 4 things, there will be 4 lines for that date.  No need for a hierarchy.  And the dropdown lists will assure consistency in the names.

I love this program, I really do.

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 #339 on: January 02, 2008, 11:33 AM »
I must say that the parameter I think of most is "data analysis". It's a drag when you've entered all your data in a structure that's opaque, not differentiated enough and only later realize that you should of separate some info.
You're quite right. I've been a consultant for the last 18 years (mostly database/information management related), and part of my task is structuring the data to balance the often contradictory customer requirements of magic-like data entry and analysis

Is there a way to merge fields in SQLNotes? If yes, then creating a multitude of fields is not a problem if you can decide to merge them later on, or even just create a "merged view/print of all fields/columns".
Yes of course!. simply create the merge field and set the equation that will do the merge. Click update. The equation can be as simple as FieldA & Field 2 & ... or a user-defined function to handle all kinds of special cases. Once done, simply remove the equation and delete the original fields

--> Would that be possible (to create a "merged view/print of all fields/columns") ?
If you've got 100's of fields, I'm not sure if it would be useful. Perhaps it would be better to select items and copy all item info (in XML format). Can you explain a bit more what you'd want?

Similarly : even if a highly hierarchical structure is adopted, it's not really a problem if a flat view was also possible, momentaneously eliminating all hierarchies, with the possibility to sort through different parameters : date, name, etc.
--> Is that possible at the moment?
Yes this is possible at the moment. However, information in a main item may not in a sub-item, so a flat view may hide some information that is at a higher level (inheritance and special fields, like the new ItemParent field can help out in those cases)

Some various comments :
--> One potentially useful feature would be to save certain tool bar arrangements and apply these "arrangements" to all grids.
Thanks for the idea. Also useful will be a hide-all-panes/restore-panes to toggle between 2 configurations

Also, 2 things have been happening with v.0.9.21

-- When I open certain grids : "Error opening grid. Check grid source criteria."
What does that ?
It could be that the source, filter or sort criteria contain a invalid field name (spelling error). You can also try a repair.

-- Sometimes, under certain varying circumstances (like selecting some empty space-columns in an empty grid, or -- but that was a while ago -- I typed alt+2 by mistake within the find window) I'll get a super crash:

Run-time error '-2147467261 (80004003)': Method '~' of object '~' failed.

And then I loose the stuff I've been working on  :( .

The data is not completely gone of course but I have to repopulate the grid manually.
I'll check on the alt-x shortcut functionality. It is a shortcut to open grids. (You assign a shortcut number in the grid properties). When you make many changes to a grid, it is a good idea to hit F5-refresh which saves the current grid configuration

--> Is there a way to save the grids content and "format" automatically or even manually with a shortcut key ?
Grid content and format is always saved... Perhaps you're referring to the item state (Grid>Save Item state)

(BTW, what's the new link for the daily builds ?  :) )
I'm working on it, but there hasn't been any recent builds, I'm on holidays  :D
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 #340 on: January 02, 2008, 11:35 AM »
For the practice description, I made a new field instead of using the "Item" field so that I can use dropdown lists to choose between a handful of prenamed descriptions.  I can post a screenshot of it later (it's at home).

Nice. I didn't even know you could do that. :-[


P.S. : Pierre : I just saw your post...

Armando

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 2,727
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #341 on: January 02, 2008, 11:43 AM »
Thanks for your message Pierre ! So you're on "holiday"s, hmmm?

If you've got 100's of fields, I'm not sure if it would be useful. Perhaps it would be better to select items and copy all item info (in XML format). Can you explain a bit more what you'd want?

You basically answered my question in a previous paragraph (and, you're right of course : it wouldn't be that useful if all fields were merged)!

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 #342 on: January 02, 2008, 07:10 PM »
OK, here's what I settled on for the jazz practice sheet.  I'm going to use a flat structure, and I created two new fields, one for the practice description, and another for the time spent practicing.  So each line should look like this:
date, practice description, minutes, (and of course the html pane for comments)

For the practice description, I made a new field instead of using the "Item" field so that I can use dropdown lists to choose between a handful of prenamed descriptions.  I can post a screenshot of it later (it's at home).

The great thing about this method is that sometimes I will only practice one item a day, and other days I may practice multiple things, and this will be able to handle both easily because each line will have the date on it.  So if I practice 4 things, there will be 4 lines for that date.  No need for a hierarchy.  And the dropdown lists will assure consistency in the names.

I love this program, I really do.

I'd recommend you start by creating a new grid, called JazzPractice, which will automatically create a folder (field) of the same name. All items you create there will be put in that folder and they'll conveniently be displayed in the grid (you may have done this, but just in case you didn't). You can later on apply a date filter using the DateFilter toolbar.

Your structure is both simple and powerful. With this structure, the pivot table/chart will be able to show you total time spent per day, week, month, per description, calculate the average practice duration, etc, as a summarizing table or chart. Export as a web page and link that page to a summary item HTML pane content.

I can see that you're really starting to grasp how to use SQLNotes to solve information management problems. It's great to see how you went from inquiring about it, to being totally confused, to seeing the possible use but not knowing how to go about it, to finally seeing the light and now flying on your own. Congratulations.  :Thmbsup: :Thmbsup: Perhaps you'll want to share it on / improve the SQLNotes wiki documentation (http://sqlnotes.wikispaces.com/)

A few thing you can add to ease the data entry
1- Set AutoAssign rules for the JazzPractice field to A:Date=int(now). This will automatically fill the date for you
2- You can do the same for the description field if most of the time, the same description comes up
3- You could use forms (with default values) to facilitate data entry (but in this case, a simple grid should suffice)

Special thanks to you, this thread has made it as #2 in the Best Software of 2007, thanks to your initiative!  :Thmbsup:
https://www.donation....com/2007/index1.php
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz
« Last Edit: January 02, 2008, 07:14 PM by PPLandry »

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 11,964
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #343 on: January 03, 2008, 05:56 AM »
I'd recommend you start by creating a new grid, called JazzPractice, which will automatically create a folder (field) of the same name. All items you create there will be put in that folder and they'll conveniently be displayed in the grid (you may have done this, but just in case you didn't). You can later on apply a date filter using the DateFilter toolbar.
(my bold)
I just noticed that on creating a new grid yesterday
A Yes/No column was created with name of grid
Does that mean that everything you put in that grid could be shown (elsewhere/somewhere) as subitems of, in this case, the item JazzPractice?

when you say
All items you create there will be put in that folder and they'll conveniently be displayed in the grid
by "there" you mean -in that particular grid-? and by "they'll conveniently be displayed in the grid" are you stating the obvious or am I missing something (I dont mean that to sound smart!)

I'm still a bit confused by items/sub-items
When I went back to my billing grid after christmas the hierarchy seems to be different to what I expected and different to how I left it last..(they partying too maybe :-\)
It should be Project/item with sub-items of hours worked
but
at the moment I have 14 entries sub-items also showing as main items -
I think what happened was that yesterday they showed a main items so I dragged them "into" their correct parent item but now they showing twice -
if I delete one copy, they completely gone
in manage grids window context is turned off, hierarchy turned on - any tips?

Also Parent should be showing sum of items (for hours worked) but isn't anymore for this item - it's not important cause invoice is already sent but just trying to figure what I done wrong case I do it again :)
Tom

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #344 on: January 03, 2008, 01:12 PM »
tomos, sounds like you're experiencing the same confusion I did initially.  Pierre should be able to clear things up.  Basically, you can display the items and subitems in a grid in a flat view if the option is checked (I forgot which option it is).  Sort of like how some file managers have an option to display subdirectory content in a flat view.  Pierre helped me out a lot with the telephone conversation.

I can see that you're really starting to grasp how to use SQLNotes to solve information management problems. It's great to see how you went from inquiring about it, to being totally confused, to seeing the possible use but not knowing how to go about it, to finally seeing the light and now flying on your own. Congratulations.  Thmbsup Thmbsup Perhaps you'll want to share it on / improve the SQLNotes wiki documentation (http://sqlnotes.wikispaces.com/)
Thanks Pierre.  Honestly, in the beginning, I would have had no idea that I would like the program this much.  I was very skeptical.  Now I realize that this is pretty much the notetaking/information management application that I was always waiting for when I first started looking a couple of years ago, which led to that huge notetaking thread and the review here.  You started a very ambitious project, and what's more remarkable is that you've actually been able to accomplish those intended goals.

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 #345 on: January 03, 2008, 01:19 PM »
Does that mean that everything you put in that grid could be shown (elsewhere/somewhere) as subitems of, in this case, the item JazzPractice?
JazzPractice is not an item. It is a field (folder). What this means, is that all items in that grid will be shown when you display the JazzPractice field. They'll also be shown if you display any other filled field (date, description, duration). So setting the source of a grid (top left box in the source bar or Grid>properties) to duration will display all items that have something in duration

by "there" you mean -in that particular grid-? and by "they'll conveniently be displayed in the grid" are you stating the obvious or am I missing something (I dont mean that to sound smart!)
There is that particular grid: true
And yes, I was stating the obvious in this specific case. But as you play with SQLNotes, you'll discover that which items are displayed in a grid depends on many parameters (source, filters, etc). Items exist independant of the grid (unlike Excel, where cells "live" in a sheet). If you delete the JazzPractice grid, you haven't erased any items. Grids are a mean of displaying items. I should say, displaying a subset of all you items (e.g. if you want to see items created/modified in a particular month, you can set-up a grid to show you that)

I'm still a bit confused by items/sub-items
When I went back to my billing grid after christmas the hierarchy seems to be different to what I expected and different to how I left it last..(they partying too maybe :-\)
It should be Project/item with sub-items of hours worked
but
at the moment I have 14 entries sub-items also showing as main items -
I think what happened was that yesterday they showed a main items so I dragged them "into" their correct parent item but now they showing twice -
if I delete one copy, they completely gone
in manage grids window context is turned off, hierarchy turned on - any tips?

Also Parent should be showing sum of items (for hours worked) but isn't anymore for this item - it's not important cause invoice is already sent but just trying to figure what I done wrong case I do it again :)

I'd need more information on the grid source to answer here, however it seems as if the main items don't meet the source, so if you want to see them, better turn on Context Parents.
Superboyac beat me on this, but definitely, a phone conversation could get you going. For those with no Ecco background, SQLNotes can seem very abstract (presentation being "disconnected" from the underlying data). I try to explain this in the wiki...

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 #346 on: January 03, 2008, 05:24 PM »
OK, just a couple of bug reports...
--Whenever I make changes to grid names or update stuff in the field properties, the grid always acts a little funny until you close to grid and reopen it.  Then everything is fine.  This behavior should be refined a little if possible.
--Today, I experienced a couple of crashes.  I can't really say what exactly caused them (it's running fine now).  It happened when I was fiddling around with the auto-hide and pinning of field/html pane options.
--About auto-hide...Most auto-hide behavior has a delayed action to it.  That is you have mouse over and mouse away for a fraction of a second before the pane appears/disappears.  Most people like it this way.  However, I'm impatient and I like the pane to appear and disappear immediately upon mouse over/mouse away (no latency or delay).  Could there be an option to control this, sort of like in TweakUI for Windows where you can set how long (in milliseconds) to delay the menu rollouts?

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #347 on: January 03, 2008, 06:57 PM »
OK, I'm trying a new application for my database.  My undergraduate transcript:
I'm going to use the Item field for the course name.  I'm going to create a new field for "grade" and it will have a drop-down list of possible grades.  Then I want to create a field for GPA average.  Here's the trick, I want the letter values in the GRADE field to have a numerical value associated with it (4.0, 3.7, 3.3...etc).  How do I do this?
Also, for the GPA field, I want it to behave in a kind of fancy way.  The items will be set up in a heirarchy based on semesters/years/total.  I want the GPA to calculate the GPA based on whatever is under it as subitems.  So the total GPA will give me the GPA for all 4 years, but if I just go to the semester item, then it should show me the GPA for just that semester.  How do I do this?

That's it, thanks.

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 #348 on: January 03, 2008, 07:46 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)
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz
« Last Edit: January 03, 2008, 07:51 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 #349 on: January 03, 2008, 08:00 PM »
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:
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz
« Last Edit: January 03, 2008, 08:03 PM by PPLandry »