From the October 2011 Issue of Prestwood eMag

Paradox P9 Book: Power Programming: Power: Chapter 15, Fields, Table Frames, and MROs By Mike Prestwood

Posted 21 years ago on 3/22/2003 and updated 6/10/2008 Take Away: Chapter 15, "Fields, Table Frames, and MROs" from Paradox 9 Power Programming by Mike Prestwood.  Tags: paradox 9 power programming , Fields , Table Frames , and MROs , chapter 15 , Mike Prestwood , paradox , objectpal , book KB100208

[Updated 6/10/2008 with pictures and formatting.-MP]

Fields, table frames, and multirecord objects (MROs) all display data. Table frames and MROs both have a record object and fields, of course, do not. You can, however, add a record object around a set of fields by using a 1-by-1 MRO. Using a record object's events is a good technique for managing inserts, updates, and deletes. Although there are several programming techniques you can use with all three, there are programming differences you need to keep in mind.

Fields

As a developer programming a database application, you need to manipulate fields and the data in the fields. This section deals with modifying fields, setting the tab order of fields, and manipulating Combo fields.

Automatically Entering Memo View

Memo view enables you to easily edit fields defined to a memo field. For example, the return key takes you to the next line in a memo field when in Memo view. To manually enter Memo view, press shift-F2. To automatically enter Memo view, put the following code in the arrive event of a standard or formatted memo field:

1: method arrive(var eventInfo MoveEvent)
2: doDefault
3: self.action(EditEnterMemoView)
4: endMethod

The following code can be used in a pushButton event to move to a memo field and put the memo field into Memo view:

1: method pushButton(var eventInfo Event)
2: MyMemoField.moveto()
3: MyMemoField.action(EditEnterMemoView)
4: endMethod

Setting the Tab Order of a Form

There are three commonly used techniques to set the tab stop order of a form: setting the Next Tab Stop property, using ObjectPAL, and using the concept of containership. The most elegant of these three solutions is the Next Tab Stop feature. The Next Tab Stop property is an interactive and easy solution. However, for the sake of demonstrating containership, and because it is very useful, I'll show you the third technique here: using containership.

Figure 1: Grouping Objects

It is also a good idea to group the fields on the right side of the form, too. This way, the user goes through all the fields on the right, even if some of the fields on the left are empty.

Often, you want to have a pick list or a Combo field available in your form when you're editing. A list field enables you to restrict the values entered into a field to those on the list. The following table lists the pertinent properties of the list object:

With the properties in the table, you can control a list object. Suppose that a user needs ID numbers or items of a specific type in a list and doesn't want to have to type hundreds of ID numbers. To fill a list object, use the dataSource property. You can place this line of code on the list object of the Combo field, as in the following example:

1: self.DataSource = "[tablename.fieldname]"

If you use the .DB file extension when you describe your table name, you need to embed the table name in quotation marks. Because the expression is already in quotation marks, you need to use \" to represent a quotation mark. The backslash character means that the next character is taken literally, as in the following example:

1: self.DataSource = "[\"tablename.db\".fieldname]"

To reach the list object, select the field and open the Object Tree by using the toolbar. Make sure the split pane is checked by selecting View | Both on the Object Explorer. Select the list object listed on the Object Tree and place the code on the open event.

This technique changes the actual value of the field when you're in Edit mode. It doesn't move to the record that matches that value. The list is a list of choices that changes the value of the current field to which the Combo field is attached.

Emptying a List

Setting a list's count property to 0 will blank the list. For example, the following code empties the list object called lst1:

1: lst1.list.count = 0

Using a Combo field to move to a new record, rather than changing the value of the current record in that field, requires a few extra steps:

1. Make the Combo field an undefined field. Name it Drop.
2. Add the self.DataSource code to the list object.
3. On the Combo field object (not the list object), change two methods as follows:
    
   
   1: ;lst1 :: open
   2: self.value = fieldname.value ;This sets the
   3: ;initial value
   1: ;lst :: newValue
   2: if eventInfo.reason() = EditValue then
   3: fieldName.locate("fieldname", DROP.value)
   4: endIf
   
4. Save and execute this code. Now the Combo field acts as a data navigator for your table. I don't recommend this technique for large tables because a Combo field with more than a few hundred options is unmanageable. In addition, ObjectPAL limits the number of items in a list to 2,500.

Populating a Combo Field

To activate a Combo field when a user arrives on it (tabs or clicks), you need to use the arrive event of the field and use the action() method. For example:

1: ;fldList :: Arrive
2: method arrive(var eventInfo MoveEvent)
3: if self.isBlank() then
4: action(EditDropDownList)
5: endIf
6: endMethod

Adding Automatically to a Combo Field

Suppose that you want to populate a Combo field and automatically add new entries to it. This next example demonstrates how to add automatically to a Combo field.

Step by Step

1. Set your working directory to Paradox's Samples directory. Create a new form with two undefined fields on it. Make the first field a Combo field. This example uses only the first undefined field. The other field is on the form so that you can move on and off the first field.
2. In the arrive event of the list object of the field, add line 3. It is important to use the arrive event of the list object and not the field (see Figure 15-2). The arrive event was selected for this task because you need the list to repopulate with the latest information every time it is selected.

Figure 2: Getting to the list object of a field



1: ;field.list :: arrive
2: method arrive(var eventInfo MoveEvent)
3: self.DataSource="[CUSTOMER.Name]"
4: endMethod

3. In the canDepart event of the field, add lines 3--18. Line 4 declares a TCursor variable. (A better place to declare the variable is before the method or in the Var window. To keep the code visually close together, the code in this example puts the variable inside the method.) Line 8 tests whether the field has a value in it. If it does, line 9 opens a TCursor that will be used in lines 10--15. Line 10 tests whether the value is already in the table. If it isn't, line 11 puts the TCursor into Edit mode. Line 12 inserts a new record. Lines 13 and 14 set the values, and line 15 posts the record. Line 17 closes the TCursor.

1: ;fldList :: canDepart
2: method canDepart(var eventInfo MoveEvent)
3: var
4: tc TCursor
5: endVar
6:
7: ;Add a value if necessary.
8: if not self.isBlank() then
9: tc.open("CUSTOMER.DB")
10: if not tc.locate("Name", self.value) then
11: tc.edit()
12: tc.insertRecord()
13: tc."Name" = self.value
14: tc.(1) = tc.cMax("Customer No") + 1
15: tc.postRecord()
16: endIf
17: tc.close() ;Not needed, but good form.
18: endIf
19: endMethod

4. Check the syntax, save the form as LIST2.FSL, and run it. Add a new entry to the Combo field. Move off the field. Move back and drop down the edit list. Your entry has been added to the list.

If you want to prevent the user from moving off a field when a validity check fails or when a key violation occurs within a field, use the following code:

1: ;Field :: depart
2: doDefault
3: if self.edit() then
4: if not self.postRecord() then
5: errorShow()
6: self.moveTo()
7: endIf
8: endIf

Table Frames

A table frame is a design object for forms and reports that represents a table and consists of columns and rows. This section discusses how you can add sorting capabilities to table frames, how to highlight records, and how to manipulate table frames using ObjectPAL.

Preventing the User from Going Past the End of a Table

You can use the Auto-Append feature of the data model to always restrict the user from going past the last record in a table frame. Suppose that you want to prevent new records from being inserted into a table that is being accessed by means of a table frame embedded in a form. You can prevent a new record from being inserted by first trapping the DataInsertRecord action constant and then disabling the code of the default event. If you just want to stop the user from inserting a new record by moving past the last record, however, then uncheck the Auto-Append property of the table in the data model.

Selecting Multiple Records from a Table Frame

Sometimes it is useful to mark a set of records permanently. dBASE does this when you delete a record. The record is marked for deletion, and it is permanently deleted when you pack the table. When you program, you often want to mark a set of records permanently. I call this mechanism a marked list.

At this point, you can do a variety of things with queries. For example, by interrogating the Marked field and looking for your character that signifies that the field is marked, you can save the answer query in another directory or do simple housekeeping chores. This is an important technique for permanently marking records. It has a broad range of uses in your applications, such as the following: 

1: ;Routine to mark field.
2: ;Field :: mouseDown
3: disableDefault
4: if self.isBlank() then
5: self.value = "X"
6: else
7: self.value = ""
8: endIf

After a user marks records, you can query for the X, or you can use a scan loop to loop through the table and do something with each record marked, as in the following example:

1: ;Routine to handle marked field.
2: var
3: tc TCursor
4: endVar
5:
6: tc.open("TABLE.DB")
7: scan tc for tc."Marked" = "X":
8: ;Do something here.
9: endScan

