Flexlines Volume IX Number 5 (a Data Access publication)
How to Let Users Define the Indexes Your Programs Use
by Michael Steffano
I was recently involved in a project with impossible-seeming challenges, but the power
of Dataflex amazed me once again in how handily it enabled us to overcome the
challenges. The problem was that we needed to support an account code (in an
accounting package) by which to sort account data with up to four sort levels, the
length and sequence of which would not be specified until the user installed the
package.
Here is an example of two users' setups to illustrate:
User A wants a three-section account code (Department, Type, and Project) with
the sort order of Project, then Type, and Department last. Department is a two-
digit field, Type three-digit, and Project, four. Their installation specification would
be: 333322211 (the first field is four digits long and third in the sort, the second field
is three digits long and second in the sort, and the third field is two digits long and
first in the sort).
User B wants Division, Department, Account, and Customer, sorted in the order
they appear in (1234). The number of digits for each of these is specified with
122333334444. The Account for Division three, Department five, Account 55434,
and Customer 8709 would look like: 3-05-55434-8709. In printouts and queries,
these fields happen to be delimited by dashes, but the delimiters are of no conse-
quence here -- the point is the variation in length, print sequence, and number of
these fields.
This is not that unusual a requirement in general-use (as opposed to custom)
applications, but it's very hard to deal with at the product development stage in
which you have to be able to program all kinds of functions and processes that rely
on the scheme ultimately specified by the user, but which can't be known in advance.
Upon first addressing it, I didn't see how it would be possible to deal with effectively,
in Dataflex or anything else. But the end solution resulted in a setup that not only
was easy for the customer, but was also totally flexible and transparent to our
programmers as well.
Overlap Fields and MAKEFILE to the Rescue
Two powerful features of Dataflex came to my rescue. They were Overlap fields and
the MAKEFILE command. The first step of the solution involved defining a 16-digit
field (the aggregate maximum number of digits we would allow in an account code,
not counting delimiters). Then four overlap fields were defined to span as much
of this field as the user's eventual specification called for (four being the maximum
number of subdivisions of this field we would allow). No lengths or starting points
were specified for these fields in the product as we shipped it -- they were just
placed in the file definition ready for specification by users during installation.
Figure 1 shows the portion of the file definitions that relate to the field in
question (ACCOUNT).
FIELD FIELD FIELD FIELD DEC MAIN RELATES--TO
NMBR OFFSET LEN TYPE PTS INDEX FILE FIELD
----- ------ ----- ----- --- ----- ---- -----
1 1 16 ASCII 0 0 0 ACCOUNT
2 1 0 OVERLAP 0 0 0 ACCOUNT_S1
3 1 0 OVERLAP 0 0 0 ACCOUNT_S2
4 1 0 OVERLAP 0 0 0 ACCOUNT_S3
5 1 0 OVERLAP 0 0 0 ACCOUNT_S4
|
Figure 1. "Blank" Account Key Definition Before Installation
FIELD FIELD FIELD FIELD DEC MAIN RELATES--TO
NMBR OFFSET LEN TYPE PTS INDEX FILE FIELD
----- ------ ----- ----- --- ----- ---- -----
1 1 16 ASCII 0 0 0 ACCOUNT
2 1 5 OVERLAP 0 0 0 ACCOUNT_S1
3 10 3 OVERLAP 0 0 0 ACCOUNT_S2
4 6 4 OVERLAP 0 0 0 ACCOUNT_S3
5 13 2 OVERLAP 0 0 0 ACCOUNT_S4
|
Figure 2. DEF File Modified for Input 11111333322244
All the default sorts used in the programs we supplied were based on an index
containing only Field 1, ACCOUNT, but for auxiliary purposes, we supplied means
for customers to query by other sorts. These other sorts were multi-segments
indexes based on the Overlap fields (Fields 2 through 5). Users could specify
these for batch reports using the Batch index (Number 10), which would be
created by the Reindex Utility accessed through a batch file -- everybody gets
what he wants! Only the index based on Field 1 was kept online. We could have
hard-coded these auxiliary sorts in our programs if we had wanted to, but there
was no way to know what the customer would want in advance, and no need to
anyway.
At installation time, the user would be queried as to the desired structure of his
account codes. Let's say he entered 11111333322244. At this point, a .DEF
ASCII file definition that we supply with the application comes into play. It looks
like Figure 1, except that it is a complete .DEF file containing header specifications,
the other fields, and so on.
The installation program reads in the standard .DEF file and edits the OFFSET and
LENGTH of the subkey fields. When it encounters 1s in the input, it modifies
ACCOUNT_S1, when it encounters 2s, it modifies ACCOUNT_S2, and so on until it
reaches the end of the specification string. There are no inside limits on length or
placement of any of the fields, and there don't need to be a full complement of
four. A customer could elect a single field only, if he chose. The result for the
input shown in the paragraph above would be that shown in Figure 2.
After the modified .DEF is saved, our program executes a MAKEFILE to create the
new database from it.
Naturally, our code had to include a certain amount of error-trapping for the overall
length of the field, number of subkeys allowed, digit values used to specify segments,
and so on. As so often happens, the volume of the error-trapping code exceeded
that of the code that actually did the job when acceptable input was provided.
All in all, we were very happy with the results and the fact that Dataflex allowed us
to meet all of our project goals.
Michael Steffano has been working with DataFlex since 1982.
