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
http://www.106sttire.com

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:
John

John¶123-456-7890

John¶123-456-7890

John

John¶123-456-7890

  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.

 

Comments

Kyle Williams   Kyle Williams, Augusto Digital
Oct 27, 2020
FYI, I have created an "IsNumeric" function that could be useful in getting rid of the "MatchValueIsNumber" parameter: https://www.briandunning.com/cf/2299
 
Kyle Williams   Kyle Williams, Augusto Digital
Oct 27, 2020
An additional thought.. the "ReturnFieldName" should be referenced indirectly in case that field name ever changes using: GetFieldName( Customers::FirstName )

This could help to refine the function so that, similar to the first parameter, we are referencing the field and the field name could be extracted within the function so that we don't have to think about the GetFieldName function when we are using this function..

I like the idea of this function, seems very useful.
 
Kyle Williams   Kyle Williams, Augusto Digital
Oct 27, 2020
An additional thought.. the "ReturnFieldName" should be referenced indirectly in case that field name ever changes using: GetFieldName( Customers::FirstName )

This could help to refine the function so that, similar to the first parameter, we are referencing the field and the field name could be extracted within the function so that we don't have to think about the GetFieldName function when we are using this function..

I like the idea of this function, seems very useful.

Was just noticing that the first parameter is asking for the full name in quotes as text...

This function can demonstrate how to use a full field reference (not just the name in quotes) and extract the field name from it within the function: https://www.briandunning.com/cf/2354
 
Philip Wang   Philip Wang, 106th Street Wheel and Tire
Oct 27, 2020
Mr. Williams, first thank you for posting your recommendations and thoughts about my function!
Now the explanation of why I did the function this way.
When I was writing scripts to get SQL results, I was not really concerned about use GetFieldName() or not.

In some scripts I like the flexibility to use simple field name directly, like "FirstName", "LastName", because I know it will not be changed. So in those cases I just put "FirstName"... etc in the parameter, and make the call easy to understand.

In some scripts I will use "FirstName" in different tables, the only thing in common is the field name. Like in Customer table and Supplier table, they both share the same field name, but different table. I just don't want to bother with putting the whole name in it and extract the field name later. It doesn't look right in the script.

But of course in other scripts I have to use GetFieldName() to make sure it will work even after design change.

So I don't want this function alone to determine the actual fieldname it will be. The programmer will decide how it's implemented. If you are concerned about design change, use GetFieldName(). If you want the flexibility of handling multiple tables with one function, use the field name directly. It's all up to you.

Now it's about IsNumeric. I encountered a lot of cases which the id is text but appears to a number. Like "1001", it can be text, and it can be a number as well. However, for SQL it needs to be specific, or there will be errors, because "1001" is indeed not equals to 1001. So I had to include the last parameter. It's not an elegant way, but it works.
To improve it, maybe I can check the field of "FullFieldName" a number or text, but why increase the com
 

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: