LET function
FirstName
The first value name.
FirstValue
The first value.
Name
An additional value name.
Value
An additional value. Values named previously are available here.
Result
The value that should be returned. Values named previously are available here.
Returns
The value of the Result parameter.
Assigns names to values and allows these values to be used in subsequent
calculations. LET(First := 2, Second
:=
First +
4, First + Second)LET(First := 2; Second := First + 4; First
+
Second) returns 8, because the value 2 is assigned the name
First, the value First + 4 (6) is assigned the name
Second, before First + Second (8) is returned.
LET is a great way to make formulas more readable and to cut down on formula repetition, helping them run faster. These topics are explored in detail below.
Parameters to LET are evaluated from left to right, meaning that values that
are given names are accessible to the right of where they are defined, but
not to the left of them. In the formula above, the value named
First is available where the value for Second is
defined, but not vice versa.
A name is defined by writing the name, followed by := and the value. An arbitrary
number of these pairs may follow, before the single parameter which defines
the result of the LET invocation. := may also be written
,;.
(Being able to use := instead of ,; is a Calcapp extension and is not
supported by spreadsheets. We think it makes formulas easier to read.)
Names defined using LET are only available in the formula itself. Use a named value to assign a name to a value that is accessible from any formula. Add a named value by adding a field and selecting Named value from the drop-down menu in the inspector on the left-hand side.
LET makes formulas more readable
Consider this formula, which calculates the cost of a residential property:
This formula is intended to be used with an app where the area of a residential property is given in square feet, as the AreaInSqFt field. The app only has access to the price in square meters, though, and so needs to convert the area first.
It performs the conversion by multiplying the area in square feet by a
constant, and assigns this value the name MetricArea. (The
CONVERT function
could also have been used.)
It then defines the price as $5,000 per square meter, assigning this value
the name MetricPrice. Finally, it calculates the price by
multiplying MetricArea by MetricPrice and returns
this value.
Performing this calculation without LET is straight-forward:
However, the LET version is arguably easier to understand, as it assigns names to all the constituent parts.
Whether the extra clarity makes up for the extra verbosity is a matter of personal preference. Long, complex formulas tend to benefit more from using LET than shorter formulas.
LET cuts down on formula repetition
Some formulas require certain parts to be repeated. With LET, these repeated parts can be written once, which makes formulas easier to read. It also helps them run faster, as the parts that were previously repeated only need to be calculated once.
Consider a formula which uses FILTER to return an array of fields whose values are greater than or equal to the value of the Threshold field:
The fields to filter are the fields of FormGroup1, which is returned by its Fields property. The formula needs to filter not just those fields, though, but also the fields represented by the range Field1:Field5Field1:Field5 (which includes Field1, Field5, and all items that appear between those two fields). It merges the two arrays using |.
Unfortunately, which fields to include needs to be repeated when providing FILTER with its second parameter: (FormGroup1.Fields | Field1:Field5) >= Threshold(FormGroup1,Fields | Field1:Field5) >= Threshold.
With LET, the solution is simple:
Not only is the formula using LET easier to read and runs faster, it is also shorter.
LET enables definitions to appear only once, instead of being duplicated all over a formula. That makes formulas more maintainable, as the risk of errors creeping in when formulas are updated is reduced.
LET and action formulas
LET works particularly well with action formulas, which are run in response to an event occurring (including buttons being pressed).
Consider an action formula that opens a report containing certain fields of the app, and resets the same fields by assigning blank values to them once the report has been opened:
With LET, the fields no longer need to be repeated:
Action formulas are especially susceptible to having parts of the formula repeated. The reason is that such formulas can perform many different actions, through functions like AWAIT and by separating different actions from one another with ;;;. Using LET, those repeated parts can be kept to a minimum.
When LET is used in an action formula, only the last parameter may invoke actions. The preceding parameters may only calculate values.
Examples
Returns 8, because the value 2 is assigned the name First,
the value First + 4 (6) is assigned the name
Second, before ultimately First + Second (8) is
returned.
Calculates the price of a residential property whose area is given in
square feet as the value of the AreaInSqFt field. The area must
be converted to square meters to determine the price. The first part of
the formula assigns the name MetricArea to the area,
converted to square meters. The second part defines the price, per square
meter, and names it MetricPrice. The final part returns the
price for the property by multiplying MetricArea by
MetricPrice.
Returns the fields whose values are greater than or equal to the value of
a field named Threshold. The fields are not repeated in the
formula, but are assigned the name Fields using the LET
function.
Opens a report, containing some fields, and resets these same fields by assigning blank values to them once the report has been opened. The LET function is used to ensure that the definition of these fields does not need to be repeated.