Loading Variant Specifications from a Spreadsheet
Standalone variant specifications for a configurable module, standalone variant specifications for an Option Set, and Managed Option Filters can be loaded from a formatted spreadsheet using a Windchill command-line utility. This utility provides the ability to load one or more variant specifications supporting the ability to create or update standalone variant specifications and managed Option Filters. This utility relies on the API that is used to programmatically load variants specifications and rules processing is applied. Choices are selected in their defined order and are considered user selected unless rule driven, selected by a choice specified earlier in the order.
 |
Ability to load variant specifications from a spreadsheet is an advanced capability subject to a separate license entitlement.
|
The following steps are required to load variant specifications from a spreadsheet:
1. Creating the Spreadsheet Loader File
2. Executing the Loader Utility
3. Reviewing the Resulting Log Files
Use the VariantSpecLoader1.xlsx file that is located in the WT_HOME\loadFiles\ato\testdata\variantSpecLoader directory, as a template when creating a spreadsheet to be loaded.
The spreadsheet contains 3 worksheets for entering information related to the variant specification, part configuration specification, and configuration specification for Option Filter:
• Variant Specification worksheet is used to specify the basic information about the variant specification and includes the selected choice information.
• Part Configuration Specifications worksheet is used to specify one or more part configuration specifications used by one or more variant specifications.
• Option Filter Configuration Specifications worksheet is used to specify one or more Option Filter configurations used by one or more variant specifications.
Variant Specification Worksheet
The Variant Specification (Variant Specification) worksheet provides information about the necessary to create or update the variant specification and references the Part Configuration Specification and Option Filter Configuration Specification information on the additional worksheets. This worksheet has the following fields:
Variant Specification Section
|
Field
|
Description
|
|---|---|
|
Name
|
Name of the variant specification. Name is required to create a new variant specification.
|
|
Number
|
Number of the variant specification. Number is required to update a variant specification.
When both name and number are provided and a variant specification matching this information exists, the variant specification is updated. If no variant specification matching the name and number is found, the variant specification is created.
When only number is provided, the variant specification matching that number is updated.
|
|
Type
|
Variant specification soft type to be created. This is an optional field.
|
|
Description
|
Provide the description for the variant specification (upto 2000 characters).
|
|
Location
|
Location to create a variant specification. This is a required field. Location is not applied when updating an existing variant specification.
|
|
Create Variants
|
For a standalone variant specification associated with a configurable module, module variants are created for the variant specification only when this field set to true. Setting it to false or leaving it blank does not generated module variants.
For a standalone variant specification associated with an Option Set or a Managed Option Filter, the value must be false or blank as variant generation is not supported.
|
Part Section
Information in this section must be provided only when creating a standalone variant specification associated with a configurable module.
|
Field
|
Description
|
|---|---|
|
Number
|
Part number of the configurable module for a standalone variant specification associated with a configurable module. For example, BICYCLE1 for a part identified as BICYCLE1, Bicycle1, Demo Organization, A.1(Design).
|
|
Owning Organization
|
Owning organization of the configurable module for a standalone variant specification associated with a configurable module. For example, Demo Organization for a part identified as BICYCLE1, Bicycle1, Demo Organization, A.1(Design).
This is required only when the same part number is used to identify different parts owned by different organizations.
|
|
Revision
|
Revision of the configurable module for a standalone variant specification associated with a configurable module. For example, A for a part identified as BICYCLE1, Bicycle1, Demo Organization, A.1(Design).
|
|
Iteration
|
Iteration of the configurable module for a standalone variant specification associated with a configurable module. For example, 1 for a part identified as BICYCLE1, Bicycle1, Demo Organization, A.1(Design).
|
|
View
|
View of the configurable module for a standalone variant specification associated with a configurable module. For example, Design for a part identified as BICYCLE1, Bicycle1, Demo Organization, A.1(Design).
This is required only when the same version of the part exists for more than one view.
|
Option Set Section
Information in this section must be provided only when creating a standalone variant specification associated with an Option Set or a Managed Option Filter.
|
Field
|
Description
|
|---|---|
|
Name
|
Name of the option set for the standalone variant specification associated with an Option Set or a Managed Option Filter.
|
|
Revision
|
Revision of the option set. The latest iteration of the revision will be used.
|
Navigation Criteria Section
|
Field
|
Description
|
|---|---|
|
Apply to top level object
|
For a standalone variant specification associated with a configurable module, the required attribute determines if the configuration specification is applied to the configurable module.
For a standalone variant specification, the required attribute determines if the configuration specification is applied to the top-level object.
It does not apply to a Managed Option Filter and must be blank.
|
|
Part Configuration Specification
|
For any standalone variant specification, it identifies the part configuration specification(s) to be included in the variant specification:
• One or more configuration specification names, from the Part Configuration Specifications tab, must be used to identify the part configuration specifications for the variant specification.
• For a standalone variant specification associated with a configurable module, a blank part configuration specification defaults to a latest configuration specification, without a life cycle state, for the view specified for the configurable module.
• For a standalone variant specification associated with an Option Set, a blank part configuration specification defaults to a latest configuration specification, without a life cycle state, for the Design view.
For a Managed Option Filter, the Part Configuration Specification must be blank.
|
|
Option Filter Configuration Specifications
|
Identifies the option filter configuration specification(s) to be included in the variant specification:
• One or more configuration specification names, from the Option Filter Configuration Specifications tab, must be used to identify the option filter configuration specification for the variant specification.
• A blank option filter configuration specification defaults to a latest option filter configuration specification without a life cycle state.
|
|
Disable Rule Checking
|
Determines whether the rule checking must be disabled for the variant specification. When no value is specified, False is used by default.
|
|
Filter Mode
|
Determines the filter mode for the variant specification. The first filter mode, commonly standard, is used when no value is provided. For more information, see Configuring Standard and Alternate Behaviors.
|
|
Start Options Column
|
The empty column represents the starting of the definition of option columns for the selected choices for each variant specification to be loaded.
|
|
Additional Columns
|
Provide one column per option whose choices you want to select for the Option Filter. Either Name or Number can be provided to identify an option or a choice.
For example, if you want to select ChoiceA1, ChoiceA3, and ChoiceA5 from OptionA, and ChoiceB4 from Option B, two columns following the Start Options column are required—OptionA and OptionB. On the variant specification information row, enter a coma-separated list of ChoiceA1, ChoiceA3, and ChoiceA5 under OptionA, and ChoiceB4 under OptionB. If the next row (that is, the next variant specification) has choices selected from a different option, create a new column with the name of the option and the respective choices in the cell under it for the row you are populating. Cells under OptionA and OptionB can be left blank for this row.
To enter a boolean choice name, you can either use the internal choice name OptionName_TRUE or OptionName_FALSE, or specify the true or false values.
|
|
End Options
|
This is the last column in the worksheet. It marks the end of all option and choice selections for the Option Filter.
|
Part Configuration Specifications Worksheet
The Part Configuration Specifications (Part Config Specs) worksheet provides information for specify one, or more, part configuration specifications to be loaded with the variant specifications. The part configuration specification is similar to the Configuration Specification tab of the Configure window and provides the ability to load a subset of the definitions possible in the interactive system. This worksheet provides the ability to load a Latest, Baseline, and a Date Effectivity Configuration Specification.
|
Field
|
Description
|
|---|---|
|
Configuration Specification Name
|
Name to identify the part configuration specification within the spreadsheet. Any name specified in the Part Configuration Specification column on the Variant Specification tab must match the name for a definition on the Part Configuration Specifications tab. When a name is defined without any values in the remaining column, a latest part configuration specification is used without lifecycle state or view. When a name is defined and values are specified, the following part configuration specifications are defined:
• Latest: Lifecycle State and View
• Baseline: Baseline
• Date Effectivity: Effectivity Date
|
|
View
|
View to be used for a latest part configuration specification.
|
|
Lifecycle State
|
Lifecycle state to be used for a latest part configuration specification.
|
|
Baseline
|
Number of an existing baseline for a baseline part configuration specification.
|
|
Effectivity Date
|
Effectivity date for a date effectivity part configuration specification.
|
Option Filter Configuration Specifications Worksheet
The Option Filter Configuration Specifications (Option Filter Config Specs) worksheet provides information defining one or more Option Filter configuration specifications. The Option Filter configuration specification is similar to the Set Configuration Specification action found on the Option Filter tab of the Configure window and provides the ability to load a subset of the definitions possible in the interactive system. This worksheet provides the ability to load a Latest and a Date Effectivity Configuration Specification.
|
Field
|
Description
|
|---|---|
|
Configuration Specification Name
|
Name to identify the option filter configuration specification within the spreadsheet. Any name specified in the Option Filter Configuration Specification column on the Variant Specification tab must match the name for a definition on the Option Filter Configuration Specifications tab. When a name is defined without any values in the remaining column, a latest option filter configuration specification is used without a lifecycle state. When a name is defined and values are specified, the following part configuration specifications are defined:
• Latest: Lifecycle State
• Date Effectivity: Effectivity Date
|
|
Lifecycle State
|
Life cycle state to be used for a latest option filter configuration specification.
|
|
Effectivity Date
|
Effectivity date for a date effectivity option filter configuration specification.
|
Loading the Variant Specification
To load variant specifications from a spreadsheet:
1. Create a spreadsheet using the template VariantSpecLoader1.xlsx available in WT_HOME\loadFiles\ato\testdata\variantSpecLoader.
2. Run the following command from the Windchill command-line utility:
Windchill com.ptc.windchill.option.variantspec.loader.VariantSpecLoader -d <My_Spreadsheet_Path>\<My_Spreadsheet>.xlsx -u <my_user> -p <my_password>
3. Review the output file located in WT_HOME\logs to resolve any errors.
Output File
When the utility is run to import a spreadsheet, the system creates an output file. The output files are located in the log folder WT_HOME\logs and are named VariantSpecLoaderReport_<random_generated_number>*.
The output file contains the following information:
• The row number on navigation criteria worksheet that is being processed.
• The operation status: SUCCESS or FAILED.
• The variant specification number (if the variant specification was successfully created).
• CREATED—If a new variant specification was created, CREATED is set to true. Otherwise, CREATED is set to false.
• UPDATED—If the variant specification is an existing one, then UPDATED is set to true. This means that the variant specification was reconfigured based on the variant specification number provided in the spreadsheet.
• ERROR—Reports errors during the validation while processing the spreadsheet or errors encountered while creating objects. If an error is encountered, the utility does not process that row, records the error in the report, and moves on to the next row.
• WARNING—If warnings or assumptions were made while processing the row, they are logged as warnings on the report.
Guidelines for Using a Spreadsheet for Loading Variant Specifications
Consider the following information:
• Use only .xlsx (default Microsoft office Excel 2007) spreadsheet format to load variant specifications.
• Note that in UNIX, a windchill command to load variant specifications is case-sensitive. Use the following syntax:
windchill com.ptc.windchill.option.variantspec.loader.VariantSpecLoader -d <My_spreadsheetpath>
• When you modify a variant specification using a spreadsheet, entering Create Variants = True generates a new variant (by overriding an existing variant) even if there is no change in the structure and choice selections for the variant specification.
• Creation of the variant specification using a user-defined variant specification number is not supported using the out-of-the-box API or spreadsheet.
• To provide the folder location for the variant specification in the spreadsheet, you can use all folders created in the /Default cabinet of that product, for example, /Default/ABC.
• To modify an existing variant specification, you must specify the part with which the variant specification is associated. Do not specify an option set in such cases.
• Modifying the variant specification based on its version is not supported. The latest version is always reconfigured.
• Follow these guidelines for special characters:
◦ Choices with double quotes in their names must be preceded with a backslash “\”, for example, “\a”1\”.
◦ Choices with special characters in their names must be included in double quotes, for example, “a_1”.
Running the Utility in the Debug Mode
You can run the utility in the debug mode that creates a log4j file in the wt_home\logs folder that you can use for debugging errors. The log file records errors for each row of the spreadsheet that is being processed.
To use the utility in the debug mode, use the following syntax:
Windchill --jap=wt.properties?com.ptc.windchill.option.variantload.java.args com.ptc.windchill.option.variantspec.loader.VariantSpecLoader -d <My_Spreadsheet_Path>\<My_Spreadsheet>.xlsx -u <my_user> -p <my_password>