Volume Number: 16 (2000)
Issue Number: 4
Column Tag: Tools of the Trade
Looking into the 4th Dimension
by William Porter, Ph.D.
Jumping Right In
The idea for this article arose when I was writing my review of FileMaker Pro 5 (MacTECH, February 2000). I observed that FileMaker is great for end users but not such a good tool for serious application development (in spite of the fact that clever developers have made money using it that way). I had been working with 4D for a few months when I wrote that review and naturally, I found myself thinking of 4D quite a bit as I wrote. It occurred to me then that it might be useful to write an introduction to 4D for FileMaker Pro developers.
This article is that introduction. It's not meant as a review of 4D and it certainly does not attempt to even mention all of the things 4D can do. It is simply a discussion of how one might go about writing a simple database in 4D. This article presumes no prior knowledge of 4D whatever, but you will get more out of the article if you have downloaded the free demo of 4D from the ACI US web site <http://www.ACIUS.com> and spent an hour working through the tutorial that comes with it.
Unlike the tutorial that comes with 4D, this article focuses on writing basic 4D code. And while I expect that anyone interested in learning how to get started with 4D will find the article useful, I am speaking particularly to FileMaker Pro developers. FileMaker thinks one way, and 4D thinks very differently. This article tries to emphasize the conceptual differences between the two development environments. Once you "get" 4D, that is, once you start to think the way it thinks, all that is left is the details.
4th Dimension or 4D (the latter appears more common nowadays) is a relational database management system (RDBMS), with a procedural programming language at its core. Yes, it has a rich graphical user interface that allows users to define tables and fields, create records and enter data, and do reports, all without writing a single line of code. And yes, 4D has an http server built in so you can easily put your database on the Web. But the real strength of 4D is the language. If you work with 4D, you will start programming. The possibilities that the language provides are simply too enticing to resist.
It is tempting to dwell in a general way upon the gross differences between 4D and FileMaker, like 4D's lack of calculation and summary fields, 4D's ability to provide custom menus, or its separation of data tables from the file containing forms and programming elements. To be sure, these are very important differences. But a list of abstract comparisons to FileMaker will actually tell you very little about what it is like actually to work in 4D. Instead, I propose to jump right in and build a database. The project we will work on is a simple checkbook management application. You can download the database from the Polytrope Data Solutions web site: <http://www.polytrope.com/4D/>. You will also find there, for comparison, a FileMaker Pro version of essentially the same application. Since you have the ability to download the database and take it apart, I'm only going to describe how certain parts of it work, starting with some of the simpler parts and moving towards the parts that require some programming.
Modeling the Data
To keep things within the scope of an article such as this, our checkbook database is going to have just three tables ("files" or "databases" in FileMaker lingo). The main table is the one that resembles a standard checkbook register, the place where you record checks and deposits. We will call this table "Transaction." Then we will add a couple files to support the Transaction file. These supporting tables are called "Account" and "Item".
- Each record in Account will represent a particular account in a particular institution, like your personal checking account at the Big Bank of the Midwest, or your savings account at the credit union. We will add a number of fields to this table so we can store useful information like account numbers, bank names and addresses. This table will contain very few records.
- Each record in the Transaction table represents a transaction of a particular type, pertaining to a particular account, and taking place on a particular date. Examples of transactions include: a check written on January 14 to your favorite bookstore for the purchase of several books; a deposit on January 15 of several checks from different sources; an ATM withdrawal; and the posting of earned interest to your personal savings account.
- Transaction records will usually consist of just one item, but may have more than one item, and it is this possibility that will make the Item table useful, especially at tax time. The check for $85.00 to the bookstore may include the purchase of a $50 computer book - a tax-deductible expense for an independent database developer - as well as $35.00 of books for purely personal use. Being able to create item records will allow us to do very precise reports each quarter when we figure how much tax we owe.
The relational model here is simple. Transaction has a one-to-many relationship to Item, and Account has a one-to-many relationship to Transaction.
We begin by launching 4D (I'm using version 6.5.4) and selecting the option to create a new database. Here 4D drops us immediately into its structure editor, the place where we will create the tables that the database is comprised of, and the fields in those tables. This part is quite straightforward in 4D, in fact, this is vastly easier in 4D than in FileMaker Pro, and not just because it's easier to draw a line than it is to use a dialog. In FileMaker, relationships are one way, betraying the fact that FileMaker remains a flat-file system at heart. If you want to relate A to B and B to A (and usually you do), you are going to have to define the relationship twice, once in each file. And you have to remember to stop there, since FileMaker will allow you to create duplicate relationships (that is, relationships between the same two files based on the same key field). This is impossible to do in 4D.
A couple other things about the difference between FileMaker's define-relationship dialog and 4D's database structure window are typical of the differences between the two programs. FileMaker gives you several options when you define a relationship: Should the user be allowed to create records in the related file while working in this file? If a record is deleted in this file, should the related records be deleted? And should the records be sorted when displayed in a portal, and if so, how? These options neatly reflect FileMaker's strength and its weakness. For many users, being able to set these options here is a godsend. The choices are easy because there are so few of them. But it's a little like buying a Mac in 1984: You did not have to spend much time shopping, because there was only one model to buy, and there was not a lot you could do with it. The disadvantages of FileMaker's method are felt only by developers who prefer control to use-of-use. For example, what if you want the related records sorted in more than one way? FileMaker's answer: Create a duplicate relationship and switch the user to a different layout with a different portal. 4D's solution: You can sort the records any way you like. You just have to write a little code.
But, I digress. Our checkbook management database will end up with a structure that looks like this:
Figure 1.The structure window in 4D resembles a loose entity-relationship diagram. 4D expects that relations are always drawn from the many table to the one table, although all relations are two-way.
Let me comment briefly on the structure. Fields must be typed in 4D, just as in FileMaker Pro, but the two programs do not have quite the same types of fields. We have already noted the absence in 4D of the calculation and summary fields that are so important in FileMaker. On the other hand, 4D has several field types that are not found in FileMaker.
Fields in the figure above identified by the letter "A" are string or "alpha" fields. These may hold between 2 and 80 characters, and you must specify the desired capacity in the definition of the field. Sometimes you know that you want two and only two characters in a field, e.g. a state-abbreviation field. A lot of the time you may not be sure. What is the longest last name you have ever heard of? If you have any anxieties about this, just set the field to 80 characters. 4D, like FileMaker, is smart about data storage and does not use 80 bytes of disk space to store a last name that contains only five characters. Most of the fields in our database that will be holding text rather than dates or numbers have been defined as string fields of various lengths. There is only one text field in the structure, the memo field for the Transaction table.
4D also distinguishes between three numeric field types: integer, long integer and real. Integers can range between +/- 2 16 ; long integers, +/- 2 32. Real numbers are non-whole numbers like 5.3 or 365.25. I have defined the Accounts ID field as an integer field (I expect this number may never get bigger than 10). Since we do not know how big check numbers are going to get, we have been defined this field as a long integer. (But if you are writing checks that exceed the limits of a normal integer, you should hire an accountant and stop doing your own checkbook.) All the dollar amount fields will hold real number data.
Date fields are quite straightforward. They work in 4D much as they do in FileMaker.
Finally, the field "cleared" in the Transaction table is a Boolean field, whose value can only be "true" or "false."
4D also has picture and BLOB ("binary large OBject") fields, but we will not be using them. What then, you may ask, is that field named "Reserved" in the ItemS table, which looks like it is a picture field? This brings up one of 4D's quirks. You can revise table and field definitions after they have been made, but you cannot delete tables or fields, and you cannot reorder them. What do you do if you discover you do not need a field after all? Make it "invisible" in the field properties dialog; this will cause it to be displayed in the structure window in italics. Give the field a name that suggests clearly that it is not in use. And convert it to data type "picture." Why? Because picture fields occupy no room at all in the file unless data is placed in them. You can make tables "invisible" as well, at least to the user. But it is useful to note that right from the start, 4D is less tolerant of slap-dash than FileMaker Pro.
I should add that I recently encountered a problem in FileMaker Pro that caused me to think that 4D's inability to delete fields was perhaps not so perverse after all. An import script in one of my FileMaker solutions broke because the old file, the one I was trying to update, contained a field that was no longer present in the new version of the file. The deletion of that field caused the fields in that file to be reordered, which in turn caused some of the field-to-field matches in the import order stored in the script to become invalid. I no longer delete fields in FileMaker; I simply convert them to globals with names that identify that they are not being used ("g this field is not being used").
In 4D, tables are named in square brackets ("[Transaction]"). If a field in a particular table is referred to, it is usually preceded by the table name ("[Transaction] ").
Our checkbook database consists of five forms - an input and an output form for each of the three tables. In 4D, every table is required to have a form designated for input and output. Usually one creates a list form for output, and a detail form for input. But the input and output forms can be the same form. In our solution, input and output for the Item table are done with the same form.
This requirement that you have designated forms for input and output has no counterpart in FileMaker. In 4D, you are constantly reminded of the difference between entering data and browsing it, while in many if not most FileMaker Pro databases, layouts routinely get used both for data entry and for browsing. (Whether this leads to a more casual attitude towards data entry in FileMaker is a philosophical question I shall cleverly avoid raising.) Although each 4D form does have a technical connection with a primary table, the user need never be aware of this, because it is quite possible to have a database consisting of a dozen tables that never displays more than a single window - with a single window title - to the user.
4D's forms are not quite the same thing as layouts in FileMaker. The simplest way to put it is to say that 4D's forms are at least an order of magnitude more complex. But the difference is not just a matter of complexity. FileMaker's layouts seem like places; you "go to" a layout. This sense is reinforced by the user's inevitable awareness that he is working with numerous files. In 4D, on the other hand, forms do not seem so local. You don't go to a form, you display it. Beyond that, 4D's forms are quite versatile. One special type of form (the subform) can be placed inside another form. The same window may display several different forms at once. And forms can be created in a variety of window types, so that you can create custom dialogs and palettes and the like. Almost all Mac window types are supported, including floating windoids (palettes).
4D's form editor is a bit intimidating to the developer coming from FileMaker. Working in FileMaker's layout mode is like working in the drawing module of ClarisWorks, while working in 4D's form editor is a bit like working Macromedia's Freehand. But 4D will create default forms for you if you wish to get to data entry immediately after you define your tables. If you have a little more time, the form wizard will walk you through the process of creating an attractive and smart custom form. And after the form has been created by the wizard, you can edit it to your heart's content, and you can save forms as templates so you can reuse them later.
There are some things that I dislike about the form editor. The "entry order" (tab order) tool is used to define the tab order for the objects on the layout, but I find it clumsy; I sometimes end up with a line that looks like someone spilled spaghetti on my layout. And the text formatting options are somewhat limited. I miss especially the ability to set text margins within fields. (I always give my text fields a few pixels of white space on the left and right. Makes them look more attractive.)
On the other hand, 4D's layout editor has many features that are not just neat, but also extremely useful. 4D style sheets work much the way style sheets work in a word processor. You define a set of text formats and give the set a name; then you can apply the set to objects such as fields. Dynamic field names (new in 4D 6.5) allow you to insert dynamic tags as field labels instead of text objects. If you change the name of a field later, its labels will be updated automatically on all the layouts that display that field. This is extremely useful if you plan to offer versions of your solution that require localization for different languages. 4D allows you to define whether labels will be placed next to or above fields. (Why doesn't FileMaker let you do this?) 4D will automatically accommodate its look to the norms for the platform on which your database is being run. And you can save your form as a template, so that you can use its underlying formats to create new forms. These features reflect the fact that 4D expects you to be creating a lot of forms for complex applications.
Figure 2.Inside the 4D Form Editor, using the "entry order" tool to determine tab order. Note that the order includes not just fields (as in FileMaker Pro), but all tab-able objects (important for Windows users). The line zigs and zags because it anchors itself to the center of each object.
We should mention finally that these forms do not necessarily have anything at all to do with reports. A carefully designed commercial application will of course have attractive and intelligible reporting forms built-in. But 4D has a Quick Reports editor that allows users themselves to create sophisticated reports without needing to have access to the innards of your application. How I wish something like this existed for FileMaker! The best one can do in FileMaker is allow the user to export the data to a file that they do have full access to and which they can use to create their own layouts.
Where the Action Is
In FileMaker Pro, you can attach scripts to the opening and closing of the file, but just about every other action that takes place in a FileMaker database is triggered when the user clicks with mouse somewhere, usually on a button. The button is about the only game in town in FileMaker.
A 4D form, on the other hand, seems almost alive, responsive to when it is loaded and unloaded, to when fields get tabbed into and out of, to when the data in a field changes, to when the data is accepted by the user (validated), to the difference between single clicks and double clicks, to various aspects of a print request, even to individual keystrokes while the user is typing inside a field. And these events have depth as well as range. Objects on the form can trap for events just as the forms themselves can; and behind the plane of the form, there are the planes of the table and the database, each of which is watching for its own events. A single event may call into play several different "methods." (A method is a piece of code that is stored by itself, similar, at least in a general way, to scripts in FileMaker. 4D used to call these bits of code "procedures," but "method" is the more conventional term.) Data gets passed from an object method to a form method, to a function, then back to the method, in and out of variables, back and forth between processes. There is a lot going on.
The first thing the 4D programmer needs to figure out is where to place his methods. (A beginner's excitement at figuring out what lines of code are needed to accomplish a particular task can be dampened quickly when he realizes that he does not know where to put the code or how to call it.) To FileMaker users, this sounds at first like figuring out what will trigger the methods, but actually is not the same thing at all. The same method may be able to go in several different places.
Database methods are triggered by a special limited set of events, of which the most important are "startup" and "exit." In the database I built for this article, the startup method contains this code:
'sets default century to 20xx
SET DEFAULT CENTURY(20)
'initializes default_date process variable
SET WINDOW TITLE("Polytrope Checkbook Manager")
ALERT("Welcome. Type Option-F from this splash screen to leave custom menus mode.")
This code is run once when the database opens, and not run again.
"Triggers" are methods attached to tables. Triggers resemble database methods in that they react to a small number of specific events, such as the deletion or saving of a record. Triggers operate at low level so that they come into play regardless of how the event the trigger responds to was caused (by a piece of code, by a user clicking on a button, etc.). They are often used to implement "business rules," validation procedures that the business depends upon, e.g., both the first name and last name fields must be filled in, or a sale order for product x cannot be completed if x is not in the inventory.
Form and object methods stored in forms and objects respectively. These resemble one another in that they are both potentially invoked by the same events, called "form events." You must tell the form or object what events to be on the lookout for (on load, on unload, on validate, on clicked, on double clicked, etc.). Then, in the method, the code says what happens when the event occurs. If the form or object is configured to respond to one and only one event, then it is not necessary to trap for that event. For example, a button might be configured to respond only to the "on clicked" event, and the method inside the button might consist of a single line:
'This line of code displays all the records in the current table
ALL RECORDS(Current form table->)
(The "current form table->" string is a pointer built into 4D, a sort of pronoun that means "me" and whose precise meaning will depend upon the form from which this piece of code is called.)
A field is an "active object" too, in 4D's terminology, and can contain methods. The event frequently used in object methods attached to fields is the "on data change" event. For example, the following bit of code multiplies the sale price times the tax rate (stored in a related table named CONSTANTS), adds the two, and places the result in the field [SALE]net_cost:
[SALE]tax:=[SALE]price * [CONSTANTS]tax_rate
[SALE]net_cost:=[SALE]price + ([SALE]price * [CONSTANTS]tax_rate)
This code gets placed in field [SALE]price. That field is set to look for the "on data change" event only. Then, when the data in this field is changed, the values for tax and net sale are recalculated. (The colon followed by the equals sign is the assignment operator in 4D; it indicates that the value after the operator should be assigned to the container in front of the operator.)
Finally, there are project methods, which are free agents that are stored apart from any objects, forms, tables or databases, and are run only when called by one of the other methods. Project methods have many advantages, but most of them boil down to efficiency of one sort or another. If there were another field that might require the net_cost to be recalculated - a "quantity" field, for example - you would need to make the other field prompt this recalculation as well. You could copy the code above and paste it into the method editor for the other field. But, if you decided to make a change in the code, you would need to remember to make the change in both methods. (This sort of aggravation is a part of daily life if you build FileMaker applications, where you might have to update your "close application" script in seventeen files if, say, you wanted to add a backup routine to it.) The better way is to create a project method named "calculate_price" containing this code, and then create object methods for price and quantity that call the project method:
Project methods become more useful the more generic you make them. Project methods are often used to define functions. The function is passed parameters from the method that calls it, and it returns a result to that method. Project methods may also describe a series of actions to be performed upon the objects referred to in pointers. This allows you to create truly generic, universal code, code that you write once and reuse in many contexts. Now we are really starting to leave FileMaker Pro behind.
The neophyte developer struggles to find a place to put a method so that it will work at all. The more advanced user begins to think about the best location for his methods. Form methods are run when the form is loaded, then again for each record displayed in the form. For this reason, 4D programming sage David Adams counsels that, whenever possible, tasks such as resetting variables and titling windows should be put in input form methods, where they will run only once, rather than in the corresponding output forms, where they will run again and again every time a record is loaded into the list. (Programming 4th Dimension, page 139.)
Three of the fields - [Account]type, [Transaction]type, and [Item]category - display option lists (FileMaker: value lists) when the user enters them. The account type list has two options, "checking" and "savings." The transaction type list includes "check," "deposit," "withdrawal," and "bank charge." The values for these lists are fixed. The third list, the one associated with [Item]category, is unlike the other two in that it may be edited by the user.
Lists are created in the list editor, which you access in the 4D Explorer, the main organizing tool in the design environment. Once defined, lists can be associated with the fields in the field definition dialog, or with field objects on particular forms. If the list is associated with the definition of the field, it will pop up every time the user enters that field on any form; it will also be available in the query dialog. On the other hand, if a list is assigned to a field object on the form, then the list will be active only on that form. In either case, the lists are displayed by default in a dialog, not in a pop-up menu or pop-up list, as in FileMaker Pro. Getting values into a pop-up list in 4D requires a bit of programming using arrays.
Figure 3.The list modification dialog allows the user to add entries to a choice list, provided that the list has been set up to be editable. Here the user has just added "Consulting Services" to the list.
In the List Editor, you can make a list user-modifiable or not. The account type and transaction type lists are not user-modifiable, but the category list is. The screen shot above shows what the user sees if she decides to edit the list herself.
There is one other list in use in the Checkbook Manager, "polarity." It has only two values, "+" and "-"; as we shall see in a minute, these are used to calculate the net value of an item. We want the user to be able to change this value, but (a) we don't want her to be able to leave this field empty and (b) we don't want to bother her with a choice list dialog when she tabs through this field, because we are going to try to manage the values in this field automatically. Solution? Instead of making this list part of the field definition, assign this list to the field object, and assign it to the object as a "required" list but not as a "choice" list. Now, if the user attempts to exit the field when it is empty or when it contains anything other than the accepted values, 4D will display an alert.
I've also defined a couple mouse-over help messages on these layouts.
A favorite trick of mine in FileMaker (since version 4) is to use the status(currentmodifierkeys) function to see if the user has clicked on an object while holding down the Option/Alt key, and if he has, I display an alert explaining briefly what that object does. I call this "quick help." Users love it when I show it to them, but the problem is that they have to remember to hold down the modifier key and they have to remember to click on the object, and most users don't remember keyboard shortcuts like this. Even when they remember, the help is intrusive; it throws up a dialog that you have to OK before it will go away.
Figure 4.The help appears when the user places the cursor (or insertion point) over the object.
In 4D, the help is invoked automatically - the user need do nothing more than put the mouse over the object - and goes away automatically. For example, when the user mouses over the account description field, a help box appears automatically. Help text is defined in the object properties dialog. This is a feature that is as easy to implement as anything in FileMaker Pro, but it gives your application a very polished, high-end interface.
I've also placed a number of buttons on these layouts. Not all buttons require that you write methods. The most common tasks assigned to buttons have been built into 4D and you can assign these tasks to buttons from a list. Here is the "variable" page of the object properties dialog for the button used to delete a transaction record:
Figure 5.The "delete record" action is assigned to this button from
the built-in list of actions. Click on the Keys button in this dialog to assign a custom keystroke to this button.
(I assigned command-backspace.)
Notice the name of the object in the dialog: "bDelete." 4D automatically uses the "b" prefix to name a button, "v" to name variables, and so on. You could rename the button "Eddie" if you wanted to. But you have a lot of different things to keep track of in your 4D project, and it is essential that you use a naming convention of some sort.
Of course, buttons become particularly powerful when you store methods in them. The "Add Record" button on the Transaction list form contains this code:
'bAdd object method
'next step initializes OK to 1 if user says yes
CONFIRM("Add a new record?")
'display the input form for the Transaction table
INPUT FORM([Transaction];"Transaction Detail";*)
'set the location and style of the window
Open window(10;30;-1;-1;Plain window ;"")
'set the size of the window with a project method
This method uses the most important system variable "OK," a boolean variable used frequently to determine whether a user is saying yes or no to a choice. (Other system variables represent the name of the current document, the delimiters used in export routines to separate fields and records, the error code, and various aspects of the status of the mouse.)
Now this may seem curious to a FileMaker user. The command ADD RECORD does not actually cause a record to be added to the table. It simply causes a blank input form to be displayed. The record is not actually added (saved, stored) until the user gives the ACCEPT command, for example, by hitting the Enter key. In the background, the repeat/until loop is sitting there, patiently, waiting for you to accept or cancel the record. Each time you hit Enter, OK is set to 1 again, the loop runs once more, and another blank form is displayed. In order to exit the loop, the user must give the CANCEL command, by clicking on a button or by typing command-period on a Mac. While this loop is running, other methods may be called, to do calculations, to enter data, and so on. (It's like scripting the user into Find Mode in FileMaker and pausing for input, and letting the user switch to another layout with a script without exiting the Find.)
Displaying Related Fields
The transaction input form displays related data from the [Account] and [Item] tables.
When the user enters the account ID in a new transaction record, the account description ("Will's business checking account") is displayed beside the ID, as soon as the user tabs out of the ID field. The many-to-one relation linking [Transaction] to [Account] was defined earlier in the structure editor; and in the relation properties dialog, we allowed this relation to be automatic, which is the default. (Advanced users often prefer to control relationships procedurally, and they have the option of doing so.) To display the field [Account]description, I simply placed the related field on the form.
The relationship between [Transaction] and [Item] is one to many, and thus items must be displayed in a "subform." This subform, unlike a FileMaker portal, is a design element independent of the transaction input form on which it is displayed. Although no one ever sees it standing on its own, this subform is actually both the input and the output form for the table [Item]. Once placed on the input form for [Transaction], the [Item] subform becomes a single object. In order to be able to edit data in the subform, I opened its object properties and configured it to be "enterable."
Calculations Without Calculation Fields
FileMaker Pro users depend on calculation fields for so many things that it comes as something of a shock when they learn that other DBMSes do not have calculation fields at all. Calculations in 4D are done procedurally and their results are stored in data fields or variables. So long as you are careful to trigger a recalculation of the result whenever appropriate, this method is quite as reliable as FileMaker's user of calculation fields, and you have more control over it, because now calculations are done only when you want them to be done, which is especially useful in files that have many records and many calculations.
Our checking database has three important calculations to perform. First, we want to get the value of the particular item into the field [Item]Net Amount. Then we want to add up all the item amounts for a transaction and place them in the field [Transaction]Net Amount. Finally, we want to get the total of all transactions in the selection when we are looking at a list of transactions. (We are not going to include the ability to balance the checkbook in this example, which would require a number of other calculations, but the principles used would be the same.)
In the checking database that I built some time ago in FileMaker Pro and have continued to use until recently, I used two different fields named "debit" and "credit" in my item records. The net value of the item was a calculation field, equaling credit minus debit. Credit was usually empty (most transactions, alas, are debits), so the net value of the transaction was usually a negative number. In this 4D database, I decided that I did not need two fields. So now there is a single field [Item]Amount, and the user enters into that field the amount of the item as a positive number, no matter whether it is a check or a deposit. The distinction between checks and deposits is based on a second field, "polarity." By default, the polarity of new items is negative ("-"). The polarity field object on the subform is associated with the polarity options list as a requirement, meaning that the two values in the list ("+" and "-") are the only acceptable values for the field. The idea here is that the net amount of the item will equal the amount multiplied by -1, unless the polarity has been changed to positive, in which case the absolute value of the amount will be used.
We don't want the user to have to select the polarity each time, so a little code is written that anticipates the polarity appropriate for the type of this transaction. When the user changes the data in the field [Transaction]type, a form method assigns a presumed polarity to a string process variable "vsPolarity." (A process variable is a sort of global variable that can be passed from one method to another, so long as it remains within the same process.) If the type is check, e-payment, bank charge or withdrawal, vsPolarity is assigned the value "-"; otherwise, it is assigned the value "+". Then a form method in the subform, triggered by the onload event, places that value in the polarity field for the user. So after selecting the type from the options list, the user has only to type the amount. Checks will automatically become negative values, and deposits and earned interest transactions will automatically remain positive.
The calculation itself is done in a project method named TRANS_get_net:
' TRANS_get_net: procedure method
'called by the amount and polarity fields in the item subform
[Item]Net Amount:=[Item]Item Amount*-1
[Item]Net Amount:=Abs([Item]Item Amount)
[Transaction]Net Amount:=Sum([Item]Net Amount)
The condition checks the polarity of the item and enters a value in the field [Item]Item Amount. Then the net amount field in the Transaction table is assigned the value of the sum of the items for that transaction. (This use of the "sum" function, to get the total of related records, is identical to the use of the sum function in FileMaker Pro, except that, as we shall see shortly, the function in 4D can be used to get the sum of any selection of records, not just records in a related file.)
This project method is then called by object methods attached to two fields in the subform: item amount and polarity. These methods consist of a single line:
(The names of project methods are italicized automatically in the method editor, to indicate that 4D recognizes the name you typed as the name of a project method.)
There is one other calculation left to do: the calculation of the total of selected records. First we need to have a container for this sum. Now if you are a FileMaker user and you have been paying attention, you should be thinking to yourself right now, "Hold on there! I thought there were no calculation fields or summary fields in 4D!" Right you are.
So we use a process variable. Now a variable is not just used in the method code, it can actually appear on a form as an object. In this context, the process variable "total_of_list" is being used much the same way you might use a global field to store (temporarily) and display a calculated result in FileMaker Pro. The variable is associated with an object named "variable 1" which is placed on the list form. (The object's name is assigned by default when the object is created and can be changed.) The variable object contains this method:
'object method for object 'variable1'
: (Form event=On Load )
In order for this method to work, the "on load" event must be activated both in the form's event list and in this object's event list. If the object is not paying attention to the on load event, it cannot react to that event when it occurs.
I thought it would be nice to display a negative balance in red. To do this in FileMaker Pro would be a bit complicated. If I were only displaying the value of a single transaction in red, I would probably create two calculation fields "c positive_value" and "c negative"value," format c negative_value to be displayed in red, and lay them on top of one another on the layout. The formulas for these fields would test to see whether the net transaction amount was negative positive, and (re)display it in whichever field was appropriate. But we are trying to display a list total here, not a record total. The total will be calculated first in a summary field ("s total_amt"). Then it needs to be displayed in a properly fomatted field. We could once again use calculation fields, but that would be crazy. Calculation fields are stored in records, and these calculations would need to be performed in 5000 records, if there were 5000 records in the found set. The better way would be to create a couple of global fields to display the total (g negative_total and g positive_total), and script the calculation to occur immediately after the display of the summary value. The short script would have these steps:
#displays negative total in field g negative_total
#displays non-negative total in field g positive_total
#start by clearing both of the global fields
set field ["g positive_total", ""]
set field ["g negative_total", ""]
#now put the total in the right global field
if ["getsummary(s total_amt, s total_amt) < 0"]
set field["g negative_total",
"getsummary(s total_amt, s total_amt)]
set field["g positive_total",
"getsummary(s total_amt, s total_amt)]
But, I hear a FileMaker user worrying that this value won't be correct if anything changes. True, this value won't update itself automatically. But if you think carefully about what you will let the user do, you will be able to see when the script needs to be performed. And you will start thinking like a 4D programmer.
To get my red total in 4D, it was not necessary to create any fields at all. There was a different sort of complication, though. A number of different things could happen that would necessitate the recalculation of the list total: the user could delete a record from the list, or click on the "show all records" button to add records to the current selection. Because of this possibility, it's best to write a project method to get the job done, then call it from each of the objects whose actions might affect the total (the variable object itself, the "delete records" and "show all records" buttons, and the form itself). The code for the project method looks like this:
'SET_COLOR project method
'following condition displays negative balance in red
SET COLOR(*;"variable1";-red) 'red text
SET COLOR(*;"variable1";-black) 'black text
The Method Editor
FileMaker Pro's ScriptMaker is easy to understand, but not so easy to use - a distinction frequently overlooked in discussions of FileMaker. 4D's method editor is a little bit like Scriptmaker, in that it includes lists of things that you can enter into a method by clicking on them. But as a practical matter, ScriptMaker is actually harder to use than the method editor in 4D. Two of the most commonly-used script elements in FileMaker, the if/then test and the "show message" command, are at the opposite ends of the command list, so that you have to scroll up and down in the list constantly. 4D helpfully breaks the code lists into three panes, one for operators, one for tables and fields, and a third for commands.
Figure 6.4D's method editor. You can type code in or click on bits of code in the panes at the bottom. You end up doing some of both.
When you enter a command from the command list, the editor suggests the parameters that it requires (see the are just under the window title bar, in the screen shot above). This is helpful, but what I like most is being able to type in code. 4D uses basic text formatting (bold, italic, underline) and colors to distinguish the elements of your code such as fields, tables, process variables, commands, local variables, process variables and comments. The method editor will catch most of your typos or syntax errors (and the programming logic errors that remain can be tracked down and sorted out using 4D's excellent debugger).
Automating Data Entry (IDs, Dates and Check Numbers)
The detail or input forms for both [Account] and [Transaction] contain the fields that the user needs to enter data in. Each table has an ID field, its primary key. I have told 4D to auto-enter a serial number into these fields. This is done by typing "#n" into the default value box on the data control page of the object properties dialog for the ID field. This is similar to using FileMaker's auto-entered serial numbers and subject to some of the same potential problems. (Most advanced developers program the entry of primary key values.)
Now let's add a couple more little features to the input form in the Transaction table. I tend to enter a number of transactions at once, so I would like the date and check number fields to be smart, so that I don't have to type a value in for every record. And since there may be more than one account in use, let's enter the last account number used into each new record by default.
Let's do the date field first. Here is the code for its object method:
'object method to capture date used in last record
'attached to the field [Transaction]date
'capture the form event in a local variable
'test the event
: ($LEvent=On Load )
'set date to variable only if new record
If (Is new record([Transaction]))
'put entered value into variable and carry forward
: ($LEvent=On Data Change )
The method starts with a local variable, $LEvent, which stores the form event that triggered the method. Storing the event this way makes little practical difference to this simple method, but in a more complicated method, it will mean that the "case of" code that follows does not need to keep looking outside the method to test the form event; it can simply look at the local variable.
Inside the "case of" code, we put the date from a process variable "default_date" into the date field of the record just loaded, but only if it is a new record. We don't want to change the date of old records! And if the user changes the date of the record, we capture that to the process variable so that it can be used when the next record is created.
How does this work for the user? When she opens the application some evening to enter checkbook data, the process variable default_date is given today's date in the startup method. So when the user creates the first transaction record in her working session, it will be given today's date by default. If that is correct, the variable will not be modified, and the next record will also be given today's date. On the other hand, if she changes the date value in the record, that is an "on data change" event for this field, so the new date she enters will be stored in the variable, replacing the original value. Each time she creates a new transaction record, the variable's date will be entered automatically in the date field, and that will continue to be the date that gets entered until she changes the date thus entered. After that, the new value will be used.
No need to discuss the account number field now. The code is simply an adapted version of the code for the date field.
The way we will increment check numbers is similar, but while every transaction has a date and account number, and the values in these fields are the same for many transactions, not every transaction has a check number, and no two check numbers are ever the same. This means that the first thing we have to do is figure out whether the record is new and of type check. If and only if these conditions are met, we will enter a value into the check number field using another process variable "next_check." Finally, we will increment the variable for later use.
We need to create two methods for two different objects, the fields [Transaction]type and [Transaction]check number. Here is the pertinent code for the type field:
'object method attached to [Transaction]type
'capture the form event to local variable
'see if it's a check
'if so, anticipate check number
: ($LEvent=On Losing Focus )
If (Is new record([Transaction])) &
An object gets "focus" when the user tabs into it or on to it (in Windows, you can tab through buttons in dialogs, giving them focus). An object loses focus when the user tabs out of it or off of it. This method is triggered when the user tabs out of the transaction type field. The method checks then to see if this is a check, and if it is, and if this is a new record, the method places the value of the variable next_check into the check number field.
Now let's put the value into the variable. This is done in an object method attached to the check number field. Here is the code:
'object method attached to field [Transaction]Check Number
'triggered exclusively by the on losing focus event
'capture last check number used
: ($LEvent=On Losing Focus )
'can only do this when the user tabs out of this field
If ([Transaction]Check Number#0)
'it's not empty, so catch it and increment it
4D does not have an 'isempty()' function, as FileMaker does, so we have to test to see if the check number field is not equal to 0, which in 4D amounts to the same thing. If it is not, then the process variable next_check is set to the value in this record's check number field + 1, so it is ready to be placed into the next check.
Here again, the process variable is empty the first time a check record is created, but after the user enters the check number once, the variable will take care of the rest.
A number of improvements could be made to our system. We could, for example, store the next check number in a field in another table when the user exits the current process. That way, when the user cranks up the application tomorrow and enters her first check, the check number field will be filled for her. It would be smart to add some additional error checking to the methods as well. At the moment, the methods assume that the user will enter the type field first, then tab into the check number field. If the user does something different, such as use the mouse to place the insertion point directly into the check number field, bypassing the type field, the system might not work properly. But adding these checks will involve the same kinds of code that we have used above.
Ordering À la Carte
All that is left is to create some navigational tools.
Since the movement from the output to input forms and back has already been taken care of with buttons, all our checking database really needs is a way for the user to move between the accounts and transaction tables. These commands could also have been placed in buttons, but it seems to make more sense to put them in a custom menu named "Tables."
A menu is nothing more than a way of running a method. The checking database has one menu ("Tables") with two commands, "List Accounts" and "List Transactions." Each of these calls a project method that creates a window, displays all the records, makes a change to the window's title bar.
The link between the methods and the menus is made in the menu bar editor.
Figure 7.The menu bar editor.
You can assign a keyboard shortcut to any menu, if you like. And you can create as many menu bars as you like, then associate them with windows that display particular forms. If a menu command should be grayed out in a certain context, you can uncheck the "enabled" button for that command in that menubar. If your database employs a large number of forms, you may need to build many different menu bars. They may display the same commands, but different commands will be enabled and disabled in each menu bar. This takes careful planning. But it's hard to see how implementing custom menus for your application could be any easier.
So there it is: a three-table application that keeps track of bank accounts and transactions. In describing the construction of the database, we have gotten a taste of the way one works in 4D. We created our tables and defined the relationships between them. We created the forms we needed and added fields and buttons to those forms. Some of the buttons take advantage of predefined button actions, and others required programming. We worked with several of the five classes of methods (database, trigger, form, object and project) and have written a number of methods using 4D's procedural language. We have encountered the most important logical operations (case of, if/then, and the assignment operator), seen what system ("OK"), process ("default_date") and local ("$LEvent") variables look like, and how variables can be used as objects on forms. We programmed the calculations that the application needs, figured out when to run them and where to display their results. We created choice lists for a couple fields. We even added a few automatic help comments. And finally, we built a custom menu bar for the application, so that the user can move between accounts and transactions.
It ain't Quicken - yet. But with a little more work, it could do everything you could dream of. Want "clairvoyance"? (This is where the program anticipates what the user is trying to type and offers to complete it.) One of the sample databases on the ACI US web site (in the "beginners corner," no less!) shows how to do it. Now that you've got 100,000 records in the database, is speed becoming an issue? Compile that sucker. 4D's compiler produces true machine code that runs blazingly fast on Macs and PCs. Want a graph of your financial picture? 4D has built-in charting capability, and there are additional tools for 4D that you might want to add, like a full-featured word processor, so you could write cover letters when you are paying bills! You probably won't want to put your checkbook on the Web, but if you did, you could either use 4D's built-in http language or take advantage of the new 4D module for the Lasso Web Data Engine from Blue World Communications.
In the meantime, with patience and a little effort, you will make your way over the learning curve and soon come to enjoy having pointers, 2D arrays and semaphores to play with on the weekend.
I need to thank all those who have helped me so much in the last two months, as I have been getting up to speed with 4D. My first thank yous must go to Elizabeth Delgado, who patiently shared her knowledge of 4D and her enthusiasm for it even when I got uppity, and to Mehboob Alam, who founded the Databasics discussion list to help new users take their first steps in 4D. It is through Alam's list that I have had the extraordinary good fortune to make the virtual acquaintance of some of the stars of the 4D world, including David Adams, Steve Hussey, Douglas Blew, Walt Nelson, and many others. They have all been most generous in volunteering their time and expertise to me and other FileMaker developers who were struggling to figure 4D out. Thank you all.
If you trot down to Barnes and Noble, you probably won't find any books on 4D on the shelf next to the "Microsoft Access" section of the bookstore. Not to worry, though. Help is available.
The best place to start is with what ACI US gives you for free. You can download a working demo of 4D from the ACI US web site: http://www.acius.com. The "Discover 4D" tutorial that comes with the demo focuses on some of the user-interface basics that were slighted in this article. There are a number of demo databases on the web site that you can take apart and study. (If the database opens to a splash screen, type Option-F to exit the runtime so you can enter design mode and look at the database's methods.) And the documentation that comes with 4D is generally very good. True, there is a lot of it. The Language Reference - an essential tool - is over 1500 pages in itself, although it's not the sort of thing you read for the plot. The User Reference, Design Reference and the 3.6 Upgrade document are all worth reading straight through.
There are two books 4D available that I have found helpful. Steve Hussey and Geoff Perlman's Inside 4th Dimension version 6 is perhaps the best thing to read to get over the learning bump quickly. If you are coming from FileMaker, you will find chapters 13 through 21 of Inside 4th Dimension especially helpful; these are the chapters that deal with managing relations and programming methods, the areas where 4D resembles FileMaker the least. After you have read Hussey and Perlman's book, the 4D documentation will start to make a lot more sense. And then you will be ready for David Adams and Dan Beckett's masterpiece, Programming 4th Dimension: The Ultimate Guide. Unlike most computer books, this is not a rehash of the documentation, but rather a complement to it. And although the book is addressed primarily to intermediate and advanced 4D users, the explanations are so lucid, and the code is so generously commented, that serious beginners will find it both useful and inspirational. It's a great book about a great subject. (NOTE: Both of these books deal with version 6, but the differences between 6 and 6.5 - the current version as of this writing - are not so great as to matter much to the reader of either book.) Both books can be purchased online at the Automated Solutions Group web site: http://www.asgsoft.com.
Dimensions Magazine is a technical journal devoted to 4th Dimension and published every other month by Computerized Pricing Systems in Broomfield, Colorado. It is a useful and up-to-date resource. The articles address issues of interest to developers of all levels of expertise.
Finally, there are two important online resources. The 4D Networked Users Group discussion list is an active list for 4D developers at all levels. Its FAQ and subscription info can be read at http://www.4dnetizens.com/4d-nug/4d_mailing_list_faq.html. The Databasics list, created by Mehboob Alam, is geared specifically to 4D newbies. You can subscribe to Databasics by sending a message directly to the server at
William Porter is the owner of Polytrope Data Solutions, a software development firm based in Houston, Texas, working in FileMaker Pro, Lasso, and now 4D, and serving clients throughout the United States and in Europe. Will is a member of the ACI US Partners program. He can be reached at firstname.lastname@example.org.