FILEMAKER: The New Lookup Functions

From Dwayne Wright
www.dwaynewright.com
info@dwaynewright.com

As you know a lookup or a relookup, copies related data into a field when triggered by another field (commonly called a relationship match field). The lookup trigger can take place during the editing of a match field during data entry, a button action or the execution of a script step. Add to that traditional mix a pair of calculation functions that can have lookup related results. As part of a function, lookup related information can now be used in calculation fields, scripts, relationships, value lists, validation, privileges and even via third party plug-ins! The two functions are Lookup and LookupNext.

LOOKUP FUNCTION - The lookup function , with the syntax of Lookup ( sourceField; failExpression), allows you to return what a current lookup value would be without actually performing a lookup action. The function even has a parameter for what to return if a valid lookup is not available. So speaking of the parameter, let us discuss that in detail. The first parameter is the related field you want the calculation to return. This will be the field in the child table that normally has it's data copied over during a lookup action. The second parameter is what the function should return if there are no related records. If there are multiple related records, FileMaker looks for the first related record using that relationships sort order. If no sort order is specified, then the first entered value will be returned. I should also note there is little difference in using the lookup function instead of an unstored related field as a calculated result.

LOOKUPNEXT FUNCTION - The LookupNext function , with the syntax of LookupNext ( sourceField ; lower/higherFlag ), seems very strange to me. From what I've read and tested on my own, the lookupnext function is used when there isn't a related value to lookup, then you can pull the next lower or higher value using that same relationship. The lower or higher value is determined by a flag argument within the syntax of the function. This argument is looking for the word lower or the word higher without quotation marks around it. I have an example file called LookupNext_f.fp7 that allows you to experiment with the function and experimenting with it is about the only way to master it. I should mention again that this function is designed for when there isn't a matching related value. I mention this because sometimes there is a match and this changes the behavior of the LookupNext function. If there is a match, the LookupNext function returns the lookup value.

So you could say the LookupNext function works where there is no lookup data available and if there is ... it essentially turns into the Lookup function.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

====================== ADVERTISEMENT ==============================
Want to take a document from your scanner right into your database? Want the ability to have email in and out capability without using your email application. You can features like this and more! Send me an email, info@dwaynewright.com
===================================================================