AmigActive 4

home *** CD-ROM | disk | FTP | other *** search

/ AmigActive 4 / AACD04.ISO / AACD / Programming / CanDo / Decks3 / Bill-it / Databases.doc < prev next >

Wrap

Text File | 1994-04-28 | 11.4 KB | 218 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. -------------------------------------------------------------------------- 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.