Brian Dunning's FileMaker Custom Functions

sql.whereInList.asList ( fqFieldToReturn ; fqFieldToMatch ; listOfValuesToMatch )

Syntactic sugar for performing a basic SQL query of the structure `SELECT <> FROM <> WHERE <> IN ( listOfvaluesToMatch>> )`.

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

Cristos Lianides-Chin   Cristos Lianides-Chin
Anchor-Buoy Software, LLC
http://www.anchorbuoysoftware.com

Share on Facebook Share on Twitter

  Sample input:
sql.whereInList.asList ( Customers::NameFull ; Customers::idCustomer ; LIST ( "CUST001" ; "CUST002" ) )
  Sample output:
John Doe
Jane Doe

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

# sql.whereInList.asList ( fqFieldToReturn ; fqFieldToMatch ; listOfValuesToMatch )
- Syntactic sugar for performing a basic SQL query of the structure `SELECT <> FROM <> WHERE <> IN ( listOfvaluesToMatch>> )`.
- Returns result as a CR-delimited list.
- **WARNING:** Use of carriage returns in values of `fqFieldToReturn` may cause unexpected behavior when parsing the resulting list. Consider using `sql.whereInList()` instead.
- __NOTE:__ This version only works for text and number fields, and may not return the expected result for Date, Timestamp, or other field types.


## PARAMETERS
### fqFieldToReturn ( field reference )
- A FileMaker reference to the field whose value you want returned from the query.
### fqFieldToMatch ( field reference )
- A FileMaker reference to the field whose value should match one of the values in `listOfValuesToMatch`.
### listOfValuesToMatch ( text|number )
- CR-delimited list of the value(s) to look for in fqFieldToMatch


## USAGE
```
sql.whereInList.asList ( Customers::NameFull ; Customers::idCustomer ; $idlistCustomer )
```
- returns a list of the values from `NameFull` for the any Customer records whose `idCustomer` is found in the list `$idlistCustomer` via ExecuteSQL


## REQUIRES
- sql.col.delimiter CF
- sql.getField custom function
- sql.getTable CF


## CHANGELOG
### v1.0 2018-02-07
- Created by [Cristos Lianides-Chin](mailto:cristos@anchorbuoysoftware.com).

 

Comments

Lee   Lee, Woodland
Feb 11, 2018
Where are the three Custom Functions available at?
 
Lee   Lee, Woodland
Feb 11, 2018
I suspect that they are at fmfunctions.com, but that site is down.
Why not jus post them here so they are available when the other site is down, which happens often.
 

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.

Under construction. Email me your wish list for improvements.