This is my generic technique for
building filters to create resultant data tables
from normalized databases. Where the user has the
ability to either select or reject records
based on many different attributes, the construction of a
filter string can grow quite
cumbersome and mentally daunting. My method goes at
this task the same way you are
supposed to eat an elephant...bite by bite.
Here is an example, exploded out into
specifics to make it easier to follow.
Modularize it as you see fit.
The example database is people who
attend medical association meetings.
We want to advertise a new meeting.
The task is to build a mailing list of people who meet user
selected criteria.
LOCAL aTests :=
{}
// this holds the filter elements
LOCAL aListChoice
LOCAL aStateChoice
LOCAL cFilter
LOCAL n
// OPEN_DBF( ) opens files, sets focus on
specific TAG
open_dbf( "PEOPLE", "ID_UNIQUE" )
// ID_UNIQUE is my key field
open_dbf( "PEO_MEET", "ID_PEO_MEE"
)
// meetings they have attended
open_dbf( "PEO_ADDR" )
// we can have many addresses for a given
person
aadd( aTests, {||
PEO_ADDR->USE_MAIL .and. .not. PEO_ADDR->BAD_ADDRES } )
// we want only good addresses
aListChoice :=
oHTML:getVar("m_LIST_NAME")
// This is the basic structure to build a filter
element.
if valtype( aListChoice ) =
"C"
// The user can pick source(s) in a pull down
selection.
aListChoice := { aListChoice }
endif
if .not. empty( aListChoice )
cFilter := ""
for n := 1 to len( aListChoice
)
if n > 1
cFilter += " .or. "
// allow for more than one acceptable
list
endif
cFilter +=
'PEOPLE->LIST_NAME="' + aListChoice[n] + '"'
next
aadd( aTests, &("{||" +
cFilter + "}") )
endif
aStateChoice
:= oHTML:getVar("m_STATE_ABBR")
// the user can limit the list to certain
states
if valtype( aStateChoice ) = "C"
aStateChoice := { aStateChoice
}
endif
if .not. empty( aStateChoice )
cFilter := ""
for n := 1 to len(
aStateChoice )
if n > 1
cFilter += " .or. "
endif
cFilter +=
'PEO_ADDR->STATE_ABBR="' + aStateChoice[n] + '"'
// these values are in a different DBF
next
aadd( aTests, &("{||" +
cFilter + "}") )
endif
cExclMeet :=
oHTML:getVar("m_EXCLUDE_MEETINGS")
// we want to exclude people already registered
for this meeting
if .not. empty( cExclMeet )
cFilter :=
"PEO_MEET->(eof())"
// criterium from yet another DBF
aadd( aTests, &("{||" +
cFilter + "}") )
endif
do while .not.
PEO_ADDR->( eof())
// step through this file and select the
records we want
// all associated DBFs seek on key
value
PEOPLE->( dbseek(
PEO_ADDR->ID_PEOPLE ) )
// ID_PEOPLE points to key value in
PEOPLE
PEO_MEET->( dbseek(
PEO_ADDR->ID_PEOPLE + cExclMeet ) )
lPass := .T.
for n := 1 to len( aTests
)
// apply each test individually
if .not.
eval( aTests[n] )
// we are looking for a reason to bail out
of this record
lPass := .F.
exit
endif
next
if lPass
TARGET->(
dbappend())
...
endif
PEO_ADDR->( dbskip())
enddo