nsahyper.blogg.se

TYP1 = MiddleWords ( FieldType ( Get ( FileName ) VAR1 ) 2 1 ) FLD2 = If ( Trim ( VAR3 ) = "*" "*" "\"" & VAR3 & "\"" ) // get the return field name(s) and format them for SQL-safe processing ("*" will return all fields) TBL1 = "\"" & VAR2 & "\"" // get the TABLE name and format it for SQL-safe processing get the lookup field name and format it for SQL-safe processing VAR4 = GetValue ( LeftValues ( MatchingValueOrList 1 ) 1 ) // lookup a single value, or list of values

VAR3 = Substitute ( VAR3 ) // allows query to include multiple return fields VAR3 = If ( Right ( VAR3 1 ) = "¶" Left ( VAR3 Length ( VAR3 ) -1 ) VAR3) // if return field has trailing ¶, remove it

VAR3 = Substitute ( VAR3 VAR2 & "::" "" ) // strip tablename from return field(s) VAR3 = If ( EvaluationError ( GetFieldName ( ReturnFieldNames ) ) ReturnFieldNames GetFieldName ( ReturnFieldNames ) ) // bypass 102 errors (unrelated table) VAR2 = If ( PatternCount ( VAR1 "::" ) > 0 GetValue ( Substitute ( VAR1 "::" "¶" ) 1 ) ) // get tablename from lookup field VAR1 = If ( EvaluationError ( GetFieldName ( LookupTableAndFieldName ) ) LookupTableAndFieldName GetFieldName ( LookupTableAndFieldName ) ) ValueCount ( MatchingValueOrList ) = 0 ""

If you are looking for a function that works EXACTLY like Excel does (can find close matches, is non-case sensitive, and returns only one value), check out my other function, named "VLookupClassic"*/ Can pull data from any table (even from non-related tables) - see notes below for instructions on how to do this. If no match is found, it returns an empty string ''.

His 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. 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. Function definition: (Copy & paste into FileMaker's Edit Custom Function window) /* Version 1.5 - Created by Doug Staubach 2014-Feb-03, Revised 2022-Jun-30.