Software Venture Consulting

FileMaker Pro downloads & Resources
FileMaker Custom Functions
FileMaker Web Viewer Examples
FileMaker Pro & Lasso Consulting
Training
FileMaker Books
FileMaker Articles
FileMaker Error Reference

Free Web Tools
Free FileMaker Tools

Personal Pages
Videos
Adventures
Links

Shopping Cart
Shopping Cart

Search:

Free Newsletter
Signup


Contact


Privacy Policy



FileMaker is a registered trademark of FileMaker, Inc. in the U.S. and other countries.

 

 FileMaker Pro Custom Functions

List  |  Show Random  |  Upload  |  Add This to Your Site

VLookup ( LookupTableAndFieldName ; MatchingValueOrList ; ReturnFieldNames )

Rate this function:  

RatingRatingRatingRatingRating
  Average rating: 4.6  (27 votes)
  Discuss this Custom Function

Doug Staubach, FileMaker Poweruser
https://www.linkedin.com/in/dougstaubach

Works similar to the Excel VLookup function (but with some nice optional features): Searches in a specific field for a matching lookup value, and returns the value from a different field in the same record. -- This version of the function is case-sensitive, and does not allow 'nearby' matches, but it can handle multiple matching values, and it can return multiple fields. If no match is found, it returns an empty string ''. Can pull data from any table (even from non-related tables). - Last updated June 20, 2017

Sample Input:
Example1: VLookup ( TableInventory::ItemNumber ; "101¶620" ; TableInventory::Description )

Example2: VLookup ( "TableInventory::Description" ; "Lamp" ; "ItemNumber" )

Example3: VLookup ( "TableInventory::Description" ; Orders::ItemNumber ; "Quantity" )

Example4: VLookup ( TableInventory::ItemNumber ; List ( ProductsPurchased ) ; Description )

Example5: VLookup ( TableInventory::ItemNumber ; "101" ; "Description;Size;Cost" )
Sample Output:
Example1: "Lamp¶Television"

Example2: "101"

Example3: "19"

Example4: "Cigar¶Rum¶Recliner"

Example5: "Lamp;Large;25.99"


 Then copy & paste into FileMaker Advanced's Edit Custom Function window.

Click here to copy To Clip Manager if you have myFMbutler's Clip Manager installed

Description:

DESCRIPTION:

Works similar to the Excel VLookup function (but with some nice optional features): Searches in a specific field for a matching lookup value, and returns the value from a different field in the same record. -- This version of the function is case-sensitive, and does not allow 'nearby' matches, but it can handle multiple matching values, and it can return multiple fields. -- If no match is found, it returns an empty list ''. Can pull data from any table (even from non-related tables).

If you are looking for a funtion that works EXACTLY like Excel does (can find close matches, is non-case sensitive, and returns only one value), check out my other function "VLookupClassic"

NOTES:

Like most other ExecuteSQL-based queries, this function doesn't need (and it doesn't care) if a relationship exists between the current layout and the table that you are searching. If a relationship does exist, this function will look at ALL of the data in the target table, not just the "matching" relational data or filtered data that would normally appear within a data portal. In essence, this is a great tool for looking up data in a calculated field, but it will ignore any sorting and filtering that you might have enabled through native FileMaker functions, so be aware of that.

If a relationship does NOT exist between the active layout table and the vlookup table (parameters 1 and 3), then the tablename and fieldnames must both be enclosed in quotes like this ... "table::field" and "field"

If you plan on using a fieldname on the current layout for parameter 2, it is suggested that you do NOT enclose the fieldname in quotes, because FileMaker will "trigger" a recalculation when the value in that field changes.

Although it relies heavily on FileMaker's built-in ExecuteSQL capabilities, this function does not require knowledge of SQL syntax, and it bypasses the strict naming rules that ExecuteSQL normally requires (all field and table names are automatically "escaped" inside this function, so they won't cause any errors, and search values are automatically converted as necessary to avoid "field type mismatch" errors).

Tested with FileMaker Pro v14 and v13 (this might also work with FileMaker Pro v12 -- main requirement is the ExecuteSQL function)

This function uses RECURSION to evaluate each item in the second parameter as a separate query (recursion is cool, but if you have a massively-large list of Matching Values, you could run out of memory).

