Substitution Formulas

💡This article contains version changes.
Spot the light bulb icon? That means you’ll find helpful details about what’s changed across versions. Read more about version changes here.

Substitution formulas are functions which are calculated at runtime and dynamically return text strings to replace those formulas in situ.

Substitution formulas can be used in specific Object properties throughout the Capsule environment or in specific Procedure steps. For example, they can be used in the Caption field of a Label Object, in the definition of a Data model security profile, or when customizing the printing layout of a Screen. They can also be used in Block Headers (see below) in a Layout object (but not anywhere in a Flex Grid object).

Substitution Formulas Library

All of the Substitution Formulas are case insensitive, except for the group of @[entityname] substitution formulas, e.g. @Month is not the same as @month.

@selection

The @Selection and @Selection_Ex formulas are definitely the most popular substitution formulas and can be used in conjunction with several parameters to customize the final output. They can be used in any Label or Button Caption, Title, or Block Header field in a Layout object. @Selection returns the current Selection in the form of the names of the Entities on which a selection was made, followed by a comma-separated list of the selected members for each of the Selection Entities.

The @Selection formula accepts no parameters, but simply shows all of the Entities used in the selection criteria (and their members).

💡The @Selection_Ex formula is the extended version of @Selection (Ex is short for "Extended").
Without parameters, the @Selection_Ex formula returns exactly the same information as @Selection. However, the @Selection_Ex formula can accept various input parameters, such as Entity, Layout, ShowActive, Interval, Display, Limit, etc. These will return only the information specifically requested by those parameters.

💡The Substitution Formulas behave differently when used in Labels or Captions rather than when used in the Block Headers of a Layout.

When the @Selection_Ex formula is used in a Layout title or Block Heading, the Layout and Block parameter must be omitted.
Two or more @Selection_Ex formulas cannot be combined nor concatenated in the same script.
The @Selection_Ex formula cannot use another @Selection_Ex formula as a parameter: i.e. the formula @Selection_Ex(Layout="Layout_Title") will return an empty value if a @Selection_Ex formula has also been used in the Layout title itself.

Block Header Substitution Formulas

💡The @Selection, @Selection_Ex, and the group of @[entityname] substitution formulas can also be used in Block headers in a Layout, and therefore on any Object based on a Layout (e.g. Data View, Chart, etc.).
When used in Block headers, these functions have both additional features and several known limitations as compared to their use elsewhere.

The substitution formulas CANNOT be used anywhere in a Flex Grid object.

@Selection in the Block Header

The formula is replaced with a list of all members in the "Refer To" function of the Data Block. The members are separated from each other by a new line, without the Entity title. If there are no members in the "Refer To" function, then the formula is not substituted. Whenever a substitution formula is not executed for any reason, it is displayed as the formula text itself, e.g. "@Selection_Ex”.

Example:

With Year: 2010 and Customer: A & N SUPPLIES in the "Refer To", the @selection substitution formula returns:

2010
A & N SUPPLIES

💡@[entityname](offset) formulas in the Block Header

The formula is replaced with a list of all members of the [entityname] Entity. The members are retrieved based on the following sequence:

1. As with the @Selection formula above, members are first searched for in the "Refer To" function of the Data Block, if the [entityname] Entity has been directly used for the select.

2. If the exact [entityname] Entity is not directly present in the "Refer To", the "Refer To" is then checked for other Entities belonging to the same hierarchy as [entityname]: if found, then the formula is replaced with the members of [entityname] that are active within the context of the related members present in the "Refer To".

3. If the "Refer To" provides no such clarification in 1. and 2. above, then the Layout Selection is checked for the selected members of [entityname].

4. Failing those 3 options, the Pager or Master object is checked for matching selections.

5. Finally, if the [entityname] members have not yet been revealed, then the Screen Selection is considered.

6. If the [entityname] Entity has not been encountered via these five paths, then the formula is not substituted (the formula itself is displayed).

The optional "offset" parameter can be specified to pick the displayed values for members that are either earlier or later in the Member sequence. By default, the offset is set to zero. The offset is applied by considering the sorting sequence that has been chosen for the [entityname] Entity, i.e. None, Code, Desc or Custom.

The @[entityname] formulas have an implicit maximum number of 10 Members to be shown. To specify a different maximum, use the Limit parameter within an equivalent @Selection_Ex formula.

Examples for a Data Model with the Time Range [2010 .. 2020] and a select = "Year: 2010"

@Year(Offset="1")  returns "2011"

@Year(Offset="-1")   returns ""         (2009 is not in range)

@Year(Offset="11") returns ""         (2021 is not in range)

Examples of exploring the sequence for the Members

With "Year: 2010" and "Customer: A & N SUPPLIES" in the Refer To, @Year returns "2010"

