[odb-help] Record Selection Tool: Basic and Advanced Tips

[Rich Cowan]

Guess what?

50 new people have joined odb-help.  In our recent notice
about OC's annual conference in New Haven on June 20-21 (see
http://organizenow.net/conference.html), we included
information on the newly released 0.9.3 version of ODB.  This
resulted in a busy holiday weekend --- not for our office, fortunately,
but for the server that hosts the ODB web site.

This email offers suggestions on the Select Records function.


A.  Basic Query Tips

The user manual has a very brief overview of ODB queries;
the ODB web site has additional information.  One thing that
we just began to do is provide more built in "custom queries".

To see what custom queries are on your machine, do the
following:

1) Click on Select Records
2) Click the checkbox labeled "View Custom Query"
3) Click the Load Query button

These built in queries (all of you should have at least four of
them) are designed to make it very easy to perform simple
maintenance on your database.  Here are examples of some
additional custom queries we recently created (which will
be available by default in future versions):

People with a phone number:
len(wphone)>0 or len(hphone)>0 or len(cell)>0

People with no phone number:
wphone="" and hphone="" and cell=""

People with an email address:
len(email) > 0

People with incomplete mailing addresses who have email:
len(email)>0 and mailcode="AI"

You can easily define these queries by copying checking
the "Add SQL to Restrict..." checkbox and then pasting the
logic part (the 2nd line) into the box below.  Then you can
save them with any name you like.  You can even create
a "Shared Queries" folder on your group's file server so
that all ODB users can access the queries you are most
likely to need.




B.  Advanced Query Question

[Note: this is a fairly advanced topic; if you are not familiar with SQL,
you may want to skip over it for now... we will revisit the issue later
in a less technical fashion!]

QUESTION:  I see that it is easy to select people WITH or WITHOUT a
Particular Activity or Issue or Constituency code.  But what if I have the
new 0.9 SRC code feature turned on and I want to find people with
zero SRC codes checked off (or everyone without a particular SRC
code checked off)?

The answer to that question is that we are still researching it!  One of
our volunteers, Susanna Schroeder did some tests of ODB last week to
see if the current version could use a "NOT IN" clause in SQL.  It turns out
that this kind of SQL query CAN be used in the current version.

For example, if you put this:

(people.id NOT IN (select srclink.peopleid from srclink))

into the "Add SQL to Restrict..." box...

ODB will return a result.  The problem is that this kind of query is very
inefficient running on a Microsoft Access setup.  If your ODB database
has thousands of people with SRC codes, running it might appear to
freeze ODB for a few minutes.  We don't know whether this affects
all dialects of SQL, or even all versions of Jet, but we want to find
out so that we can incorporate the information into future manuals,
including a manual for a version of ODB running on MySQL.

We tried this on OC's database.  Selecting everyone from the state
of NY took no time at all.  Selecting everyone from NY with any SRC
code:

State = "NY" and (people.id IN (select srclink.peopleid from srclink))

took 2 seconds (144 people).  Selecting everyone from NY with no SRC code:

State = "NY" and (people.id NOT IN (select srclink.peopleid from srclink))

took about 7 seconds (335 people total).  For those of you with a math
background, this is known as "exponential" growth; this means that if
we selected a state with 900 people the query would take about a minute
and with 5,400 people it would take about an hour.  As far as we know,
this is not something that just affects ODB; ebase queries can also
take a long time, for example.

It is quite possible that in the future, for the desktop version of ODB,
it will actually be easier to manipulate the database by tagging all the
entries with a particular SRC code with an "Activities" code (say "T1")
and then you could do the query:

Select * from People where not activit like "*T1*

To find the people you were originally looking for.  Then, you could
simply remove the activity code that you temporarily created once
you did what you needed to do with the search results.  This is better
than running a query that will take hours, in many cases.  Of
course, the ability to do this will depend on the much awaited
"mass update" feature which is now in the design stage.

-rich cowan



Date: Fri, 23 May 2003 16:58:45 -0400
From: Susanna Schroeder <vze495hn@verizon.net>
To: Rich Cowan <rich@organizenow.net>

Yes, ODB understands the syntax

select * from people
where people.id not in (select srclink.peopleid from srclink)

and will select all people not linked to any particular source code.You can 
easily use this in the ODB software by typing

    people.id not in (select srclink.peopleid from srclink)

into the "Add SQL to further restrict your search" textbox in the "Select 
Records" screen

The user really cannot utilize outer joins with ODB because both methods of 
adding SQL (to restrict or broaden) place the added code into the WHERE clause.

If what we want is to select all people who do not have a particular src 
code  (who might have no src code), we have to choose all src codes that 
are not the src code you do not want the person to have, then put

    (people.id NOT IN (select srclink.peopleid from srclink )  )

into the "Add SQL to broaden your search" textbox,

making the SQL statement

    Select *  from people
    inner join (srclink inner join srccodes on srclink.SRCID = srccodes.ID)
    on srclink.peopleID = people.id
    where ((srccode = 'NTN' or srccode = 'TB1' )
    or (people.id NOT IN (select srclink.peopleid from srclink )  ))
    ORDER BY last, first

and finally select those records.

Hope this helps,
Sus