hierarchy ( )

Function stats

Average user rating
5.0000
37
353
9999
Support
FileMaker 10.0 +
Date posted
27 March 2013
Version
1
Recursive function
Yes

Author Info
 Fabrice

74 functions

Average Rating 4.4

author_avatar



 

Function overview

Prototype

hierarchy  ( _id.list;   _primaryKey;   _parentKey;   _direction;   _numberOfLevels )


Parameters

_id.list  a ¶ or , delimited list of item IDs


_primaryKey  the full field name of the primary key. e.g. GetFieldName ( table::id )


_parentKey  the full field name of the parent foreign key. e.g. GetFieldName ( table::id_parent )


_direction  A -> ascending (default); D -> descending


_numberOfLevels  number. To limit the number of iterations. For example set it to 2 if you want the children and the grand children but not the grand grand children. Empty means no limit


Description

Tags:  Sql   Interface  

hierarchy ( _id.list ; _primaryKey ; _parentKey ; _direction ; _numberOfLevels )

by Fabrice Nordmann, 1-more-thing
http://www.1-more-thing.com
Twitter: 1morethingtweet

v.1.0        July 2012

returns the list of 'ancestors' or 'descendents' in a recursive data structure (tree view)
requires FileMaker 12 (SQL)
recursive

Demo available here :
http://www.1-more-thing.com/Hierarchal-Portals-Using.html

Examples

Sample input

hierarchy ( "23¶35" ; getfieldname ( table::id ) ; getfieldname ( table::id_parent ) ; "ASC" ; "" )


Sample output

the list of ID of all ascendents of records with ID = 23 or 35

 

Function code

/*
hierarchy ( _id.list ; _primaryKey ; _parentKey ; _direction ; _numberOfLevels )

by Fabrice Nordmann, 1-more-thing
http://www.1-more-thing.com
Twitter: 1morethingtweet

v.1.0        July 2012

returns the list of 'ancestors' or 'descendents' in a recursive data structure (tree view)
requires FileMaker 12 (SQL)
recursive

http://www.1-more-thing.com/Hierarchal-Portals-Using.html

========= PARAMETERS ============
_id.list
    a ¶ or , delimited list of item IDs
_primaryKey
    the full field name of the primary key. e.g. GetFieldName ( table::id )
_parentKey
    the full field name of the parent foreign key. e.g. GetFieldName ( table::id_parent )
_direction
    A -> ascending (default)
    D -> descending
_numberOfLevels
    number. To limit the number of iterations. For example set it to 2 if you want the children and the grand children but not the grand grand children
    empty -> no limit

*/


Let ([

    $hierarchy.i = $hierarchy.i + 1 ;    // iterator
    $sql.query = Case ( $hierarchy.i = 1 ; "" ; $sql.query );
    _p = UniqueList ( Substitute ( _id.list ; "," ; ¶ ) ; 0) ;    // deduplicate parameter
    _p2 = FilterList ( _p ; "notEquals" ; $cf.journal ; 0 ) ;    // avoid loop in complex hierachy where an item can have several parent records (n-n or two-table hierarchy)
    $cf.journal = List ( $cf.journal ; _p2 ) ;            // journalize the processed ids
    _in = Case ( not IsEmpty ( _p2 ) ; "'" & Substitute ( _p2 ; ¶ ; "','" ) & "'" ) ; // SQL build the IN clause
    _direction = Case ( Left ( _direction ; 1 ) = "D" ; "DESC" ; "ASC" ) ; // parameters D{escending}. Default is A{scending}

    _id.parent = GetValue ( Substitute ( _parentKey ; "::" ; ¶ ) ; 2 ) ;
    _id.child = GetValue ( Substitute ( _primaryKey ; "::" ; ¶ ) ; 2 ) ;
    _table = GetValue ( Substitute ( _primaryKey ; "::" ; ¶ ) ; 1 ) ;

    _q = "SELECT " & Case ( _direction = "ASC" ; _id.parent ; _id.child ) & " FROM " & _table & " WHERE " & Case ( _direction = "ASC" ; _id.child ; _id.parent ) & " IN (" & _in & ")" ;
     _result = Case ( not IsEmpty ( _in ) ; ExecuteSQL ( _q ; "" ; "" ))
];
Let ( $sql.query = Case ( Get ( AccountPrivilegeSetName ) = "[Full Access]" ; List ( $sql.query ; _q )) ;
    Case ( not IsEmpty ( _result ) and _result <> "?" and ( not _numberOfLevels or $hierarchy.i <= _numberOfLevels );
        UniqueList ( Case ( _direction = "ASC" ; List ( hierarchy ( _result ; _primaryKey ; _parentKey ; _direction ; _numberOfLevels ) ; _result ) ; List ( _result ; hierarchy ( _result ; _primaryKey ; _parentKey ; _direction ; _numberOfLevels ))) ; 0 ) ;
        Let ([ $cf.journal = "" ; $hierarchy.i = "" ]; "" )    // last iteration, clear the mess
    )
)
)

// ===================================
/*

    This function is published on FileMaker Custom Functions
    to check for updates and provide feedback and bug reports
    please visit http://www.fmfunctions.com/fid/353

    Prototype: hierarchy( _id.list; _primaryKey; _parentKey; _direction; _numberOfLevels )
    Function Author: Fabrice (http://www.fmfunctions.com/mid/37)
    Last updated: 27 March 2013
    Version: 1

*/
// ===================================