Table Relations & Delete Actions

 

Table Relations

A relation on a table can restrict the rows in the table, or restrict the values that can be in particular fields.

A common use of relations is to associate rows in one table to corresponding rows in another table.

Relations enable many forms to display data from multiple tables.

Some relations restrict the rows in the table by testing the values in each row against constant values.

Other relations restrict the rows by comparing values in each row to values in a row in another table.

 

Add a Relation to a Table in the AOT:

The initial steps for adding a relation are the same regardless of the relation type that you are adding. The later steps diverge based on the relation type.

Remember to save your changes in the AOT.

1. In the AOT, move to Data Dictionary > Tables, and then expand the table that the relation will be added to.

2. Right-click the Relations node, and then select New Relation.

3. Right-click the newly added relation, and then select Properties.

4. Set the name of the new relationship by modifying the Name property.

5. In the Table property, select the related table.

6. Use the Validate property to determine whether the relation should be used to validate data when information is entered into forms.

7. Right-click the new relation, select New, and then click one of the following:

    1. Normal to specify relation fields without conditions.

    2. Field fixed to specify relation fields to restrict the records in the primary table.

    3.Related field fixed to specify relation fields that restrict the records in the related table.

    4. ForeignKey to specify a correspondence between a foreign key field in the present table to the primary key field in another parent table.

 

 

 

Creating a relation through Table reference property on EDT:

Relations and Lookups

1. First create new EDT: ATTCustId

2. Create new table: ATTCustTable and drag drop the EDT in this table

3. Create index for new EDT field in the table and make the property of this index as " Allow duplicate" = "No" and Alternative key to "Yes"

4. Select the properties of the ATTCustTable and set the "primary index property" to newly created index.

5. Then go to the EDT set the property "Reference Table " to ATTCustTable and add a new table reference and the field CustId from the table.

6. Create a second table ATTCustTrans

7. Drag drop the EDT field in this table also

     it will prompt one dialog box

     in that dialog box click Yes button then lookup will get created on that field

    This will automatically a new relation of type "Normal" in ATTCustTrans table in the Ralations node.

 

Create 2 more fields in table ATTCustTrans: TransDate and AmountMST.

For "related field fixed" you have to have the "Normal" relation first.

and related field fixed should be always on Enum field of "Parent table"

it will give the "Where" clause to your select query.

Select * from ATTCustTable where ATTCustTable.Gender == Gender::Female

Foreign key relation: Goto ATTCustTrans, create a new relation and then create the "Foreign key" relation. You will get two options there: "Primary key based" and "Single field alternate key based"

Primary key based will create a relation as created above by EDT. The advantage of creating by EDT is that, once you create on EDT, you can reuse it.

 

"Single field alternate key based": will create relation for "RecId" field in ATTCustTable.

 

Delete Actions in Table:

The DeleteAction element helps maintain database consistency when a record is deleted.

Delete actions to specify what should occur when data being deleted in the current table is related to data in another table.

 

For example, use a cascading delete action to specify that the system is to delete a customer's address when that customer is deleted from the

 CustTable table. Another example is to use a restricted delete action to prevent a customer from being deleted from the

 CustTable if one or more transactions exist for the customer in the CustTrans table.

 

Add a Delete Action

1. In the Application Object Tree (AOT), expand the Data Dictionary.

2. Expand Tables, and then locate the table that you want to add a delete action to.

3. Click the table, right-click DeleteActions, and then click New DeleteAction.

4. Right-click the new delete action, and then click Properties.

5. Select a related table from the Table property list.

6. Set the DeleteAction property. The following table describes the available values.

 

1. None

2. Cascade

Deletes related records.

Setting the DeleteAction property to Cascade extends the functionality of the table's delete method.
As a result, super(), in delete, initiates a cascaded deletion, propagating the delete from table to table.
A cascaded delete is implicitly protected by tts. Database changes aren't committed until the entire transaction is complete.

Example: On the CustTable table, a cascading delete action has been defined for the CustBankAccount table.

When a customer is deleted from the CustTable table, the delete method also ensures that the corresponding bank account information is automatically deleted.

3. Restricted

Restricts deletion in the current table if data is present in related tables.

Setting the DeleteAction property to Restricted extends the functionality of the table's validateDelete method.

As a result, super(), in validateDelete, checks whether records exist on related tables. If records do exist, validateDelete returns false.

The forms system ensures that the deletion is not performed. In your own X++ code, check the return value of validateDelete. Don't delete the primary or related records if the method returns false.

Example

On the CustTable table, a restricted delete action has been defined for the CustTrans table.

When a customer is deleted in the CustTable table, the validateDelete method ascertains whether transactions exist for the customer in the CustTrans table.

If so, validateDelete returns false.

 

4.Cascade+Restricted

Cascade the delete, even though records exist on related tables.

Setting the DeleteAction property to Cascade+Restricted extends the functionality of the table's validateDelete and delete methods.

As a result, super(), in validateDelete, ascertains whether records exist on related tables. Whether deleting records from forms or X++,

 if validateDelete returns false, the primary record isn't deleted and the cascading delete isn't performed. You should first delete the records in the related table before deleting the primary record.

If the primary record is being deleted as part of a cascading delete, the primary record and the records in the related table will be deleted.

 

Example

The Cascade+Restricted delete action is used in the standard application for LedgerJournalTrans on LedgerJournalTable.

This type of delete action is useful when you prefer a total clean-up—when you delete a customer, you also delete all the transactions associated with that customer.