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:

• https://www.donation...ex.php?topic=27336.0
• https://www.donation...dex.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: Text [Select]

1. (Title LIKE '%Merged clips%')

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

Code: Text [Select]

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

-mouser (August 02, 2012, 08:50 PM)

^ 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:
https://www.donation.../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:
https://www.donation.../virtual_folders.htm
...

-mouser (August 03, 2012, 04:35 AM)

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)     

[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? ...
_______________________

-DonationCoderTransmit (July 19, 2011, 06:16 AM)

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

Spoiler

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

[Select]

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

to

[Select]

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

that will probably reduce the false positives.
_________________________

-mouser (October 19, 2006, 11:33 PM)

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

_____________________

-mouser (October 05, 2014, 02:41 PM)