FM Function of the Week: Let

When is a function kinda not really a function? Unlike most functions that do some predefined process for you and kick back a result, the Let function is more about streamlining complicated calculations. Let permits you to define what amounts to calculation variables at the top of the calculation and then refer to them by name throughout the rest of the calculation. In the abstract, here’s a model of a Let function.

Let (
item = <calc to determine the value of item>;

<calc that can refer to the above named item>
)

Supposing that you have to create some crazy complicated calculation to figure out the values for items 1 and 2, placing them at the top of the calc can make the whole thing much more wieldy. A concrete example will, I hope, crystallize the case for why Let can be so useful. Consider the calculation used in last week’s FMFotW:

Case (

  DayName ( Get ( CurrentDate ) ) = "Monday";
  "Macaroni!";

  DayName ( Get ( CurrentDate ) ) = "Tuesday";
  "Taquitos!";

  DayName ( Get ( CurrentDate ) ) = "Wednesday";
  "Waldorf Salad!";

  DayName ( Get ( CurrentDate ) ) = "Thursday";
  "Thumbprint Cookies!";

  DayName ( Get ( CurrentDate ) ) = "Friday";
  "Frozen Yogurt!";

  DayName ( Get ( CurrentDate ) ) = "Saturday";
  "Sangria!";

  DayName ( Get ( CurrentDate ) ) = "Sunday";
  "Grilled Cheese!";

  "What calendar are you using!?"

)

Sure the above calculation works, but it’s the very model of inefficiency. It repeats the same darn expression seven times in a row. Using a simple Let implementation, we can instruct FileMaker to calculate the name of the current day once and keep that result on hand. The second line of the calculation below is where the magic happens. vToday = DayName ( Get ( CurrentDate ) ) tells FileMaker “Figure out the name of the current day and keep it handy for the rest of this calculation. Give it the shorthand ‘vToday’ so I can just type that wherever I want to use the name of the current day in this calculation.”

Let (
  
  vToday = DayName ( Get ( CurrentDate ) );

    Case (

      vToday = "Monday";
      "Macaroni!";

      vToday = "Tuesday";
      "Taquitos!";

      vToday = "Wednesday";
      "Waldorf Salad!";

      vToday = "Thursday";
      "Thumbprint Cookies!";

      vToday = "Friday";
      "Frozen Yogurt!";

      vToday = "Saturday";
      "Sangria!";

      vToday = "Sunday";
      "Grilled Cheese!";

      "What calendar are you using!?"

    ) // end Case
  
) // end Let

Using Let cuts the length of this calculation by about 120 characters and speeds it up a tiny bit by ensuring FileMaker only has to figure out the day name once and not up to seven times. It’s also a lot easier to maintain. Imagine that you had a typo in the expression used to determine the day. In the original calculation you have to fix the typo then update the other six places it appears. With the Let function, you just have to correct it in a single spot.

You can choose to name your Let values almost anything you want. I chose “vToday” because Let values are basically calculation variables– the “v” stands for variable and “Today” is the descriptor. You’re free to adopt whatever convention (or lack thereof) you wish as long as you stay within FileMaker’s limitations. The name of a Let value can’t be the same as any field name, nor can it be the name of any existing function or any reserved words used in calculations such as “not” or “and”. Complete naming rules can be found in FileMaker’s on-screen help.

I haven’t shown you everything Let can do. For more uses check out FileMaker help, or <shameless plug> FileMaker Pro 13: The Missing Manual </shameless plug>.