sql.match ( )

Function stats

Average user rating
37
329
9999
Support
FileMaker 10.0 +
Date posted
11 April 2012
Last updated
04 September 2013
Version
Recursive function
No

Author Info
 Fabrice

74 functions

Average Rating 4.4

author_avatar



 

Function overview

Prototype

sql.match  ( _requestedFieldName;   _matchFieldName;   _match )


Parameters

_requestedFieldName  the full name of the field you want to get the value from


_matchFieldName  the full field name of the field that must match the _match parameter


_match  a match criteria : 324 or


Description

Tags:  Sql   dev  

makes writing a _VERY_ simple and standard sql query easier and naming independent.


Sample :
sql.match( GetFieldName ( JAM::FLAVOR ) ; GetFieldName ( JAM::ID ) ; 348 ) => "Apricot"

sql.match( GetFieldName ( JAM::ID ) ; GetFieldName ( JAM::FLAVOR ) ; "Apricot" ) => "348¶672" => OMG! I have a duplicate Apricot jam!

Examples

Sample input

sql.match( GetFieldName ( JAM::FLAVOR ) ; GetFieldName ( JAM::ID ) ; 348 )


Sample output

"Apricot"

 

Function code

/* sql.match ( _requestedFieldName ; _matchFieldName ; _match )

by Fabrice Nordmann, 1-more-thing

http://www.1-more-thing.com
Twitter : 1morethingtweet

makes writing a _VERY_ simple and standard sql query easier and naming independent.

Sample :

sql.match( JAM::FLAVOR ; JAM::ID ; 348 ) => "Apricot"
sql.match( GetFieldName ( JAM::ID ) ; GetFieldName ( JAM::FLAVOR ) ; "Apricot" ) => "348¶672" => OMG! I have a duplicate Apricot jam!
GetFieldName is only required if used in database definition to avoid the 'unrelated' alert. Not needed in a script.


FileMaker 12 required.

v2 - Sept 2013
    - GetFieldName not mandatory (except in database definition)
    - Better job with data types
    - Case insensitive
    - Retrives null (matchField = "")
v1 - Apr 2012


*/

Let ([
    _requestedFieldName = Case ( Position ( _requestedField ; "::" ; 1 ; 1 ) ; _requestedField ; GetFieldName ( _requestedField )) ;
    _matchFieldName = Case ( Position ( _matchField ; "::" ; 1 ; 1 ) ; _matchField ; GetFieldName ( _matchField )) ;
    _match = _match ;
    _rq = Substitute ( _requestedFieldName ; "::" ; ¶ ) ;
    _rqt = GetValue ( _rq ; 1 ) ;
    _rqf = GetValue ( _rq ; 2 ) ;
    _qf = GetValue ( Substitute ( _matchFieldName ; "::" ; ¶ ) ; 2 ) ;
    _isText = GetAsBoolean ( Position ( FieldType ( Get ( FileName ) ; _matchFieldName ) ; "Text" ; 0 ; 1 )) ;
    
    $sql.match.query = Case ( IsEmpty ( _match ) ;
        "SELECT " & _rqf & " FROM \"" & _rqt & "\" WHERE " & _qf & " IS NULL" ;
        "SELECT " & _rqf & " FROM \"" & _rqt & "\" WHERE " & Case ( _isText ; "UPPER(" & _qf & ")" ; _qf )& "=?"
    );
    _result = ExecuteSQL ( $sql.match.query ; "|" ; "¶" ; Case ( _isText ; Upper ( _match ) ; _match ))
];

_result

)

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

    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/329

    Prototype: sql.match( _requestedFieldName; _matchFieldName; _match )
    Function Author: Fabrice (http://www.fmfunctions.com/mid/37)
    Last updated: 04 September 2013
    Version: 2.0

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

 

 

 

 

 

 

 

Top Tags

Text Parsing  (33)
List  (31)
Date  (28)
XML  (26)
Format  (23)
Sql  (22)
Dev  (20)
Debug  (17)
Interface  (15)
Layout  (15)
Text  (14)
Variables  (12)
Layout Objects  (11)
Filter  (11)
Design  (10)
Array  (7)