End-users are invited to share SQL for virtual collections that they find useful.
Please post questions/problems in another thread, and leave this thread for contributions.
Usage: Right-click on the "virtual" collection in ClipMate Explorer's Collection Tree, Add New Collection. Then it asks where to put the collection, say "below the currently-selected collection (virtual)".
This brings up the collection properties dialog. Ignore the Purging Rules and Garbage Avoidance sections. Set the title, icon, and then change the Collection Type to Virtual. A SQL box should appear.
Enter the SQL to retrieve clips for this virtual collection. Ex:
Code:
select Clip.* from Clip, ClipData where ClipData.Clip_ID = Clip.ID
and Clip.Creator = 'FIREFOX'
and del = false
As you see, virtual collections contain SQL that defines what should be retrieved. I've broken it into 3 lines for clarity. The first will be the same for all valid SQL - retrieve all clips with matching clipdata. This is mandatory, and should not change.
The middle statement is the important one. Here is where I specified the criteria. I wanted all clips where the creator = 'FIREFOX'.
The last line deals with whether or not to accept trashcan clips. Here, I've decided to exclude the trash.
If you look at some of the other stock virtual collections, you'll see that the date-based ones have special markers like: #DATE#. These are placeholders for real-time substitutions which happen right before the query is run. Look at this one:
where Clip.TimeStamp >= "#DATEMINUSLIMIT#"
Limit? What limit? It uses the retention limit of the collection. If you look at the "this week" collection, and temporarily switch the collection type to "normal", you'll see that it also carries settings for a time-based collection, keeping clips for 7 days. When set to "virtual", it can access the time limit field and use it for a date calculation. So the above timestamp calculation will use today-7 days.
If you look at the "bitmaps" collection, you'll see this in the SQL:
ClipData.Format = 2
The formats are as follows: 1=text, 2=bitmap, HTML is -3, RTF is -1, HDROP (file pointer) is 15.
You can usually figure out what SQL to use by using the SEARCH dialog, and examining the resulting SQL in the SQL box (which may be "rolled-up"). Eventually, I hope to link the Virtual Collections and Search dialog directly, so that you can save queries as Virtual Collections.
Login
Register
FAQ
Search
