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
  • September 02, 2015, 05:33:06 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

Author Topic: sql statements help?  (Read 2126 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: 34,977
    • View Profile
    • Mouser's Software Zone on DonationCoder.com
    • 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
  1. (Title LIKE '%Merged clips%')
  2.  

Actually it would be a little more efficient to make the sql:
Code: Text
  1. (Title LIKE 'Merged clips%')
  2.  

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: 5,432
  • 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: 34,977
    • View Profile
    • Mouser's Software Zone on DonationCoder.com
    • 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: 5,432
  • 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?