Remember that you also need to clear out the Marked field at some point, as in the following example:

1: ;Routine to clear marked field.
2: var
3: tc TCursor
4: endVar
5:
6: tc.open("TABLE.DB")
7: tc.edit()
8: scan tc:
9: tc."Marked" = ""
10: endScan

Redefining a Table Frame with the TableName and FieldName Properties

This section discusses redefining table frames in Run mode. This next example demonstrates how to define which table is bound to a table frame.

Suppose that you want to define an undefined table frame when the user presses a button. To do this, set the tableName property of the table frame in the pushButton event of a button.

Step by Step

1. Change your working directory to Paradox's Samples directory. Create a new form with no tables in its data model.
2. Place a button labeled Set on the form and an undefined table frame named tfCustomer on the form (shown here).

Figure 3



3. Add line 3 to the pushButton event of the Set button.

1: ;Button :: pushButton
2: method pushButton(var eventInfo Event)
3: tfCustomer.tableName = "Customer.db"
4: endMethod

4. Check the syntax, save the form as DMADD1.FSL, and run it. Click the Set button. The table frame has exploded from one column to all the fields in the table, as the following illustration shows.

Figure 4

tfCustomer is the name of the table frame on the form. TableName is a property of the table frame object. This code doesn't rely on dmAddTable() to add the table to the data model. Simply setting the property does that.

Defining the Columns of a Table Frame

The technique used in the preceding example is useful in some situations, such as when you want the table frame redefined. In many cases, however, you want to define just a few columns. This next example shows you a technique for doing that.

Suppose that you want to define three columns of an undefined table frame without affecting its size. To do this, do not use the tableName property; instead, use the fieldName property.

Step by Step

1. Change your working directory to Paradox's Samples directory. Create a new form with no tables in its data model. Place a button labeled Show Customers on the form.
2. Add an undefined table frame to the form with three columns. Name the fields of the columns as follows: Col1, Col2, and Col3 (shown here).

Figure 5



3. Add lines 3--5 to the pushButton event of the Set button. Lines 3--5 set the FieldName property of all three columns.

1: ;Button :: pushButton
2: method pushButton(var eventInfo Event)
3: Col1.FieldName = "Customer.Customer No"
4: Col2.FieldName = "Customer.Name"
5: Col3.FieldName = "Customer.Phone"
6: endMethod

4. Check the syntax, save the form as DMADD2.FSL, and run it. Click the Set button. Now the columns of the table frame are defined (shown here).

Figure 6

You can use this technique to redefine a table, too. The trick is to redefine the table frame to the Null character before trying to assign a new table to it. In the following code, tfCustomer is the name of the table frame.

1: tfCustomer = ""

Then, you can redefine the columns without a problem, as in the following example:

1: Col1.FieldName = "LINEITEM.Order No"
2: Col2.FieldName = "LINEITEM.Stock No"
3: Col3.FieldName = "LINEITEM.Total"

Resizing a Table Frame

The following code snippet demonstrates how to redefine and resize a table frame:

;DMADD3 :: btn :: pushButton
method pushButton(var eventInfo Event)
delayScreenUpdates(Yes) ;This is for a smooth look.
tfTemp.verticalScrollBar = False ;Turn off vertical scrollbar.
tfTemp.TableName = "X.DB" ;One column dummy table.
tfTemp.design.sizeToFit = True ;Tell the table frame to resize.
tfTemp.TableName = "ZIPCODES.DB" ;Bind table to table frame.
tfTemp.verticalScrollBar = True ;Turn on vertical scrollbar.
endMethod

Validating Fields at the Record Level of a Table Frame

Earlier we discussed using the record level of a table frame to validate fields. This next example demonstrates how to use this important technique.

Suppose that you want to make sure that the user hasn't entered a date beyond today's date in the Contact Date field of the Customer table. To do this, use the canDepart event of the table frame's record object. Compare the field's value with today's date using today(). If the value is greater then today's date, use setErrorcode() to prevent the user from posting the value.

Step by Step

1. Set your working directory to Paradox's Samples directory. Create a new form with the Customer table in the data model and displaying the columns displayed below.

Figure 7



