Understanding Database Rules

Add

The Add rule adds a value to a field. You can add a constant value or a variable value from a field. When adding variable values, you can select a field from the same file or a related file. If you add a field to another field, the fields must be the same type and size. If you add a constant value to a field, the value must be the same type and no larger than the size of the field.

Example

Invoices are grouped under a Batch file record. In an Add event, the following rules add the invoice amount to the invoice total of the Batch record and increase the actual count (Actual Count) of the Batch record by 1.

Add Invoice Amt    To Batch->Invoice Total
Add 1              To Batch->Actual Count

Assign

This rule assigns values to fields. You can assign a constant value, a variable value from a field, or the current date. When assigning variable values, you can select a field from the same file or a related file. If you assign a field to another field, the fields must be the same type and size. If you assign a constant value to a field, the value must be the same data type and no larger than the size of the field.

Example

The Employee file has a one-to-one relation with the Department file. When you add an employee to the Employee file, the following rules assign fields from the Department file to fields in the Employee file.

Assign Department -> Acct Unit    To Acct Unit
Assign Department -> Account      To Account
Assign Department -> Sub Account  To Sub Account

You do not have to assign key fields that are used to relate the two files. For example, in the one-to-one relation from the Employee file to the Department file, the Company and Department fields associate the two files with each other. The relation assigns the values of these two fields in the Department file to the same fields in the Employee file.

Auto Sequence

The Auto Sequence rule updates a numeric field with the next sequential number. Because the system can sequence a field only from a field in another file, there must be a one-to-one required relation between the first file and the second file.

Example

The Poheader file stores the number of the last detail line created for a purchase order in its Last Line Nbr field. When a user or a program creates a new detail line, the following rule updates the Line Nbr field in the Podetail file with the next sequential line number from the Last Line Nbr field.

AutoSeq Line Nbr Using Poheader->Last Line Nbr

Default

This rule assigns a default value to a field. If you do not type a value in the field when you add a record to the file, the rule assigns the default value to the field. You can use a constant value, a variable value from a field, or the current date as a default for a field. When defaulting to variable values, you can select a field from the same file or a related file. If you use a value from another field as the default for a field, the fields must be the same type and size. If you use a constant value as the default for a field, the value must be the same type and no larger than the size of the field.

Example

If you add an employee to the Employee file and do not type the hire date, the following rule defaults the employee hire date to the current date.

Default Hire Date To "Current Date"  

Field Relation

The Field Relation (FldRel) rule defines relationships between fields. This rule states that the relationship must be true; otherwise, there is an error. You define the error message when you define a FldRel rule.

Example

An invoice record stores the date an invoice is created and the date the invoice is due. The invoice date and the due date have a relationship that states the due date must be greater than or equal to the invoice date. If the due date is less than the invoice date, the system displays an error. In the following example, the condition Duedt Ge Invdt states that the due date is greater than or equal to the invoice date. If the Duedt Ge Invdt condition is not true, the system displays the error message shown on the second line of this example.

FldRel (Duedt Ge Invdt)
Due Date Cannot Be Less Than Invoice Date

Invoke

The Invoke rule executes another event.

Example

Before the Poheader file Delete event deletes a purchase order, it creates a Summary record of the purchase order in the Pohistory file. To create the Summary record, the program must invoke the Pohistory file Add event (in the Edit state). The syntax for the Invoke rule displays the event, the state, and the file that the rule is invoking.

In the following example, the first rule invokes the Pohistory file Add event, which creates a Pohistory record. The remaining rules assign the key field (Date and Vendor) values of the Poheader record to the key fields of the Pohistory record.

Invoke Add Edit Pohistory

Assign Date      To Pohistory->Date
Assign Vendor    To Pohistory->Vendor

Make Transition

The Make Transition rule takes the records processed by an event to a different state. State transition is not necessarily based on the sequential ordering of the states.

You can only use this rule in a function-type event. See Defining Events for more information.

Example

When you add a Timerecord record, the file puts it in the Edit state with a status of 0. When you submit the time record (Submit event), the Make Transition rule updates the time record Status field to 1 and puts the record in the Submitted state.

Make Trans To Submitted

After the time record is approved (in the Approve event), the Make Transition rule updates the Status field to 2 and puts the record in the Approved state.

Make Trans To Approved

After the system processes and posts the time record (the Process event), the Make Transition rule updates the Status field to 3 and puts the record in the History state.

Make Trans To History

No Entry

The No Entry rule states that a field must be blank.

Example

If an employee is paid hourly, the salary field must be blank.

If (Hourly)
   Salary Can't Be Entered

Reference Restricted

The Reference Restricted (RefRstr) rule prohibits access to all records in a subtype. If a user tries to access restricted records, the system displays an error message. You define the error message when you define a RefRstr rule. This rule is valid only under a subtype.

Example

The subtype Inactive Items includes only inactive items. The following rule belongs to the Inactive Items subtype. It restricts users from accessing records in this subtype.

RefRstr
   Cannot Reference An Inactive Item

Required

The Required rule ensures that the user types a value in a field.

Example

The following rules require the user to enter values in the Company and Vendor fields in the Invoice file to complete the invoice record.

Company Is Required
Vendor Is Required

Subtract

The Subtract rule subtracts a value from a field. You can subtract a constant value or a variable value from a field. When subtracting variable values, you can select a field from the same file or a related file. If you subtract a field from another field, the fields must be the same type and size. If you subtract a constant value from a field, the value must be the same type and no larger than the size of the field.

Example

The Lawson Accounts Payable application groups invoices under a Batch file record. In a change event, the following rules subtract the old amount of the invoice from the invoice total of the Batch record, and add the new amount of the invoice to the invoice total of the Batch record.

Subtract    Old Inv Amt From  Batch->Invoice Total
Add         Invoice Amt To    Batch->Invoice Total

Update

The Update rule updates numeric, signed, or percent fields. It adds amounts in an add event and subtracts amounts in a delete event. In a change event, it subtracts the old amount and adds the new amount. You can define Update rules for only a file or a state. To update fields in an event, use the Add and Subtract rules.

Example

Each line of a purchase order has an amount. When you add a detail line, the following rule adds the amount of each line to the total amount of the Poheader file. When you delete a line, this rule subtracts the amount of each line from the total amount of Poheader. When you change the line amount, this rule subtracts the old amount from the total amount and adds the new amount to the total amount.

Update Poheader->Total Amt Using Line Amt