AX2157

Calc method control tag syntax

This section provides a detailed explanation of the calc method control tag syntax, and several examples.

Standard insertion control syntax (InsertCMColumn)

The control column syntax for standard calc method controls is as follows:

[InsertCMColumn; HeaderLabel]

The InsertCMColumn portion of the tag is required, and flags the column as the control column.

HeaderLabel is the text to appear on the Add Row(s) > Custom Inserts menu, for this set of enabled rows. The header text could be the name of the sheet, or it could describe the allowed actions. For example:

[InsertCMColumn; Add Rows to Budget Sheet]
[InsertCMColumn; Add New Employees]

The header label parameter is optional and may be omitted if not necessary. For example, if you are enabling a calc method control column for the sole purpose of enabling rows for overwriting, then no insertion points will display on the menu anyway, and you can omit the header text. Note that if you omit the header label, you must still include the delimiting semicolon like so: [InsertCMColumn;]

NOTES:

The primary tag must be placed in the first 500 rows of the sheet.
Formulas can be used to create the tags, as long as the initial bracket and identifying keyword are whole within the formula. For more information, see Using formulas with Axiom feature tags.

Dynamic insertion control syntax (DynamicCMColumn)

The control column syntax for dynamic insertion controls is as follows:

[DynamicCMColumn; Label; DynamicMarker; Table|Filter|SheetDestination|CalcMethodDestination; MultipleInsert]

The DynamicCMColumn portion of the tag is required, and flags the column as the control column. The remaining parameters must be completed as follows:

Item	Description
Label	The text to appear on the Add Row(s) > Custom Inserts menu. For example, "Update an account". This is the only text that will appear on the menu for this column. Unlike when using the InsertCMColumn tag, any label text defined in the individual InsertCM tags is ignored.
DynamicMarker	Optional. The text that identifies an InsertCM row as the "active" row for dynamic insertion. For example, you could use the text "InsertHere" as the dynamic marker. After the user selects an account and the sheet is calculated, Axiom Software will look for the first InsertCM tag in the column that contains a matching "InsertHere" tag. That row becomes the active row for insertion. If omitted, then the active row is the first row in the column that contains an InsertCM tag.
Table	The table to present to the user, so that the user can select the desired item in the table. The table can be any reference table, such as ACCT or DEPT. The full syntax for specifying the table is as follows: Table.PrimaryColumn.AdditionalColumn.AdditionalColumn The PrimaryColumn is the column that contains the items for the user to select. In most cases this is the key column for the table. Any additional column listed will display in the dialog to help the user make a selection. You can list as many additional columns as desired, delimited by periods. For example: ACCT.ACCT.Description In this case the user will select an account from the ACCT table, and the Description column will also display in the dialog. NOTE: Additional columns only apply if the PrimaryColumn is the key column for the table. Otherwise they will be ignored.
Filter	Optional. A filter to limit the items to display in the selection dialog. The filter must be based on the specified table. Use standard filter criteria syntax. For example: ACCT.Category NOT IN ('Balance Sheet','Statistics') Accounts belonging to these categories will not be listed in the dialog. If omitted, then all items in the table will be listed in the dialog. If you omit this parameter, you must delimit it with an empty pipe character.
SheetDestination	The cell in the workbook to place the user's selection. Specify both the sheet name and the cell reference, even if you want the item to be placed on the current sheet (the sheet where the tag is defined). For example: Budget!B25. This is a fixed location. The spreadsheet logic that you build within the sheet to determine the active row for insertion must point to this cell to obtain the user's selected item.
CalcMethodDestination	Optional. The cell within the inserted calc method to place the user's selection. This location is relative to 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 value should be placed in column B of the calc method, but within the third row of the calc method, enter the following: `B3`. If omitted, then the user's selection is not placed inside the inserted calc method. NOTES: This location must apply to all calc methods that could potentially be inserted by use of the dynamic insertion controls. This item is ignored if the action performed on the active row is simply to navigate ("goto") an existing row rather than insert a new calc method.
MultipleInsert	Optional. Type the keyword `MultipleInsert` if you want the dynamic insertion to allow multiple insertion points. Omit this parameter to use the standard behavior of a single insertion. If enabled, then a calc method will be inserted at all matching locations, not just the first matching location. Enabling this behavior has the following limitations: All matching rows must be limited to a single calc method. This means that the InsertCM tag for the matching row must either specify a single allowed calc method, or a calc method group that only contains one calc method. Also, the MaxInsertCount parameter for the InsertCM tag must be blank (meaning inserting multiple instances of the calc method is not allowed). The allowed calc method for the matching row cannot use any calc method variables. Matching rows flagged as GoTo or Change rows are ignored. The idea is that multiple calc method insertions can take place without requiring any further input from the user. Any configuration that requires additional input, such as selecting a calc method or completing variable values, is considered invalid and will prevent the insertion.

