The Let function creates a temporary holder for a value, called a variable, which can be plugged into a calculation over and over again. You'll do a little more work upfront to set up a variable, but that effort pays off with faster calculations that are easier to read, edit, and troubleshoot.

11.3.1. Defining Calculation Variables

In your Let function, you define a value and give it a name, and then use that name as often as you need throughout the calculation. In this case, you can calculate the amount due once, and store the result in a variable called Amount Due.

The Let function is unique among functions because it controls the way you write your calculation, not the result. Here's an example:

Let ( [ L = 5 ; W = 10 ; H = 3 ] ; L * W * H )

Like the Substitute function described on Section 10.3.3.4, Let uses bracketed notation. It really takes just two parameters. The first is a list of variable definitions. Each variable gets a name and a value using this format:

Name = Value

* Tip:* FileMaker uses the terms "var" for Name and "expression" for Value in its manual and help files. The terms mean the same things, but "var" and "expression" sound much more impressive.

If you have more than one variable to define (as in the example above), put a semicolon between each one, and put them all in-between a pair of square brackets. You can use any calculation expression as the value.

In fact, the expression that determines the value of a variable can even use other variables that were defined earlier.

For example, the next calculation is perfectly legal. Its hours variable has a value of 240: 24 times the value of the Days variable:

Let ( [ Days = 10 ; Hours = 24 * Days ; Minutes = 60 * Hours ]; Minutes & " Minutes" )

The second parameter can be any calculation expression. This parameter is special because you can use any of the variables you've defined inside the expression, just like fields. In the first example above, there are three defined variables (L, W, and H); the expression then multiplies them together.

When FileMaker evaluates the Let function, it determines the value of each variable just once, and then plugs this value into the expression every time that variable is used. The result of a Let function is simply the result of its expression.

11.3.1.1. Total Due calculation #3: using Let

Your Total Due calculation can use the Let function to solve all its problems. Just put the Amount Due in a variable and use it throughout the calculation:

Let ( Amount Due = Invoice Amount Total Paid ; If ( Get(CurrentDate) > Date Due and Amount Due > 0; TextColor ( Amount Due ; RGB(255;0;0) ); Amount Due ) )

This version of the calculation is simpler, easier to change, and more efficient. You can't beat that.

11.3.2. The Life of a Variable

Most variables last only as long as it takes FileMaker to work through the calculation, then they're gone. This type of variable is called a local variable because they aren't valid outside the Let function that calls them into existence. But you can also create a special variable, called a global variable, that lives beyond your calculation. Read on to see when to use each type.

11.3.2.1. Local variables

The variables you've written so far have all been local variables. Now it's time to learn that local variables having shockingly short memories.

Local variables can lose their values even before a calculation is finished. If you write:

Let ( Amount Due = Invoice Amount Total Paid; If ( Get ( CurrentDate ) > Date Due and Amount Due > 0; TextColor ( Amount Due ; RGB ( 255 ; 0 ; 0 ) ); Amount Due ) ) & If ( Amount Due < 0 ; "CR" ; "" )

The calculation tries to use the Amount Due variable after the end parenthesis in the Let function. Anything that happens after that in the calculation is outside the Let function's scope, so when you try to close the Specify Calculation dialog box on this calculation, FileMaker complains that it doesn't know what that last Amount Due is supposed to be. One way to rewrite that calculation using a local variable is:

Let ( Amount Due = Invoice Amount Total Paid; Case ( Get ( CurrentDate ) > Date Due and Amount Due > 0; TextColor ( Amount Due ; RGB ( 255 ; 0 ; 0 ) ; Amount Due < 0 ; "CR" ; "" ) )

In this example, you're including the last test condition within the scope of the Let function and you've switched to a Case function, so that you don't have to read a set of nested If functions.

If you want the local variables you set inside calculations to follow the same naming conventions as variables you set in scripts (see Chapter 15), prefix their names with "$." In that case, you'd write the calculation you just saw like this:

Let ( $Amount Due = Invoice Amount Total Paid; Case ( Get ( CurrentDate ) > Date Due and $Amount Due > 0; TextColor ( $Amount Due ; RGB ( 255 ; 0 ; 0 ) ; $Amount Due < 0 ; "CR" ; "" ) )

Notice that you have to include the prefix in the Let function and in the formula that follows it.

11.3.2.2. Global variables

But just as FileMaker gives you global fields, it also gives you global variables. Unlike local variables, global variables hold their results after the Let function is finished. To create a global variable, add a "$$" prefix to its name. Here's the same calculation rewritten with a global variable:

Let ( $$Amount Due = Invoice Amount Total Paid; Case ( Get ( CurrentDate ) > Date Due and $$Amount Due > 0; TextColor ( $$Amount Due ; RGB ( 255 ; 0 ; 0 ) ; $$Amount Due < 0 ; "CR" ; "" ) )

The only difference you can see in the calculation is the $$ prefix. But the practical difference is vast: Global variable values remain until you change them (through another calculation or through a script), or until you close the file.

POWER USERS' CLINICField Formatting Calculations |

Now that you understand most of FileMaker's calculation power features, you're ready to see something really neat. Sometimes you have a field that lets you have free entry, but you want to be sure the end result is formatted properly. The trick is to attach an Auto-Enter calculation to a field, and make the calculation depend on the field itself. Whenever the user changes the field, FileMaker recalculates the auto-enter calculation and updates the field. For example, imagine you want all customer phone numbers entered in this form: (555) 555-1212 Since Phone Number is a text field, there's nothing keeping the user from typing it this way instead: 555/555/1212 Or like this: 555-555-1212 Or like this: 55 55 55 12 12 To clean up the data when it's entered, just add an auto-enter calculation to the Phone Number field. This calculation does the trick: Let( clean phone = Filter(Phone Number; "0123456789"); Case( Length(clean phone) = 10; "(" & Left(clean phone; 3) & ") " & Middle(clean phone; 4; 3) & "-" & Right(clean phone; 4); Phone Number ) ) First the calculation uses the Filter function to remove any non-numeric characters from the entered phone number, and puts the result in a variable called "clean phone." Then, if the clean phone has exactly 10 digits, the calculation breaks it apart according to the format you want. Otherwise it just returns the phone number the way the person entered it. To make the calculation work properly, be sure you turn off "Do not replace existing value (if any)" in the Field Options dialog box. |

* Tip:* FileMaker's Advanced Data Viewer (Section 19.1.1.4) eliminates guesswork by letting you check the contents of local and global variables.

You could run a script that checks to see if a Payment was made within 10 days of the Invoice Date and if it was, apply a 1% discount to the $$Amount Due field. Sure, you can do something similar with a straightforward calculation field, but in that case, it gets a little trickier to apply the discount to some of the records, but not to others. With a script, you can find the records you want to give a spur-of-the-moment discount, run the script on that found set, and you're done.

11.3.3. Nesting Let Functions

As with other functions, you can nest Let functions inside one another. In fact, you can define a variable once, and then redefine it inside a nested Let function. The variable's value changes while inside the nested Let function, then changes back when it ends. By the same token, you can define a variable with the same name as a field, and FileMaker uses the variable's value while inside the Let function.

Here's a very simple example of a Let function inside another Let function:

Let ( X = 3 ; // only X is defined here Let ( Y = 4 ; // X and Y are both defined here X * Y ) // Only X is defined here too )

You can also use Let more than once in a single calculation without nesting:

Let ( [X = 3; Y = 4] ; X * Y ) & Let ( units = "inches"; " " & units )

Part I: Introduction to FileMaker Pro

Your First Database

- Your First Database
- An Very Quick Database Tour
- Creating a New Database
- Opening and Closing Database Files
- Saving Your Databases
- Adding Records to Your Database
- Navigating Your Database
- Same Database, Multiple Windows

Organizing and Editing Records

- Organizing and Editing Records
- Views
- Advanced Find Mode
- Changing Multiple Records
- Sorting Records
- Editing Whats in Your Fields
- Paragraph Formatting
- Beyond Text: Container Fields
- Checking Spelling
- Printing and Preview Mode

Building a New Database

- Building a New Database
- Tables and Fields
- Defining Fields
- Advanced Field Options
- Bringing It All Together

Part II: Layout Basics

Layout Basics

Creating Layouts

Advanced Layouts and Reports

- Advanced Layouts and Reports
- Setting up Field Controls
- Adding Field Controls to Layouts
- Field Behavior
- Tab Order
- Print-Related Layout Options
- Format the Number/Date/Time/Graphic
- Adding Buttons
- Tab Panels
- Reports and Summary Fields

Part III: Multiple Tables and Relationships

Multiple Tables and Relationships

- Multiple Tables and Relationships
- Relational Databases Explained
- Modeling Your Database
- Creating a Relational Database
- Relational Databases

Advanced Relationship Techniques

- Advanced Relationship Techniques
- Creating Related Records
- Table Occurrences
- Managing Data with Data Tunneling
- Building a Data Tunneling Interface
- Connecting Databases
- Lookups
- Advanced Relationships

Part IV: Calculations

Introduction to Calculations

- Introduction to Calculations
- Understanding Calculations
- The Structure of a Calculation
- Creating a Calculation Field
- Auto-Enter Calculations
- Validate Data Entry with a Calculation
- Replacing Data Using a Calculation
- Comments

Calculations and Data Types

- Calculations and Data Types
- Number Crunching Calculations
- Going Beyond Basic Calculations
- Text Parsing Calculations
- Date and Time Calculations
- Containers in Calculations

Advanced Calculations

- Advanced Calculations
- Stored, Unstored, and Global Fields
- Logical Functions
- The Let Function and Variables

Extending Calculations

Part V: Scripting

Scripting Basics

- Scripting Basics
- Your First Script
- The Importance of the Layout
- Running Scripts
- Branching and Looping in Scripts

Script Steps

- Script Steps
- Go to Layout
- Scripting for Fields
- Working with Records
- Finding Records
- Sorting Records
- Working with Windows
- Working with Files
- Printing
- Other Script Steps

Advanced Scripting

- Advanced Scripting
- Commenting Scripts
- Asking and Telling the User
- Organizing Complex Scripts
- Handling Errors
- Putting a Complex Script Together

Part VI: Security and Integration

Security

Exporting and Importing

- Exporting and Importing
- Exporting a File
- Export Formats
- Importing Data
- Import Data Sources
- Importing and Exporting in a Script

Sharing Your Database

- Sharing Your Database
- FileMaker Network Sharing
- Sharing Over the Internet
- FileMaker Server
- Server Hardware

Developer Utilities

- Developer Utilities
- Script Debugger
- Disable Script Steps
- The Database Design Report
- Tooltips
- Copying Database Structure
- Custom Menus
- Developer Utilities
- File Maintenance

Part VII: Appendixes

Appendix A. Getting Help

FileMaker Pro 8: The Missing Manual

ISBN: 0596005792

EAN: 2147483647

EAN: 2147483647

Year: 2004

Pages: 176

Pages: 176

Authors: Geoff Coffey, Susan Prosser

Similar book on Amazon

Flylib.com © 2008-2017.

If you may any questions please contact us: flylib@qtcs.net

If you may any questions please contact us: flylib@qtcs.net