Brian Dunning's FileMaker Custom Functions

VLookup ( LookupTableAndFieldName ; MatchingValueOrList ; ReturnFieldNames )

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

  Average rating: 4.6 (27 votes) Log in to vote

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

Share on Facebook Share on Twitter

  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"

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

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"
- Any fieldname that is specified in the third parameter will ALWAYS come from the same table as the first parameter (if any table name is included, it will be ignored)
- A single fieldname from the current layout table or a related table does not have to be enclosed in quotes.
- 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?)

 

Comments

Nick   Nick, California
Nov 18, 2015
This is exactly what I'm looking for. I am just not able to get it it work.

I have two Tables (In House Jobs) & (Paper Catalog)

The paper catalog is set up like an excel sheet. I have all my different papers I offer. I consolidated the sizes and names into one field so I can pull it from a dropdown menu over into the In House Jobs. When I select the paper in a drop down list on the In House Jobs Table I want it to pull the cost per sheet from the Paper Catalog into another field on that same table. Is this possible? I was doing it with Vlookup in excel without any problems.

Thanks!
 
RoYoMi   RoYoMi, Kenosh Wi
Feb 21, 2016
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.
 
Doug Staubach   Doug Staubach, Denver, Colorado (USA)
Feb 22, 2016
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   Doug Staubach, Denver, Colorado (USA)
Feb 24, 2016
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
 
Jack   Jack, OKC
Jan 7, 2017
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.
 
Doug Staubach   Doug Staubach, Denver, Colorado (USA)
Jun 20, 2017
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
 
Alexander Klemenow   Alexander Klemenow, Berlin
Mar 24, 2018
Hi, Doug!

Your function is great, thank you!
 
Doug Staubach   Doug Staubach, Denver, CO, USA
Jun 11, 2018
HI Alexander:

Thank you for the nice comment - I'm glad that you like it!

Regards,
Doug
 

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.

Under construction. Email me your wish list for improvements.