paleolith and the Forgotten Cache Challenge

Here is the list of my qualifying finds for the Forgotten Cache Challenge. I qualify twice over—that is, I have at least two finds for each of the "lonely months". I have two 10-month finds, and more on all the rest of the 1-12 lonely months. I have picked two finds to list for months 1-9 and have listed all my finds over 9 months, including my 13 "bonus" finds. The 50-month find is a bit soft: the cache wasn't there, but the owner was there and told me to log a find. But my 31-month find is absolutely solid.

A description of how I gathered this information is below the table.

Cache Name Lonely Months Date I found Date previously found
Solar System Tour 50 01/21/2011 11/10/2006
Bulldog Bells 31 11/22/2007 04/24/2005
Gnarly, Kidney Buster Cache 23 10/26/2008 11/12/2006
Breadcrumbs 22 01/24/2009 03/18/2007
TWO THE TOP, 2 21 11/30/2008 02/24/2007
El Matador 19 10/27/2007 03/30/2006
Top Of The Mountain 18 01/23/2011 07/18/2009
Molly's Cache 18 01/23/2011 07/18/2009
Maggie's Cache 18 01/23/2011 07/18/2009
CampyC 17 06/11/2008 01/19/2007
1999 17 06/11/2008 01/19/2007
Hidden Pond Trail 14 10/17/2007 08/26/2006
TICKled Pink 13 02/18/2009 01/19/2008
Night to Remember and Then Some 12 01/21/2008 01/25/2007
Buddah's Belly? 12 02/23/2008 02/03/2007
4 islands view 12 03/15/2008 03/06/2007
Lonely Barbeque 12 05/13/2008 05/05/2007
Saturn 12 01/19/2011 01/11/2010
The animal box 11 06/29/2008 07/01/2007
Santa Monica Mtns History Adventure 11 07/25/2009 08/18/2008
Red Rock Roost 11 02/18/2008 03/10/2007
Piuma Cache 11 06/30/2007 07/15/2006
Neptune 11 01/17/2011 02/14/2010
Cloud City 10 02/18/2008 04/19/2007
Roadside Oak Rest 10 04/02/2008 06/24/2007
4X4 Adventure 9 03/29/2008 06/09/2007
Gordon's Power Work Out Cache 9 06/11/2008 09/07/2007
Mounds 8 02/18/2009 06/27/2008
SURF'S DOWN 8 06/20/2007 10/29/2006
PCH 7 01/22/2011 06/11/2010
PEACE---------OUT 7 08/23/2009 01/25/2009
vis-a-vis Josepho 6 06/18/2008 12/29/2007
The Serengeti 6 10/03/2009 04/12/2009
Mabry Park East 5 09/09/2011 04/10/2011
Diamond in the Rough Returns 5 02/27/2008 09/08/2007
Slippery Slope 4 06/04/2009 02/11/2009
PALISADES HIGH POINT 4 03/30/2008 11/17/2007
Panorama View 3 11/29/2008 08/04/2008
Sweet Smell Spot 3 01/13/2011 10/29/2010
Charming Mouse 2 05/26/2007 03/13/2007
tozainamboku's Pacific View 2 11/29/2009 09/05/2009
The Crack 1 05/26/2008 04/27/2008
Which way the Fault Goes? 1 05/25/2009 04/11/2009

So how did I do this?

Creating the table, that is. If you're reading this, then you probably know how I found the caches.

The easy way is to use my Lonely Finds macro in GSAK. Please provide feedback in the GSAK Macro Support forum. (You still have to do steps 1-3 of The Original Way. You need to read the notes in the macro source, which explain this.)

In early March 2012, GroundSpeak reduced the speed at which logs can be retrieved. (This was due to abuse by an unrelated macro.) As a result, it now takes roughly nine times as long as before to retrieve all your logs. Those with many finds could be looking at a couple of weeks, so it's advisable to figure out a way to break it up. As of April 1 (no fool), GSAK and GroundSpeak are investigating how to provide the "get logs" functionality with less load on the GroundSpeak web servers, so I'm not going to try to code a work-around until things settle down.

The Original Way

Here's how I did it originally. I've left this information in case anyone is interested in the underpinnings of the macro.

The keys are GSAK version 8 with direct access, and SQLite Spy to access the GSAK databases directly instead of using macros. I could have done this with GSAK v7 by saving the complete pages of each cache I'd found and then analyzing the saved HTML with a macro, or by accessing the pages directly but counter to terms and conditions. I could have done it even in GSAK v6 by writing a macro to walk the caches and logs. But direct access and SQL make it so much easier.

This is somewhat technical. If you are comfortable working with GSAK, you should have no trouble following the procedure. This could be made into a fully functional GSAK macro with a lot less user action needed, but since this is only for occasional use, I doubt it's worth the time. But if I hear that a lot of people are using this, it might be worth the time.

Feel free to with questions or comments! I do want to know how (and whether) people are using this.

