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 http://www.106sttire.com |
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)
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, 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, 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, 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, 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 |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.