Home | Blog | Software | Reviews and Features | Forum | Help | Donate | About us
topbanner_forum
  *

avatar image

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

Login with username, password and session length
  • December 06, 2016, 04:22:31 AM
  • Proudly celebrating 10 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 485655 times)

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 10,325
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #550 on: February 11, 2008, 06:27:36 AM »
OK
in Duration field:-
I notice "Value=>sub-items" is NOT selected - it was previously-
I didnt even check this because other projects are currently showing total of sub-items in Duration field :tellme:
(just double-checked that)

Could this be something to do with the Non-Billable and Duration fields
I havent been filling in the non-billable field, but started messing with it lately - didnt change any settings though
Duration field equation is: 
WorkTime( StartDate , EndDate , 8) - ( NonBillable )

THe Ardfert project reacts as expected to estimates sub-item - i.e. adding duration to project duration
but doesnt respond at all to the other sub-item (Work done)

Meanwhile in another project (same grid - Billing - source set to project)
it shows duration of sub-items but doesnt respond to non-billable

Now I change it to "Value=>sub-items" selected
I'm afraid nothing really changes - the Ardfert project now reacts to nothing in terms of Duration field :(
weird things going on here :huh:
I reckon either I've broke something or there's a bug in here somewhere ...
Tom
« Last Edit: February 11, 2008, 06:29:40 AM by tomos »

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 694
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #551 on: February 11, 2008, 02:23:27 PM »
@tomos,

This is getting hard to follow  :-[

Project is a text field, it lets you enter what project it is related to. You probably what inheritance set for this field (to ease pivot tables) Field properties: values->subs checked

I was proposing to create a yes/no field, called projects which would contain all active projects. You would check that field for each of your projects and then set the grid source to this field.

>it still isnt showing the Duration of subitems
You need to change a sub-item to have force a column-level calc. (i.e. recalculate the parent)

>In Duration field:- I notice "Value=>sub-items" is NOT selected
This should not be checked (this is inheritance) since you want parents=sum(children)

>Could this be something to do with the Non-Billable and Duration fields
No, equations are set-up so that empty field are ignored
Non-billable simply changes the EndDate time. i.e. if startDate is 09h00 and you've worked 6 hours + 2 hours non-billable, then EndDate will be 17h00

>If column calc is not working as expected, make sure that items do not have 2 or more parents

Best is if you can send me the file and I can phone you to work it through.
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 10,325
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #552 on: February 12, 2008, 02:08:06 AM »
This is getting hard to follow  :-[

tell me about it ;D will send you the file..
Tom

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,070
  • Is your software in my list?
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #553 on: February 12, 2008, 05:11:28 PM »
small bug alert (I think):
In the Grid menu, the options for "hierarchy" and "Context parents" are always shown as off.  I thought that before if you selected it, it would turn on/off meaning you can see a box around it if it was on.  I can't tell if it's on or off now.

I'll try using Mantis later, I'm not registered yet.

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 694
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #554 on: February 12, 2008, 05:27:11 PM »
Are you using the latest build?

In fixing the CPU utilisation bug (idle was using from 5-10%, now 0%), this was an unfortunate consequence. It is fixed in the latest build.
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 694
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #555 on: February 12, 2008, 05:29:08 PM »
This is getting hard to follow  :-[

tell me about it ;D will send you the file..
Got the file, I will look at it this evening or tomorrow.

[edit] File modified and sent back. Main reason Duration was not updating was that Hierarchy calculations were turned off. Simple set for the Duration Field: Parent=sum(Children) and click the Update button.
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz
« Last Edit: February 12, 2008, 10:03:07 PM by PPLandry »

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 10,325
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #556 on: February 13, 2008, 07:13:15 AM »
thanks again Pierre for all the direction  :up:

Duration field equation is: 
WorkTime( StartDate , EndDate , 8) - ( NonBillable )

could you (or anyone) tell me what the 8 in there is?
I cant show it without the "code" - cause I get 8) instead :)

Tom

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 694
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #557 on: February 13, 2008, 07:20:22 AM »
thanks again Pierre for all the direction  :up:

Duration field equation is: 
WorkTime( StartDate , EndDate , 8) - ( NonBillable )

could you (or anyone) tell me what the 8 in there is?
I cant show it without the "code" - cause I get 8) instead :)