INPUTS:
(1) The first parameter (LookupTableAndFieldName) must include both a tablename, and a fieldname, formatted like so (Table::Field).
- A single fieldname from the current layout table or a related table does not have to be enclosed in quotes
- If the fieldname is from an urelated table, you must enclose the value in quotes like this ... "table1::field1"
- Multiple values are not allowed for this parameter (only one lookup table and one lookup field)
(2) The second parameter (MatchingValueOrList) can be static text enclosed in quotes like "Apple" or a list of values, like "Apple¶Banana¶Pear", or the name of a field
- If you want to provide a fieldname for the second parameter, and the field is from the current layout table or a related table, then it does not have to be enclosed in quotes (recommendation is to not use quotes)
- If you want to provide a fieldname for the second parameter, and the field is NOT from the current layout table or a related table, then it must be enlosed in quotes like this "table::field"
(3) The third parameter (ReturnFieldNames) can be a single fieldname like "Price" or multiple fieldnames like "Price;Quantity;Description" or even "Price¶Quantity¶Description"
- A single fieldname from the current layout table or a related table does not have to be enclosed in quotes (you can just use the picklist in FileMaker to choose tablename and fieldnames)
- If the return fieldname is from an unrelated table (or if you are specifying multiple field names), you must enclose the entire value in quotes like this ... "table1::field1" or "field1;field2;field3"

OUTPUTS:
(Note: This changed in v1.3) - Always returns a string value or an empty string. If the query finds multiple values for the same field, each value will be separated by the ¶ character (much like a FileMaker list). If multiple fields were requested, each field's contents will be separated by ";" characters. If no match was found, an empty string will be returned. -- Starting with version 1.3, there are no longer any trailing ¶ characters.

EXAMPLES:
Assume that the current layout table is TableInventory, and we have an unrelated table called TableCatalog
Example1: VLookup ( TableInventory::ItemNumber ; "101¶620¶" ; Description ) ... look up two item numbers from the local or related table TableInventory and returns two descriptions ... "Lamp¶Television"
Example2: VLookup ( "TableCatalog::Description" ; "Lamp" ; "TableCatalog:ItemID" ) ... search UNRELATED TableCatalog for description=Lamp (case-sensitive) and return ItemID ... "101"
Example3: VLookup ( "TableCatalog::Description" ; "LaMp" ; "TableCatalog:ItemID" ) ... search same TableCatalog for description=LaMp (case-sensitive = no match), so return empty string ... ""
Example4: VLookup ( TableInventory::ItemNumber ; List ( TableCatalog::ItemID ) ; TableInventory::Description ) ... for each item in the dynamic list, returns a description ... "Cigar¶Rum¶Recliner"
Example5: VLookup ( TableInventory::ItemNumber ; "101" ; "Description;Size;Cost" ) ... looks for matching item number, and returns three fields (separated by ";" marks) ... "Lamp;Large;25.99"

New in v1.2:
(1) Fixed an issue that became apparent with the upgrade to FileMaker v14 (no way to add unrelated tables to a function) - modified code to accept unrelated table and fields with quotes around them.
(2) The user can now specify multiple fields for the return values. This caused me to rename the third parameter from "ReturnFieldName" to "ReturnFieldNames" -- Multiple field values will be separated by ";" characters
(3) In this version, if the ExecuteSQL statement results in an error, an error message will be returned (instead of an empty string). The error will show the SQL query that failed (this can be very useful for troubleshooting).

New in v1.3
Trailing carriage returns ¶ have been removed.

(Let me know if you like it?)

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

This is my Custom Function and I want to edit it

Discuss:

5 most recent comments | Show all 6 comments

This is pretty cool function. There is a problem with that it always appends a paragraph character to the end of the string.

To correct for this I modified the lines in the function as follows:

Was:
& "¶" & VLookup ( LookupTableNameAndFieldName ; RightValues ( MatchingValueOrList ; ValueCount ( MatchingValueOrList ) - 1) ; ReturnFieldName ) // recursion (move to next item and go again)

Corrected:
& If (
RightValues ( MatchingValueOrList ; ValueCount ( MatchingValueOrList ) - 1) ≠ ""
; "¶" & funVLookup (
LookupTableNameAndFieldName
; RightValues ( MatchingValueOrList ; ValueCount ( MatchingValueOrList ) - 1)
; ReturnFieldName
)
; ""
) // recursion (move to next item and go again)


