Relation Types (AD_RelationType)

Overview

Relation types are located in the menu under:

Menu/Application-Dictionary/Relation Type (AD_RelationType)

Clicking on the menu item will open a list of all available relation types. Relation types can be divided into two groups based on the checkbox IsTableRecordIdTarget which can be checked or not checked.

Example of how relations appear on WebUI:

Steps

a) IsTableRecordIdTarget is checked

1. Define a reference type

In this case you need to define only one relation, the target relation.

2. Define a reference for the relation type

1. Give the reference a Name.

2. In the field ValidationType, pick T_Tabellenvalidierung.

   

3. Open the tab Tabellenvalidierung (table validation).
4. In the field AD_Table_ID, set the table where to look for the relation.
5. In the field AD_Key, always set the unique key of the table.

6. In the field AD_Window_ID, set the target window.

   

   As a result, on the WebUI, the application will look for the reference in the source window and show it in the sidebar:

   

   …and when clicking on the reference this will take you to the respective target window:

   

   In the example above the source page (e.g., C_Invoice) is looking for any entry in the target table which has the

   • table_id = (select ad_table_id from ad_table where tablename = 'C_Invoice')
     and
   • record_id = @currentRecord@

   The query executed behind the scene is:

    select *
    from Fact_Acct_Transactions_View
    where ad_table_id = (select ad_table_id from ad_table where tablename = 'C_Invoice')
    AND record_id = 1002044;
   

b) IsTableRecordIdTarget is NOT checked

This relation type is needed when we want to display relations between entities which are not directly related.

1. Define a reference type

1. Give the reference type a Name.

   Note: We recommend using the pattern source_table -> target_table.

2. Tick the checkbox isDirected to ensure that the direction of the relation is from source to target.

   Note: If you also want the reverse relation (target to source) you will need to define a separate relation type for it.

3. Set the AD_Reference_Source_ID.

   Note: Should be defined before continuing to step 2.

4. Set the AD_Reference_Target_ID.

   Note: Should be defined before continuing to step 3.

5. In the field Entity Type, pick Dictionary.
6. All other fields can be ignored.

2. Define a source reference

1. Create a new reference.
2. Give the reference a Name.

   Note: We recommend including the source/target words in the name to make the purpose of the reference clear.

3. In the field ValidationType, pick T_Tabellenvalidierung.

   

4. Open the tab Tabellenvalidierung (table validation).
5. In the field AD_Table_ID, pick a table, e.g., C_Order.
6. In the field AD_Key, pick the column on which the reference is based, e.g., C_Order_ID.
7. In the field WhereClause, enter a condition for the source table, e.g., IsSOTrx = 'N' (i.e. should be a purchase order).
8. In the field AD_Window_ID, pick the source window, e.g., Bestellung.

   Note: This is not mandatory, but recommended.

   

3. Define a target reference

1. Create a new reference.
2. Give the reference a Name.

   Note: We recommend including the source/target words in the name to make the purpose of the reference clear.

3. In the field ValidationType, pick T_Tabellenvalidierung.

   

4. Open the tab Tabellenvalidierung (table validation).
5. In the field AD_Table_ID, pick a table, e.g., M_Forecast.
6. In the field AD_Key, set the unique key of the table, e.g., M_Forecast_ID.
7. In the field Entity Type, pick Dictionary if nothing else is specified.
8. In the field AD_Window_ID, pick the target window, e.g., Prognose.

   Note: This is not mandatory, but recommended.

9. In the field WhereClause, define a logic to display elements from the AD_Table_ID (target) based on the AD_Key from the source reference (see example above C_Order_ID).

   

Explanation of the WhereClause of the target reference

1. Looking for items in the M_Forecast table. If they exist they shall be set as AD_Table_ID in the target reference.
2. JOINing all the necessary tables until we get the relation to the source table C_Order.
3. Adding MAIN conditions:
   • f.M_Forecast_ID = M_Forecast.M_Forecast_ID => Target_Table_Name.Target_Key
   • o.C_Order_ID = @C_Order_ID / -1@ => Source_KEY

How it appears on WebUI:

• Source Window:

  

• Target Window: