<TBL>.SUBTRACT()

Syntax

Specify the required values in the Subtract dot variable, then use the following command:

<TBL>.SUBTRACT()

Variable	Type	Description
Subtract.t_db	C	The filename of the secondary, transaction table that is subtracted from the primary table <TBL>.
Subtract.o_file	C	The filename of the resulting table.
Subtract.m_key	C	Expression string that describe how fields or combinations of fields from the master (or primary) table will form the linking keys.
Subtract.t_key	C	Expression string that describe how fields or combinations of fields from the transaction (or secondary) table will form the linking keys.
Subtract.m_filter	C	A character filter expression that selects records from the master (or primary) table.
Subtract.t_filter	C	A character filter expression that selects records from the transaction (or secondary) table.
Subtract.delete_o_dd	L	The target table that you specify may already exist, and may have a data dictionary. You can specify whether the target table’s pre-existing dictionary (if any) should be retained, or deleted. To retain the pre-existing dictionary set subtract.delete_o_dd to .F.
Subtract.m_count	N	The number of fields in the resulting table.
Subtract.m_field1 ... Subtract.m_fieldN	C	A list (1 ... N) of the field names. Each field name string must include the table name or alias as a prefix (i.e., table -> field ). Field names from the transaction (or secondary) table must have an "@" in front of the table or alias name.

Description

The <TBL>.SUBTRACT() method is a high-level utility used to create a new table by merging the records from a Primary table with records from a Secondary table. The new table contains one record for each record in the Primary table that has no matching record in the Secondary table. The Primary table must be an open table that is identified by the object pointer, <TBL>. The Secondary table is specified through its filename. The merged records are saved in the new table named by the Subtract.o_file parameter.

Records in the Secondary table are matched to records in the Primary through linking keys. The Primary Key Expression and Secondary Key Expression parameters contain expression strings that describe how fields or combinations of fields from each table will form the linking keys.

Supported By

Alpha Five Version 5 and Above

Example

This script shows how two tables that have one or more similar fields can be subtracted. The result table has those records from the primary table (Customer) that have no matching record in the secondary table ( inv_head ) (i.e, The result table shows which customers have NOT placed orders).

tbl = table.open("c:\a5\a_sports\customer")

subtract.t_db = "c:\a5\a_sports\inv_head.dbf"

subtract.o_file = "c:\a5\a_sports\result.dbf"

subtract.m_key = "CUST_ID"

subtract.t_key = "CUST_ID"

subtract.m_filter = ""

subtract.t_filter = ""

subtract.delete_o_dd = .T.

subtract.m_count = 6

subtract.m_field1 = "@INV_HEAD->INV_NO"

subtract.m_field2 = "CUSTOMER->CUST_ID"

subtract.m_field3 = "CUSTOMER->FIRST_NAME"

subtract.m_field4 = "CUSTOMER->LAST_NAME"

subtract.m_field5 = "@INV_HEAD->INV_TOTAL"

subtract.m_field6 = "CUSTOMER->HOME_PHONE"

tbl.subtract()

tbl.close()