AX1623

Using utility processing for plan files

You can configure plan file templates to enable "plan file processing with utilities" instead of traditional plan file processing. This means that when plan files are processed, a list of utilities is iteratively processed per plan file, using data for that plan code. This feature is primarily intended to be used with form-enabled plan files that use embedded forms, where the content of the form plan files is comprised of multiple embedded utility files instead of within the plan file itself.

For example, imagine that you have a plan file that uses Utility1, Utility2, and Utility3 as embedded forms for content. When users are working in the form plan file, they can switch between the embedded utilities by using a Menu component. They can input values in each utility and save data to the database. The embedded utilities are filtered to only show and save data for the current plan code, by use of shared variables.

Now imagine that a driver value in the file group has changed, so you want to process plan files to save updated data to the database. You cannot use the regular Process Plan Files feature for this purpose, because it only processes and saves the plan file. In this example, the plan file itself is only a "shell" that provides context for the embedded utility files. The utility files are where all of the data processing occurs for the plan code, so it is the utility files that need to be processed and saved. Therefore in order to process these plan files, you must perform iterative processing of utilities per plan code. In this example, this means that Utility1, Utility2, and Utility3 would be processed repeatedly—once for each plan code—in order to save the updated data for each plan code to the database.

Although plan files with embedded forms are the intended use case for this feature, "processing with utilities" can be used with any kind of plan file where regular utility processing per plan code is required. However in most other cases, using regular multipass file processing with the utility is usually sufficient, and easier to set up.

Setting up plan file utility processing

In order to use plan file processing with utilities, the plan file templates and the utility files must be set up as follows:

• Define the data source for utility processing. The plan file template (and therefore the resulting plan files) must contain a ProcessPlanFileUtilities data source. This data source lists the utility files to be processed, and the processing order. If the list of utilities varies per plan file, formulas can be used to dynamically enable and disable utilities as needed. Document variables can also optionally be passed to each utility for processing. For more information, see Creating ProcessPlanFileUtilities data sources.

• Use variables to pass the current plan code. Either shared variables or document variables must be used to pass the current plan code (and any other necessary values) from the plan file to the utility files. Shared variables can also be used to pass information from one utility to another utility that is later in the processing order.

  For plan files with embedded forms, the template and the utility files are already set up to use shared variables, because this is how the files share values in the forms environment. If you use utility processing with other types of plan files, then you can either choose to set up shared variables in the files just for utility processing, or you can choose to pass the plan code to each utility using document variables.

• Filter utilities by current plan code. The utility files must be set up to filter data queries as needed for the current plan code being processed (and any other necessary values). Utility processing does not automatically filter the utility files by the current plan code.

  You must manually set up the filters as needed using either shared variables or document variables.

  • When using shared variables, at minimum the template must contain a SetSharedVariable function to set the plan code value, and the utilities must contain a GetSharedVariable function to return the plan code value.

  • When using document variables, at minimum the ProcessPlanFileUtilities data source in the template must be set up to pass the plan code value as a document variable, and the utilities must contain a GetDocumentInfo function to return the plan code value.

  Again, when using plan files with embedded forms, the utilities are already set up to be filtered by the current plan code using shared variables, as that is how the utilities show the appropriate values in the forms environment.

Executing plan file utility processing

To execute plan file processing with utilities, use the Process Plan Files feature with the Process with Utilities option. You can select the plan files to process, and select which ProcessPlanFileUtilities data source to use (if there are multiple). For each plan file to be processed, the following occurs:

• The plan file is opened and refreshed, and shared variables are set.
• Each utility is opened and processed as follows:
  • Shared variables and document variables are passed to the utility.
  • Data queries are refreshed.
  • A save-to-database is performed.

For more information, see Processing plan files with utilities.

Enabling plan file utility processing as the default processing mode

If you have a file group where utility processing is the primary means of plan file processing, you can configure the file group so that it is the default processing mode. In the file group properties, on the Options tab, enable Process Plan Files with Utilities. Typically this would only be done for file groups that use form-enabled plan files with embedded forms.

Enabling this setting has the following impacts:

• When using Process Plan Files with the file group, the Process with Utilities option is selected by default.
• For plan file process definitions, if a step is configured to Save and validate plan file before advancing to next step, then utility processing will be executed for the plan file instead of executing a save-to-database on the plan file itself. A ProcessPlanFileUtilities data source must be flagged as the ProcessValidation data source in the plan file to be used for this purpose (or else the default data source is used).

