sql statements help?

[RashidMalik]

I am trying to create a virtual folder that would contain all the merged clips. I found that virtual folders can be programmed using sql statements. SQL statements are mentioned in the help file but not explained. Where can I get the detailed explanation of those sql statements?

[mouser]

Hi Rashid, welcome to the site, and welcome to an advanced feature that not many people use.

And your question is a great one -- it's a perfect case where a virtual group might be useful.

These threads might be the best place to start in general about virtual groups and sql:

• http://www.donationcoder.com/forum/index.php?topic=27336.0
• http://www.donationcoder.com/forum/index.php?topic=4135.0

And I really need to post a list of field clip variable names you have access to.

But for your specific question, here's an answer:

When you merge clips, CHS names the new clip with a title like "Merged clips (4)".

So we create a new group like so:


The key is the sql:

Code: (sql)

(Title LIKE '%Merged clips%')

Actually it would be a little more efficient to make the sql:

Code: (sql)

(Title LIKE 'Merged clips%')

Because we know the clip title will START with the word "Merged" so we dont need the wildcard % at the front.

[IainB]

And I really need to post a list of field clip variable names you have access to.

^ Yes, please!

[RashidMalik]

Huge amount of thanks mouser!
Your query did the job and thus has now made me hungry for learning/understanding the programmability (is this the right word) of the virtual folders. So after the reply I am going to visit the the two links you have, so kindly, provided. Are there other places (in CHS) where we can use them (SQL and variables) as well?

[mouser]

I see I actually do provide a list of fields in the Advanced Features->Virtual Folders page of help, which in online here:
http://www.donationcoder.com/Software/Mouser/clipboardhelpandspell/help/virtual_folders.htm

Fields available for sql statements:
• Index
• UserKeywords
• ClipType
• ParentId
• ParentGroup
• OtherGroups
• Application
• Title
• Description
• ImageIndex
• ExcerptText
• ClipText
• CreationDateTime
• ModificationDateTime
• ViewDateTime
• MarkDateTime
• DueDateTime
• IsFavorite
• UserFlag
• UserCheckbox
• UserRating
• Protected

Are there other places (in CHS) where we can use them (SQL and variables) as well?

Nope, but I'm open to suggestions.

[IainB]

I see I actually do provide a list of fields in the Advanced Features->Virtual Folders page of help, which in online here:
http://www.donationcoder.com/Software/Mouser/clipboardhelpandspell/help/virtual_folders.htm
...

Oh. I already had those 22...
I was kinda hoping that you might have been about to enable a one-to-one relationship with the ID Fields, maybe?  (hint, hint)   
[attach]

[RashidMalik]

How do you write a query to filter results by dates?
For example, suppose I want to find clips older than, say 60 days?
OR
Clips that were taken between two dates?

[IainB]

@mouser: I figured I should post this to re-open this thread, since the title is appropriate, the comment I want to make follows on from the 2nd comment above, and it fits with the suggestion made here:

... Mouser ... How about you then starting a (sticky?) SQL Expressions thread, posting a few examples and inviting users to post theirs and/or suggestions of how and why they use them? ...
_______________________

Having done some exploration/discovery in CHS lately, I needed to update the table showing the one-to-one relationship between Grid Field<-->SQL term.
Are you able to confirm whether the table image below is correct and what the 5 unattached SQL items at the bottom are? (The text from the image is in the spoiler below it.)
(Thanks in advance.)

[attachimg=#]

Spoiler (click to show/hide)

Menu Image of fields Field name ID Menu Text Fields available to SQL statements
Sorted A-->Z
ID (Record ID#) 1. Application Application
Parent Id (Virtual Folder ID#) 2. ApplicationPath N/A
Imagelndex 3. ClipFormat N/A
Title (Note/clip Title) 4. Created CreationDateTime
Ordering 5. DataSize N/A
Application 6. DueDate DueDateTime
Favorite? (True/False) 7. Excerpt ExcerptText
Created 8. Favorite? (True/False) IsFavorite
Modified 9. FileCount N/A
Viewed 10. FileList N/A
Marked 11. Flag UserFlag
DueDate 12. HashText N/A
Excerpt 13. ID (Record ID#) Index
Type 14. Imagelndex ImageIndex
Keywords 15. Keywords UserKeywords
Flag 16. Marked MarkDateTime
Rating 17. Modified ModificationDateTime
Notes 18. Notes Description
DataSize 19. Ordering N/A
ApplicationPath 20. ParentId (Virtual Folder ID#) ParentId
ClipFormat 21. Rating UserRating
FileList 22. Title Title
FileCount 23. Type ClipType
HashText 24. Viewed ViewDateTime
=============
1. ClipText (text in Memo pane)
2. OtherGroups (?)
3. ParentGroup (?)
4. Protected (?)
5. UserCheckbox (?)

[IainB]

Refer: Creating a virtual clip group in CHS to show you all clips with URLs in them

...you could change

Code

(Lower(ClipText) LIKE '%http%') OR (Lower(ClipText) LIKE '%www.%')

to

Code

(Lower(ClipText) LIKE '%http://%')

that will probably reduce the false positives.
_________________________

[IainB]

From: Re: CLIPBOARD HELP+SPELL LATEST VERSION INFO THREAD - v2.25.03 Beta - Oct 5, 2014

New feature automates the creation of SQL for a new Virtual Folder, using the Search string. This (see emboldened item below) is potentially a real timesaver and can avoid having to mess about with SQL:

I've added a couple of important features to the beta; i'd appreciate any bug reports.
New stuff:

• [Feature] New clip grid menu items for creating new groups based on selection (moves clips) or search (creates a virtual group).
• [Feature] Group tree drop target is now highlighting while dragging over it.
• [Feature] Quick search/filter now provides a drop-down of favorite searches; set favorites in the Misc. Options 2 tab.
• [Feature] Better statusbar info about selected group; reports total # clips in view and # selected.

_____________________