Help/Advice on using Custom searches

[G4DUL]

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

[G4POP]

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

[G4DUL]

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

73 Martin, G4DUL

[hb9cat]

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

[G4POP]

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

[hb9cat]

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

HB9CAT

[G4POP]

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

HB9CAT

Yes save it as a search parameter for future use

[HB9BRJ]

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

[IW3HMH]

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

[DF2MC]

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