Creating ProcessPlanFileUtilities data sources

If your plan files need to use plan file processing with utilities, the plan file template must contain a ProcessPlanFileUtilities data source. This data source specifies the utilities to be processed and the processing order. You can also optionally pass one or more document variables to the utilities to use during processing.

A plan file template can contain one or more ProcessPlanFileUtilities data sources as needed. When using Process Plan Files, you can select the data source to use for processing. When performing utility processing as part of plan file process step validation, one of the data sources must be marked as the data source to use for processing.

To create a ProcessPlanFileUtilities data source in a template:

• Right-click the cell in which you want to start the data source, then select Axiom Wizards > Insert Process Plan File Utilities Data Source. The current cell and any necessary cells below and to the right must be blank in order to insert the data source. This option is only available in template files.

The primary tag, column tags, and a row tag are placed in the sheet. From here, you can add more row tags and complete the column contents as needed.

The following screenshot shows an example ProcessPlanFilesUtilities data source. In this example Utility1 is processed first, followed by Utility3 and then Utility2. Utility2 is processed twice, using different values for the document variable Project.

Example ProcessPlanFileUtilities data source

The ProcessPlanFileUtilities data source uses the following tags:

Primary tag

[ProcessPlanFileUtilities;DataSourceName;DataSourceType]

The DataSourceName identifies this data source. When running Process Plan Files, you specify the data source to use by choosing the data source name. The name is required even if the template only has one data source.

The DataSourceType is optional, and can be used to flag a data source as one of the following types:

• Default: Flags the data source as the default data source for use with Process Plan Files. The default data source will be used for processing unless you select a different data source.

• ProcessValidation: Flags the data source to be used when validating the plan file as part of completing a plan file process step. This data source is used when Process with Utilities is enabled for the file group, and Save and validate plan file before advancing to next step is enabled for the step. If no data source is flagged as the ProcessValidation data source, then the default data source is used.

The placement of this primary tag defines the control column and the control row for the data source.

• All column tags must be placed in this row, to the right of the tag.
• All row tags must be placed in this column, below the tag.

Row tags

[Utility]

Each row flagged with this tag specifies a utility file to include in the processing. Utilities are processed in the order listed in the data source, from top to bottom.

Column tags

[UtilityPath]

The file path and name of the utility to be processed. If only a file name is listed with no path, the location is assumed as the \Utilities folder for the current file group.

For example, you can list a file as follows:

\Axiom\File Groups\Capital\Utilities\Utility1.xlsx

Utility1.xlsx (if the file is in the root of the Utilities folder)

SubFolder\Utility1.xlsx (if the file is in a subfolder of the Utilities folder)

If you choose to list the full path, it is recommended to use formulas to construct the path dynamically, so that the path will update if the file group is cloned and the utilities are copied to the new file group. For example, you can use the GetFileGroupProperty("UtilitiesPath") function to return the path to the Utilities folder for the current file group.

[IsEnabled]

Specifies whether the row is enabled for processing (True/False). By default, blank is interpreted as False, so you must enter True if you want the utility to be processed. If a row is flagged as False, that row is skipped for processing.

This column can be used to enable dynamic processing of utilities, so that a utility can be processed or not based on a condition. The data source is evaluated separately for each plan file being processed, so a formula could be used that resolves to True for some plan files, and False for others.

[DocumentVariables]

Defines one or more document variable / value pairs to pass to the target utility. Variable / value pairs are specified as follows:

Variable1=Value;Variable2=Value

Separate multiple variable / value pairs using semicolons. If a value contains a semicolon, then it must be preceded by a backslash (\) so that Axiom does not treat the semicolon as a delimiter. Equals signs within a value (such as to pass a filter criteria statement as a value) do not need to be specially treated.

When the utility is opened for processing, the designated document variables are passed to the target utility and can be returned in that file using GetDocumentInfo functions. For example, if the [DocumentVariables] column contains Project=Remodel, that value can be returned within the utility by using the following function:

=GetDocumentInfo("Variable","Project")

You might use document variables if you want to process the same utility repeatedly for the same plan file, using different values each time the utility is processed. The previous example screenshot processes Utility2.xlsx twice, using different values for the Project variable.

You might also use document variables if you are using utility processing with regular spreadsheet plan files, and therefore the plan files and utilities are not already set up with shared variables. In this case, document variables can be used as an alternative to pass the current plan code (and any other relevant values) into each utility file.