FAQ home

This document describes how LOGic's picture logging facility works, and shows you how to do neat things like check that all QSOs that have a picture associated with it have the QSL Rcvd field showing a confirmation.

We highly recommend making a backup of your log before trying anything here!

All commands here are entered in the Database Commands window.  Click Tools/Advanced/Database Commands.  You will see a little COMMAND window inside a larger window.  You can type any valid Visual FoxPro command here. You could type invalid ones too for that matter! To execute a command, type it, and press {Enter}.  For instance, try typing

? "I LOVE LOGIC"

then press {Enter}. As you see, this prints I LOVE LOGIC in the bigger outer window.

Here's another:

SELECT * FROM LOG

You've just used your first SQL command!

A history of your commands stays in the COMMAND window.  To re-execute it, position the cursor anywhere on the command, and press {Enter}.  You may edit the command before pressing {Enter}. If you have used Visual FoxPro, dBase, or the Visual Basic Immediate window, this will be familiar to you.

If a command is too long for the width of the command window, just keep typing.  It will wrap.  Likewise, the commands shown below may be too big to fit in your browser window without wrapping.  So just type the whole command before pressing {Enter}.  Do not press {Enter} in the middle of a wrapped command!

You can copy commands from your browser and paste them into the command window.

How it works

Here is how LOGic stores pictures, and associates the stored pictures with a QSO.

Each QSO in LOGic is assigned a Globally Unique IDentifier, or GUID.  This is a really huge QSO identifying number  that is unique in all the universe. No other QSO presently  in your log, or any QSO that you may ever enter in the future, will have the same number. Furthermore, no other LOGic user in the world will have a QSO with the same number.

In case you are interested, a GUID is 128 bits long, and is generated using the MAC (hardware) address of the Ethernet interface in your computer, and the time. Each Ethernet device in the world is also supposed to have a unique MAC address. If your computer does not have an Ethernet adapter, some mysterious algorithm is used to hokey one up for the purposes of generating a GUID.

You can see the GUID field in LOGic.  In the Database Commands window, type

SELECT CALL, GUID FROM LOG

The GUID does not look very pretty--it is a bunch of binary characters stored in a character field.

Ok, back to logging pictures. In addition to the log file, which contains your main QSO data, there is also a log_pictures file.  It contains one record for every picture logged into LOGic.  Let's take a look at it. Type

SELECT * FROM LOG_PICTURES

You will see an ID field.  This field relates to the GUID field in the log file. When LOGic displays a picture, it takes the GUID in the log, and finds all matching IDs in log_pictures. Note that you can store any number of pictures with any QSO. If you do, there will be duplicate IDs in log_pictures. We did not name this field GUID, because in this context, it may not be unique.

Pan to the right to see other fields in the log_pictures table.  Note we see GUID again.  This GUID is unique within the log_pictures table, and within the universe for that matter.  It tells LOGic where to find the picture on disk. Use Windows Explorer to look in the LOGDATA\LOG_PICTURES folder, and the naming scheme will be obvious.

The THUMBNAIL field contains the .JPG-formatted thumbnail that you see in LOGic's picture browse window. Double-click to see the actual data.  It is just a bunch of numbers, as is ultimately everything that is stored in a computer.

Also note that the ID and GUID fields look prettier here than they do in the log.  This is because these fields are stored in a binary-type field, as opposed to a character field in the log.  The values in the log and log_pictures are identical, they are just formatted into hex notation for display purposes in log_pictures. We stored the GUID in the log in a character field so that the data could be imported into earlier versions of LOGic, whose database engine does not support this type of binary field.

The QSO_DATE_T field is the time and date of the QSO. It is provided as a backup to the GUID field in the log. If you somehow lost the GUID field from your log, it would be possible, given enough time and money, to reconnect your QSOs to your pictures.

Managing QSL RCVD vs. Pictures

Ok, enough about the boring internals.  Let's have some fun (OK, I admit I need to get a life).  We will see how to list all QSOs that have pictures on file, but does not show a QSL as having been received, or QSOs that show a QSL has been received, but does not have a picture.

Note that the assumption is made that only QSL cards scans have been entered into LOGic. If you have scanned QSL cards, and also logged say SSTV images, none of this will work, as there is no definitive way to tell if  a picture is a scan of a QSL card or not. On the other hand, if the descriptions of the pictures, or the original file name of the pictures had something that would tell us if it is a QSL scan or not, we could in fact accomplish this with simple additions to the commands we will be discussing.

Let's view all QSOs in the log that have a picture on file, but where QSL_RCVD is not set to Fulfilled.  Type

SELECT call, qso_date, band, mode, qsl_sent FROM log WHERE qsl_rcvd<>"F" and guid in (SELECT id FROM log_pictures)

You can type everything in caps if you want, in this command and all succeeding commands.

Or to see all field in the log table, type

SELECT * FROM log WHERE qsl_rcvd<>"F" and guid in (SELECT id FROM log_pictures)

To see QSOs with QSL_RCVD set to F (Fulfilled), but with no pictures on file, type

SELECT * FROM log WHERE qsl_rcvd="F" and guid not in (SELECT id FROM log_pictures)

To put the results of these commands into an Excel file, type either of the above commands, followed by

INTO CURSOR TMP

In other words,

SELECT * FROM log WHERE qsl_rcvd<>"F" and guid in (SELECT id FROM log_pictures) into cursor tmp

This copies the data to a temporary disk table. CURSOR stands for CURrent Selection Of Records. TMP is the name of the cursor.  This is not the name used to store the file on disk -- it is stored in the Windwes\Temp directory or somewhere like that, and is given a random name. TMP is the name that Visual FoxPro uses to refer to the name. You do not need to delete this temporary file.  The database engine will do it when you close the file, or LOGic closes the file for you when you exit the Database Commands screen.

Now transfer the cursor to an XLS file. Type

COPY ALL TO C:\MYLOGDATA.XLS

You can update your log so QSL_RCVD will be set to F (Fulfilled) for  any QSO that has a picture. At this point PLEASE MAKE A BACKUP if you have not done so already.  So far, we have not written to the database. This command will!

UPDATE log SET qsl_rcvd="F" WHERE guid in (select id from logpictures)

You can type this command in all upper-case of course, but regardless, be sure the F for qsl_rcvd is upper-case.

Using what you learned here, you can do a lot of  things that cannot be done with the Mass Change screens in Database commands, do ad-hoc queries on your log faster than you could in the report writer, and export data in unique ways that are not possible from LOGic's export facilities. The possibilities are endless. We have assisted users in writing a script to automatically import his thousands of QSL card scans into LOGic.

© 2007 Personal Database Applications, Inc. All rights reserved.
LOGic support - advanced topics.