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 08, 2016, 09:50:59 PM
  • 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

Author Topic: sql statements help?  (Read 3387 times)

RashidMalik

  • Participant
  • Joined in 2010
  • *
  • default avatar
  • Posts: 3
    • View Profile
    • Donate to Member
sql statements help?
« on: August 02, 2012, 11:26:07 AM »
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

  • First Author
  • Administrator
  • Joined in 2005
  • *****
  • Posts: 36,421
    • View Profile
    • Mouser's Software Zone on DonationCoder.com
    • Read more about this member.
    • Donate to Member
Re: sql statements help?
« Reply #1 on: August 02, 2012, 08:50:29 PM »
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:

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:
Screenshot - 8_2_2012 , 8_49_49 PM.png

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

  • Supporting Member
  • Joined in 2008
  • **
  • Posts: 6,141
  • Slartibartfarst
    • View Profile
    • Donate to Member
Re: sql statements help?
« Reply #2 on: August 02, 2012, 09:57:10 PM »
And I really need to post a list of field clip variable names you have access to.
^ Yes, please!

RashidMalik

  • Participant
  • Joined in 2010
  • *
  • default avatar
  • Posts: 3
    • View Profile
    • Donate to Member
Re: sql statements help?
« Reply #3 on: August 03, 2012, 03:15:53 AM »
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

  • First Author
  • Administrator
  • Joined in 2005
  • *****
  • Posts: 36,421
    • View Profile
    • Mouser's Software Zone on DonationCoder.com
    • Read more about this member.
    • Donate to Member
Re: sql statements help?
« Reply #4 on: August 03, 2012, 04:35:37 AM »
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.donationc.../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

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

Nope, but I'm open to suggestions.

IainB

  • Supporting Member
  • Joined in 2008
  • **
  • Posts: 6,141
  • Slartibartfarst
    • View Profile
    • Donate to Member
Re: sql statements help?
« Reply #5 on: August 03, 2012, 08:19:07 AM »

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.donationc.../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)     :)
CHS IDs and SQL Fields aligned.jpg

RashidMalik

  • Participant
  • Joined in 2010
  • *
  • default avatar
  • Posts: 3
    • View Profile
    • Donate to Member
Re: sql statements help?
« Reply #6 on: August 05, 2012, 06:32:51 AM »
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

  • Supporting Member
  • Joined in 2008
  • **
  • Posts: 6,141
  • Slartibartfarst
    • View Profile
    • Donate to Member
@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.)

08_714x594_07387862.png

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

  • Supporting Member
  • Joined in 2008
  • **
  • Posts: 6,141
  • Slartibartfarst
    • View Profile
    • Donate to Member
SQL statements help: Create a Virtual Folder to show clips containing URLs.
« Reply #8 on: January 07, 2016, 10:52:46 AM »
Refer: Creating a virtual clip group in CHS to show you all clips with URLs in them
...you could change

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

to

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

that will probably reduce the false positives.
_________________________



IainB

  • Supporting Member
  • Joined in 2008
  • **
  • Posts: 6,141
  • Slartibartfarst
    • View Profile
    • Donate to Member
SQL statements help: Automate Virtual Folder creation from Search string.
« Reply #9 on: January 07, 2016, 12:07:55 PM »
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.
_____________________