Version: 10.1

USMeta.Relationships

note

This article is about the Relationships method of the USMeta internal component.

USMeta.Relationships

At runtime, gets metadata information about relationships and relationship columns from the model developed in USoft Definer. For each retrieved relationship, this information takes the form of name/value pairs:

Name	Value
PARENT_TABLE	Parent Object
CHILD_TABLE	Child Object
PARENT_ROLE	Parent Role
MODULE_NAME	If the relationship is not from a consumed module: the current application name. If you are calling from a USoft application: the USoft application name (e.g., USD for USoft Definer). If you are calling from a User Application: the User Application name (e.g., SALES). If the relationship is from a consumed module: the name of the consumed module. Tip: The relationship is from a consumed module if it is in the .CON file that the module provides. If the parent table and child table are both usable as interface, it is also possible for the current application to define the relationship itself.
CHILD_ROLE	Child Role
ALIAS_PARENT	Alias Parent
REV_MENU_LINE	Lookup Window Name
MENU_LINE	Related Window Name
FK_MANDATORY	Foreign Key Mandatory
FK_CHECK_PARTIAL	Check Partial Foreign Key
LOCK_ON_FK_CHECK	Lock Foreign Key on Check
FK_UPDATABLE	Foreign Key Updatable: 'Y' if Yes; 'ONLY_IF_NULL' if Only If Null; 'N' if No.
CHILD_MANDATORY	Child Must Exist
MIN_NO_OF_CHILDREN	Minimum Children
MAX_NO_OF_CHILDREN	Maximum Children
LOOKUP_METHOD	Lookup Method: 'WINDOW PAGE' for "Lookup Window or Page" (default); 'DROPDOWN' for "Dropdown List"; 'RADIO' for "Radio".
DELETE_RULE	Delete Rule: 'RESTRICTED', 'CASCADING', or 'NULLIFY'.
UPDATE_RULE	Update Rule: 'RESTRICTED', 'CASCADING', or 'NULLIFY'.
TYPE_OF_RELATIONSHIP	Type Of Relationship: 'REFERENCE' or 'COMPOSITION'.

Relationship columns are shown in a child element, for example:

<Relationship PARENT_TABLE="PERSON" CHILD_TABLE="ORDER" ... >
    <Relationship_Columns>
        <Column PARENT="PERSON_ID" CHILD="ORDER_ID"/>
    </Relationship_Columns>
</Relationship>

Returns an XML document of the form demonstrated in Example 1 below.

Syntax

SELECT USMeta.Relationships(
    *parent-table-name-pattern*   Parents
,   *child-table-name-pattern*    Children
,   *role-name-pattern*           Roles
,   *mime-type*                   MimeType
)

*mime-type*   ::=  {  application/xml
                 ,  xml
                 ,  application/json
                 ,  json  }

All parameters are optional. Parameter values that you pass are mapped to parameters by alias name. The 'PERSON' value in the following example is for parent-table-name-pattern because of the "Parents" alias:

SELECT   USMeta.Relationships( 'PERSON' Parents )

tip

For backward compatibility only, it is possible to supply parameter values by position. Using this syntax, a list of non-aliased values maps to the parameter list in the order stated, with empty values at the end of the list if you supply less than 3 values, and '' (the empty string) as a way of explicitly declaring an empty value.

Parent-table-name-pattern is a regular expression that matches zero, one or more parent tables in the model. The result data is limited to relationships that have a parent table matching the pattern. If parent-table-name-pattern is omitted, then result data is not limited in this way.

Child-table-name-pattern is a regular expression that matches zero, one or more child tables in the model. The result data is limited to relationships that have a child table matching the pattern. If child-table-name-pattern is omitted, then result data is not limited in this way.

Role-name-pattern is a regular expression that matches zero, one or more role names in the model. The result data is limited to relationships that have a Parent Role name matching the pattern. If role-name-pattern is omitted, then result data is not limited in this way.

Mime-type determines whether the output is in XML ( ‘application/xml‘ or 'xml’) or in JSON ('application/json’ or 'json’). The default is XML.

Example 1

This example returns relationship information on all relationships that have the TOUR table als their child table:

SELECT USMeta.Relationships( 'TOUR' Children )

The return value of this statement could be:

<?xml version="1.0" encoding="UTF-16"?>
<Meta>
	<Relationships>
		<Relationship PARENT_TABLE="GUIDE" CHILD_TABLE="TOUR" PARENT_ROLE="GUIDES" MODULE_NAME="TRAVEL" CHILD_ROLE="are guided by" ALIAS_PARENT="" REV_MENU_LINE="Persons guiding Tours" MENU_LINE="Tours guided by this Person" FK_MANDATORY="N" FK_CHECK_PARTIAL="Y" LOCK_ON_FK_CHECK="Y" FK_UPDATABLE="Y" CHILD_MANDATORY="N" MIN_NO_OF_CHILDREN="0" MAX_NO_OF_CHILDREN="0" LOOKUP_METHOD="WINDOW PAGE" DELETE_RULE="RESTRICTED" UPDATE_RULE="RESTRICTED" TYPE_OF_RELATIONSHIP="REFERENCE">
			<Relationship_Columns>
				<Column PARENT="PERSON_ID" CHILD="GUIDE"/>
			</Relationship_Columns>
		</Relationship>
		<Relationship PARENT_TABLE="TOUR_PROGRAMME" CHILD_TABLE="TOUR" PARENT_ROLE="IS FOLLOWED BY" MODULE_NAME="TRAVEL" CHILD_ROLE="follows" ALIAS_PARENT="" REV_MENU_LINE="Tour_programmes is followed by tour" MENU_LINE="Tours is followed by tour_programme" FK_MANDATORY="Y" FK_CHECK_PARTIAL="Y" LOCK_ON_FK_CHECK="Y" FK_UPDATABLE="Y" CHILD_MANDATORY="N" MIN_NO_OF_CHILDREN="0" MAX_NO_OF_CHILDREN="0" LOOKUP_METHOD="WINDOW PAGE" DELETE_RULE="RESTRICTED" UPDATE_RULE="CASCADING" TYPE_OF_RELATIONSHIP="REFERENCE">
			<Relationship_Columns>
				<Column PARENT="DESTINATION" CHILD="DESTINATION"/>
				<Column PARENT="TOUR_TYPE" CHILD="TOUR_TYPE"/>
			</Relationship_Columns>
		</Relationship>
	</Relationships>
</Meta>

Example 2

This example returns relationship information about all relationships that have as their child table name an alphanumeric uppercase string that contains 'TOUR':

SELECT USMeta.Relationships( '^[A-Z]*TOUR[A-Z]*$' Children )

USMeta.Relationships​

USMeta.Relationships