Tech Tip: Utility Method to Obtain Relation Name Between Two Fields

PRODUCT: 4D | VERSION: 15.x | PLATFORM: Mac & Win

Published On: May 14, 2018

The object notation allows relational queries to be performed by passing the relation's name. The following utility method will return the relation's name between two fields from the first field passed.

// Method: GetRelName
// Description:
// Returns name of Relation from first input to second input
// Parameters:
// $1 - Pointer to Initial Field
// $2 - Pointer to Second Field
// $3 - Pointer (Optional) to Boolean to obtain status of first parameter
// True - First Parameter is Many Field
// False - First Parameter is One Field
// Output:
// $0 - String Name of Relation of Initial Field
//
//-----------------------------------------------------------------------

C_POINTER($1;$ref1_p)
C_POINTER($2;$ref2_p)
C_POINTER($3)
C_BOOLEAN($isMany_b)

C_TEXT($0;$relationName_t)

C_TEXT($fieldName1_t)
C_TEXT($tableName1_t)
C_TEXT($fieldName2_t)
C_TEXT($tableName2_t)

If (Count parameters>1)

  $ref1_p:=$1
  $ref2_p:=$2

  $fieldName1_t:=Field name($ref1_p)
  $tableName1_t:=Table name($ref1_p)
  $fieldName2_t:=Field name($ref2_p)
  $tableName2_t:=Table name($ref2_p)

  C_TEXT($struct_t)
  C_TEXT($xmlRef_t)
  C_TEXT($kind_t)
  C_TEXT($xmlfieldRef_t)
  C_TEXT($fieldName_t)
  C_TEXT($xmltableRef_t)
  C_TEXT($tableName_t)

  C_LONGINT($floop1_l)
  C_LONGINT($floop2_l)
  C_LONGINT($ManyToOne_l)

  ARRAY TEXT($relationsRef_at;0)
  ARRAY TEXT($fieldsRef_at;0)


  EXPORT STRUCTURE($struct_t)
  $xmlRef_t:=DOM Parse XML variable($struct_t)
  DOM Find XML element($xmlRef_t;"base/relation";$relationsRef_at)
  For ($floop1_l;1;Size of array($relationsRef_at))
    DOM Find XML element($relationsRef_at{$floop1_l};"relation/related_field";$fieldsRef_at)
    $ManyToOne_l:=-1
    For ($floop2_l;1;2)
      DOM GET XML ATTRIBUTE BY NAME($fieldsRef_at{$floop2_l};"kind";$kind_t)
      $xmlfieldRef_t:=DOM Find XML element($fieldsRef_at{$floop2_l};"related_field/field_ref")
      DOM GET XML ATTRIBUTE BY NAME($xmlfieldRef_t;"name";$fieldName_t)

      Case of
        : ($fieldName_t=$fieldName1_t)
          $xmltableRef_t:=DOM Find XML element($fieldsRef_at{$floop2_l};"related_field/field_ref/table_ref")
          DOM GET XML ATTRIBUTE BY NAME($xmltableRef_t;"name";$tableName_t)
          If ($tableName_t#$tableName1_t)
            $floop2_l:=2
          Else
            Case of
              : ($kind_t="source")
                $ManyToOne_l:=1
              : ($kind_t="destination")
                If ($ManyToOne_l=2)
                  DOM GET XML ATTRIBUTE BY NAME($relationsRef_at{$floop1_l};"name_1toN";$relationName_t)
                  $isMany_b:=False
                  $floop1_l:=Size of array($relationsRef_at)
                End if
            End case
          End if
        : ($fieldName_t=$fieldName2_t)
          $xmltableRef_t:=DOM Find XML element($fieldsRef_at{$floop2_l};"related_field/field_ref/table_ref")
          DOM GET XML ATTRIBUTE BY NAME($xmltableRef_t;"name";$tableName_t)
          If ($tableName_t#$tableName2_t)
            $floop2_l:=2
          Else
            Case of
              : ($kind_t="source")
                $ManyToOne_l:=2
              : ($kind_t="destination")
                If ($ManyToOne_l=1)
                  DOM GET XML ATTRIBUTE BY NAME($relationsRef_at{$floop1_l};"name_Nto1";$relationName_t)
                  $isMany_b:=True
                  $floop1_l:=Size of array($relationsRef_at)
                End if
            End case
          End if
      End case
    End for

  End for
  DOM CLOSE XML($xmlRef_t)

  If(Count parameters=3)
    $3->:=$isMany_b
  End if
  $0:=$relationName_t
End if

Example of using Method
For the following structure:

Calling the following Code:

ARRAY TEXT($relName_at;5)
ARRAY BOOLEAN($isMany_ab;5)

$relName_at{1}:=GetRelName (->[Table_1]Field_4;->[Table_2]Field_C;->$isMany_ab{1})
$relName_at{2}:=GetRelName (->[Table_1]Field_3;->[Table_2]Field_B;->$isMany_ab{2})
$relName_at{3}:=GetRelName (->[Table_2]Field_C;->[Table_1]Field_4;->$isMany_ab{3})
$relName_at{4}:=GetRelName (->[Table_2]Field_B;->[Table_1]Field_3;->$isMany_ab{4})
$relName_at{5}:=GetRelName (->[Table_1]Field_4;->[Table_2]Field_B;->$isMany_ab{5})

Returns:

Commented by Charles Wirth on May 15, 2018 at 5:21 PM

One minor glitch... If both fields have the same name the method returns a blank string.