With "Month: Jan. 12" and "Customer: A & N SUPPLIES" in the Refer To, @Year returns "2012"

With "Customer: A & N SUPPLIES" in the Refer To, "Year: 2010" in the Layout Select, @Year returns "2010"

With "Customer: A & N SUPPLIES" in the Refer To, "Year: 2010" in the Screen Select, @Year returns "2010"

With "Year: 2010, Customer: A & N SUPPLIES" in the Refer To, @Year(Offset="1") returns "2011"

With "Month: Jan. 12, Customer: A & N SUPPLIES" in the Refer To, @Year(Offset="-3") returns "2009"

Important: it is NOT possible for a user to access members or related data that have NOT been made available within that user’s Security restrictions, nor to access data which is outside the range of Members available in the source data. Users cannot access Members outside the valid range using offset or outside the current Security restrictions.

Technically, the formula @[entityname](Offset="n") is exactly equivalent to @Selection_Ex(Entity="entityname", Offset="n", ShowActive="True").

The @[entityname] formulas effectively work with an implicit ShowActive parameter set to True. If a different value for ShowActive is required, it can be set using that parameter in an equivalent @Selection_Ex formula.

💡@Selection_Ex(Entity, Offset, Limit, ShowActive, Display, Interval) in the Block Header

The @Selection_Ex formula functions exactly like the @[entityname] formula above, but offers the option to specify additional customization parameters. All of the parameters are described in detail in the @Selection section above. Technically, the formula @[entityname](Offset="n") is exactly equivalent to @Selection_Ex(Entity="entityname", Offset="n", ShowActive="True").

For Blocks of type Algorithm, if a Substitution Formula is specified in the Algorithm field and the Heading field is left empty, then the formula is automatically copied into the Heading field of the Block.

If a Drill is applied to a Layout that contains a Substitution Formula in a Block Header, then the Substitution Formulas will be recalculated on the Layout that results after the Drill.

Examples

With "Customer: A & N SUPPLIES" in the Refer To, Year: 2010 in Screen Select, @Selection_Ex(Entity="Year") returns "2010"

With "Year: 2010, Customer: A & N SUPPLIES" in the Refer To, @Selection_Ex(Entity="Year", Offset="1") returns "2011"

With "Month: Jan. 12, Customer: A & N SUPPLIES" in the Refer To, @Selection_Ex(Entity="Year", Offset="-3", ShowActive="True") returns "2009"

With "Customer: A & N SUPPLIES" in the Refer To, and "Year: 2010, 2011, 2012 in Screen Select", @Selection_Ex(Entity="Year", Interval="Range") returns "2010 .. 2012"

With "Month: Jan. 12, Customer: A & N SUPPLIES" in the Refer To, @Selection_Ex(Entity="Month", Offset="1", Display="CodeDesc") returns "201202 Feb. 12"

With "Year: 2010" and "Customer: A & N SUPPLIES" in the "Refer To", the @Selection substitution formula returns:

2010
A & N SUPPLIES

💡Known limitations of the substitution formulas in the Block Headers

• The substitution formulas cannot be used in the Block header of a Flex Grid object (nor anywhere else in a Flex Grid).

• If the entity selected by the substitution formulas is used inside the Layout by row or by column, OR one of its parents is used by row or by column, then the substitution formula is not executed.

• Custom sorting on the select Entity is applied when using a substitution formula with the Offset parameter, so be aware that the results will not reflect any previous sorting rules.

• In general, custom sorting on the select Entity is NOT taken into consideration by the Interval parameter, which therefore may not work as expected with a value of First, Last or Range in @Selection_Ex formulas inside a Block Header. See Creating an Entity for details about custom sorting of Entities.

• The Limit parameter wins over Offset. If the number of selected members exceeds the fixed Limit, then the ratio between the selected members and the total number of members is directly displayed, without performing the Offset first.

• When a Limit is applied, the total number of members displayed by the ratio considers ALL the members of that Entity, without considering any limitations due to the user’s security select.

• If an Entity name includes one of these four special characters, @"() , then that Entity CANNOT be used in a substitution formula inside a Block header. The correct result can still be obtained using an equivalent @Selection_Ex formula instead.

• If a special character (a character that is NOT alphanumeric or a space, e.g. @"() ) occurs in the description of an @[entityname] formula, then the text must be specified in parentheses. In such cases, the Offset parameter is not supported, but can be specified using an @Selection_Ex formula with the Entity and Offset parameters instead.

Examples of Entity names requiring special treatment in substitution formulas

Entity name	Correct formula	Incorrect formula
custom-field	@(custom-field)	@custom-field
IBAN number	@(IBAN number)	@IBAN number