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 - Show more from this author
106th Street Wheel and Tire
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)
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.
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.