SqlSelectWhere ( _selectField ; _whereField ; _operator ; _whereValue )

sql select where

  Be the first to rate this function Log in to vote

Arend van Asselt - Show more from this author Easy to Work http://www.easytowork.nl

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

***Generic SQL select where function***

SqlSelectWhere ( _selectField ; _whereFIeld ; _operator ; _whereValue )

To use it in a calculation field and get data from a foreign table, use getfieldname ( table::field ) at _selectField

To get multiple fields in the result use getfieldname ( _selectField1 ) &","& getfieldname ( _selectField2 ) &","& …….

To use an extensive statement leave the _whereField and the _whereValue empty and write your
own statement. To check the statement put "@" in _whereValue


Example in a Script:
SqlSelectWhere ( project::projectNumber ; project::startDate ; ">" ; '01-01-2020' )
SqlSelectWhere ( project::projectNumber ; project::startDate ; ">" ; global::date )


Example in a calculation field:
SqlSelectWhere ( getfieldname ( project::projectNumber ) ; project::startDate ; ">" ; '01-01-2020' )
SqlSelectWhere ( getfieldname ( project::projectNumber ) ; project::startDate ; ">" ; global::date )

Example multiple fields (for both scripts an calc fields):
SqlSelectWhere ( getfieldname ( project::projectNumber ) &","& getfieldname ( project::projectName ) ; project::startDate ; ">" ; global::date )

Example of an extensive sql statement:
SqlSelectWhere ( GetFieldName ( Content::text ) ; "" ; 'SqlField ( Content::date ) & " < '07-06-2020' or " & 'SqlField ( Content::date ) & " > '11-07-2020' " ; "" )

Example of showing the sql statement:
SqlSelectWhere ( GetFieldName ( Content::text ) ; "" ; 'SqlField ( Content::date ) & " < '07-06-2020' or " & 'SqlField ( Content::date ) & " > '11-07-2020' " ; "@" )


To create a robust extensive sql statement you have to use an extra CF ( 'SqlField with parameter _field ) to get the robust fieldname:

Let ( [
¬isFieldName = GetFieldName ( _field ) ≠ "?" ;
¬fieldName = If ( ¬isFieldName ; GetValue ( Substitute ( GetFieldName ( _field ) ; "::" ; ¶ ) ; 2 ) ; _field ) ;
¬result = Quote ( ¬fieldName )
] ;
¬result
)

Comments

Log in to post comments.

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

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 20 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: