AX1121

Calendar calc method variable

Calendar calc method variables prompt users to select a date from a calendar. The date is written back to the specified location in the calc method.

When used in the Web Client, calendar variables can also be used to select a month or year.

Variable behavior

The variable displays as a text box with a calendar button next to it. The user can click the button to select a value from the calendar.

Desktop Client: Example Calendar calc method variable

Web Client: Example Calendar calc method variable

In the Web Client, the current date shows at the bottom of the calendar for reference.

Once the user has selected a date from the calendar, the selected date displays in the text box. The user cannot type a date into the text box; only selection from the calendar is allowed.

In the Web Client only, Calendar calc method variables can also be used to select a month/year combination, or a year. The month and year options are not available in the Desktop Client.

Web Client: Example Calendar calc method variable to select month/year

Web Client: Example Calendar calc method variable to select year

Variable properties

This section explains how to complete a variable row in the CalcMethodVariables data source when defining a Calendar variable. Some data source columns do not apply in this case and are not discussed here. If these inapplicable columns are present in the data source, they should be left blank on rows that define Calendar variable types. For more information on the CalcMethodVariables data source in general, see Defining calc method variables using a CalcMethodVariables data source.

General variable properties

All calc method variables use a common set of general properties such as the variable name, display name, and whether the variable is enabled or required. Any special considerations for Calendar variables are noted.

Column Tag	Description
[Name]	The name of the variable. This name identifies the variable row in the data source, and is also used as the variable display name to users if a separate display name is not defined in the `[DisplayName]` column. The name should not contain any non-alphanumeric characters such as question marks or periods. If you want the variable name that displays to users to include non-alphanumeric characters, use the display name. The name cannot be dynamic; it must remain static because it is used to identify the variable. If you are configuring a dependent variable and you need the name to change based on the selection of the parent variable, then you must make the display name dynamic instead of the name.
[DisplayName]	Optional. The display name of the variable. If defined, the display name will be used instead of the name when the variable displays to users.
[VariableType]	Specifies the variable type. Enter `Calendar` to allow users to select a date from a calendar.
[RelativeLocation]	The location to place the variable value when the calc method is inserted, relative within the calc method. The location is specified as follows: `<ColumnLetter><CalcMethodRow>`. For example: If the value should be placed in column B of the calc method, within the first row of the calc method, enter the following: `B1`. If the calc method is a single-row calc method, the calc method row is always 1. If the value should be placed in column B of the calc method, but within the third row of the calc method, enter the following: `B3`. The entry for the calc method row must be valid within the context of the current calc method. For example, if the current calc method is a three-row calc method, then the only valid entries for the row are 1, 2, and 3. If you attempt to specify B4 for the three-row calc method, the entry is invalid. For Calendar variables, the value written to this field is an Excel date serial number.
[IsEnabled]	Specifies whether the variable is enabled (True/False). If `True`, then the variable will be included in the Calc Method Variables dialog. If blank or `False`, then the variable will not be included in the Calc Method Variables dialog. This evaluation is determined when the Calc Method Variables dialog is opened. If the variable is flagged as dependent using the `[DependsOn]` column, and the current context is a spreadsheet file in the Desktop Client, then the evaluation will be performed again after a value has been selected for the parent variable.
[SelectedValue]	For calc method variables, the `[SelectedValue]` column is only used for dependent variables. When the calc method is inserted and the user selects a value for the parent variable, the selected value is written back to this field temporarily, in the background. This means you can write formulas that change something about the dependent variable based on the selected value of the parent variable. For more information, see Using dependent calc method variables. This field cannot be used to set a default value for the variable. Calc method variables do not support the ability to define a default value. For Calendar variables, the value written to this field is an Excel date serial number.
[IsRequired]	Optional. Specifies whether the user must enter a value for this variable (True/False). If `True`, then the user must specify a value for this variable in order to insert the calc method. If blank or `False`, then the user can leave the variable blank. The calc method should be configured so that it works as expected if the variable is left blank. The display of required and optional variables depends on the environment. In the Desktop Client, the text (optional) follows the name of optional variables. In the Web Client, required variables that do not yet have a selected value are indicated with a red bar along the side of the variable field.
[DependsOn]	Optional. Specifies that the variable is dependent on a "parent" variable. To make a variable dependent on another variable, enter the name of the parent variable. Some variable types require a parent variable, such as RelatedColumnValue variables. Other variable types can be made dependent as needed. Dependent variables can be updated dynamically in response to the selected value for the parent variable. For more information, see Using dependent calc method variables.
[InsertOnly]	Specifies when the variable should be displayed: If `True`, then the variable is only displayed when a user inserts the calc method. It is excluded from the dialog during all other operations, including change (overwrite) and double-click. If all variables for the calc method are set to Insert Only, then the dialog only displays when inserting. If blank or `False`, then the variable is displayed in all cases.

Variable-specific properties

The following additional properties apply to Calendar variable types:

Column Tag	Description
[MinDate]	Optional. The earliest date that is valid for a user to select in the calendar. If specified, the calendar control will not allow the user to select a date that is earlier than this date. When using the Month or Year display format, the minimum date must still be a full date. The appropriate minimum month and year will be determined from that date. NOTE: The cell format for this property should be set to Date. DateTime formats are not supported and may cause errors. For more information, see Handling date formats.
[MaxDate]	Optional. The latest date that is valid for a user to select in the calendar. If specified, the calendar control will not allow the user to select a date that is later than this date. When using the Month or Year display format, the minimum date must still be a full date. The appropriate minimum month and year will be determined from that date. NOTE: The cell format for this property should be set to Date. DateTime formats are not supported and may cause errors. For more information, see Handling date formats.
[DisplayFormat]	Optional, applies to Web Client only. Specifies the type of date value for selection: Date: Users select specific dates from a calendar control. This is also the default behavior if no display format is specified. Month: Users select a month and year combination from a drop-down selection. Year: Users select a year from a drop-down selection. The display format determines the values for selection and the display of the selected value in the variable. However, the return value written to the `[SelectedValue]` field is always a full date. See Handling date formats for more information.
[AutoQuoteString]	Optional. Specifies whether the date value is placed in single quotation marks when it is written to the `[SelectedValue]` column (True/False). If omitted or blank, the default setting is False, which means the date value is not quoted.

The following properties do not apply to Calendar variables: PlaceHolderText, ListChoices, ColumnName, AdditionalColumns, ColumnFilter, AllowMultiSelect, DataSourceName, Hierarchies, TooltipColumn, PrimaryTable, LimitColumn, MinValue, MaxValue, StepFrequency.

Example data source

The following screenshot shows example Calendar variables.

The first two variables define start and end dates. The End variable is dependent on the Start variable, so that the minimum value of the end date can use a formula that points to the selected value of the start date. This is to ensure that the user does not accidentally set the end date to an earlier value than the start date. Note that currently this configuration only works in the Desktop Client.
The third and fourth variables use the [DisplayFormat] to select a month/year combination and a year. This configuration is only valid for use in the Web Client; it will be ignored in the Desktop Client.

Handling date formats

Within the Calendar calc method variable, the selected values are displayed as follows, depending on the specified [DisplayFormat]. The exact format depends on your system locale.

Date: Dates are displayed in an Excel "short date" format, such as 1/1/2020.
Month: Months are displayed in month/year format, such as January 2020. Only applies to Web Client.
Year: Years are displayed as the year number, such as 2020. Only applies to Web Client.

However, in all cases, the selected value is written to the calc method location as an Excel date serial number.