This forces the function to test if recursion is necessary. If it is necessary then it also includes the paragraph mark.

RoYoMi, Kenosh Wi
February 21, 2016 6:02pm

Hi Nick:

Yes, this formula will work. Your formula should be similar to the following:

VLookup ( Paper_Catalog:Sizes_And_Names ; In_House_Jobs::Paper_Selected ; Paper_Catalog::Cost_Per_Sheet )

Note that table names are not needed for the second and third parameter, so the following formula would work just as well, and is a bit shorter:

VLookup ( Paper_Catalog:Sizes_And_Names ; Paper_Selected ; Cost_Per_Sheet )

I included the table names in the first example, because you are working with two different tables, and I though it might be useful to illustrate which table contains which fields.

Let me know if you have any other issues.

Thanks,
Doug

Doug Staubach, Denver, Colorado (USA)
February 22, 2016 2:29pm

Hi RoYoMi:

Thank you! - I appreciate your comments and suggestions.

Your suggested fix to remove the trailing "¶" character might be useful for some people, but I'm not going to include it in the main code of the function (see below for an explanation).

I purposely left the trailing "¶" characters as part of this function, to make it behave in the same way that all of FileMaker's built-in valuelist functions work (example: LeftValues, MiddleValues, RightValues, and FilterValues all do this). -- FileMaker says this is good for combining lists. I don't have a real preference, but since FileMaker does it that way, then I will also.

Note: For those people who want to clean up the final result, you can always put this function inside of another function that is specifically designed to remove the last "¶" character from any text (there are several functions on this site that do this, including Super_Trim, Trim_CR, and TrimReturns). -- For example: Trim_CR ( VLookup ( LookupTableAndFieldName ; MatchingValueOrList ; ReturnFieldName ) )

Thanks,
Doug

Doug Staubach, Denver, Colorado (USA)
February 24, 2016 4:24pm

I love this formula and use it throughout my solution, but I noticed it does not appear to return any results when executed on an Android device via WebDirect. On all other platforms, WebDirect uses this function just fine.

Your VLookupClassic function however, works as intended on Android devices. I cannot determine the difference but would like to keep using your this function, even though, in terms of features, both functions would work just fine.

Jack, OKC
January 07, 2017 2:04pm

Hello everyone:

@Nick - Perhaps Android can't handle recursive functions? - Try with the last version 1.3 and see if it makes a difference. - Other than that, I'm not sure that I can help (I'm an iPhone user myself).

@RoYoMi - I use this function quite a bit in my own work, and I started to grow weary of trimming the trailing carriage return ("¶" character), so I decided to remove it from version 1.3 - thanks for your patience.

@Everyone - Let me know if this function was useful to you (I'd love to hear your stories and feedback!)

Thanks,
Doug

Doug Staubach, Denver, Colorado (USA)
June 20, 2017 7:06pm

Make a comment about this Custom Function (please try to keep it brief & to the point). Anyone can post:

Your Name:
City/Location:
Comment:
characters left. If you paste in more than 1500 characters, it will be truncated. Discuss the function - advertisements and other useless posts will be deleted.
Answer 7 + 1 =
Search for Custom Functions:

Custom Functions Widget
Download the Custom Function Dashboard Widget for OS X
Keep all the latest Custom Functions right at your fingertips!

Newest Custom Functions:

1. decFromHex ( hexValue )
  (Mon, Oct 16, 12:34pm)
2. MonthYearList ( startmonth ; startyear ; numbermonth ; short )
  (Mon, Oct 16, 3:38am)
3. ErrorDescription ( errorNumber )
  (Wed, Sep 27, 2:51am)
4. decodeEntities (text)
  (Wed, Sep 27, 1:22am)
5. HexidecimalToNumber ( HexidecimalValue ; counter )
  (Mon, Sep 25, 12:39pm)
6. FixedFieldConverter ( Fieldname ; f1 ; f2 ; f3 ; f4 ; f5 ; f6 ; f7 ; f8 ; f9 ; f10 ; f11 ; f12 ; f13 ; f14 ; f15 ; f16 ; f17 ; f18 ; f1
  (Fri, Sep 15, 12:34pm)
7. ShannonEntropy ( text )
  (Thu, Sep 07, 5:59am)
8. LetterCount ( text ; summary )
  (Thu, Sep 07, 5:53am)

RSS Feed of Custom Functions