WorkTime is a user-defined function (view>user code):
----------
Code: Visual Basic [Select]
  1. Function WorkTime(d1, d2, HrsPerDay)    ' d2 is the later date. Handles multi-day tasks
  2.         dim diff, days, hrs
  3.         if isnull(d1) then exit function
  4.         if isnull(d2) then exit function
  5.         d1=cdate(d1): d2=cdate(d2)
  6.         diff=d2-d1
  7.         if diff>=1 then
  8.                 days=datediff("d",d1,d2)
  9.                 WorkTime=days*HrsPerDay
  10.         end if
  11.         Hrs=(diff-int(diff))*24
  12.         WorkTime=WorkTime+Hrs
  13.         workTime=formatnumber(workTime,2)
  14. end function
-----------
Basically, it calculates the worked time based on the start and end. The '8' is used when start and end are on different days. If start is on monday and enddate on thursday, then the duration will be 4 days * 8 hrs = 32. When working full-time on a project, it can be a time saver to simply enter mon-fri and it will bill the correct number of hours
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 10,325
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #558 on: February 13, 2008, 09:47:58 AM »
thanks Pierre

I have a done (yes/no) field
if I set Parent=sum(children) and all sub-items are ticked, then Parent is automatically ticked as expected
BUT
if I add a new sub-item or deselect one of the subitems the Parents dont respond (nor after refresh)
(You know the way, job is done but oops something forgotten or some minor extra..)
Is there something else I can change to affect that or is that a bug?

Another minor thing you prob aware of:
On some fields I changed the Heading name of field -
this doesnt show on actual heading of column until you restart the programme

BTW I (sucussfully) added estimated time and cost fields to billing grid so I can now look at estimates and work done :)
Tom

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 694
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #559 on: February 13, 2008, 11:00:59 AM »
thanks Pierre

I have a done (yes/no) field
if I set Parent=sum(children) and all sub-items are ticked, then Parent is automatically ticked as expected
BUT
if I add a new sub-item or deselect one of the subitems the Parents dont respond (nor after refresh)
(You know the way, job is done but oops something forgotten or some minor extra..)
Is there something else I can change to affect that or is that a bug?

Another minor thing you prob aware of:
On some fields I changed the Heading name of field -
this doesnt show on actual heading of column until you restart the programme

BTW I (sucussfully) added estimated time and cost fields to billing grid so I can now look at estimates and work done :)

Yes/No Done field: Very interesting use, one which I hadn't thought of. Very good. And yes this is a bug. It is now fixed (in next release, no more daily builds until next release). But you need to use the max function, not the sum (parent=max(children). Next release will be out tomorrow.

