Writing some consistency checks may demand having to reference information already collected either within the same database file or across multiple
database files. In this section we look at the case of writing consistency checks for fields within the same database file.
Two examples are considered. In the first example, consistency is established between two fields, the start date and the end date, in an episode file. In the second example, consistency is established between the records in the same file.
For the first example, a consistency check is needed to restrict the end date of an episode to be greater than the begin date of an episode. The
ResidencyRowChecks stored procedure is part of the database and it serves as the record row checker for the table Residency (see the record validation slot under the tab TABLE for the Residency table). The consistency rule should be placed in this procedure - associating it with a field would restrict the order of data entry. The code to put in the ResidencyRowChecks is as follows :
m.sdate = residency.sdate m.edate = residency.edate
if not empty(m.edate) and (m.sdate > m.edate)
whrsadderror("Starting date is later than the ending date ") m.validitystatus = .F.
endif
return m.validitystatus
The procedure WHRSadderror is a stored procedure that collects all errors for another stored procedure whrsdisplayerror to display them. A quick way to get to any stored procedure is to double click on any of the stored procedures, then right mouse button click - a list of methods will come up, chose
Residencyrowchecks and close the dialog.
To put the same consistency check in a data entry form, a check in the edate (end date) control needs to check that a value for sdate has been entered and then compare the two fields. The code in the checkvalidity method of the edate control would look something like the following :
m.sdate = thisform.osdate.value if empty(m.sdate)
this.displayerrormessage(" cannot enter an ending date without putting in starting date first ") return -3
endif
if (this.value < m.sdate)
return .F. endif
Thisform.osdate.value gets the value of sdate from the form control
associated with sdate. A return -3 statement moves the focus of control three fields back (the location of the sdate control).
To put this rule into the validation module would require some work for, at the moment, we do not look into event or episode files to check the consistency of an individual. In the near future, this should be added.
In the second example, we check for consistency in the father IDs that are entered in the individual record. The basic idea is to check that we have either a legal ID value or one of the possible values for an ID field that is unknown. If there is a legal ID value, then the gender of the father ID is checked. The field level rule that is placed in fatherid of the table Individual is as follows :
otherid(fatherid).OR.legalmaleid(fatherid)
Both otherid and legalmaleid are stored procedures associated with the
database. While it is not necessary to know how legalmaleid works, it may be instructive to look inside the stored procedure. A portion of the code in this procedure follows :
m.savealias = alias()
select individid,gender from Individual where individid == pindividid into cursor ccheckList m.individid = ccheckList.individid m.gender = cchecklist.gender m.reccount = reccount('ccheckList') IF USED("ccheckList") USE IN ccheckList ENDIF if not empty(m.savealias) select (m.savealias) endif
if (m.reccount = 1) and (m.individid == pindividid) and (m.gender = "M") return .T.
endif return .F.
m.savealias is a local variable that holds the value of the currently open table (as given by alias()). The select statement is an SQL command that extracts a portion of data from a table. The select command is used throughout the
HRS2. An understanding of the select command is essential for making
of writing SQL statements as this is done in a number of places. The select command creates a new table (cCheckList) and makes this table active. After extracting the data from the table, it is closed and the m.savealias table is made active. The above code is very similar to a large number of consistency checks in HRS2.
The code in the checkvalidity method of a HRS2 control associated with a fatherid field would look like the following :
if otherid(fatherid).OR.legalmaleid(fatherid) return .T.
endif return .F.
Note that in baseline data entry, the consistency check will look in the individual file as well as in the collection of records that are currently being entered. This gets a bit more complicated and is not central to this
presentation. See the code for more details.
Putting this code in the validation module is a straightforward extension of the concepts presented above so it is not covered in any more detail.
5.3 Writing consistency checks that look across
multiple files (databases)
In certain cases consistency rules may be need to check against date data residing in different database files. This subsection describes how to do this. All events and episodes have at least one observation ID associated with the record. The observation ID allows someone to link into the observation table to determine the field visit date, the location, and the field worker who found the event or episode. A referential integrity check is needed to only legal observation IDs.
The field level rule associated with the observeid field in any one of the event or episode tables is as follows :
observeidfound(observeid)
Again, we make use of a stored procedure in the database - in this case, the observeidfound procedure. To gain more familarity with the SQL select
command, we look inside the observeidfound procedure :
local m.individid, m.reccount, m.savealias *
* Certain entry operations (such as Baseline) enter in a collection of records at one time. * Since the IDs are constructed in a consistent manner and it is very difficult
* to check for consistency among partially committed records, we allow an escape *
if (type('oGHRS') == 'O') and oGHRS.relaxidrules return .T.
endif
m.savealias = alias()
select observeid from Observation where observeid == pobserveid into cursor ccheckList m.observeid = ccheckList.observeid m.reccount = reccount('ccheckList') IF USED("ccheckList") USE IN ccheckList ENDIF if not empty(m.savealias) select (m.savealias) endif
if (m.reccount = 1) and (m.observeid == pobserveid) return .T.
endif return .F.
Notice the similarity with the legalmaleid stored procedure described above. This code includes a few more lines that were actually present in the
legalmaleid method. These lines check whether the GHRS objects has been created and, if it has been created, is the relaxidrules variable set to True. The oGHRS.Relaxidrules variable is used when a collection of records are entered and those records point to one another for ID values. Since the HRS2 does not force a sequence for the tables to be saved in and the SQL select command only extracts from saved records, we cannot do complete ID checking in some data entry forms. The baseline data entry form is one
example - fortunately, in this form, the IDs are constructed in a way that the new records are consistent.