Brian Dunning's FileMaker Custom Functions

MyLookup ( FullFieldName ; MatchValue ; ReturnFieldName ; MatchValueIsNumber )

A simplied sql lookup function. Return only one row or data, but can have multiple fields returned. Requires function FieldList4SQL( ) by me.

  Be the first to rate this function Log in to vote

Philip Wang   Philip Wang - Show more from this author
106th Street Wheel and Tire

Share on Facebook Share on Twitter

  Sample input:
MyLookup("Customers::LastName"; "Smith"; "FirstName"; 0)

MyLookup("Customers::LastName"; "Smith"; "FirstName¶PhoneNumber"; "")

MyLookup("Customers::LastName"; "Customers::Smith"; "Customers::FirstName¶Customers::PhoneNumber"; "")

MyLookup(("Customers::ID"; 1001 ; "FirstName"; 1) // ID field is a number, not text.

MyLookup(("Customers::ID"; 1001 ; "FirstName¶PhoneNumber"; True)
  Sample output:





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

*** This function requires my other function: FieldList4SQL( ) to work.

First, Kudos for Doug Staubach for his excellent vlookup( ) and vlookupclassic( ) function. They are very useful and work very well. The reason I make this function is to have a simplified lookup function. Also I don't like the lookup function returns "N/A" or "?" when there is a problem. I just want it to return an empty value, so my programming can be easier. Of course it might make the troubleshooting a little more difficult, but the trade off is worth it.

This look up function will return the first result it will find. No sorting or grouping. If you need sorting or grouping or others you'd better just write the whole sql query. This function is for easier and faster returning field value(s) given an ID or a matching value.

The ReturnFieldName is the trick here. It can be a simple one, or it can have a whole list of field names. If the function returns a record, the return fields will be separated by lines, in the order of the ReturnFieldName list, as the sample output shows. So it's better not return a field value which might have ¶ in it. Your program might get confused. Of course you can use your own separator to fix this kind of problem.



Log in to post comments.


Note: these functions are not guaranteed or supported by 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 15 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: