Let()

Let()

Category: Logical

Syntax: Let ({[}var1=expression1 {; var2=expression2...]} ; calculation)

Parameters: var(n) ”any valid variable name . The rules for naming variables are the same as for defining fields. expression(n) ”any calculation formula, the results of which are assigned to the var(n) variable. calculation ”any calculation formula.

Parameters in curly braces {} are optional.

Description: The Let() function enables you to declare local variables within a calculation formula. The variables exist only within the boundaries of the Let() function itself.

The first parameter of the Let() function is a list of variable names and expressions. If multiple variables are declared, the list needs to be enclosed in square brackets. The items in the list are separated by semicolons. The variables are set in the order in which they appear.

This means that you can use previously defined variables as part of the expression to define another variable.

The final parameter, calculation , is some calculation formula that you want to evaluate. That formula can reference any of the variables declared in the first half of the function.

Duplicate variable names are not allowed, but variables can be named the same as existing fields. If this happens, the value assigned to the variable, not the field, will be used in future references to the variable within the Let() function.

Examples:

The following formula extracts the domain name from an email address:

 Let([    start = Position (eMail ;"@" ;1 ;1);    numberOfCharacters = Length (eMail) - start];    Right (eMail ; numberOfCharacters)) 

The following example produces a summary of a student's grades:

 Let ([    TotalGradePoints = Sum (Grades::GradePoints);    CreditPoints = Sum (Classes::CreditPoints);    GPA = Round (TotalGradePoints/CreditPoints ; 2)] ;    "Total Grade Points: "& TotalGradePoints & "" &    "Available Credit Points: " & CreditPoints & "" &    "Your GPA is: " & GPA) 

The final example formula returns the volume of a pyramid:

 Let(SideOfBase = 2 * Sqrt(2 * SlantHeight^2 - Height^2;    SideOfBase^2 * Height/3) 

Comments:

Let() can be used to simplify complex, nested calculation formulas. In the case where a subexpression is used many times within a formula, the Let() function may also provide a performance benefit because the subexpression is evaluated only once, when it is assigned to the variable.