SQLNotes...what is it exactly?

[tomos]

You're welcome!
...

EDIT/
* in "WorkDone" I have files as Parent items but wont give them InvoiceIDs themselves
i.e. File has subitems that are given invoice number
- SQLNotes01.psd
- - Drawing of same - 2hrs - Invoice#200

-tomos (February 20, 2008, 11:33 AM)

Exactly. This is how I was imagining the structure. 

-Armando (February 20, 2008, 01:11 PM)

added a few more edits back there!

will try find, havent used it yet
BTW I just made a filters grid myself this morning

must try out the "alike" as well...

[Armando]

1- ANDable Named filters
2- Grid history. Move backwards/forwards in the grid setting history

-PPLandry (February 20, 2008, 01:15 PM)

Nice. I saw #1 in mantis and in the forum, but didn't know about #2. This should ease work flow quite a bit

[Armando]

(I've decided to post here instead of the SQLNotes forum because I'm not receiving notices on a regular basis...)

I'm still struggling too with parents and children. I don't understand what I'm seeing.
 
1) Here are  my main settings.
- Item Saved --> OFF
- Context Parents -->OFF
- Hierarchy --> ON
 
No filter and 1 source (a yes/no field called "test").
 
2) I have 6 items (see the screenshot) :
 
1- Girl Rebecca
2- Girl Diane
3- Boy John
 
4- Mummy Rose
5- Daddy Bernard
6- 2nd Daddy Eugene


3) Ok, so here are the relationships in our family :

“Mummy Rose” is the parent of  all children :
- Girl Rebecca
- Girl Diane
- Boy John

“Daddy Bernard” is the parent of two children :
- Girl Rebecca
- Girl Diane

“2nd Daddy Eugene” is the parent of one child :
- Boy John

All the children have the same main parent : Mummy Rose.


4) According to my understanding the grid should look like that (when hierarchical view is enabled, with the settings described earlier):
 
--2nd Daddy Eugene
------ Boy John

--Daddy Bernard
------ Girl Rebecca
------ Girl Diane

--Mummy Rose
------ Girl Rebecca
------ Girl Diane
------ Boy John
 
 

But this is what I see (after refresh etc.) :



Why is that?

As you can see, Mummy Rose appears as the parent as only 2 children, while, in theory, she’s the (main) parent of three (and if you look at the screenshot you can also see that “Boy John” is selected and has 2 parents, his main one being “Mummy Rose”).

Mummy rose is the parent of 3 children, here's the proof  :



 

[PPLandry]

For this to work fine, the children must not have test checked. Either that or set hierarchy off

I can explain why later if you want.

[Armando]

Sure. Thanks Pierre. I removed the checks as you said, and yes, it works.

(But ... While I still don't understand why I should uncheck the "test" field for children, what I mostly don't understand is why -- when all the children also have the "test" field checked -- is the item "boy john" specifically not represented beneath its 2 parents... while the others are represented (and hence have "duplicates"). Anyhow, take your time!  ).

[PPLandry]

Here is why,

The grid starts off with the data source, in this case it is all parents and children. There is only 1 boy john in the database.

In flat view, you'd get that list. Expanding each parent would show all children (grid creates new items for each children)
When you click hierarchy, the grid will arrange the source items under their parent. This works fine when there is 1 parent. It doesn't work when there is 2 parents and both are shown in the grid (since a grid item cannot be in 2 places). You can click "show all children" to display the ones which aren't shown.

There are times where this way is the best and other times where showing all is the best
In the previous version, I had it the other way. I'll need to add an option I guess 

[Armando]

Thank you very much Pierre.

I'm still not absolutely sure why certain items would show in 2 places (like you said) and not other items (in my screenshot : Girl Diane and Girl Rebecca are "duplicated", but not "boy John"). Especially in a grid situation where a source would be based on a filter such as category alike "test" (where "test" is a key word among many other keywords) -- removing the word "test" from all the subitems just to have a clean hierarchical view would be pretty inconvenient.

Then, there is the "Show all sub items" option, which will effectively show all children. Unfortunately, this won't stick with a refresh.

So, in my very humble opinion -- I'm really willing to understand why it should be otherwise -- to facilitate visualization of the hierarchies, the default should probably be to always show all subitems. I just can't imagine a situation where I'd need it otherwise.

[PPLandry]

Here is an example where you do not want to see all subs:

You have a list of contacts arranged (sometimes) as Company with your company contacts as subs. This is great since SQLNotes will search the parent when dialing, hence you only need to enter the company number once.

so you have this, but under the company, you also have lots of other info (not related to contacts). If you show all subs, you'll get a list with not just your contacts, but all that other stuff (There is a workaround using filters but still).

It is also much much quicker to simply re-arrange items than generating the grid with all subs, so for large grids, this way is better. Both ways have advantages and disadvantages...

As to Girl Rebecca, I'm sure that Test was not checked for her to show twice

Show hierarchy, simply means, re-arrange the flat list to show parent-child relationship. Better off to leave it off in this case

[Armando]

Thanks Pierre. I understand the usefulness of not showing certain subitems, and showing others. But how do I know what's going to show as subitems and what's not? What's the rule?

In my previous example, I still don't understand why "boy John" is not appearing in "mummy Rose"'s children ( I'm starting to feel a bit stupid with my silly characters), but others will. They all obey to the same criterias, they ALL have Mummy Rose as their main parent.

[PPLandry]

Thanks Pierre. I understand the usefulness of not showing certain subitems, and showing others. But how do I know what's going to show as subitems and what's not? What's the rule?

In my previous example, I still don't understand why "boy John" is not appearing in "mummy Rose"'s children ( I'm starting to feel a bit stupid with my silly characters), but others will. They all obey to the same criterias, they ALL have Mummy Rose as their main parent.

-Armando (February 20, 2008, 09:09 PM)

Basically, you take the flat view and re-arrange it. If an item has multiple parents shown in the grid, I believe, it puts the item under the main parent.

[Armando]

The flat view is not a problem : each parent have all the expected children.

Yes I was expecting that the subitems would be under their main parents, but that's not what's happening, as I said.

In the hierarchical view, the "mummy rose" item has only 2 children, even if it is the main parent of 3 items including "boy john". And "2nd daddy eugene" item has 1 child, "boy john", even if it's not the main parent.

[PPLandry]

The process is quite simple:
Take the flat view with "Save item state off"
You get a list.

When you set hierarchy to ON, it simply re-arranges that list.

[Armando]

Yes, I understand that perfectly well... But that's not my question. My question is simple. You say :

If an item has multiple parents shown in the grid, I believe, it puts the item under the main parent.

-PPLandry (February 20, 2008, 09:12 PM)

And that seems to apply to "flat view"

But what's the rule governing what duplicate children goes under which parent in the hierarchical view?
(was expecting that each would go under its main parent or something like that, but in a very strict and logical way. But I fail to see the logic. Again : look at the screenshot. Some subitems are duplicated and some not, and there are no apparent reasons. All the subitmens have the same main parent, they have the same fields filled. Yet, "boy john" is the only one that's not duplicated and appears under a parent that's not its main one.  )

This is what I don't get.

[PPLandry]

In the latest build, there are now 2 modes for hierarchy. Full hierarchy shows all subs which is what you need for this case.

[Armando]

That was really quick! This is wonderful. 

I'll admit that I still don't understand what are the rules underlying the "non-full" hierarchical view though (so full hierarchy will definitely be the default for me)...  It will surely be easier to understand over a coffee or something. I how you a big one !

[superboyac]

Hi Pierre, I noticed the new "Full Hierarchy" option.  I still don't know how to fix my music grid.  Did you fix the issue with the hierarchies that have more than 500 items?  I still can't get my original hierarchy, no matter how I tweak the options.  You should have a copy of my database, it's in the last email I sent to you.
I'm still wondering about the whole renaming grids process.  I get your point that if the checkbox was also renamed, then the equations would potentially get screwed up.  How about using an ID number similar to the Item ID number that you are currently using, except the number will apply to grids.  That way, if grids are renamed, the ID number stays the same and maybe it won't be as confusing (like having " " being entered for the MusicItem field even though I'm now using Item).

[Armando]

How about using an ID number similar to the Item ID number that you are currently using, except the number will apply to grids.  That way, if grids are renamed, the ID number stays the same and maybe it won't be as confusing (like having " " being entered for the MusicItem field even though I'm now using Item).

-superboyac (February 22, 2008, 11:54 AM)

If that isn't already functioning that way, I think that this a good idea : ID numbers for grids AND fields (fields might be functioning that ways already though, haven't checked...), independent from the names. Equations, formulas, etc. would only deal with the underlying ID number.

[superboyac]

As far as I know, only the items are using ID numbers.  Obviously, Pierre knows the implications of this better than us.  But just by speculating, assigning ID numbers to grids and fields has pros/cons.  For the grids, the benefit is that renaming the grid won't cause confusion for the user like I'm experiencing.  On the other hand, it's much easier and more intuitive to use field names instead of numbers in equations.  Let's say you use a field name in an equation and then you rename that field.  Do all the equations get updated with the new name?  Or do the equations stop working because that name no longer exists?  I don't know.  As Pierre said earlier, when a grid is renamed, there's an internal workaround where even though the old name isn't being used anymore, it still is including a blank entry " " so that the item still can be recognized as part of the grid (regarding that checkbox field that determines what the grid will show).

[Armando]

If I understand correctly, for ID numbers to be user friendly, Grid/field names would  have to be always transparently translated into the underlying ID numbers (normally "invisible" to the user). The problem is that even in the case where SQLNotes would be aware that 2 different names are used for the same field/grid, it could become confusing for the user... who might not remember that 2 (OR MORE) different names are in fact used for the same grid/fields!

In the ideal idiot proof world, SQLNotes would correct all the formulas when you change the field/grid's name... 

Like you said, "Pierre knows the implications of this better than us"....

[suleika]

Thank you, superboyac and Armando, and all contributors here!  It was a long thread, but it broke the ice of my understanding about how this program works.  And thank you, PPL, for all your posts here.  I hope you are still around and reading -  I'd prefer to post here for now but let me know if you want me to cross-post onto your forum.

I'm more than curious to try SQLNotes but I'd like to understand just a little better how it can play as a relational database.  The reason I ask is that although I'm no expert, I have used Access and HanDBase before, both of which are relational, and I know how to go about creating fields and items in that kind of structure.  I just can't get my head around how that translates to SQLNotes.  I'm going to play with a few items and fields and grids, but if anyone has already been through this thought process and can give me some pertinent metaphors or examples, I'd be really grateful.

An example of the "relational" sort of thing I would want to use it for is a historical work database for my singing gigs (to help me remember what I've done!).  I would be listing jobs, singers, conductors, venues, dates of performances, works performed etc.  Any given singer might have performed in any number of jobs with me, any work might have been performed in any number of jobs, any job might have many works performed and even more than one conductor, and a work would have a composer and might have other properties such as translation or edition.

I've been seriously considering using a wiki for this, but although I love wikis I find them a bit slow and/or clunky, and even in the clever Wikidpad, where it's possible to auto-extract lists of "items" with certain "fields", I would have to learn some python (a lot for a beginner like me!) to generate useful lists and views.  I considered a serious database solution but HanDBase on the desktop has a horrible GUI and I try to avoid MSOffice as much as possible.  I have a feeling SQLNotes would be ideal here.

I have various lists and spreadsheets dotted around (in Excel, in PhatNotes, in an old Wikidpad, and in Outlook) that I would love to consolidate.  I've procrastinated the consolidation (and the historical work database) because I imagined I could find something sophisticated enough but also syncable with palm/windows mobile, but I find that more and more I use my handheld as a static device, to which I write information, but on which I edit very little.  So now I'm after something that works best for me on the desktop but which can export to readable formats like HTML or to spreadsheets.

[PPLandry]

Hi suleika,

I'll answer in more depth later on, but in a nutshell:
1- SQLNotes is based on a relational database
2- Relational databases (i.e. Access and others) can read SQLNotes data
3- Using inheritance, you can arrange SQLNotes data so it it appears relational to other databases to the point that you can base Access reports on SQLNotes data.

[Armando]

An example of the "relational" sort of thing I would want to use it for is a historical work database for my singing gigs (to help me remember what I've done!).  I would be listing jobs, singers, conductors, venues, dates of performances, works performed etc.  Any given singer might have performed in any number of jobs with me, any work might have been performed in any number of jobs, any job might have many works performed and even more than one conductor, and a work would have a composer and might have other properties such as translation or edition.

You can do that in SQLNote. Items can have multiple parents, items can be in different "grids", etc. Wikis aren't as powerful and aren't as flexible.

Like Pierre likes to say : Think of SQLNotes and its grids as a 3d container (like a cube) to allow you to have different perspectives on your data. Each face is a different way at looking at your own data. (Of course, the "container" could have as many faces as you want... a dodecahedron?...  )

SQLNotes can be used to organize many many things. SQLNotes can also export to html (you can also create special templates...).

Of course, even if I'm not  yet an "expert", I could help you too... but this week is going to be pretty busy for me... I'll try.

In the mean time, you could start with the basic Wiki. Have a look the Getting started page. In the Wiki documentation you'll learn a a bit about Items, Fields, Equations, Search/Filtering, etc.

Don't forget to also have a look at some of the examples! (look at the column on the left of the Getting started page) And look inside all the forums for help.

 

(IMO, the hardest think with SQLNotes might be all the subtleties allowing to display your data exactly the way you want (flat view, hierarchical view, save item state, filter criterias apply to subitems, etc.), and also the filters/sources "language" if you're not used to writing your own filters.)

PS : I,m also including the new alias I use with farr to search for info concerning SQLNotes! If you use farr, put the alias in your find and run robot alias directory.

[PPLandry]

Just a heads up... Latest build is the first version to support Unicode content (in grid, properties pane and rich-text pane) (http://mantis.sqlnot...s.net/view.php?id=78)

Also, grid hyperlinks is partially enabled (http://mantis.sqlnot...s.net/view.php?id=62):
<a xxx>linkText</a>

where xxx can be:
1- an itemID
2- a Field/folder enclosed in [  ]
3- a Wikitag. Wikitags are enclosed in { } and entered in the WikiTag field

If the linked item is in the grid, focus is moved to it. Otherwise or if the link returns more than 1 item, the scratchpad grid is opened with the linked item(s).

[Armando]

Great!!!! 

[PPLandry]

Just a heads up: Daily build was updated to include in the properties pane, the parents tree (sometimes called lineage):