Updates:
Jan 3, '17: Added option to view a more advanced set of GSAK SQl queries to check for qualification on your own local MyFinds database within GSAK Sqlite. More queries to be added.
Apr 23, '13: Added daily caching career grid to general stats spanning first to last caching day. Also, log date reporting actually fixed.
Apr 17, '13: Fixed log date reporting. That pesky Groundspeak Timezone problem for logs submitted with times. Known issue: Find logs submitted with a time exactly on the hour but near after midnight (1:00:00am) may be reported as the next day.
Apr 14, '13: Fixed GC3VBKH,GC3VHMF,GC3NPYJ - "Not chosen" size wasn't reporting properly.

MyFinds Challenge Checker Updated: Jan 3/2017

by thebruce0

Option 1

Advanced: GSAK Users - locate the "GSAK SQL" button beside a challenge listed below, if the button is available. Click it to display instructions for running SQL queries in GSAK's Sqlite Manager on your local Finds database to determine if you qualify for the challenge, and if so help determine which caches to list as proof in your log.

Option 2

Recommended browser: Firefox, Chrome, or Safari! (IE works, but takes forever as its scripting engine is slower)
Paste your FINDS pocket query file in the text box below
  1. Download and open your finds PQ zip file
  2. Open the GPX file in notepad
  3. Copy and paste the entire content into the textbox below (this may take more than a few seconds).
    * this data is not sent to the server - parsing is done in your browser, locally and privately.
    * note: with large Finds PQs, some functions may take a while, or appear to stall your browser
    * Firefox and Chrome are the fastest parsers
  4. Click Convert GPX to parse the data
    Convert GPX
  5. Select a challenge button below to check qualifications!

Current challenges supported: (if you notice any problem or incorrect results, please let me know)
GC3VHMF-> Check: Challenge - 1x3x5 [1 day, 3 icons, 5 finds each]GSAK SQL
GC3NPYJ-> Check: Challenge - 8 Icons [1 day, 8 Icons]GSAK SQL
GC3NYGW-> Check: Challenge - 100 Stars 3/3+ [1 day, 100+ Stars of 3/3+ DT]GSAK SQL
GC33EWD-> Check: Challenge: Do You Like It Rough? [1 day, 10 finds of 3T+]GSAK SQL
GC33FGR-> Check: Challenge: Do You Like It Hard? [1 day, 15 finds of 3D+]GSAK SQL
GC3VBKH-> Check: Challenge - Size x2 [1 day, 2 finds per Size]GSAK SQL
GC3YHA5-> Check: Challenge - Size x3 [1 day, 3 finds per Size]GSAK SQL
GC3GFAA-> Check: The Unknown Alphabet Challenge [A-Z Alphabet, Unknown cache type, no challenges]GSAK SQL
GC40EE3-> Check: The GREWAL Icon & Size Challenge [1 day, 7 icons, 7 sizes]GSAK SQL
GC40V9A-> Check: Challenge: Can You Spell carnigrewal? [Spell CARNIGREWAL with 11 different cache types]GSAK SQL
GC43VKH-> Check: The Aaron %$&!*#^" Haynes Challenge [Caches with one of 25 symbols in each]
GC11PZW-> Check: Tequila: 81 Proof [Full D/T grid, date restrictions]
GC3B39A-> Check: Star Gazing at Lingham Lake ; A Challenge [325 stars in one day]
GC3YKFV-> Check: anOTHER GREWAL Fizzy Challenge [Full D/T grid, no Traditionals]
GC4ZR68-> Check: Carni Challenge Trail: Well Rounded Attributes [1 of each of 100 attributes]

Check All

Analyze general stats



If you know of a challenge cache for which you think this tool would be of great service, shoot me an email!



(reload script)

