AmigActive 4/AACD04.ISO/AACD/Programming/CanDo/Decks3/Bill-it/Databases.case

Text File | 1994-04-28 | 11.5 KB | 222 lines

BILL-IT AND DATABASES DOCUMENTATION

by Miles Goodhew, Desktop Utilities, March/April 1994
for AustBBS Desktop Utilities CanDo User Group Disk 4


This tutorial attempts to explain the concept of databases for application
in CanDo.  There are several "serious" database concepts and methods that I
have left out, but they're not of much relevance here.  For all you could
want to know about database programming see CanDo Tutorials (from Rush
Software or Desktop Utilities) pp.48-63, and general references such as
Capron's "Computers:  Tools for an information age" pp.448-473.

The case study refers to the Bill-it program, which provides a facility for
generating and tracking invoices.

--------------------------------------------------------------------------

What is a Database?

    A Database is quite simply a collection of information.  This
information is organied to allow easy access for operations such-as adding,
sorting and searching, etc.  A database consists of a number of records.

--------------------------------------------------------------------------

Records

    These show up in most higher-level languages, such-as the Pascal/Modula/Oberon
family and in C as the STRUCT datatype.  A record is a structured or compound
data type that incorporates several other pieces of information, which can
be any type at all.

    To explain this, take an example:  A small business operates a database
which has records for each of the employees.  The information stored for
each employee is:  Name, age, wage, and address (which corresponds to a
record structure consisting of a string, two numbers and another string).
So in their CanDo program, they have an array of records as their database.
Each record consists of a name string, age number, wage number and address
string.  e.g.:

        For 42-year old Fred Smith of No.1 Credibility Street, Bruce,
A.C.T.  26xx, who's paid $230 per week:

        Let Database[1].Name = "Fred Smith"
        Let Database[1].Age  = 42
        Let Database[1].Wage = 230
        Let Database[1].Addr = "1 Credibility Street, Bruce, A.C.T. 26xx"

        Example1 - An example of a record - the "Database" name is, of
course, not required - it could quite easily be "Employees".  In this
example, a new record has been created (or an old one overwritten) at
location 1 of the database array.

    As you can see from this, all the information in the database is
identical in structure , even though each record may have different
information within it.

--------------------------------------------------------------------------

That's all good and well, but what can it do for me?

    Most people view databases as (like the example above) lists of
employees, or catalogues of objects, etc.  What many people don't know at
first is that all sorts of information can be stored as a database.  One
popular application is in ordering or invoicing programs.

    Invoices and order forms often consist of several lines of identically
formatted information (e.g.  Stock no., quantity, description, price/unit,
total price).  These lines lend themselves quite well to being stored as
records in a database (a unique database for each order form or invoice.
Although all of the databases have the same record structure).

--------------------------------------------------------------------------

Forms

    Database forms, much like their paper namesakes are a collection of
fields that need to be filled by the user.  Usually fields have a logical
input focus flow from one to another, and the last one usually "commits" a
record to the database.

==========================================================================

A Case Study

Several users have approached us with queries about making databases or
making invoicing-type programes.  As you might have noticed from the above
(very brief!) explanation, it's not just some simple tip that can be relayed
over the phone.  I also happens that our invoicing program is a little
temperamental, so I thought I might as well write another invoicing program,
and hit two or three birds with the one stone.

--------------------------------------------------------------------------

    What should it do?

Well, the screen layout consists of three main sections:
    1) Entry fields for information unique to each invoice (Name, date, etc.)
    2) A group of fields for entry of multiple lines of order information
(product code, description, quantity, etc.)
    3) A list-box containing the all the lines of order information.

    The essential operaton is as follows:
    · Input focus flows from the first of the unique fields through to the
last of the "multipe-entry" fields, and then jups back to the first of the
"multiple-entry" fields (N.B.  NOT the unique fields).
    · As return is hit in various fields, values are calculated and
