Alphabet letter challenges

[comkelpModerator]

I’m looking for a macro to total how many caches starting with each letter of the alphabet. In my field notes, I have 26 caches ready to log, when I get this figured out. Thanks ahead!

Jeff Grosvold

[CodeJunkieParticipant]

Not sure about this because I’ve seen different variations of cache challenges involving the letters of the alphabet. Almost every one that I’ve seen has specifics regarding the cache name.

Examples:
1) Starts with the letter (simple requirement)
2) Starts with letter, but ignore numbers in front of it.
3) Starts with letter, but ignore WSQ (and similar)
…

I think you get the idea, but if you could be more specific in what you’re trying to accomplish perhaps there’s a solution.

[comkelpModerator]

Thanks for the reply!

example #1

Jeff Grosvold

[CodeJunkieParticipant]

Not sure of a macro, but thinking this should be doable via the built in filtering. You may need to do 26 filters (one for each starting letter), but …

Don’t have my PC with GSAK handy right now, so I can’t test my theory. Writing a macro to do this should be pretty easy though and maybe I’ll give it a whirl tomorrow.

[comkelpModerator]

Thanks! I seem to be a very slow learner in gsak. I am making slow progress though.

Jeff Grosvold

[LacknothingParticipant]

@codejunkie wrote:

Not sure of a macro, but thinking this should be doable via the built in filtering. You may need to do 26 filters (one for each starting letter), but …

Don’t have my PC with GSAK handy right now, so I can’t test my theory. Writing a macro to do this should be pretty easy though and maybe I’ll give it a whirl tomorrow.

If you use http://www.mygeocachingprofile.com/ Isn’t that automatically one of the things you can track? I am thinking I have it on my profile.

The views expressed here are that of myself only and do not necessarily represent that of the WGA board.

[Team Black-CatParticipant]

@codejunkie wrote:

Not sure of a macro, but thinking this should be doable via the built in filtering. You may need to do 26 filters (one for each starting letter), but …

Don’t have my PC with GSAK handy right now, so I can’t test my theory. Writing a macro to do this should be pretty easy though and maybe I’ll give it a whirl tomorrow.

Not something for a novice, but this could be done rather easily in GSAC using SQL in a macro. I don’t have GSAK on this computer, so I’m not sure of the exact syntax and filed names, but something like this might work.

SELECT DISTINCT LEFT(CacheName, 1) As 'Name Starts With', COUNT(*) As 'Total'

FROM CacheTable

GROUP BY Left(CacheName, 1)

ORDER BY Left(CacheName, 1)

“LEFT” might actually need to be SubStr(CacheName,1,1) or something like that.

[WStempleParticipant]

Here is a quick little SQL statement that I whipped up and ran:

Select upper(substr(ltrim(Name),1,1)) as 'First Character'

, count(*) as 'Total'

From Caches

Group by upper(substr(ltrim(Name),1,1))

Order by 1

The LTRIM is used to remove leading spaces from some of the names and the UPPER should be obvious. Just fire up Sqlite Manage from the TOOLS menu and then open the proper database that you want to query.

[labrat_wrParticipant]

huh???? ran this text through Babelfish and it made no sense at all. What language is this??? 🙄

Disclaimer : Always answering to a higher power.

[Team Black-CatParticipant]

Like I said, not for a novice, but someone with intermediate GSAK macro experience and a little background in SQL could create a nice, easy to use macro that would spit out exactly what comkelp is asking for. If CJ or Wayne haven’t posted a solution by the time I get home, I’ll see if I can throw something together.

[WStempleParticipant]

@labrat_wr wrote:

huh???? ran this text through Babelfish and it made no sense at all. What language is this??? 🙄

What I posted is SQL (Standard Query Language). It is used in thousands of applications and on all type of machines. If you look at some of the macros, you will see SQL statements. The $_sql command is used to execute them in a macro.

This SQL statement is meant to run in the Sqlite Manager window, but can be included in a macro also. Additional things can be done with it, like filtering out archived and disabled caches, etc. Based on the original post, I think this is what he was looking for. Just a simple list of the first character in the name and how many caches start with it.

[WStempleParticipant]

If you add:

Where upper(substr(ltrim(Name),1,1)) between "A" and "Z"

to the SQL statement, then you have what he is looking for based on his response to CJ’s list of different type of challenges. It just gets add after the FROM Caches clause.

[CodeJunkieParticipant]

AHHH! It feels good to dabble in programming for a change.

Got it working via a macro:
http://webpages.charter.net/edmarks/GSAK/FoundCachesByInitialCharacter.gsk

You should be able to “right click” on the link and save this to your PC and then install the macro.

It runs a query for the “FOUND” caches and displays the data in a webpage.

[comkelpModerator]

Thanks Everyone! I appreciate your help and glad to say I got it………finally!

Jeff Grosvold

[Team Black-CatParticipant]

Good job CJ!
Don’t forget to set a filter for found caches if you don’t have a seperate database for them.

EDIT: Scratch that. I didn’t see the “Where Found” the first time through…

[Author]

Posts