Working with table records

Paradox offers many ways to add and manipulate table records. A record is a group of related fields of data in a table row. For example, a table may contain information about a company's customers. The customer data is stored in various fields, such as customer identification number, name, and address. Each row (record) in the table consists of the collection of information related to each customer. Using Paradox, you can add and delete records. You can also move and navigate records. Table records can be locked to prevent others from editing or deleting them and they can be subtracted, which lets you remove the records from one table that match the records in another table.

In this section, you'll learn about

  adding table records
  
  deleting table records and packing tables
  
  moving table records
  
  navigating table records
  
  navigating table records using the keyboard
  
  saving table records
  
  locking table records
  
  subtracting table records
  
Adding table records

Paradox lets you add new records in a table and copy a record from a source table to a target table, provided the tables have the same structure.

When you copy a record to a table, Paradox acquires a read lock on the source table and a write lock on the table to which you add records. Until the records are added, others cannot change the contents or structure of either table, and they cannot perform any operation that requires a write or exclusive lock on the target table. If either table already has a write or exclusive lock on it, you must wait until the lock is removed before adding the record.

When you add records from a source table, the target table you add records to can have more fields than the source table, as long as the first fields of the table are compatible with all fields of the source. The tables must also have compatible field types appearing in the same order. Paradox places null values in the extra fields.

The source table can also have more fields than the target table, but its fields must be compatible with the first fields of the source. They must have compatible field types appearing in the same order. For fields to be compatible, Paradox must be able to change the existing field type to the new field type in a restructure operation. For example, Paradox number (N) and money ($) fields are compatible, but Paradox number (N) and graphic fields (G) are not. Paradox ignores the extra fields.

If the target table is keyed, the added records must conform to the rules of the key. Paradox places records that do not conform in the temporary keyviol.db table in your private directory.

To add a record to a table

1	In Edit mode, click Record  Insert.

2	Type the record data.

3	Exit the record by moving to another record to post the data.

Note

  If you add a record to a filtered view of a table's data or a direct query view, and the record does not meet the criteria established by the filter or query, you won't see the record when it is posted.
  
To copy a record from one table to another

1	In a table to which you want to copy a record, click Tools  Utilities  Add.

2	Choose the file and folder where the table from which you want to copy a record is stored.

3	In the Add records in dialog box, choose the file and folder where the table to which you want to copy a record is stored.

4	In the Options area, enable one of the following check boxes:

  Append - adds records to the target table without affecting existing records
  
  Update - updates records already in the target table. Records in the source that don't match the target table are not added.
  
  Append and update - overwrites matching records, and adds new records to the target table
  
Note

  Some field type conversions result in invalid records, which are written to the temporary Problems table. If this happens, edit the records in the Problems table, and then add them again. The Problems table is not generated for Structured Query Language tables; the invalid records are dropped.
  
Tip

  To add many records to a table, you can merge the records from another table that has the same structure. The two tables can be of different types, as long as their fields are compatible.
  
Deleting table records and packing tables

You can delete one or all records from a table. For example, you may want to remove a customer name from a table or remove a product when it becomes obsolete. You can also empty a table of all records while retaining the table's structure and validity checks. By deleting all records in a table, you are not reclaiming disk space, however, you have the option of reclaiming disk space; by packing a table.

To delete a record from a table

1	In Edit mode, click in a record.

2	Click Record  Insert.

3	Click Record  Delete.

If you accidentally delete the wrong record, click Edit  Undo.

Notes

  You cannot retrieve deleted records from Paradox tables.
  
  When deleting a record in a dBASE table, it is not permanently removed. You can view deleted dBASE records with the Show deleted command.
  
To delete all records from a table

1	Tools  Utilities  Empty.

2	Choose the drive and folder where the table is stored.

3	Click the filename.

4	Click Empty.

To reclaim disk space after deleting a record

1	Click Format  Restructure table.

2	Enable the Pack table check box.

Note

  Packing a dBASE table removes from the table records marked for deletion.
  
Moving table records