All required parameters must be present in the tag in order for the label text to appear on the Add Row(s) menu.

NOTES:

The primary tag must be placed in the first 500 rows of the sheet.
Formulas can be used to create the tags, as long as the initial bracket and identifying keyword are whole within the formula. For more information, see Using formulas with Axiom feature tags.

Examples of DynamicCMColumn tags

[DynamicCMColumn; Update an account;InsertHere; acct.acct.description.category|acct.category NOT IN ('balance sheet','statistics')|Budget!BB22|F1]

This example places the text "Update an account" on the Add Row(s) > Custom Inserts menu. When a user clicks on this item, a dialog opens so that the user can select an account. In addition to the list of accounts, the Description column and the Category column also display in this dialog. The list of accounts is filtered to not include accounts that belong to the Balance Sheet and Statistics categories.

After the user selects an account, the selected item is placed into cell BB22 of the Budget sheet, and the sheet is calculated. The active row for dynamic insertion is then identified by looking for the first row in the column that contains an InsertCM tag with the matching dynamic marker text of "InsertHere". If a calc method is inserted as a result of the dynamic insertion process, then the selected account is placed in the first row of the inserted calc method, in column F.

[DynamicCMColumn; Update an account; ; acct.acct||Budget!BB22]

This example is the same as the prior example, except that only the required parameters have been specified. This is provided to contrast the difference in behavior when certain optional parameters are omitted. In this case:

No dynamic marker is used, so the active row will be determined by locating the first row in the column that contains an InsertCM tag.
No additional columns are specified for the table, so the selection dialog will use the "simple view" (account codes with the description column appended in parentheses).
No filter is specified for the table, so all accounts will be listed in the selection dialog.
No calc method destination is specified, so the selected item will not be placed in any inserted calc method.

[DynamicCMColumn; Add a new account; InsertHere; acct.acct.description.category| |Budget!BB22|F1;MultipleInsert]

This example uses the MultipleInsert parameter to allow calc method insertion at multiple locations, not just the first matching location. The user would select an account and that account might be inserted at one or more locations in the sheet (in this example, the plan file might contain several departments so multiple instances of the same account would make sense).

Enabled row syntax (InsertCM)

The enabled row syntax for calc method controls (standard and dynamic) is as follows:

[InsertCM; InsertionPointLabel; CalcMethodList; GroupList; AllowedAction; InsertLocation; MaxInsertCount; DynamicMarker]

The InsertCM portion of the tag is required, and marks the row as enabled for a calc method action. The allowed actions, allowed calc methods, and navigation depend on the optional parameters:

Parameter	Description
InsertionPointLabel	Defines the text to display on the Add Row(s) menu of custom insertion points, organized under the header text of the control column. NOTE: This parameter only applies when using standard calc method controls (InsertCMColumn). It is ignored when using dynamic insertion controls (DynamicCMColumn), but must still be delimited with an empty semicolon. This parameter only applies to rows that are enabled for insert (either insert only, or insert and overwrite). When an insertion point is selected from the menu, the action is always to insert a calc method at that location. If omitted, then this insertion point does not display on the Add Row(s) menu, but users can still insert calc methods manually at this location (if the row is enabled for insert). Custom insertion points cannot be used to navigate users to a location for overwriting. If the only action allowed on the row is overwriting, then this parameter is ignored.
CalcMethodList	Defines the list of allowed calc methods for the row. Separate multiple calc method names with commas. If a calc method list is defined, then only calc methods that match this list can be used to insert or overwrite at this location (unless a group list is also defined). If both the CalcMethodList and GroupList are omitted, then any calc method can be used to insert or overwrite at this location.
GroupList	Defines the list of allowed calc method groups for the row. Separate multiple group names with commas. If a group list is defined, then only calc methods that belong to the specified groups can be used to insert or overwrite at this location (unless a calc method list is also defined).
AllowedAction	Specifies the allowed action for this location. The action can be one of the following: `Insert`: Users can insert calc methods at this location. When using dynamic insertion, a calc method will be automatically inserted at this location if the row is identified as the active row. `Change`: Users can change calc methods at this location. The keyword "Overwrite" is also recognized here, for backward-compatibility. When using dynamic insertion, the user will be taken to this location if the row is identified as the active row, but no further action occurs. If the user has the rights to change calc methods, they can use the Add Rows > Change Calc Method option to do so. `Goto`: When using dynamic insertion, the user will be taken to this location if the row is identified as the active row. Insert and/or overwrite are not allowed at this location. NOTE: This option only applies when using a dynamic insertion control column (DynamicCMColumn). If you are using a standard calc method control column (InsertCMColumn) and GoTo is specified as the allowed action, then this row does not display on the custom inserts menu, and no calc method actions can be performed on it. <Blank>: Users can insert and change calc methods at this location. When using dynamic insertion, a calc method will be automatically inserted at this location if the row is identified as the active row.
InsertLocation	Specifies where a calc method will be inserted relative to the current row, if the row is enabled for insertion: `Above` (or blank): New calc methods are inserted above the current row. `Below`: New calc methods are inserted below the current row.
MaxInsertCount	Specifies the maximum number of calc method instances that can be inserted at this location, if the row is enabled for insertion. For example, if the user is inserting detail rows, you may want to give the user the option to insert multiple detail rows at a time. The behavior of this option depends on how many calc methods are available for insert at this location: If the Insert Calc Method dialog will display to the user (i.e. more than one calc method is available for insert at this location), then this setting controls what the user can enter into the Number of items to insert field in the dialog. In this circumstance, blank or 0 means unlimited—the user can insert as many instances as they wish. You can enter a number if you wish to limit the number of instances that can be inserted, or you can enter 1 if you do not want the user to be able to insert more than one instance of the calc method. If only one calc method is available for insert at this location, then this setting controls whether or not the user will be prompted to insert more than one instance of the calc method, and if so what is the maximum number they can input. You should leave this setting blank if you do not want the user to be prompted. If you want the user to be prompted, you can enter 0 for unlimited instances, or enter a number higher than 1. (Entering 1 does not make sense in this circumstance, because this will cause the prompt to display but the user will be unable to change it to a number higher than 1.)
DynamicMarker	This item is only applicable when used in a dynamic insertion control column that has a dynamic marker defined in the DynamicCMColumn tag. After a user selects the desired account (or other dimension) and the sheet is calculated, the "active" row for insertion is determined as the first row in the column with a matching dynamic marker. Use of a dynamic marker is optional. If no dynamic marker is specified in the DynamicCMColumn tag, the active row is determined as the first row in the column with an InsertCM tag.

The CalcMethodList and GroupList are combined together to result in the full list of calc methods allowed at the location. You can use one or the other, or both. Note the following:

If only one calc method is allowed, that calc method is automatically inserted or used to overwrite when the user performs the calc method action on the row. The calc method dialog does not display to the user.
If multiple calc methods are allowed, then the calc method dialog is filtered to only show the allowed calc methods.

If a parameter is omitted, and you want to specify a parameter after that parameter, you must delimit the omitted parameter with a semicolon.

NOTES:

