See Also: Constraints and Filters, Editing Data-Dictionary Structures, OnConstrain method, Constrain_File property
To specify the constrained set of records for a DataDictionary object. It may be used outside of DataDictionaries as well using Constraint_Sets.
Constrain {table.column} {mode} {value} [And {value}]
Constrain {child-table} Relates To {parent-table}
Constrain {table} As (expression)
|
Argument |
Explanation |
|
{table} |
Is the name of the database table that is being constrained. |
|
(expression) |
Is any Boolean expression. The expression is tested for each possible record in the constrained set of data. A False result excludes the record from the constrained set. |
|
{table.column} |
Is the database table.field that is being tested. The test is performed for each possible record in the constrained set of data. A False result excludes the record from the constrained set. |
|
{mode} |
May be any one of… |
|
{value} |
Is a variable or constant. The Between mode requires an And between two values. |
Constrain commands are used in the OnConstrain event method of data dictionary-objects to define a constrained set of records. OnConstrain is an event method defined in the DataDictionary class.
Each Constrain command in the OnConstrain method is evaluated for each record in the data dictionary-object's main file. The result of this evaluation will determine if the record will be part of the object's constrained set of data. If a record fails any Constrain command then it will be excluded from the constrained data set. If there are no Constrain commands in the OnConstrain event of a data-dictionary object, then all records in the object's main file will be part of the object's data set.
There are three types of constraints: Constrain Table.Column, a Constrain Relates-to, and a Constrain-as.
The Between mode is inclusive of the two values that indicate the beginning and end of the constraint. You can think of it as a combination of the GE and LE modes.
The Constrain Table.Column is used to create a static constraint for a table.field value. This table.field value, almost always a field value from the data-dictionary's main_file, is constrained by a static expression. The expression is static in that it is created once when the constraint set is built and is not rebuilt every time a new record is found. Because this expression is built by the runtime, it can be optimized. If the appropriate index is used, the constraint engine will attempt jump in and out of an index whenever possible. When properly set up, these types of constraints can be very fast.
The following example sets up constraints for an order data-dictionary object (DDO) so that only the active orders are found. If records were being found on an index whose first segment was orders.status, the constraint finding process would be optimized (the DDO would be able to jump to the first valid record and jump out after the last valid record). If the records were being found on any other index, the finding process would not be optimized (the DDO would have to start and the beginning of the table and possibly search all records).
Object Orders_DD is an Orders_DataDictionary
Procedure OnConstrain
Constrain Orders.Status eq "A"
End_Procedure
End_Object
A constraint set can consist of multiple constraint commands. When there are multiple constraints, only those records that satisfy all the constraints will be found. The following example sets up constraints for an order data-dictionary object so that only active orders in 1999 are found. If the first two segments of the finding index was Status x OrdDate, the find process could be optimized:
Object Orders_DD is an Orders_DataDictionary
Procedure OnConstrain
Constrain Orders.Status eq "A"
Constrain Orders.OrdDate Between "1/1/1999" And "12/31/1999"
End_Procedure
End_Object
A constraint is "built" when the data-dictionary is initialized. It is not built each time the record is found. In the following example, the order status is determined by checking a property value.
Object Orders_DD is an Orders_DataDictionary
Property String psValidStatus
Procedure OnConstrain
String sValidStatus
Get psValidStatus to sValidStatus
Constrain Orders.Status eq sValidStatus
End_Procedure
End_Object
Note that the property value is checked only once when the DDO is initialized. If you change the property value while you are finding records, the constraint will not be affected. If you wish to notify the DDO that a constraint has changed you must rebuild the constraints by sending the message Rebuild_Constraints to the DDO.
Object Orders_DD is an Orders_DataDictionary
Property String psValidStatus
Procedure OnConstrain
String sValidStatus
Get psValidStatus to sValidStatus
Constrain Orders.Status eq sValidStatus
End_Procedure
End_Object
Procedure ChangeStatus String sNewStat
Set psValidStatus of Orders_DD to sNewStat // change property
Send Rebuild_Constraints of Orders_DD // rebuild the constraint
End_procedure
You may change constraints in a more dynamic fashion using the Constrain As type of constraint.
The "relates to" clause limits the constrained set of data to records in the {child-table} that are related to the current record in the data-dictionary belonging to the {parent-table}. Note: the respective data-dictionary objects must be correctly connected with each other. Refer to Editing Data-Dictionary Structures for more information on connecting data-dictionary objects.
Because relates-to constraints are use so often a special data-dictionary property, Constrain_File, has been created that allows you to set a child parent constraint. When the Studio creates DDO structures it uses this Constrain_File property and not the relates-to constraint command. For this reason, you will rarely see this variant of the constrain command used.
The following example shows how a relates-to constraint is created using the Constrain_File message.
Object Orderdtl_DD is a Orderdtl_DataDictionary
Set DDO_Server to (Orderhea_DD(Self))
Set Constrain_File to Orderhea.File_Number // this is the suggested syntax
End_Object
If, for some reason, you wanted to create the same constraint using the constrain command, the same constraint could be coded as follows (this technique is not recommended):
Object Orderdtl_DD is a Orderdtl_DataDictionary
Set DDO_Server to Orderhea_DD
Procedure OnConstrain
Constrain OrderDtl Relates To Orderhea
End_Procedure
End_Object
Relates-to constraints (and the Constrain_File property) can be used with other constraints. In the following example, orders are limited to records that are related to a customer record and have an open status.
Object Orders_DD is a Orders_DataDictionary
Set DDO_Server to Customer_DD
// create a relates to constraint to customer
Set Constrain_File to Customer.File_Number
Procedure OnConstrain
Constrain Orders.Status eq "O"
End_Procedure
End_Object
Relates-to constraints (whether defined with the Constrain_File message or the constrain command) can and almost always should be optimized. This means that the first segment of the child table index should be the field that relates to the parent table.
You can use the constrain-as constraint to use expressions to set constraints. Unlike the other types of constraints the expression defined to the right of the "as" parameter is evaluated each and every time a record is found.
The constraint engine has no idea what the expression actually contains. Therefore it is unable to optimize constrain-as constraints. This means that you cannot jump into and out of an index when finding a record. The advantage of the constrain-as method is that any type of expression may be used and you do not need to rebuild your constraints when the conditions of the expression change. The constrain-as constraint therefore represents a trade-off between speed and flexibility.
In the following example, records in the Invoice table will be found when either their InvDate field has a value less than the date limit stored in the pdDateLimit property or their Balance field has a balance in excess of 1000.
Property Date pdDateLimit
Object Invoice_DD is an Invoice_DataDictionary
Procedure OnConstrain
Constrain Invoice As ((Invoice.InvDate < (pdDateLimit(self)) ) OR ;
(Invoice.Balance > 1000) )
End_Procedure
End_Object
Only use constrain-as constraints when other constraint cannot be used. While the following two code segments will find the same records, the expression based "constrain … as" can be significantly slower.
// These can be optimized
Constrain Orders.Status eq "O"
Constrain Orders.Balance gt O
// This cannot be optimized
Constrain Orders as (Orders.Status="O" AND Orders.Balance>0)
When a constrain-as constraint is in use, every record will be moved into the buffer and tested — index jump-in/jump-out optimization cannot be used. However, if an expression-based constraint is used with other constraints that are capable of index optimization, the optimizations implied by the other constraints will be used. The non-optimized record searching will be limited to the smaller set of optimized records. This technique greatly increases the usability of expression based constraints.
In the following example orders are limited to records that relate to a customer, are open, and have a balance that is less than or equal to zero or greater than or equal to 1000. If the index used to find these records was Customer_Number x Status x Number, the record finding will be partially optimized.
Object Orders_DD is a Orders_DataDictionary
Set DDO_Server to Customer_DD
// create a relates to constraint to customer
Set Constrain_File to Customer.File_Number
Procedure OnConstrain
Constrain Orders.Status eq "O"
Constrain Orders as (Orders.Balance<=0 OR ;
Orders.Balance>=1000)
End_Procedure
End_object
Important: When using Constrain ... As, do not use any local variables in the constraining expression. Since record scanning and expression evaluation occurs outside of the procedure in which the constraint is set those local variables will not exist when the expression is evaluated.
Constraints can be changed at runtime. Any time the conditions of a constrain-as constraint is changed, the change will be immediately applied to the next record found. If the conditions of a constrain table.field or relates-to constraint is changed, the constraint must be rebuilt by sending the DDO the message Rebuild_Constraints.
The following example has an OnConstrain method that creates a constraint block that is controlled entirely by properties defined in the view.
// these are being defined within the view
Property Boolean pbRelatesToCust // if true, relate to customer
Property String psOrderStatus // show: ""=all, "O"=open, "C"=closed
Property Boolean pbShowBadOrders // If true, show only bad orders
Object Orders_DD is a Orders_DataDictionary
Set DDO_Server to (Customer_DD(self))
Set DDO_Server to (SalesP_DD(self))
Procedure OnConstrain
Boolean bRelatesToCust bShowBadOrders
String sOrderStatus
// set relates to constraint based on pbRelatesToCust property
Get pbRelatesToCust to bRelatesToCust
If bRelatesToCust Set Constrain_File to Customer.File_Number
Else Set Constrain_File to 0
// set conditional constraint for status. Only set this if
// a status exists
Get psOrderStatus to sOrderStatus
If (sOrderStatus <> "") Constrain Orders.Status EQ sOrderStatus
// only test for bad orders if we are constraining the order
If bRelatesToCust Begin
Get pbShowBadOrders to bShowBadOrders
If bShowBadOrders ;
Constrain Orders as (Orders.Balance <= 0 OR ;
Orders.Completed = "N")
End
End_Procedure
End_Object
// call this procedure to change constraint rules
Procedure ChangeConstraints Boolean bRelatesTo string sStatus Boolean bShowBad
// set new property values
Set pbRelatesToCust to bRelatesTo
Set psOrderStatus to sStatus
Set pbShowBadOrders to bShowBad
// reinitialize the constraints
Send Rebuild_Constraints of Orders_DD
// find the first valid record in the new set
Send Request_find of Orders_DD FIRST_RECORD Orders.File_Number 1
End_Procedure
Constraints in data-dictionaries are additive. Within a DDO structure, the constraints of parent DDOs structure are applied to (ANDed to) a child DDO's constraint set.
Between ... And may be used only as a comparison operator in a simple Constrain command. It may not be used with other commands or in expressions.
In older programs, you may see constrain commands used within the Begin_Constraints / End_Constraints command block. These commands are obsolete and can be replaced with the OnConstrain procedure. You may also see constrain commands used within the procedure Constrain. While this technique is valid, the preferred method is to use the OnConstrain method.
You can use the predefined global variables, Constrain_Found_Count and Constrain_Tests_Count to determine if constrained records are being found in an optimized manner.
The Constrain command is also used to specify a constrained set of records outside of data-dictionary objects. The Constrain command is used in the same way but commands must be used to create, initialize, clear and find records. See the Constraint_Set and Constrained_Find commands. If you are using data dictionaries you will probably never need to use these lower level constraints.