inserted into other fields (e.g.  an item total is calculated from that
item's price and quantity).  When return is pressed in the final field, all
the "multiple-entry" fields are copied into a database (array of records),
and typed into the list box.
    · Clicking on a line in the list-box "edits" that line by copying the
corresponding databse element to the input fields, and selecting the first
"multipe-entry" field for user input.  Then when return is pressed again in
the final field, the values are copied back to the database and the list
refreshed.
    · The List refresh erases the list's document, and types in information
from the database (formatting numbers, etc as it goes).  As it types each
line, it also keeps a running total of the prices and tax components.  When
it's finished going through the database, the totals are calculated and
printed at the bottom of the screen.
    · There are menu items to let you load or save and invoice, to get a
new invoice, or to quit the program.  There's also an edit menu, which
allows you to cut copy, pase and delete lines from the list-box (and their
corresponding database entries).

--------------------------------------------------------------------------

    How is it done?

     The central information store of this program is a record called
CurrRcpt, which has entries for all the unique input fields, as well as an
array called "Items".  The Items array's elements are records which in-turn
have elements for each of the "Multiple-entry" fields (make sense? - have a
look at figure 2).  It's important to note that the lines of the list-box
correspond to the elements of the Items database array (i.e.  line 1 is
element 1, line 2 is element 2, etc.).  So whenever the user clicks on a
line, the returned number is the index (i) to use for
SeDBObjects(CurrRcpt.items[i]) (that is - "put the selected record into the
edit fields").

    Unfortunately CanDo only allows for one group of database objects on a
card at one time.  This poses a problem, as the two groups of input fields
(the "Unique" and "Multiple entry" groups) need to be accessed in different
ways and at different times.  For example, you wouldn't want each element
in the Items array to include a copy of the "Unique" invoice information.
Similarly, you wouldn't want the CurrRcpt variable to include, at the
top-level, a copy of the most recently selected item from the list.  So,
only one of the groups can be named as database objects.  I decided that,
as they were used most often, the "multiple entry" fields would be database
objects (i.e.  their names start with "."), and would thus be able to use
the get and set DBObjects commands.  For the "Unique" fields, I made two
new scripts to get and set their values to/from the unique information in
the invoice (these scripts are used within the load and save operations).

    Loading and saving of the database is simplicity itself, thanks to
CanDo's LoadVariable and SaveVariable commands.  There is a little
complexity in the load and save routines, as if the user requests to Quit,
Load or clear the invoice when the current recieipt has been edited since it
was last saved, then the program queries the user that this s what they
really want to do.  You've probably seen this is many other programs
("Document blah has been edited, really quit?"), it involved keeping track
of an "Edited" flag or "Dirty Bit".  This flag is set (assigned a TRUE
value) whenever the user's actions modify the data in memory (e.g.  adding
or changing an invoiced item, etc.).  The flag is cleared (assigned a FALSE
value) whenever the user loads, saves or clears an invoice (Of course, if
the invoice had been edited, then the load or clear operations would have
first produced the "Are you sure" confirmation).

    You might have noticed that there is not element in the CurrRcpt
variable for the invoice number.  This is because the invoice number is
saved as part of the filename (and ONLY as part of the filename).  Why?  -
because this then allows a user to re-index the invoice simply by renaming
it.  The Save function generates the filename by taking the value from the
invoice number field, and appending it to the invoicee's name (Note the use
of the "||" operator).  The Load function extracts the second "word" of the
filename (using "." as the word separator) as the invoice number.

--------------------------------------------------------------------------

    What can be added from here?

* Printing - This is easily achieved by writing to the "PRT:" device.  That
is, execute OpenFile, with filename "PRT:", ioflag of WRITEONLY, and
accessflag of NEWFILE (The buffer name can be anything - "Printer" is
fairly easy to remember, though).  Having opened the printer device, you
now use one of the FileWrite commands to send the information out
(FileWriteLine would probably be best).  The format of what you print is
entirely up to you (that's something outside the scope of this article).
For style hints, try examining a few invoices you might have received.
Having printed out all the information you feel necessary, you then close
the printer file (using "Close <buffername>").  If you find it easier to
get the layout of the invoice in a document, then you can print it by using
the SaveDocument command, and the "PRT:" device (Don't forget to specify
ASCII filetype!).  Using the document approach is simpler from a
programmer's perspective, but it has the slight drawbacks of consuming a
bit more memory and time.

* "Auto stocknumbers" - That is:  When a stocknumber is entered, the
invoicing program fills the description and price fields accordingly.  This
is a little more complex than printing, but nevertheless the method is
still fairly clear.
    The easiest way of doing this is create a directory for stock item
records.  Each file in this directory has a stocknumber for its name, and
consists of a single record with entries for description, price and taxrate
(or whatever you want).  So, when the user enters a stocknumber, and hits
return, the rpogram loads-in the relevant file, and copies the information
from the file to the display fields (i.e.  description, taxrate, etc.).
    If the program can't find a given stocknumber then it assumes that
this is a new stocknumber, and remembers this by setting a variable (say
"NewStockNumber") to true.  Then, when the user hits return in the final
entry field, the "Commit" script can check this variable, and save the
relevant information to the new stocknumber.
    If you need to modify your stock item information at-least
semi-regularly, then you'll need to implement some sort of stock-item
modification functionality.  However, I'll leave this up to you, as you
should have learnt enough thoughout this tutorial to be able to do this
yourself.  The only hints I can offer are:  it will probably involve
another card, and some input fields similar to the "multiple-entry" ones
mentioned previously.