Logical Functions
| < Day Day Up > |
| The category of functions known as the logical functions contains a strange hodgepodge of things. Chapter 8 discussed two of them: the If and Case conditional functions. The logical functions covered here include several that are new to FileMaker Pro 7. The Let functionThe Let function enables you to simplify complex calculations by declaring variables to represent sub-expressions. These variables exist only within the scope of the formula and can't be referenced in other places. As an example, this is a formula that was presented in Chapter 8 for extracting the last line of a text field: Right(myText; Length(myText) - Position(myText; ""; 1; PatternCount(myText; ""))) With the Let function, this formula could be re-written as Let ([fieldLength = Length(myText) ; returnCount = PatternCount(myText; "") ; positionOfLastReturn = Position (myText; ""; 1; returnCount) ; charactersToGrab = fieldLength - positionOfLastReturn]; Right (myText, charactersToGrab)) The Let function takes two parameters. The first is a list of variable declarations. If you want to declare multiple variables, then you need to enclose the list with square brackets and separate the individual declarations within the list with semicolons. The second parameter is some formula that you want evaluated. That formula can reference any of the variables declared in the first parameter just as it would reference any field value.
Notice in this example that the third variable declared, positionOfLastReturn , references the returnCount variable, which was the second variable declared. This capability to have subsequent variables reference previously defined ones is one of the powerful aspects of the Let function because it enables you to build up a complex formula via a series of simple ones. It is fair to observe that the Let function is never necessary; you could write every formula that uses the Let function either as a complex nested formula or by explicitly defining or setting fields to contain sub-expressions. The main benefits of using the Let function are simplicity, clarity, and ease of maintenance. For instance, one formula that returns a person's age expressed as a number of years , months, and days is the following: Year (Get (CurrentDate)) - Year(birthDate) - (DayOfYear(Get(CurrentDate)) < DayOfYear This is a fairly complex nested formula, and many sub-expressions appear multiple times. Writing and debugging this formula is difficult, even when you understand the logic on which it's based. With the Let function, the formula could be rewritten as Let ([ C = Get(CurrentDate); yC = Year (C) ; mC = Month (C) ; dC = Day (C) ; doyC = DayOfYear (C) ; B = birthDate; yB = Year (B) ; mB = Month (B) ; dB= Day (B) ; doyB = DayOfYear (b) ; num_years = (yC - yB - (doyC < doyB)) ; num_months = Mod (mC - mB - (dC <dB) ; 12) ; num_days = C - Date (mC - (dC < dB) ; dB ; yC) ] ; num_years & " years, " & num_months & " months, and " & num_days & " days") Because of the extra space we've put in the formula, it's a bit longer than the original, but it's vastly easier to comprehend. If you were a developer needing to review and understand a formula written by someone else, we're sure you'd agree that you'd prefer seeing the Let version of this rather than the first version. Besides simplicity and clarity, there are also performance benefits to using the Let function. If you have a complex sub-expression that you refer to multiple times during the course of a calculation, FileMaker Pro evaluates it anew each time it's referenced. Alternatively, a sub-expression in a variable is evaluated only once. In the example just shown, for instance, FileMaker would evaluate Get(CurrentDate) eight times in the first version. In the version that uses Let , it's evaluated only once. In many cases, the performance difference may be trivial or imperceptible. But other times, optimizing the evaluation of calculation formulas may be just the answer for increasing your solution's performance. The more you use the Let function, the more likely it is that it will become one of the " core " functions that you use. To help you become more familiar with it, we use it frequently throughout the examples in the rest of this chapter. TIP The Let function makes it really easy to debug calculation formulas. It used to be that if you wanted to make sure that a sub-expression was evaluating correctly, you'd need to create a separate field to investigate it. Using Let , you can just comment out the second parameter of the Let function and have the function return one or more of the sub-expressions directly. When you've got them working correctly, just comment out the test code and comment in the original code. The Choose FunctionThe If and Case functions are sufficiently robust and elegant for most conditional tests that you'll write. For several types of conditional tests, however, the Choose function is a more appropriate option. As with If and Case , the value returned by the Choose function is dependent on the result of some test. What makes the Choose function unique is that the test should return an integer rather than a true/false result. The syntax for Choose is as follows : Choose (test ; result if test=0 ; result if test=1 ; result if test =2 ....) A classic example of where a Choose function comes in handy is when you have categorical data stored as a number and you need to represent it as text. For instance, you might import demographic data where the ethnicity of an individual is represented by an integer from 1 to 5. The following formula might be used to represent it to users: Choose (EthnicityCode; ""; "African American"; "Asian"; "Caucasian"; "Hispanic"; "American Of course, the same result could be achieved with the following formula: Case (EthnicityCode = 1; "African American"; EthnicityCode = 2; "Asian", EthnicityCode = You should consider the Choose function in several other situations. The first is for generating random categorical data. Say your third-grade class is doing research on famous Presidents , and you want to randomly assign each student one of the six presidents you have chosen . By first generating a random number from 0 to 5, you can then use the Choose function to select a president. The formula would be Let (r = Random * 6; // Generates a random number from 0 to 5 Choose (r, "Washington", "Jefferson", "Lincoln", "Roosevelt", "Truman", "Kennedy")) Don't worry that r isn't an integer; the Choose function ignores anything but the integer portion of a number. Several FileMaker Pro functions return integer numbers from 1 to n, so these naturally work well as the test for a Choose function. Most notable are the DayofWeek function, which returns an integer from 1 to 7, and the Month function, which returns an integer from 1 to 12. As an example, you could use the Month function within a Choose to figure out within which quarter of the year a given date fell: Choose (Month(myDate)-1; "Q1"; "Q1"; "Q1"; "Q2"; "Q2"; "Q2"; "Q3"; "Q3"; "Q3"; "Q4"; "Q4"; The “1 shifts the range of the output from 1 “12 to 0 “11, which is more desirable because the Choose function is zero based. There are more compact ways of determining the calendar quarter of a date, but this version is very easy to understand and offers much flexibility. Another example of where Choose works well is when you need to combine the results of some number of Boolean tests and produce a distinct result. As an example, imagine that you have a table that contains results on Myers-Briggs personality tests. For each test given, you have scores for four pairs of personality traits (E/I, S/N, T/F, J/P). Based on which score in each pair is higher, you want to classify each participant as one of 16 personality types. Using If or Case statements, you would need a very long, complex formula to do this. With Choose , you can treat the four tests as a binary number, and then simply do a "conversion" back to base-10 to decode the results. The formula might look something like this: Choose ((8 * (E>I)) + (4 * (S>N)) + (2 * (T>F)) + (J>P); "Type 1 - INFP" ; "Type 2 - INFJ" ; "Type 3 - INTP" ; "Type 4 - INTJ" ; "Type 5 - ISFP" ; "Type 6 - ISFJ" ; "Type 7 - ISTP" ; "Type 8 - ISTJ" ; "Type 9 - ENFP" ; "Type 10 - ENFJ" ; "Type 11 - ENTP" ; "Type 12 - ENTJ" ; "Type 13 - ESFP" ; "Type 14 - ESFJ" ; "Type 15 - ESTP" ; "Type 16 - ESTJ") Each less-than comparison is evaluated as a 1 or 0 depending on whether it represents a true or false statement for the given record. By multiplying each result by successive powers of 2, you end up with an integer from 0 to 15 that represents each of the possible outcomes. (This is similar to how flipping a coin 4 times generates 16 possible outcomes .) As a final example, the Choose function can also be used anytime you need to "decode" a set of abbreviations into their expanded versions. Take, for example, a situation where survey respondents have entered SA, A, N, D, or SD as a response to indicate Strongly Agree, Agree, Neutral, Disagree, or Strongly Disagree. You could map from the abbreviation to the expanded text by using a Case function as follows: Case (ResponseAbbreviation = "SA"; "Strongly Agree"; ResponseAbbreviation = "A"; "Agree" ; ResponseAbbreviation = "N"; "Neutral" ; ResponseAbbreviation = "D"; "Disagree" ; ResponseAbbreviation = "SD"; "Strongly Disagree") You can accomplish the same mapping by using a Choose function if you treat the two sets of choices as ordered lists. You simply find the position of an item in the abbreviation list, and then find the corresponding item from the expanded text list. The resulting formula would look like this: Let ([a = "SAANDSD" ; r = "" & ResponseAbbreviation & "" ; pos = Position (a; r ; 1 ; 1) ; itemNumber = PatternCount (Left (a; pos-1); "") / 2]; Choose (itemNumber, "Strongly Agree"; "Agree"; "Neutral"; "Disagree"; "Strongly In most cases, you'll probably opt for using the Case function for simple decoding of abbreviations. Sometimes, however, the list of choices isn't something you can explicitly test against (such as with the contents of a value list) and finding one's position within the list may suffice to identify a parallel position in some other list. Having the Choose function in your toolbox may offer an elegant solution to such challenges. The GetField FunctionWhen writing calculation formulas, you use field names to refer abstractly to the contents of particular fields in the current record. That is, the formula for a FullName calculation might be FirstName & " " & LastName . FirstName and LastName are abstractions; they represent data contained in particular fields. Imagine, however, that instead of knowing in advance what fields to refer to in the FullName calculation, you wanted to let users pick any fields they wanted to. So you set up two fields, which we'll call UserChoice1 and UserChoice2 . How can you rewrite the FullName calculation so that it's not hard-coded to use FirstName and LastName , but rather uses the fields that users type in the two UserChoice fields? The answer, of course, is the GetField function. GetField enables you to add another layer of abstraction to your calculation formulas. Instead of hard-coding field names in a formula, GetField allows you to place into a field the name of the field you're interested in accessing. That sounds much more complicated than it actually is. Using GetField , we might rewrite our FullName formula as follows: GetField (UserChoice1) & " " & GetField (UserChoice2) The GetField function takes just one parameter. That parameter can either be a literal text string or it can be a field name. Having it be a literal text string, though possible, is not particularly useful. The function GetField("FirstName") would certainly return the contents of the FirstName field, but you can achieve the same thing simply by using FirstName by itself. It's only when the parameter of the GetField function is a field or formula that it becomes interesting. In that case, the function returns the contents of the field referred to by the parameter. There are many potential uses of GetField in a solution. Imagine, for instance, that you have a Contact table with fields called First Name, Nickname, and Last Name (among others). Sometimes contacts prefer to have their nickname appear on badges and in correspondence, and sometimes the first name is desired. To deal with this, you could create a new text field called Preferred Name and format that field as a radio button containing First Name and Nickname as the choices. When doing data entry, a user could simply check off which name should be used for correspondence. When it comes time to make a Full Name calculation field, one of your options would be the following: Case (Preferred Name = "First Name"; First Name; Preferred Name = "Nickname"; Nickname) & " " & Last Name Another option, far more elegant and extensible, would be the following: GetField (PreferredName) & " " & Last Name When there are only two choices, the Case function certainly isn't cumbersome. But if there were dozens or hundreds of fields to choose from, then GetField clearly has an advantage. Building a Customizable List ReportOne of the common uses of GetField is for building user-customizable list reports . It's really nothing more than an extension of the technique shown in the previous example, but it's still worth looking at it in depth. The idea is to have several global text fields where a user can select from a pop-up list of field names. The global text fields can be defined in any table that you want. Remember, in calculation formulas, you can refer to a globally-stored field from any table, even without creating a relationship to that table. The following example uses two tables: SalesPeople and Globals. The SalesPeople table has the following data fields:
The Globals table has six global text fields named gCol1 through gCol6 . With these in place, you can now create six display fields in the SalesPeople table (named ColDisplay1 through ColDisplay6) that will contain the contents of the field referred to in one of the global fields. For instance, ColDisplay1 has the following formula: GetField (Globals::gCol1) ColDisplay2 through 6 will have similar definitions. The next step is to create a value list that contains all the fields that you want the user to be able to select. The list used in this example is shown in Figure 14.1. Keep in mind that because the selection is used as part of a GetField function, the field names must appear exactly as they have been defined. Figure 14.1. Define a value list containing a list of the fields from which you want to allow a user to select for the custom report. The final task is to create a layout where users can select and see the columns for their custom list report. You might want to set up one layout where the user selects the fields and another for displaying the results, but we think it's better to take advantage of the fact that in FileMaker Pro 7, fields in header parts of list layouts can be edited. The column headers of your report can simply be pop-up lists. Figure 14.2 shows how you would set up your layout this way. Figure 14.2. The layout for your customizable list report can be quite simple. Here, the selection fields act also as field headers. Back in Browse mode, users can now click into a column heading and select what data they want to appear there. This one layout can thus serve a wide variety of needs. Figures 14.3 and 14.4 show two examples of the types of reports that can be made. Figure 14.3. A user can customize the contents of a report simply by selecting fields from pop-up lists in the header. Figure 14.4. Here's another example of a how a user might configure the customizable list report. Extending the Customizable List ReportAfter you have the simple custom report working, there are many ways you can extend it to add even more value and flexibility for your users. For instance, you might add a subsummary part that's also based on a user-specified field. A single layout can thus be a subsummary based on any field the user wants. One way to implement this is to add another pop-up list in the header of your report and a button to sort and preview the subsummary report. Figure 14.5 shows what your layout would look like after adding the subsummary part and pop-up list. BreakField is a calculation in the SalesPeople table that's defined as follows: GetField (Globals::gSummarizeBy) Figure 14.5. A subsummary part based on a user-defined break field gives your custom report added power and flexibility. The Preview button performs a script that sorts by the BreakField and goes to Preview mode. Figure 14.6 shows the result of running the script when Territory has been selected as the break field. Figure 14.6. Sorting by the break field and previewing shows the results of the dynamic subsummary. The Evaluate Function
The syntax for the Evaluate function is as follows: Evaluate (expression {; [field1 ; field2 ;...]}) The expression parameter is any calculation formula that you want evaluated. The optional parameter is a list of fields whose modification triggers the reevaluation of the expression. For example, imagine that you have a text field named myFormula and another named myTrigger . You then define a new calculation field called Result , using the following formula: Evaluate (myFormula; myTrigger) Figure 14.7 shows some examples of what Result will contain for various entries in myFormula . Figure 14.7. Using the Evaluate function, you can have a calculation field evaluate a formula contained in a field. There's something quite profound going on here. Instead of having to "hard-code" calculation formulas, you can evaluate a formula that's been entered as field data. In this way, Evaluate provides an additional level of logic abstraction similar to the GetField function. In fact, if myFormula contained the name of a field, then Evaluate(myFormula) and GetField(myFormula) would return the exact same result. It might help to think of Evaluate as the big brother of GetField . Where GetField can return the value of a dynamically specified field , Evaluate can return the value of a dynamically specified formula . Uses for the Evaluate FunctionA typical use for the Evaluate function is to track modification information about a particular field or fields. A timestamp field defined to auto-enter the modification time is triggered any time any field in the record is modified. There may be times, however, when you want to know the last time that the Comments field was modified, without respect to other changes to the record. To do this, you would define a new calculation field called CommentsModTime with the following formula: Evaluate ("Get(CurrentTimestamp)" ; Comments) The quotes around the Get(CurrentTimestamp) are important, and are apt be a source of confusion. The Evaluate function expects to be fed either a quote- enclosed text string (as shown here) or a formula that yields an expression (as in the Result field earlier). For instance, if you want to modify the CommentsModTime field so that rather than just return a timestamp, it returns something like "Record last modified at: 11/28/2003 12:23:58 PM by Fred Flintstone," you would need to modify the formula to the following: Evaluate ("\"Record modified at: \" & Get (CurrentTimeStamp) & \" by \" & Get Here, because the formula you want to evaluate contains quotation marks, you must escape them by preceding them with a slash. For a formula of any complexity, this becomes difficult both to write and read. There is, fortunately, a function called Quote that eliminates all this complexity. The Quote function returns the parameter it is passed as a quote-wrapped text string, with all internal quotes properly escaped. Therefore, you could rewrite the above function more simply as: Evaluate (Quote ("Record modified at: " & Get (CurrentTimeStamp) & " by " & Get In this particular case, using the Let function further clarifies the syntax: Let ([ t = Get (CurrentTimeStamp) ; a = Get (AccountName); myExpression = Quote ("Record modified at: " & t & " by " & a) ] ; Evaluate (myExpression ; Comments)) Evaluation ErrorsYou typically find two other functions used in the vicinity of the Evaluate function: IsValidExpression and EvaluationError . IsValidExpression takes as its parameter an expression, and it returns a 1 if the expression is valid, a 0 if it isn't. An invalid expression is any expression that can't be evaluated by FileMaker Pro, whether due to syntax errors or other runtime errors. If you plan to allow users to type calculation expressions into fields, then by all means be sure to use IsValidExpression to test their input to be sure it's well formed . In fact, you probably want to include something right within your Evaluate formula: Let (valid = IsValidExpression (myFormula) ; If (not valid; "Your expression was invalid" ; Evaluate (myFormula)) The EvaluationError function is likewise used to determine whether there's some problem with evaluating an expression. However, it returns the actual error code of the problem. One thing to keep in mind, however, is that rather than testing the expression, you want to test the evaluation of the expression. So, as an error trap used in conjunction with an Evaluate function, you might have the following: Let ([result = Evaluate (myFormula) ; error = EvaluationError (result) ] ; If (error ; "Error: " & error ; result)) Customizable List Reports ReduxWe mentioned previously that Evaluate could be thought of as an extension of GetField . In an example presented in the GetField section, we showed how you could use the GetField function to create user-customizable report layouts. One of the drawbacks of that method that we didn't discuss at the time is that your field names need to be user- and display-friendly. However, there is an interesting way to get around this limitation that also happens to showcase the Evaluate function. We discuss that solution here as a final example of Evaluate . To recap the earlier example, imagine that you have six global text fields (gCol1 through gCol6) in a table called Globals. Another table, called SalesPeople has demographic and sales- related data for your salespeople. Six calculation fields in SalesPeople, called ColDisplay1 through ColDisplay6 , display the contents of the demographic or sales data fields, based on a user's selection from a pop-up list containing field names. ColDisplay1 , for instance, has the formula: GetField (Globals::gCol1) We now extend this solution in several ways. First, create a new table in the solution called FieldNames with the following text fields: FieldName and DisplayName . Figure 14.8 shows the data that might be entered in this table. Figure 14.8. The data in FieldName represents fields in the SalesPerson table; the DisplayName field shows more "user-friendly" labels that will stand in for the actual field labels. Before, we suggested using a hard-coded value list for the pop-up lists attached to the column selection fields. Now you'll want to change that value list so that it contains all the items in the DisplayName column of the FieldNames table. Doing this, of course, causes all the ColDisplay fields to malfunction. There is, for instance, no field called "Ph. Number," so GetField ("Ph. Number") will not function properly. What we want now is the GetField function not to operate on the user's entry, but rather on the FieldName that corresponds to the user's DisplayName selection. That is, when the user selects "Ph. number" in gCol1 , ColDisplay1 should display the contents of the Phone field. You can accomplish this by creating a relationship from the user's selection over to the DisplayName field. Because there are six user selection fields, there need to be six relationships. This requires that you create six occurrences of the FieldNames table. Figure 14.9 shows the Relationships Graph after you have set up the six relationships. Notice that there's also a cross-join relationship between SalesPeople and Globals. This relationship allows you to look from SalesPeople all the way over to the FieldNames table. Figure 14.9. To create six relationships from the Globals table to the FieldNames table, you need to create six occurrences of FieldNames. The final step is to alter the calculation formulas in the ColDisplay fields. Remember, instead of "getting" the field specified by the user, we now want to get the field related to the field specified by the user. At first thought, you might be tempted to redefine ColDisplay1 as: GetField (Fields1::FieldName) The problem with this is that the only way that ColDisplay1 updates is if the FieldName field changes. Changing gCol1 doesn't have any effect on it. This, finally, is where Evaluate comes in. To force ColDisplay1 to update, you can use the Evaluate function instead of GetField . The second parameter of the formula can reference gCol1 , thus triggering the re-evaluation of the expression every time gCol1 changes. The new formula for ColDisplay1 is therefore Evaluate (Fields1::FieldName ; Globals::gCol1) There is, in fact, still a slight problem with this formula. Even though the calculation is unstored, the field values don't refresh onscreen. The solution is to refer not merely to the related FieldName , but rather to use a Lookup function (which is covered in depth in the next section) to explicitly grab the contents of FieldName . The final formula, therefore, is Evaluate (Lookup (Fields1::FieldName) ; Globals::gCol1) There's one final interesting extension we will make to this technique. At this point, the Evaluate function is used simply to grab the contents of a field. It's quite possible, however, to add another field to the FieldNames table called Formula , and have the Evaluate function return the results of some formula that you define there. The formula in ColDisplay1 would simply be changed to Evaluate (Lookup (Fields1::Formula) ; Globals::gCol1) One reason you might want to do this is to be able to add some text formatting to particular fields. For instance, you might want the Sales_2002 field displayed with a leading dollar sign. Because all the ColDisplay fields yield text results, you can't do this with ordinary field formatting. Instead, in the Formula field on the Sales_2002 record, you could type the following formula: "$ " & Sales_2002 There's no reason, of course, why a formula you write can't reference multiple fields. This means that you can invent new fields for users to reference simply by adding a new record to the FieldNames table. For example, you could invent a new column called "Initials," defined as Left (FirstName; 1) & Left (LastName; 1) You could even invent a column called "Percent Increase" that calculates the percent sales increase from 2002 to 2003. The formula for that would be Round((Sales_2003 - Sales_2002) / Sales_2002 *100, 2) & " %" Figure 14.10 shows the contents of the FieldNames table. Note that for columns where you just want to retrieve the value of a field (for example, FirstName ), the field name itself is the entire formula. Figure 14.10. The expression in the Formula field is dynamically evaluated when a user selects a column in the customizable report. This technique is quite powerful. You can "cook up" new columns for the customizable report just by adding records to the FieldNames table. Figure 14.11 shows an example of a report that a user could create based on the formulas defined in FieldNames. Keep in mind, Initials, $ Increase, and Percent Increase have not been defined as fields anywhere . Figure 14.11. In the finished report, users can select from any of the "columns" defined in the FieldNames table, even those that don't explicitly exist as defined fields. The Lookup Functions
The two lookup functions operate quite similarly to their cousin, the auto-entry lookup option. In essence, a lookup is used to copy a related value into the current table. Lookups (all kinds) have three necessary components : a relationship, a trigger field, and a target field. When the trigger field is modified, then the target field is set to some related field value. It's important to understand the functional differences between the lookup functions and the auto-entry option. Although they behave similarly, they're not quite equivalent. Some of the key differences include the following:
LookupThe syntax of the Lookup function is as follows: Lookup (sourceField {; failExpression}) The sourceField is the related field whose value you want to retrieve. The optional failExpression parameter is returned if there is no related record or if the sourceField is blank for the related record. If the relationship specified matches multiple related records, the value from the first related record is returned. There are two main differences between using the Lookup function and simply referencing a related field in a formula. The first is that calculations that simply reference related fields must be unstored, but calculations that use the Lookup function to access related fields can be stored and indexed. The other difference is that changing the sourceField in the related table does not cause the Lookup to retrigger. Just as with auto-entry of a looked up value, the Lookup function captures the sourceField as it existed at a "moment in time." The alternative of simply referencing the related field results in what's known as a cascading update: When the related value is updated, any calculations that reference it are updated as well. LookupNextThe LookupNext function is designed to allow you to map continuous data elements to categorical results. It has the same effect as checking the "copy next lower value" or "copy next higher value" options when specifying an auto-entry lookup. Its syntax is LookupNext (sourceField ; lower/higherFlag) The acceptable values for the second parameter are Lower and Higher . These are keywords and shouldn't be placed in quotes. An example should help clarify what we mean about mapping continuous data to categorical results. Imagine that you have a table that contains information about people, and that one of the fields is the person's birth date. You want to have some calculation fields that display the person's astrological information, such as a zodiac sign and ruling planet. Birthdates mapping to zodiac signs is a good example of continuous data mapping to categorical results: Ranges of birthdates correspond to each zodiac sign. In practice, two small but instructive complications arise when you try to look up zodiac signs. The first is that the zodiac date ranges are expressed not as full dates, but merely as months and days (for example, Cancer starts on June 22 regardless of what year it is). This means that when you set up your zodiac table, you'll use text fields rather than date fields for the start and end dates. The second is that Capricorn wraps around the end of the year. The easiest way to deal with this is to have two records in the Zodiac table for Capricorn, one that spans Dec. 22 “Dec. 31, and the other that spans Jan 1 “Jan 20. Figure 14.12 shows the full data of the Zodiac table. The StartDate and EndDate fields, remember, are actually text fields. The leading zeros are important for proper sorting. Figure 14.12. The data from the Zodiac table is looked up and is transferred to a person record based on the person's birth date. In the Person table, you need to create a calculation formula that generates a text string containing the month and date of the person's birth date, complete with leading zeros so it's consistent with the Zodiac table. The DateMatch field is defined as follows: Right ("00" & Month (Birthdate); 2) & "/" & Right ("00"& Day (Birthdate); 2) Next, create a relationship between the Person and Zodiac tables, matching the DateMatch field in Person to the StartDate field in Zodiac. This relationship is shown in Figure 14.13. Figure 14.13. By relating the Person table to Zodiac, you can look up any information you want based on the person's birth date. Obviously, many birthdates aren't start dates for one of the zodiac signs. To match to the correct zodiac record, you want to find the next lower match when no exact match is found. For instance, with a birth date of February 13th (02/13), there is no matching record where the StartDate is 02/13, so the next lowest StartDate , which is 01/21 (Aquarius), should be used. In the Person table, therefore, you can grab any desired zodiac information by using the LookupNext function. Figure 14.14 shows an example of how this date might be displayed on a person record. The formula for ZodiacInfo is as follows: "Sign: " & LookupNext (Zodiac::ZodiacSign; Lower) & "" & "Symbol: " & LookupNext (Zodiac::ZodiacSymbol; Lower) & "" & "Ruling Planet: " & LookupNext (Zodiac::RulingPlanet; Lower) Figure 14.14. Using the LookupNext function, you can create a calculation field in the Person table that contains information from the "next lowest" matching record. It would have been possible in the previous examples to match to the EndDate instead of the StartDate . In that case, you would simply need to match to the next higher instead of the next lower matching record. An entirely different but perfectly valid way of approaching the problem would have been to define a more complex relationship between Person and Zodiac, where the DateMatch was greater than or equal to the StartDate and less than or equal to the EndDate . Doing this would allow you to use the fields from the Zodiac table as plain related fields; no lookup would have been required. There are no clear advantages or disadvantages of this method over the one discussed previously. NOTE Other typical scenarios for using LookupNext are for things such as shipping rates based on weight ranges, price discounts based on quantity ranges, and for defining cut scores based on continuous test score ranges. |
(birthDate)) & " years, " & Mod (Month(Get(CurrentDate)) - Month (birthDate) - (Day (Get 
Another use of Evaluate is presented in "Passing Multi-valued Parameters," 
| < Day Day Up > |
