@SqlUpdateBE ( _database ; setFldRefOrName ; _setValue ; whereFldRefOrName ; _whereValue )

Modifies matching records in the FM database by executing an SQL UPDATE statement with the BaseElements plugin.

Average rating: 5.0 (1 vote) Log in to vote

Alex Zueiv - Show more from this author
Tashkent
https://github.com/zueiv

Sample input:

@SqlUpdateBE ( "" ; Table::FieldToSet ; "value to set" ; Table::FieldToMatch ; "matching value" )

Sample output:

none or ? if failed

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

/**
* @SqlUpdateBE ( _database ; setFldRefOrName ; _setValue ; whereFldRefOrName ; _whereValue )
*
* Modifies matching records in the FM database by executing
* an SQL UPDATE statement with the BaseElements plugin.
*
* Supports all field types except Containers.
* Returns none or a question mark (?) if failed.
* 
* Both fields must be from the same table. Parameters with
* an underline (_) can be empty. Specify fully qualified field
* names as text constants when modifying another _database.
*
* Usage examples:
*
*	@SqlUpdateBE ( "" ; Table::Field ; "foo" ; Table::PK ; 1 )
*	@SqlUpdateBE ( "AnotherFile" ; "Table::Field" ; "bar" ; "Table::Field" ; "foo" )
*
* Test script:
*
*	Set Variable [ $x ;
*		Let ( [
*			$a = GetFieldName ( Table::FieldA ) ;
*			$b = GetFieldName ( Table::FieldB )
*		] ; "" )
*	]
*
*	# first, clear both fields
*	Set Variable [ $x ; @SqlUpdateBE ( "" ; $a ; "" ; $b ; GetField ( $b ) ) ]
*	Set Variable [ $x ; @SqlUpdateBE ( "" ; $b ; "" ; $a ; "" ) ]
*
*	# then, init fields accordingly to their types
*	Set Variable [ $x ; @SqlUpdateBE ( "" ; $a ; GetField ( $a ) + 1 ; $b ; "" ) ]
*	Set Variable [ $x ; @SqlUpdateBE ( "" ; $b ; GetField ( $b ) + 1 ; $a ; GetField ( $a ) ) ]
*
* WARNING: This CF modifies ALL matching records in the DB!
*
* Created 2020-07-26 by Alex Zueiv
* https://www.briandunning.com/cf/2406
**/

Let ( [

// isolate local variables
d = $d ; n = $n ; v = $v ;

$d = _database ;

// sql convertor function
fx = "let(
	t = middlewords ( fieldtype ( $d ; $n ) ; 2 ; 1 ) ;
	case (
		t = \"text\" ; \"'\" & substitute ( $v ; \"'\" ; \"''\" ) & \"'\" ;
		t = \"number\" ; substitute ( getasnumber ( $v ) ; \",\" ; \".\" ) ;
		t = \"date\" or patterncount ( t ; \"time\" ) ;
			upper ( t ) & \"'\"
			& if ( t = \"date\" or t = \"timestamp\" ;
				right ( \"0000\" & year ( $v ) ; 4 ) & \"-\"
				& right ( \"00\" & month ( $v ) ; 2 ) & \"-\"
				& right ( \"00\" & day ( $v ) ; 2 )
			)
			& if ( t = \"timestamp\" ; \" \" )
			& if ( t = \"time\" or t = \"timestamp\" ;
				hour ( $v ) & \":\" & minute ( $v ) & \":\" & seconds ( $v )
			)
			& \"'\"
	)
)" ;

// convert the value to set
$v = _setValue ;
x = If ( EvaluationError ( Let ( $n = GetFieldName ( setFldRefOrName ) ; "" ) ) ;
	Let ( $n = setFldRefOrName ; "" )
) ;
SetVAL = If ( _setValue ≠ "" ; Evaluate ( fx ) ) ;
SetVAL = If ( SetVAL = "" ; "NULL" ; SetVAL ) ;
SetQF = Quote ( GetValue ( Substitute ( $n ; "::" ; ¶ ) ; 2 ) ) ;

// convert the matching value
$v = _whereValue ;
x = If ( EvaluationError ( Let ( $n = GetFieldName ( whereFldRefOrName ) ; "" ) ) ;
	Let ( $n = whereFldRefOrName ; "" )
) ;
WhereVAL = If ( _whereValue ≠ "" ; Evaluate ( fx ) ) ;
WhereVAL = If ( WhereVAL = "" ; " IS NULL" ; "=" & WhereVAL ) ;
WhereQTF = Substitute ( Quote ( $n ) ; "::" ; "\"¶\"" ) ;

// build the query
QRY = "UPDATE "& GetValue ( WhereQTF ; 1 ) &"
	SET "& SetQF &"="& SetVAL &"
	WHERE "& GetValue ( WhereQTF ; 2 ) & WhereVAL ;

// restore isolated variables
$d = d ; $n = n ; $v = v

] ;

// call the function BE_FileMakerSQL() of the BaseElements plugin
BE_FileMakerSQL ( QRY ; "" ; "" ; _database )

)

Requires the BaseElements plugin.
Supports all field types except Container.
Allows to modify tables in another file.

WARNING: This CF modifies ALL matching records in the DB!

Comments

		Philippe Auguste Louis, MWH Suisse SA Jul 26, 2020
Great CF, thanks Alex

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

@SqlUpdateBE ( _database ; setFldRefOrName ; _setValue ; whereFldRefOrName ; _whereValue )

Comments

Support this website.

Support this website.