2. Add lines 3--9 to the canDepart event of the record object of the table frame bound to the Customer table (the record object is highlighted in the previous illustration). Line 3 checks whether the date entered in the Sale_Date field is later than today's date. If it is, line 4 sets the CanNotDepart error code. Lines 5--8 notify the user of the inaccurate date by changing the color of the record object to yellow, displaying a warning, and moving back to the field. You can customize your own warning system if you want.

1: ;TableFrame.Record :: canDepart
2: method canDepart(var eventInfo MoveEvent)
3: if First_Contact.value > today() then
4: eventInfo.setErrorCode(CanNotDepart)
5: self.color = Yellow
6: msgStop("Invalid Date!", "Contact date is invalid.")
7: self.color = Transparent
8: First_Contact.moveTo()
9: endIf
10: endMethod

Note: You also can use a validity check at the table structure level. When you restructure a table, you can use Today in the maximum validity check field. In fact, this should be your first choice for data validity.

3. Check the syntax, save your work, and run the form. Press F9 to enter Edit mode, and try to change the First Contact field to tomorrow's date. You get a message indicating that the date is invalid, and the record turns yellow while the message is displayed (shown here).

Figure 8

Multirecord Objects

Using the record object of a table frame to manipulate data at a record level is great, but what do you do when you have several fields? You use a 1-by-1 multirecord object (MRO) and surround the fields, which causes the MRO to bind to the underlying table.

Figure 9

Don't panic as your screen becomes jumbled. Next, change the MRO to a 1-by-1 MRO and resize it so that the fields fit within it. This cleans up your screen and automatically binds the record object to the underlying table (shown here).

Figure 10

After you set up the record object, you can use the MRO object's methods to manipulate the data at the record level.

Validating Fields at the Record Level by Using a Multirecord Object

Suppose that you want to make sure that the user doesn't leave the Telephone Number field of the Customer table empty. You can do this on a group of fields by surrounding the fields with a 1-by-1 multirecord object and using the record object to trap for when the user moves from record to record.

Step by Step

1. Set your working directory to Paradox's Samples directory. Create a new form with the Customer table in the data model. Create field objects and define them to the Customer table's Customer No, Name, and Phone fields.
2. Add a 1-by-1 MRO around the fields (refer to the illustrations in the previous example to help set up the 1-by-1 MRO).
3. Add lines 3--9 to the canDepart event of the record object in the multirecord object. Line 3 checks whether the Phone field is blank. If it is, line 4 sets the CanNotDepart error code. Lines 5--8 notify the user by turning the box yellow, displaying a warning, and moving back to the field. You can customize these warnings to your own liking.

 1: ;MRO.Record :: canDepart
 2: method canDepart(var eventInfo MoveEvent)
 3: if Phone.isBlank() then
 4: 	eventInfo.setErrorCode(CanNotDepart)
 5: 	self.color = Yellow
 6: 	msgStop("Warning!", "Phone number required on this form.")
 7: 	self.color = Transparent
 8: 	moveTo(Phone)
 9: endIf
10: endMethod

4. Check the syntax, save your work as RECORD2.FSL, and run the form. Press F9 to enter Edit mode, and try to leave the Phone field blank. You get a message indicating that the phone number is required, and the record turns yellow while the message is displayed (shown here).

Figure 11

Summary

In this chapter you learned how to manipulate fields, table frames, and multirecord objects. You learned about the power and ease of use of the record object for validating records and fields. You also learned that it is preferred to do field validation at the table structure level; in that case, you don't use ObjectPAL at all. Proper field validation should be at the table or database level. Otherwise, your users could open the table directly and enter bad data. If you want to restrict input only on a particular form, however, you can use the techniques discussed in this chapter.

Linked Message Board Threads

 change a data model of a form with code? in ObjectPAL MB Topic (1 replies)

Mike Prestwood

Comment 1 of 1 Today I updated this chapter with screen shots and cleaned up some formatting issues. Posted 16 years ago

KB Post Contributed By Mike Prestwood:

Mike Prestwood is a drummer, an author, and creator of the PrestwoodBoards online community. He is the President & CEO of Prestwood IT Solutions. Prestwood IT provides Coding, Website, and Computer Tech services. Mike has authored 6 computer books and over 1,200 articles. As a drummer, he maintains play-drums.com and has authored 3 drum books. If you have a project you wish to discuss with Mike, you can send him a private message through his PrestwoodBoards home page or call him 9AM to 4PM PST at 916-726-5675 x205. Visit Profile