Tech Tip: Utility Method to Get Related Many Fields

PRODUCT: 4D | VERSION: 14.3 | PLATFORM: Mac & Win

Published On: June 4, 2015

Below is a method to obtain an array of pointers of the related many fields of a selected field.

// Method: GetRelatedMany
// Parameters:
// $1 - Pointer the the One Field
// $2 - Pointer to the Array Pointer which will contain
// pointers of the related many fields
//-----------------------------------------------------------------------

C_POINTER($1;$field_p)
C_POINTER($2)
ARRAY POINTER($result_ap;0)

C_LONGINT($tableID_l;$fieldID_l;$count_l)
ARRAY LONGINT($tableID_al;0)
ARRAY LONGINT($fieldID_al;0)

If(Count parameters=2)

  $field_p:=$1
  $fieldID_l:=Field($field_p)
  $tableID_l:=Table($field_p)

  Begin SQL
    SELECT UCC.TABLE_ID, UCC.COLUMN_ID, UCC.CONSTRAINT_NAME
    FROM _USER_CONS_COLUMNS AS UCC, _USER_CONSTRAINTS AS UC
    WHERE UC.CONSTRAINT_TYPE = '4DR'
    AND UC.RELATED_TABLE_ID = :$tableID_l
    AND UCC.CONSTRAINT_ID=UC.CONSTRAINT_ID
    AND UCC.RELATED_COLUMN_ID=:$fieldID_l
    INTO :$tableID_al,:$fieldID_al;
  End SQL

  For ($count_l;1;Size of array($fieldID_al))
    APPEND TO ARRAY($result_ap;Field($tableID_al{$count_l};$fieldID_al{$count_l}))
  End for

  COPY ARRAY($result_ap;$2->)
End if

EXAMPLE
Using the method on the one table in the following table structure:

Example Code:

C_POINTER($field_p)
ARRAY POINTER($result_ap;0)

$field_p:=->[SampleOne]ID
GetRelatedMany ($field_p;->$result_ap)

TRACE

$result_ap will contain the pointers to the related many fields: