VLookup ( LookupTableAndFieldName ; MatchingValueOrList ; ReturnFieldNames )
Rate this function: Average rating: 4.5 (28 votes)
Discuss this Custom Function
Doug Staubach, FileMaker Poweruser
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
|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" )
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"
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).
(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"
(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.
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
5 most recent comments | Show all 8 comments
Make a comment about this Custom Function (please try to keep it brief & to the point). Anyone can post:
Newest Custom Functions:
||(Sat, Jun 16, 1:55pm)
||httpResponseCode ( responseHeaders )
||(Tue, Jun 12, 10:40pm)
||FindDuplicateCharacters ( string )
||(Fri, Jun 08, 5:01pm)
||FieldRepetitionLast ( field ; maxRepetition )
||(Wed, Jun 06, 6:05pm)
||CountModifiedRecords ( NameTimestampField ; StartTimestamp ; EndTimestamp )
||(Mon, May 28, 8:23am)
||JSONArrayLength ( JSONArrayStr )
||(Fri, May 25, 7:46am)
||interpolation (y1; y2; y3; x1; x2; x3; x1y1; x2y1; x1y2; x2y2; rnd)
||(Sat, May 19, 3:55am)
||@JSONFormatElements ( json )
||(Tue, May 15, 3:31am)