My days at work were pretty standard.
Most of the time, my days looked like:
|
8:30 – 1:00
|
Work on current programming project
|
|
1:00 – 1:30
|
Lunch break
|
|
1:30 – 3:30
|
More programming
|
|
3:30 – 5:00
|
Review projects with boss, and get ideas on how to complete
or enhance them
|
I worked more on a project-by-project basis than day-to-day.
Here is an excerpt from my notes on the progression of a new form for
making sure that data is correct before allowing it to be uploaded to the
web server.
Problem:
Current edit checks that have been used the past few
years are slow. They are SQL
statements, but SQL is slow with dBase, especially when querying against
thousands of lines of data, and doing so for over 30 checks.
Solution:
Run one query for each table to be uploaded (select all).
Then use dBase objects to find the errors in the queries loaded to
memory.
Technical Details:
I began working on a test form to be sure that my idea would even
work. Once I got a couple of
the edits working, I copied the new code into the real form to be modified.
The program works by reading the code for the edit checks from a
table, and then dynamically executing the code to see if there are any
errors. Here’s the primary
function for the edits (slightly modified for display):
|
function
structEdits(limit, docno, ccnum)
local
tblRowset, retVal
private
getrowfunc
retVal
= true
if(form.query4.rowset.first())
do
tblRowset
= class::getTableRowset(
form.query4.rowset.fields["TABLENAME"].value.rightTrim(),
docno, ccnum)
//turn
the field into a function!
getRowFunc
= "{;"+form.query4.rowset.fields["FUNCTION"].value+"}"
tblRowset.canGetRow = &getrowFunc
if(not tblRowset.endofset) //there are errors
//write bad fields to file
class::writeBadData(tblRowset)
retVal = false //reject the document
endif
tblRowset.canGetRow = null //get rid of filter
tblRowset.clearRange() //noch ein
form.f.custprogress1.value += 1 //update the progress bar
until
(not form.query4.rowset.next())
endif
form.f.close()
return
(retVal)
|
Here is the table structure for the table that
contains the edit functions.
FUNCTION – the actual code that gets executed
TABLENAME – the name of the table to check
ERROR – the error message to display
REPHIGHLIGHT – the field to highlight in the report
Problems Encountered:
Obviously the code is not too complex, but getting it to work at
first was very difficult. My
first problem involved managing how the data would be organized to most
efficiently edit it. Since each
edit pertains to a different table, I decided it would be best to just load
all the tables into memory at first, then determine which one to check based
on the field “TABLENAME.” My
next big challenge was figuring out how to find duplicate lines.
Duplicate lines exist if the same line number is in the same
paragraph. But since the
dynamic code I was running was a method of the current row, somehow I had to
check the other rows against that row.
So I decided that a separate query had to be made for duplicate lines
so that there would be something to compare against.
The final result is as follows:
//set
a filter
this.parent.parent.dupeQuery1.rowset.setRange(
this.fields["DOCNO"].value +
this.fields["CCNUM_CHGNR_FY"].value +
this.fields["PARNO"].value + this.fields["PERLN"].value)
//if there’s
more than one, there’s an error
return
(this.parent.parent.dupeQuery1.rowset.count() > 1)
In the code, “this.parent.parent” is the main
form, and “dupeQuery1” is the duplicate query, indexed by document,
paragraph, line.
As if that wasn’t enough, I found this function
started returning data that I had already deleted!
The problem was, when you delete rows in dBase, the data is “soft
deleted” a.k.a. still there but not visible.
So, I had to put a method before the edits start that “packs” the
tables to get rid of deleted data. This
was by far the hardest thing to figure out, because according to the dBase
documentation, the object-oriented methods do not support “soft
deletes.”
I also changed how the errors were displayed.
Before, each type of error was generated on a separate page.
I created a report that displayed the error name, and then listed
each error in that category. The
problem was, I had to figure out how to uniquely identify errors, but for
five different tables. The only
consistent data element is the header, so I decided I had to put in
paragraph and line, but make them optional data fields.
Duplicate headers started acting up again with the new edit
reports. If you had two lines
that were the same, they would appear on the report four times. Now the edits use less paper, and are easier to read.
Final Results:
The display also had to be updated.
A new, modern look would also let user’s know that the program had
changed. Here is the old vs.
new look:
Old
New
Notice there is also an “Upload Privileges”
box. This was a later feature
that was implemented for greater document security.
This forces the users to login to the web server, and then the web
server sends back information pertaining to their username on what documents
they are allowed to upload.