You can move child records to a new parent or master table. You may need to move records if you have a record in one table that corresponds to a record in another table. This can occur in a referential integrity relationship where one record in a parent table is related to one or more records in a child table. It can also occur in a multi-table form where one record of the master table is related to one or more records in the detail table.

For example, a table containing a list of salespersons is the parent table, and it has a child table called Customers. Referential integrity is established on a common Salesperson Identification field. Each salesperson can have many customers assigned, but only one salesperson can be assigned to a customer. This is called a one-to-many relationship. If a salesperson named Jones has fifty customers assigned, then in the customer.db table, fifty customers have Jones listed as their salesperson. If Jones leaves the company, those fifty customers must be reassigned to another salesperson. This is called moving dependent records.

To move a record in a child table

1	In a child table, press F9.

2	Click a record that corresponds to the first field in the parent table in a referential integrity relationship.

3	Click Record  Reference  Move help.

4	In the Move help dialog box, click a record in the parent table.

Note

  You can move only fields for which one-to-many or referential integrity relationships have been defined.
  
Navigating table records

Paradox lets you navigate table records to allow you to access to your data. You can also move through table records by using the scroll arrows and scroll bars.

To navigate through table records

  On the property bar, click any of the following arrow buttons:
  
 lets you move to the first record of the table

 lets you move up one record set; the number of records displays in the table window

 lets you move to the previous record of the table

 lets you move to the next record of the table

 lets you move down one record set; the number of records displays in the table window

 lets you move to the last record of the table

To scroll through records vertically

  On the vertical scroll bar, click any of the following arrow buttons:
  
  up arrow - lets you scroll upwards through a table one record at a time
  
  down arrow - lets you scroll downwards through a table one record at a time
  
Notes

  In dBASE tables, the vertical scroll box is always centered vertically when the Table show deleted check box is disabled
  
  If the table is keyed, Paradox displays the range of values in the key field, or displays the first field of a composite key, on the status bar as you move the vertical scroll box.
  
To scroll through records horizontally

  On the horizontal scroll bar, click any of the following arrow buttons:
  
  left arrow - lets you scroll to the left through the columns of the table
  
  right arrow - lets you scroll to the right through the columns of the table
  
Navigating table records using the keyboard

You can navigate horizontally and vertically through table records. You can also use the keyboard to select table records. For example, you can select the first or last field in the current record. You can also select the first field of the first record or the last field of the last record in a table. In addition, you can use the keyboard to scroll the table windows.

To horizontally navigate through table records by using the keyboard

  Press the following keys on the keyboard to horizontally navigate through table fields and records:
  
Key	Action

	Selects the field to the left of the selected field. If the selected field is the first field in the record, selects the last field of the previous record.

	Selects the field to the right of the selected field. If the selected field is the last field in the record, selects the first field of the next record.

To vertically navigate table records by using the keyboard

  Press the following keys on the keyboard to vertically navigate through table fields and records:
  
Key	Action

	Selects the same field in the record below the current one

	Selects the same field in the record above the current one

To select the first field in the current record by using the keyboard

  Press HOME.
  
To select the first field of the first record in the table by using the keyboard

  Press CTRL + HOME.
  
To select the last field in the current record by using the keyboard

  Press END.
  
To select the last field of the last record in the table by using the keyboard

  Press CTRL + END.
  
To display records by using the keyboard

  Press the following keys on the keyboard:
  
  PAGE DOWN - displays the next set of records
  
  PAGE UP  - displays the previous set of records
  
To scroll the table windows by using the keyboard

  Press the following keys on the keyboard:
  
  CTRL + PAGE DOWN - scrolls the window to the next set of fields
  
  CTRL + PAGE UP - scrolls the window to the previous set of fields
  
Saving table records

Saving a record is often called posting or committing a record. When working in a multi-user environment, others do not see changes you've made until you've posted them. Paradox automatically saves (posts) changes when you exit a record, but you can save your edits before they leave the record.

If a table is keyed, Paradox automatically moves the record to its correct location in the table. If a table is not keyed, the new record stays where you added it.

To save a table record

  Click Record  Post/keep locked.
  
Notes

  When you post a record in a keyed table, the record is moved to its correct position in the table. If the record's proper position is off the screen, the record may seem to disappear as it is posted. However, if you look at the record count on the status bar, you'll see that the record has been added. Your view of the table might not change when the record is posted, but the cursor remains where it was when you pressed INSERT.
  
  If a record is moved to a different location when you post it, it's probably because the table is keyed and the new record is not in its correct location in the table. In that case, the record is moved to its correct location. When you click Record  Post/keep locked, the moved record remains active, and the table view is updated.
  
Locking table records

A record is automatically locked when you edit it and the lock is removed when you exit the record; however, you can add a lock to a record. When you lock a record; others can view it, but they can't edit or delete it. Locking a record also prevents others from placing a read or write lock on the table and from performing any operations that require a read or exclusive lock, such as restructuring the table. You can also unlock a table record.

To lock a record

1	In Edit mode, select a table record.

2	Click Record  Lock.

Notes

  The status bar indicates that the record is locked.
  
  Paradox automatically unlocks a record when you exit the record or switch from Edit mode to View mode.
  
Tip

  You can also lock a record by selecting the record and pressing F5 or CTRL + L.
  
To unlock a table record

1	Select a locked table record.

2	Click Record  Unlock.

Notes

  If you have locked a record manually, you must unlock the record to release it for others to edit or delete. If you try to edit a record and discover it locked, the status bar displays the name of the person who locked the record.
  
  Paradox automatically unlocks a record when you exit the record or switch from Edit mode to View mode.
  
Tip

  You can also unlock a record by selecting the record and pressing SHIFT + F5.
  
Subtracting table records

Subtracting table records allows you to remove the records from a one table that match the records in another table, which is called the subtraction table. You can subtract records only from a keyed table. During a subtract operation, Paradox removes any record containing a value in its key that matches the corresponding field(s) of a record in the subtraction table.

You would perform a subtraction to permanently remove records from a table. For example, a company that recently mailed its customers want to pare down customer records to include only the customers who answered the mailing. You would do this by creating a subtraction table that contains the names of all customers who didn't answer the mailing. You would then subtract the information from the customer.db table. The subtracting process matches the subtraction table, which contains the list of customers who didn't answer the mailing, to the names in the customer.db table, and removes the records that match. The result is a smaller customer.db table containing only the names of the customers who responded to the last mailing. Paradox deletes the records of customers who did not respond to the last mailing.

The two tables you use in a subtraction must have compatible structures. This means compatible fields in the same field order, which can be achieved by borrowing the original table's structure. In addition, if the table from which you subtract is the parent table in a referential integrity relationship, the subtraction subtracts only records that do not exist in the child table. You must first delete the referential integrity by restructuring the child table or delete the related record in the child table. For more information about referential integrity, see "Modifying and removing a referential integrity relationship."

When subtracting, Paradox needs to acquire a read lock on the table that contains the records you are subtracting and a write lock on the table from which you are subtracting records. This means that until the records are subtracted, others cannot change the contents or structure of either table or perform any operation that requires a write or exclusive lock on either table. If either table already has a write or exclusive lock on it, you must wait until the lock is removed before subtracting.

To subtract records from a table

1	Click Tools  Utilities  Subtract.

2	Choose the drive and folder where the table whose records you want to match is stored.

3	Double-click the file.

4	In the Subtract records from dialog box, choose the drive and folder where the table from which you want to subtract records.

5	Click Subtract.

Notes

  Because Windows lets you open several instances of the same table, you could be considered another user of a table, which would prevent you from subtracting records. You cannot subtract records from a table open in edit mode.
  
  Because dBASE and Structured Query Language tables do not support Paradox keys, you cannot subtract records from dBASE or SQL tables. Instead, use a DELETE query. For more information about the DELETE query, see "Using reserved words to perform queries." 
  