If you want calc method actions to be available on inserted calc method rows within a sheet (whether inserted via Axiom query or by a user), then the calc method itself must be designed so that the InsertCM tag is saved as part of the calc method.
If the calc method is a multiple-row calc method, and you want to allow users to change it, it is not necessary to tag all rows with an InsertCM tag. Typically, only the first row is marked with an InsertCM tag and enabled for change calc method, but any tagged row within the calc method will trigger the ability to change the calc method.
The InsertCM tag can be placed within a formula if desired. If you are using dynamic insertion, it is essentially a requirement (so that the tags can adjust dynamically based on the user's dimension selection). In this case, you may want to use the right-click helper dialog to construct the initial tag, and then modify the tag to convert it to a formula. Once the tag is within a formula, you will be unable to make further modifications using the right-click dialog.

Examples for use with a standard calc method control column (InsertCMColumn)

[InsertCM;]

In this example, users can manually insert or overwrite using any calc method at this location. Because no insertion point label is defined, no text appears on the Add Row(s) menu for this location. Note that if you omit all other parameters, you must include the first delimiting semicolon.

[InsertCM; Insert New Employee]

In this example, users can manually insert or overwrite using any calc method at this location. Users can also insert at this location by selecting the "Insert New Employee" insertion point from the Add Row(s) menu.

[InsertCM; Insert New Employee; Add New Salaried Employee, Add New Hourly Employee; ; Insert]

In this example, users can insert one of the two specified calc methods at this location. The GroupList parameter is not used, and is delimited with an "empty" semicolon. Users can only insert calc methods at this location, as specified by the AllowedAction parameter. Overwriting is not allowed.

[InsertCM; Add Employees; ; Payroll]

In this example, users can insert or overwrite using any calc method that belongs to the Payroll group. The CalcMethodList parameter is not used, and is delimited with an "empty" semicolon.

[InsertCM; ; Input Monthly; Budget ;Change]

In this example, users can change calc methods using the Input Monthly calc method, or using any calc method that belongs to the Budget group. Because users cannot insert at this location, the InsertionPointLabel parameter is not used, and is delimited with an "empty" semicolon. In order to change the calc method, a user must manually place the cursor at this location and select Add Row(s) > Change Calc Method.

[InsertCM; Insert New Employee; New Employee; ; Insert; Above; 5]

In this example, only one calc method, New Employee, is allowed for insertion at this location. When the user selects this insertion point, the specified calc method is automatically inserted at the location. The Insert Calc Method dialog does not display (even if the user is an administrator). However, this example also uses the MaxInsertCount parameter, so the user will first be prompted to specify how many instances of the calc method to insert (up to 5).

Examples for use with a dynamic insertion control column (DynamicCMColumn)

In the following examples, the current row is being identified as the active insertion row by the presence of the DynamicMarker text "InsertHere" in the final parameter. This assumes that the DynamicCMColumn contains the same DynamicMarker text. If the DynamicCMColumn does not use a dynamic marker, then the described actions would only apply if the row is identified as the active row by being the first row marked with InsertCM in the column.

[InsertCM; ; Budget Detail; ; Insert; Below; 0; InsertHere]

In this example, the row is enabled for insert, and only the Budget Detail calc method is eligible for insert, so the user will not be prompted to select a calc method. The MaxInsertCount parameter is used so that the user will be prompted to specify how many items to insert; since the number is 0, the user can insert an unlimited number of detail rows. Finally, since the InsertLocation is specified as below, the rows will be inserted below the current row (instead of the default behavior of above).

[InsertCM; ; ; ; GoTo; ; ; InsertHere]

In this example, the AllowedAction parameter is set to GoTo, so the user is taken to the current row and no calc method is inserted. Since calc method insertion is not possible on this row, the CalcMethodList, GroupList, InsertLocation, and MaxInsertCount parameters are irrelevant and therefore left blank. This type of tag is intended for use on existing rows, so that if a user selects a dimension item that already exists in the sheet, they are simply taken to the existing row.

[InsertCM; ; ; BudgetGroup; Change; ; ; InsertHere]

This example is similar to the previous example, except in this case the AllowedAction is Change instead of GoTo. The dynamic insertion behavior is the same: the user is taken to the current row and no calc method is inserted. However, if the user has Allow Calc Method Change permissions, they have the additional option of changing the existing row with another calc method (in this case, any calc method that belongs to the BudgetGroup). The user must explicitly go to the Add Row(s) menu if they want to perform the change; the dynamic insertion process does not open the Change Calc Method dialog or prompt the user in any way.