Step 1: install GSAK version 8 and SQLiteSpy. Note that SQLiteSpy does not install in the normal sense. You just download a zip file, and either run the program from inside the zip file or save the .exe file wherever you want to run it from. (I've heard from one person that my link for SQLiteSpy didn't work for them, even though it worked for me. You can find it other places, such as CNET. Just make sure you get it from a reliable site and that you have version 1.9.1 or higher. Earlier versions don't work with GSAK v8.)

Step 2: If you don't already have an up to date GSAK database of your finds, make one. Create a new GSAK database if necessary. Run the My Finds PQ and load it into that DB. (If you are using GSAK direct access to PQ results, note that the My Finds PQ cannot be downloaded that way as of October 2011. It just doesn't show up. You must download it from the web page or get it by email.)

Step 3: In GSAK, with your My Finds database open, do " access -> Get Logs". If this is your first time using direct access, you'll need to log on via GSAK. Pick "All in current filter" and set "Max logs per cache" to 99999. Click OK. If you've found more than a few dozen caches, go for coffee. Getting logs does not count against your direct access limits, but the rate is throttled and amounts to about 500 logs per minute, so if you've found thousands of caches, it will take a while—one user with 7200 finds reports that it took 20 hours. (If you are doing an update run, it's worth setting a filter to include just the caches you've found since the last time you did this "get all logs". After all, logs made to caches you've already analyzed are not relevant to the challenge unless they are "late logs", log date changes, log deletions, or other unusual events which invalidate your "lonely months" claim.)

Step 4: Run SQLiteSpy and open your My Finds database. Specifically, do File->Open Database (not ctrl-O, which does an Open SQL), navigate to the GSAK database folder, open the "my finds" folder. In that folder is a file called sqlite.db3. That's the database. Open it. (The GSAK database folder location is configured in GSAK in Tools->Options->General, Database Folder.)

Note: if you allowed GSAK to install files in the default location, then the database folder is probably inside AppData. This is normally a hidden folder, causing problems navigating to it normally. You may be able to get around this by copying the Database Folder location from the configuration in GSAK, pasting it into the File Name box of the Open Database dialog, hitting enter, and navigating from there. Or in Windows Explorer, do Tools->Folder Options, View, "Show hidden files and folders". Or find a young kid to do it for you.

Step 5: Copy and paste the following SQL code in the upper right window:

select 'Cache name',myl.lparent 'GC code',myl.ldate 'Date found',
max(other.ldate) 'Date previously found',
(strftime('%Y',max(other.ldate))*12+strftime('%m',max(other.ldate))) LonelyMonths
from (caches myc join logs myl on myc.code = myl.lparent)
join logs other on myl.lparent=other.lparent and myl.ldate>other.ldate
where myl.ltype='Found it' and myl.lby like 'paleolith' /* <<<<<<<<<<<<<<<<< change the ID */
and (other.ltype='Found it' or other.ltype='Publish Listing' or other.ltype='Webcam Photo Taken')
group by code,myl.ldate
order by LonelyMonths desc, name;

Change "paleolith" to your ID. Make sure to enter it exactly, including spaces and punctuation, though it's not case-sensitive. Then press F9. This produces a table with the information you see in my table, plus the GC number. This table includes all your finds, even the zero-lonely-month finds, with some exceptions noted below under "limitations".

Step 6: You can simply use that table directly. If you want to take it further, click anywhere in the table, then Select All, then Copy. (It looks like only one column is selected, but in fact this copies the entire table.) You can paste into various programs. To generate a table like mine, open a new spreadsheet in Excel and Paste. This should fill columns A through E, with headings in row 1.

Note: It appears that versions of Excel and/or Windows vary in whether they paste the column headings. I'm using Excel 2002 under Windows Vista, and the column headings are pasted, with formatting. Alan reports that Excel 2010 under Windows 7 does not paste the column headings. If you find no column headings after pasting into Excel, insert a line if needed so that the first cache is on row 2. This is only necessary if you plan to use the formula below; otherwise you can choose to operate without column headings if you prefer. If you want to add column headings, the labels I use are Cache name,GC code,Date found,Date previously found,LonelyMonths.

In cell F2 (the top of the first empty column, below the heading line), paste the following formula:

="<tr><td><a href='"&B2&"'>"&A2&"</a></td><td>"&E2&"</td><td>"&TEXT(C2,"mm/dd/yyyy")&"</td><td>"&TEXT(D2,"mm/dd/yyyy")&"</td></tr>"

Fill this down the column through the last row. You can now copy the cells in this column and paste them into a web page as the rows of a table, as I did for the table above. You'll have to build the web page structure and the table element (though you are welcome to copy that outline from this page if you want).

And if you're good with Excel, you can transform the data in other ways.

Step 7: If you want to create a bookmark list of your qualifying finds, first create the bookmark list using the web site. Then go back to GSAK, check the User Flag on the ones you want in the list, set the User Flag filter, and do access -> Add to bookmark list. Select the list, check All in current filter, and OK.

Limitations and explanations

The limitations are minor, but some of them did confuse me, so I'll list them here.