Help/Advice on using Custom searches

Need help? - Post here and we will find a solution for you.
G4DUL
Novice Class
Posts: 9
Joined: 16 Aug 2014, 11:00

Help/Advice on using Custom searches

Post by G4DUL » 05 May 2015, 08:38

I hope someone can help.

I have never used (custom) SQL searches, so I wonder if anyone can give a simple example that I might use as a basis, or point me to a tutorial web site. I have tried www.w3schools.com/sql/, but can't relate the examples given to Log4OM.

Sorry for my lack of knowledge.

Thanks again

73 Martin, G4DUL

User avatar
G4POP
Log4OM Alpha Team
Posts: 6957
Joined: 21 Jan 2013, 14:55
Location: Burnham on Crouch, Essex UK

Re: Help/Advice on using Custom searches

Post by G4POP » 05 May 2015, 09:46

G4DUL wrote:I hope someone can help.

I have never used (custom) SQL searches, so I wonder if anyone can give a simple example that I might use as a basis, or point me to a tutorial web site. I have tried http://www.w3schools.com/sql/, but can't relate the examples given to Log4OM.

Sorry for my lack of knowledge.

Thanks again

73 Martin, G4DUL
Martin,

If you want to use the "Custom update query" in the "Field update" area of the QSO Archive manager I must warn you that unless you are very familiar with that sort of query it can be very dangerous and has the potential to destroy your logbook!

However I will assume that you want to do a custom query in the "Search Parameters" of Log4OM? and this is quiet harmless as far as your data is concerned so here is a how to for you to experiment with.

EG: I have several QSO's where 'Claude' is part of the name - Like John Claude Pasquale but I also have other QSO's where Claude is the only name - I need to display all QSO's where the name Claude appears somewhere in the 'Name' field.

Type:

Name='Claude' and you will only get those QSO's where Claude is the only name in the field

Equals (=) works for EQUALS (Exact match) only

Type:

Name LIKE '%Claude%' OR Name like '%claude%' OR Name like '%CLAUDE%' - results in all QSO's where 'Claude' is somewhere in the Name text

LIKE is "similar to"

Where % means everything so %Claude matches everything that ends with Claude

Claude% matches everything that starts with Claude

%Claude% matches everything that contains (or start/end) with Claude
73 Terry G4POP

G4DUL
Novice Class
Posts: 9
Joined: 16 Aug 2014, 11:00

Re: Help/Advice on using Custom searches

Post by G4DUL » 05 May 2015, 10:26

Thank you Terry, that gives me a basis to start from.

73 Martin, G4DUL

hb9cat
Advanced Class
Posts: 37
Joined: 12 May 2014, 05:57

Re: Help/Advice on using Custom searches

Post by hb9cat » 07 May 2015, 07:33

All,

I'm also trying to make use of custom MySQL queries, but I'm a bit stuck.
Here's an Example of what I want to do: I want to run a query to find my status for the 2m VUCC Award, it's about working distinct locator Grids (Squares).
I have built a simple MySQL query, running it directly on the Log4OM MySQL database from the MySQL Workbench works fine;

Query:

SELECT
COUNT(DISTINCT LEFT(GridSquare,4)) AS "Squares"
FROM
log
WHERE
Band = '2m'

Result:
Squares
--------
237

--------------------------

Can I integrate this search somehow in Log4OM ? Ideally this should be a new Award definition.

73 de Marco HB9CAT

User avatar
G4POP
Log4OM Alpha Team
Posts: 6957
Joined: 21 Jan 2013, 14:55
Location: Burnham on Crouch, Essex UK

Re: Help/Advice on using Custom searches

Post by G4POP » 07 May 2015, 08:01

To add that as an award you need a CSV file of grid squares related to DXCC countries and references
73 Terry G4POP

hb9cat
Advanced Class
Posts: 37
Joined: 12 May 2014, 05:57

Re: Help/Advice on using Custom searches

Post by hb9cat » 07 May 2015, 08:45

Ok then forget the award, can I just place that as log query ?

HB9CAT

User avatar
G4POP
Log4OM Alpha Team
Posts: 6957
Joined: 21 Jan 2013, 14:55
Location: Burnham on Crouch, Essex UK

Re: Help/Advice on using Custom searches

Post by G4POP » 07 May 2015, 09:22

hb9cat wrote:Ok then forget the award, can I just place that as log query ?

HB9CAT

Yes save it as a search parameter for future use
73 Terry G4POP

HB9BRJ
Old Man
Posts: 112
Joined: 23 Nov 2014, 10:46
Location: Schaffhausen

Re: Help/Advice on using Custom searches

Post by HB9BRJ » 07 May 2015, 19:41

As the title above the data entry line says, it accepts SQL update queries only. I have tried things like

Select Count(*) From Log

but the result always shows a figure 0. Update queries however are working ok. Let's assume I did a SOTA activation today and forgot to enter "My Sota Ref":

Update Log Set MySotaRef = 'HB/SH-002' Where QsoDate = 20150507

The same result can be achieved more easily using Log4OM's field update feature.

The primary index of the Log table is called QsoId and contains a time stamp showing when the record (QSO) was added to the database. As soon as you have to access this primary key you need an external tool such as the free Database Browser from http://www.etl-tools.com.

If you stay with the default SQLite database, there is another very nice freeware tool for browsing the database or running queries: DB Browser for SQLite from http://sqlitebrowser.org/.

73, Markus HB9BRJ

User avatar
IW3HMH
Site Admin
Posts: 2608
Joined: 21 Jan 2013, 14:20
Location: Quarto d'Altino - Venezia (ITA)
Contact:

Re: Help/Advice on using Custom searches

Post by IW3HMH » 14 May 2015, 16:39

When you set the query parameters, available in every grid, you must simply switch the radio button to custom query.

Here you can type, as example:

DXCC <> '291' AND BAND = '20m'
to see QSO not made with USA in 20m band

So:
1) select CUSTOM QUERY in the SEARCH PARAMETERS
2) type your WHERE (without WHERE statement)
3) press the + button in the top right part of the search screen
4) apply with V button
Daniele Pistollato - IW3HMH

DF2MC
Advanced Class
Posts: 53
Joined: 20 Sep 2015, 06:55

Re: Help/Advice on using Custom searches

Post by DF2MC » 06 Nov 2015, 10:29

Why the custom search show me all countrys not only Japan as requested.

My query: Country = 'Japan' AND Mode = 'JT9' OR Mode = 'JT65'

What is wrong with my search query ?

As soon as I combine two modes and the country, search result show me all country from the log not only Japan ?
If I delete one mode I get only Japan and the one requested mode !

Could you give me please a few example of query`s, I could modify my self, mainly I have problems to combine more than two parameter in the query !

Thanks for your Support !

73 Hans DF2MC

Post Reply