In the last 2 issues, I talked about the differences between Alpha Four and Alpha Five when using some basic scripts (issue 1), and the user interface (issue 2). This month, I'd like to show Alpha Four users how to create field rules in Alpha Five.

While there are many similarities in creating field rules in the two programs, there are also some fundamental differences. We should look at these differences first, to avoid confusion and frustration. After that, I'll show the additional field rules available in Alpha Five.

First of all, you can create field rules for individual tables¹ or for sets in Alpha Four. This is often an advantage, but can also be a disadvantage if you aren't careful². In Alpha Five, all field rules are created at the table level. Whether you create the field rules in the individual table, or in the set (which shows all the tables for that set), the rules affect the table for which they are created, and not the set.

This difference means that, in Alpha Five, you cannot create a field rule for a field in the parent table that directly references a field in one of the child tables³. However, there is a way to do this, as I will show.

Let's take as an example a case in which you want the value of the child's ORDER field to be the same as the parent's JOB field (assuming the two tables are linked by a field other than JOB).

In Alpha Four, you would go into the field rules for the set, scroll down to the ORDER field in the child, define the field as either a calc field or default, and then put JOB in the expression. Alpha Four will assume the field is from the parent. If you want a value from a field in the same set other than the parent, you have to preface the field name with the table name; TABLE->JOB.

In Alpha Five, you have to use a LOOKUP function to get the value. Using the same example, the field rule expression for ORDER would be:

LOOKUPC("F",KEY,"JOB","PARENT.DBF","INDEX")

In this expression⁴, the F parameter will find the first match; KEY is the name of the set's linking field; PARENT is the name of the parent table; INDEX is the name of the index for the linking field.

Another field rule available in Alpha Four is the summary rule. This allows you to get a total of a field from the child table, and put it into a field in the parent. To set this up, you go to the parent field in the set's field rules, define the field as summary, and the select the type of summary operation.

In Alpha Five, you would use the DBSUM function as follows:

DBSUM("TABLE.DBF","INDEX",KEY,"FIELD")

This expression will return the sum or total from FIELD in TABLE, based on the INDEX used to find the value KEY.

I think it's worth giving an actual example here. Let's say you want to get the total for all sales for a given customer, and put this total in the CUST_TOT field in the SALES table. In this case, the expression would look like this:

DBSUM("CUSTOMER.DBF","CUST_ID",CUSTID,"AMOUNT")

In this case, the CUSTOMER table has a field that links it to SALES called CUSTID. It also has an index called CUST_ID that sorts on the CUSTID field. Finally, it has a field called AMOUNT in which the amount for each sale is stored.

The DBSUM expression will use the CUST_ID index to find all records in CUSTOMER that match the value in the linking field CUSTID, and will return the total from the AMOUNT field for these records.

Therefore, you can re-create virtually all of the field rules from Alpha Four in Alpha Five. For most field rules found in Alpha Four other than the two examples described in this article, the procedure is very nearly identical, with only minor syntax differences.

However, Alpha Five has some field rules that are not available in Alpha Four:

^• You can set a default value as the value of the previous record.

^• You can define where the cursor will appear when entering a field, and whether or not the text will be selected. This could be useful if a particular field often has data appended to it.

^• You can define the case of text (as in Alpha Four), and can also define exceptions, such as McDonald.

^• There is a speed glossary available, similar to that used in word-processing programs.

^• You can create a lookup list of previous values. Every time the user adds a value, it is added to the list (in Alpha Four, you can approximate this by using a lookup database, but the lookup table must be changed in field rules).

^• Values from a lookup list automatically fill in when the first matching letter is entered (similar to Quicken).

^• There is an option in Lookups called Popup on Mismatch. This will only pop up if the user enters a value that does not exist in the lookup.

^• In a table lookup (not a list), the user can add or edit values in the lookup, based on a default or pre-defined form.

Next month, the series on the Alpha Four to Alpha Five transition will continue with an article about indexes.

¹ Even though you are used to calling a DBF a database in Alpha Four, I am going to call all DBF's tables for either program.

² Since you can create a field rule in one of the individual tables, and can also create a field rule for the same field in that table within the set's field rules, there is always the possibility that you could create conflicting field rules for a field.

³ In Alpha Five version 5, you will be able to reference fields from any table within a set.

⁴ LOOKUPC is used to find character values; the functions for numeric and date are LOOKUPN and LOOKUPD respectively.

^•	You can set a default value as the value of the previous record.
^•	You can define where the cursor will appear when entering a field, and whether or not the text will be selected. This could be useful if a particular field often has data appended to it.
^•	You can define the case of text (as in Alpha Four), and can also define exceptions, such as McDonald.
^•	There is a speed glossary available, similar to that used in word-processing programs.
^•	You can create a lookup list of previous values. Every time the user adds a value, it is added to the list (in Alpha Four, you can approximate this by using a lookup database, but the lookup table must be changed in field rules).
^•	Values from a lookup list automatically fill in when the first matching letter is entered (similar to Quicken).
^•	There is an option in Lookups called Popup on Mismatch. This will only pop up if the user enters a value that does not exist in the lookup.
^•	In a table lookup (not a list), the user can add or edit values in the lookup, based on a default or pre-defined form.