SQL #1: Qualification checker. Resulting rows list any qualified days. No results means no qualification found.
Columns: Date, QualifiedTypes, FindsPerType
select foundbymedate as Date, count(*) as QualifiedTypes, group_concat(coalesce(Finds,'?') || ' ' || coalesce(CacheType,'?')) as FindsPerType from (select foundbymedate,Count(*) as Finds, rtrim(substr('Letterbox CITO Event Wherigo Multi Earthcache TraditionalUnknown Virtual Webcam GPS Mega ', instr('B C E I M R T U V W X Z ', CacheType), 11)) as CacheType From Caches Group By foundbymedate, CacheType having Finds>=5) as t1 group by foundbymedate having finds>=15 and QualifiedTypes>=3 order by QualifiedTypes desc, Finds desc
SQL #2: Logging helper. Resulting rows list all individual caches based on a desired date from which to select caches for the proof of qualification.
* Replace YYYY-MM-DD with the date desired.
Columns: Code, Type, Name
select Code, rtrim(substr('Letterbox CITO Event Wherigo Multi Earthcache TraditionalUnknown Virtual Webcam GPS Mega ', instr('B C E I M R T U V W X Z ', CacheType), 11)) Type, Name from caches where FoundByMeDate='YYYY-MM-DD' order by Type, Code, Name
SQL #1: Qualification checker. Resulting rows list any qualified days. No results means no qualification found.
Columns: Date, Types
select Date, count(*) Types from (select distinct foundbymedate as Date, cachetype as Type from Caches group by foundbymedate, cachetype) group by date having types>=8 order by Types desc
SQL #2: Logging helper. Resulting rows list all individual caches based on a desired date from which to select caches for the proof of qualification.
* Replace YYYY-MM-DD with the date desired.
Columns: Code, Type, Name
select Code, rtrim(substr('Letterbox CITO Event Wherigo Multi Earthcache TraditionalUnknown Virtual Webcam GPS Mega ', instr('B C E I M R T U V W X Z ', CacheType), 11)) Type, Name from caches where FoundByMeDate='YYYY-MM-DD' order by Type, Code, Name
SQL #1: Qualification checker. Resulting rows list any qualified days. No results means no qualification found.
Columns: Date, Stars
select foundbymedate as Date, sum(difficulty+terrain) Stars from caches where difficulty>=3 and terrain>=3 group by foundbymedate having Stars>=100 order by Stars desc
SQL #2: Logging helper. Resulting rows list all individual caches based on a desired date from which to select caches for the proof of qualification.
* Replace YYYY-MM-DD with the date desired.
Columns: Code, Difficulty, Terrain, Name
select Code, Difficulty, Terrain, Name from caches where FoundByMeDate='YYYY-MM-DD' and difficulty>=3 and terrain>=3 order by Code, Name
SQL #1: Qualification checker. Resulting rows list any qualified days. No results means no qualification found.
Columns: Date, Finds
select foundbymedate as Date, count(*) Finds from caches where terrain>=3 group by Date having finds>=10 order by finds desc
SQL #2: Logging helper. Resulting rows list all individual caches based on a desired date from which to select caches for the proof of qualification.
* Replace YYYY-MM-DD with the date desired.
Columns: Code, Difficulty, Terrain, Name
select Code, Difficulty, Terrain, Name from caches where FoundByMeDate='YYYY-MM-DD' and terrain>=3 order by Code, Name
SQL #1: Qualification checker. Resulting rows list any qualified days. No results means no qualification found.
Columns: Date, Finds
select foundbymedate as Date, count(*) Finds from caches where difficulty>=3 group by Date having finds>=15 order by finds desc
SQL #2: Logging helper. Resulting rows list all individual caches based on a desired date from which to select caches for the proof of qualification.
* Replace YYYY-MM-DD with the date desired.
Columns: Code, Difficulty, Terrain, Name
select Code, Difficulty, Terrain, Name from caches where FoundByMeDate='YYYY-MM-DD' and difficulty>=3 order by Code, Name
SQL #1: Qualification checker. Resulting rows list any qualified days, with number of finds per size. No results means no qualification found.
Columns: Date, QualifyingSizes, SizeList
select Date, count(*) as QualifyingSizes, group_concat(Finds||' '||Size) SizeList from (select Sizes.s as Size, count(*) as Finds, FoundByMeDate as Date from (with sz(s) as (values('Micro'),('Regular'),('Other'),('Not chosen'),('Small'),('Large')) select s from sz) Sizes left join Caches on Sizes.s=Caches.Container group by Sizes.s, FoundByMeDate having Finds>=2) group by Date having QualifyingSizes=6 order by Date
SQL #2: Logging helper. Resulting rows list all individual caches based on a desired date from which to select caches for the proof of qualification.
* Replace YYYY-MM-DD with the date desired.
Columns: Code, Size, Name
select Code, Container as Size, Name from caches where FoundByMeDate='YYYY-MM-DD' order by Size, Code, Name
SQL #1: Qualification checker. Resulting rows list any qualified days. No results means no qualification found.
Columns: Date, QualifyingSizes, SizeList
select Date, count(*) as QualifyingSizes, group_concat(Finds||' '||Size) SizeList from (select Sizes.s as Size, count(*) as Finds, FoundByMeDate as Date from (with sz(s) as (values('Micro'),('Regular'),('Other'),('Not chosen'),('Small'),('Large')) select s from sz) Sizes left join Caches on Sizes.s=Caches.Container group by Sizes.s, FoundByMeDate having Finds>=3) group by Date having QualifyingSizes=6 order by Date
SQL #2: Logging helper. Resulting rows list all individual caches based on a desired date from which to select caches for the proof of qualification.
* Replace YYYY-MM-DD with the date desired.
Columns: Code, Size, Name
select Code, Container as Size, Name from caches where FoundByMeDate='YYYY-MM-DD' order by Size, Code, Name
SQL #1: Qualification checker. The SQL can't conclusively determine qualification, so you'll need to review possible candidates to determine if every letter qualifies.
Columns: Letter, MatchByFirstLetter, MatchByFirstCharacter, PossChallenges, LikelyQualifiers * Challenges aren't officially flagged as challenges, so the result list includes a count of caches merely containing "challenge" as a word in the title [PossChallenges] - those caches can no longer be included as qualifiers and must be ignored; however, some non-challenges may contain "challenge" in the title and thus are allowed.
* [MatchByFirstCharacter] Number of titles that match strictly by first character (eg, "#13 Name" counts as "#", not "N")
* [MatchByFirstLetter] Number of titles that match ignoring any non-letter prefixes (eg, "#13 Name" counts as "N")
* [LikelyQualifiers] If this is greater than zero then it's pretty much guaranteed that the letter has a qualifying cache (FirstLetter count minus PossChallenges).
select Letter, FindsLoose MatchByFirstLetter, FindsStrict MatchByFirstCharacter, PossChallenges, FindsLoose-PossChallenges as LikelyQualifiers from (select Lets.L as Letter, count(*) as FindsLoose, sum(case when Lets.L=Prefix then 1 else 0 end) FindsStrict, Sum(PossChallenge) PossChallenges from (with Lets(L) as (values ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')) select L from Lets) Lets left join (select FoundByMeDate as Date, upper(g_regexreplace('^[^a-z]*?([a-z])[\s\S]*$',name,'$1','$')) L, g_regexreplace('^([^a-z]*?)([a-z])[\s\S]*$',name,'$1$2','$') Prefix, case when g_regex('\bchallenge\b',Name) then 1 else 0 end as PossChallenge, Name from caches where cachetype='U') QCaches on Lets.L=QCaches.L group by Lets.L) order by Letter, LikelyQualifiers
SQL #2: Logging helper. Resulting rows list all individual caches' first letter (A-Z only), prefix to the first letter, and whether it may be a challenge - from which to select caches for each letter as proof of qualification.
Columns: Code, Date, Letter, Prefix, PossChallenge, Name
select Code, FoundByMeDate as Date, upper(g_regexreplace('^[^a-z]*?([a-z])[\s\S]*$',name,'$1','$')) Letter, g_regexreplace('^([^a-z]*?)([a-z])[\s\S]*$',name,'$1$2','$') Prefix, case when g_regex('\bchallenge\b',Name) then 1 else 0 end as PossChallenge, Name from caches where cachetype='U' and g_regex('^[a-z]',Letter) order by Letter, Name
SQL #1: Qualification checker. Resulting rows list any qualified days. No results means no qualification found.
Columns: Date, Types, Sizes
select FoundByMeDate Date, count(distinct CacheType) Types, count(distinct Container) Sizes from Caches group by FoundByMeDate having Types>=7 and Sizes>=7
SQL #2: Logging helper. Resulting rows list all individual caches based on a desired date from which to select caches for the proof of qualification.
* Replace YYYY-MM-DD with the date desired.
Columns: Code, Size, Type, Name
select Code, Container as Size, rtrim(substr('Letterbox CITO Event Wherigo Multi Earthcache TraditionalUnknown Virtual Webcam GPS Mega ', instr('B C E I M R T U V W X Z ', CacheType), 11)) Type, Name from caches where FoundByMeDate='YYYY-MM-DD' order by Size, Type, Code, Name
SQL #1: Preliminary summary. SQL can't determine exact qualification as proof is quite complex requiring selection. This query will summarize how many cache Types are found for each letter and the number required to potentially qualify.
* Any type count less than the letter's required minimum means it's currently not possible to qualify.
Columns: Letter, Required, TypesFound
select Letter, Required, count(*) as TypesFound from (select distinct upper(g_regexreplace('^[^a-z]*?([a-z])[\s\S]*$',name,'$1','$')) Letter, CacheType Type from caches where g_regex('^[^a-z]*?[carnigewl]',Name) order by Letter, Type) c left join (with Req(Let,Required,o) as (values ("C",1,1),("A",2,2),("R",2,3),("N",1,4),("I",1,5),("G",1,6),("E",1,7),("W",1,8),("L",1,9)) select * from Req) Req on c.Letter=Req.Let group by Letter order by Req.o
SQL #2: Detailed summary. Resulting list is the expansion of each letter showing each cache type found. This list can be used to selectively choose a unique type for each letter, and determine if it is possible to qualify, and with which Types.
Columns: Letter, Type
select distinct upper(g_regexreplace('^[^a-z]*?([a-z])[\s\S]*$',name,'$1','$')) Letter, rtrim(substr('Letterbox CITO Event Wherigo Multi Earthcache TraditionalUnknown Virtual Webcam GPS Mega ', instr('B C E I M R T U V W X Z ', CacheType), 11)) Type from caches where g_regex('^[^a-z]*?[carnigewl]',Name) order by Letter, Type
SQL #3: Logging helper. If qualification is successful, this result lists individual caches for each letter and type from which to select caches to include as proof of qualification.
Columns: Code, Date, Letter, Type, Name
select Code, FoundByMeDate as Date, upper(g_regexreplace('^[^a-z]*?([a-z])[\s\S]*$',name,'$1','$')) Letter, rtrim(substr('Letterbox CITO Event Wherigo Multi Earthcache TraditionalUnknown Virtual Webcam GPS Mega ', instr('B C E I M R T U V W X Z ', CacheType), 11)) Type, Name from caches where g_regex('^[^a-z]*?[carnigewl]',Name) order by Letter, Type, Name