Heading: yes this is a known small bug. You can report it in the bug tracking if you wish.
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz
« Last Edit: February 13, 2008, 11:04:33 AM by PPLandry »

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 10,325
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #560 on: February 13, 2008, 11:48:41 AM »
havent got bugtracking setup yet :-[
have to add it to the list ..


I wasnt sure would I use the "Done" field or not, but then I was thinking it might be a way of combining a sort of a ToDo list with this grid.
It would be great if I could colour the not done items (automatically) :-\  - would that be possible under Manage Fields/Auto-assign rules ?
YES YES => itemcolor = "red"  :-*
Of course I could sort them by project or whatever as well (or by date if I have them planned that way)


I want to be able to fit it all on one grid -
not a bad idea really/maybe - for me anyways - keeps me reminded of whats done/ToDo as well as keeping track
Tom

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 10,325
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #561 on: February 13, 2008, 11:53:32 AM »
It would be great if I could colour the not done items (automatically) :-\  - would that be possible under Manage Fields/Auto-assign rules ?
YES YES => itemcolor = "red"  :-*

in my enthusiasm there I didnt realise this marks done items as red
can I reverse that someway to undone ones being red?
Also notice it only affects items newly marked as done - others remain unhighlighted even after restart..

EDIT/ def the wrong thing to say [ itemcolor = "red" ] - very unpredictable results!
Tom
« Last Edit: February 13, 2008, 12:08:41 PM by tomos »

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 694
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #562 on: February 13, 2008, 12:08:49 PM »
It would be great if I could colour the not done items (automatically) :-\  - would that be possible under Manage Fields/Auto-assign rules ?
YES YES => itemcolor = "red"  :-*

in my enthusiasm there I didnt realise this marks done items as red
can I reverse that someway to undone ones being red?
Also notice it only affects items newly marked as done - others remain unhighlighted even after restart..
To remove the color, you can double-click on the itemcolor field "(which will display all colored items in the scratchpad grid) and select all items affected and in the properties pane, erase the color. This will apply it to all selected items

A better system for coloring would be
1- in StartDate (or some other field), add A:ItemColor="red"
2- in Done: add A:ItemColor=
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,070
  • Is your software in my list?
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #563 on: February 13, 2008, 12:29:51 PM »
Are you using the latest build?

In fixing the CPU utilisation bug (idle was using from 5-10%, now 0%), this was an unfortunate consequence. It is fixed in the latest build.
OK, it's fixed in the latest build.  But now I'm confused because I'm having a problem similar to the problems I had when I first started using SQLNotes.  When I first started using it to catalog my favorite music artists, I made a separate grid for each artist (One for Atlanta Rhythm Section, and another for Gene Harris).  Then, I figured what's the point of separate grids, so I combined them by renaming one of the grids to "Music" and checking the box for the other artist's grid to be "on" in the "Music" grid.  Then, I deleted the artist grids in the grid manager because I don't need them anymore.
Now, I have a grid display problem.  Initially, it was fine because the hierarchy was set up like the following:
Artist
--Albums
----Songs

But now I go back, and all the songs are at the top-level, as well as the sub-levels.  But the grid options are fine and changing them around doesn't really do anything.  I have both Hierarchy and Context Parents off, which is what I want so the sub-items won't show as top-level items.

I think the problem is this, and I brought it up once before.  If I create a grid, an "on/off" checkbox is automatically created with the same name as the grid.  This is what determines what items will be shown in the grid.  Now, let's say I rename the grid later...does that checkbox field get renamed also?  It doesn't seem like it does.  I renamed "ARS" to "Music", but the ARS field is still there, but there is no "Music" field.  Furthermore, let's say I delete a grid.  Now what happens to that checkbox?  This is not as confusing a problem...the user just has to remember that he once had a grid called whatever it was.  The renaming is where the problem arises.
I think that there needs to be a more sophisticated way to manage those checkboxes so that they are synchronized with renaming grids, deleting grids, combining grids, etc.  I don't know how to do this...I may even be way off in my assumptions here.  But that's what I think I've seen so far.

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 694
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #564 on: February 13, 2008, 12:35:47 PM »
1- If you don't want to have all songs top level, you need to set the hierarchy=on (it all depends on the grid source: is it something that songs have or something artists have?)

2- Keep in mind that there is no link between the data (fields) and the presentation (grids). This is by design.

Think of it as a box or a cube. You put info into the box (fields). The cube faces (grids) display some of the info in the box. You control what is displayed on each cube face
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,070
  • Is your software in my list?
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #565 on: February 13, 2008, 01:23:20 PM »
I understand the way sqlnotes works with the data and the presentation.  What I'm saying is that something is not working right, or I'm doing something wrong that I don't know about.  Am I not correct that when a grid is created, a checkbox is automatically created by SQLNotes with the same name as the grid?  And doesn't this checkbox determine what is presented in that grid?  If I'm wrong, I need to know, because that's what I understood.  How else would a grid know what data to show out of all the data in the database?

And here's the problem, look at these two screenshots:
Screenshot_20080213-110533.pngSQLNotes...what is it exactly?   Screenshot_20080213-110729.pngSQLNotes...what is it exactly?
The one on the left is my random notes grid and I am happy with the way it is displayed.  The one on the right is the Music grid, and somehow all the songs got into the top-level, while they are still in the sub-levels where I want them.  Both of them have the same grid display options (hierarchy=off, context parents=off).  When I first merged the data into my Music grid, in its collapsed state only two artists were showing (Atlanta Rhythm Section, Gene Harris).  Now, all the songs are showing top-level also even though I didn't change any of the grid display options (again, which are the same as the random note grid).  So, that is why i'm confused.  And I'm guessing this all happened when I was fiddling with merging all the data by using those checkbox fields and deleting grids in the grid manager.

What's adding to the confusion is that turning hierarchy on/off doesn't seem to change anything in either of those grids.  I turn it off, refresh...no change.  I turn it on, refresh...no change.  In the case of my random notes grid, turning it on or off, the hierarchy remains intact, with no subitems appearing in top levels.

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 694
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #566 on: February 13, 2008, 01:51:37 PM »
Am I not correct that when a grid is created, a checkbox is automatically created by SQLNotes with the same name as the grid?  And doesn't this checkbox determine what is presented in that grid? 

You are perfectly right. Excellent!

As to the problem reported, it is a bug. To improve performance, if the item count was greater than 500, I would silently disable hierarchy. I'll no longer do that.

Next release due tomorrow!
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,070
  • Is your software in my list?
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #567 on: February 13, 2008, 02:03:37 PM »
Am I not correct that when a grid is created, a checkbox is automatically created by SQLNotes with the same name as the grid?  And doesn't this checkbox determine what is presented in that grid?
You are perfectly right. Excellent!
OK, now if that is correct, don't you see a problem that arises when grids are renamed?  If the name of that checkbox field doesn't change, then things get a little confusing because the user will have to remember what the grid USED to be called instead of what it IS currently called.  Right?

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 694
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #568 on: February 13, 2008, 02:14:06 PM »
OK, now if that is correct, don't you see a problem that arises when grids are renamed?  If the name of that checkbox field doesn't change, then things get a little confusing because the user will have to remember what the grid USED to be called instead of what it IS currently called.  Right?
Not really. The grid properties (grid>properties) and the source bar clearly show the fields which specify which items are displayed. It is true that when you create a new grid AND the grid name is not that of an existing field, then a field is created with the same name as the grid. But this is just a convenience and to hide some of the subtilities to beginners. But sooner or later, users must realize that grid names and what is displayed in the grid is not necessarely related.

Renaming field can have consequences on equations, other grids, etc so I'm hesitant to rename fields on grid rename...

How about a prompt on grid rename (only when the grid name = field name) ?
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 10,325
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #569 on: February 13, 2008, 02:24:00 PM »
A better system for coloring would be
1- in StartDate (or some other field), add A:ItemColor=

that seems to work well*
but I want items NOT ticked done to highlight - is that possible?

* BTW still doesnt affect items already ticked done before adding A:ItemColor="red" even after restart
Doesnt bother me - just for the record

EDIT/ and highlight/colour doesnt go away if I deselect "Done" - again FTR...
Tom
« Last Edit: February 13, 2008, 02:26:14 PM by tomos »

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 694
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #570 on: February 13, 2008, 02:28:19 PM »
that seems to work well*
but I want items NOT ticked done to highlight - is that possible?
That is what the StartDate: A:ItemColor="Red" does. Turn on when you put a StartDate

* BTW still doesnt affect items already ticked done before adding A:ItemColor="red" even after restart
Doesnt bother me - just for the record
Auto-assign rules are applied on Add (A prefix), Modify (M prefix) and Erase (E prefix) details here:
http://sqlnotes.wikispaces.com/Fields
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 10,325
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #571 on: February 13, 2008, 02:41:51 PM »
that seems to work well*
but I want items NOT ticked done to highlight - is that possible?
That is what the StartDate: A:ItemColor="Red" does. Turn on when you put a StartDate

Sorry, I wasnt clear

My idea was to highlight things that arent done yet
So my question is if it's possible to highlight items in the grid that arent ticked yes in the "Done" grid -
When I ask it like that seems unlikely to be possible...you'd have to highlight everything else in the database ;D

It was just a trivial question really but I'm following up everything cause I'm learning a lot that way
(hope I'm not driving you crazy.. no wait - dont answer that!)

EDIT/ great, I hadnt looked at the wiki in a while - I need to study equations (amongst other things!)
Tom
« Last Edit: February 13, 2008, 02:44:36 PM by tomos »

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 694
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #572 on: February 13, 2008, 02:49:28 PM »
OK, how about this. All items in the billing grid have the Project field filled. You could put the auto-assign on that field...

Do you want this for both Estimates and Work Done items/subs ? Or just Estimates ? I need to know a bit more how you want to work...

p.s. no you are not driving crazy  :D
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 10,325
    • View Profile
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #573 on: February 13, 2008, 02:57:57 PM »
OK, how about this. All items in the billing grid have the Project field filled. You could put the auto-assign on that field...

Do you want this for both Estimates and Work Done items/subs ? Or just Estimates ? I need to know a bit more how you want to work...

p.s. no you are not driving crazy  :D
phew :D
- I'm not sure yet exactly - I'm exploring!

I think what I would like to know now (in general) is this:

Is there a way to have a colour highlight on an item when a yes/no field is selected and
that the highlight will go/disappear if the yes/no field is deselected?

This doesnt work for the "Done" field:-
AM: ItemColor="red"

I would certainly use this on the "Done" field to show what isnt done -
i.e. colour red = done
Tom

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 694
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: SQLNotes...what is it exactly?
« Reply #574 on: February 13, 2008, 03:00:33 PM »
Is there a way to have a colour highlight on an item when a yes/no field is selected and
that the highlight will go/disappear if the yes/no field is deselected?

This doesnt work for the "Done" field:-
AM: ItemColor="red"
You're close: try AM: ItemColor="red" | E:ItemColor=
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz