TableOccurrencesWithCascadeDelete ( file ; tableOccurrence )

List of tables related to tableOccurrence that have a relationship with a cascade delete. Searches recursively so grandchild deletes will be included etc.

Be the first to rate this function Log in to vote

Joshua Willing - Show more from this author
Willing Apps

Sample input:

TableOccurrencesWithCascadeDelete ( "" ; "MyTable" )

Sample output:

RelatedTable1
AnotherRelatedTable
ThirdOne
More_Here

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/**
 * =====================================
 * TableOccurrencesWithCascadeDelete ( file ; tableOccurrence )
 *
 * RETURNS:
 *		List of tables related to tableOccurrence that have a relationship with a cascade delete. Searches recursively so grandchild deletes will be included etc.
 *		If the relationship contains circular references to `tableOccurrence`, `tableOccurrence` will be included in the result
 *
 * PARAMETERS:
 *		file | the file name. Set to "" if you want to use the current file
 *		tableOccurrence | the starting table occurrence name
 *
 * DEPENDENCIES: none
 *
 * HISTORY:
 *		2024-01-23	Joshua Willing
 *		2024-01-24	Joshua Willing	use a list instead of array for ~queue
 * =====================================
 */

While (
[

	// Init vars
	~file = file ;
	// put input on queue
	~queue  = tableOccurrence &¶ ;
	~visitedList = "" ;
	~res = ""
] ;
 
ValueCount ( ~queue ) > 0 ;

[

	// Get first TO in queue and remove it
	~table = GetValue ( ~queue ; 1 ) ;
	~queue = RightValues ( ~queue ; ValueCount ( ~queue ) - 1 ) ;


	// If not visited, loop over RelationInfo and get all TOs with an "Options" row that contains "Delete"
	~visited = not IsEmpty ( FilterValues ( ~visitedList ; ~table ) ) ;
	~tosWithDelete = If (
		~visited ;
		"" ;

		// else
		While (
			[
				~relationInfo = RelationInfo ( ~file ; ~table ) ;
				~i = 0 ;
				~count = ValueCount ( ~relationInfo ) ;
				~table = "" ;
				~tablesWithDelete = ""
			] ;
			~i < ~count ;
			[
				~i = ~i + 1 ;
				~val = GetValue ( ~relationInfo ; ~i ) ;
				~table = If (
					Left ( ~val ; 6 ) = "Table:" ;
					Right ( ~val ; Length ( ~val ) - 6 ) ;
					~table
				) ;
				~options = If (
					Left ( ~val ; 8 ) = "Options:" ;
					Right ( ~val ; Length ( ~val ) - 8 ) ;
					""
				) ;
				~tablesWithDelete = If (
					not IsEmpty ( ~options ) and PatternCount ( ~options ; "Delete" ) ;
					List ( ~tablesWithDelete ; ~table ) ;
					~tablesWithDelete
				) 

			] ;
			~tablesWithDelete
		)
	) ;


	// Append any TOs with delete to the result
	~res = List ( ~res ; ~tosWithDelete ) ;


	// Push tosWithDelete onto queue to recursivel check if _they_ have any children with cascade deletes
	~queue = If (
		Right ( ~queue ; 1 ) = ¶ ;
		~queue & ~tosWithDelete ;
		List ( ~queue ; ~tosWithDelete )
	) ;

	// Add the table to visitedList to avoid circular references
	~visitedList = If ( ~visited ; ~visitedList ; List ( ~visitedList ; ~table ) )

] ;

SortValues ( UniqueValues ( ~res ) )

)

List of tables related to tableOccurrence that have a relationship with a cascade delete. Searches recursively so grandchild deletes will be included etc.

If the relationship contains circular references to `tableOccurrence`, `tableOccurrence` will be included in the result

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

TableOccurrencesWithCascadeDelete ( file ; tableOccurrence )

Comments

Support this website.

Support this website.