Loading Variant Specifications from a Spreadsheet
Multiple variant specifications can be loaded from a spreadsheet using a Windchill command-line utility. This utility relies on the API that is used to programmatically load variants specifications.
|
|
To use this capability:
• The Platform Structures module must be installed.
• You must be a Site Administrator or a member of the Platform Structures group.
|
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
A template VariantSpecLoader1.xlsx for the variant specification spreadsheet loader file is located in the WT_HOME\loadFiles\ato\testdata\variantSpecLoader directory.
The spreadsheet contains 3 worksheets for entering information related to the variant specification, the part configuration specification, and the option filter configuration specification:
• Part Configuration Specification worksheet (Part Config Specs)— Information for this worksheet is used to specify some of the configuration specifications for a part similar to Configuration Specification tab of the Configure window. This worksheet has 5 fields: Configuration Specification Name, View, Life Cycle State , Baseline, and Effectivity Date. The Name is required. Values for View must be valid entries corresponding to the View field on the Configuration Specification tab on the Edit Filter window. The system validates the values you enter for a Latest, Baseline, or Date Effectivity Configuration Specification.
• Option Filter Configuration Specification worksheet (
Option Filter Config Specs)–This worksheet defines the configuration specifications for the option filter. This worksheet has 3 fields:
Configuration Specification Name,
Life Cycle State, and
Effectivity Date. The information for this worksheet is used to specify the configuration specifications for the option filter (available from the
Option Filter tab of the
Edit Filter window by clicking the
icon). The system validates the values for a Latest or Date Effectivity configuration specification.
• Variant Specification worksheet (Variant Specification)– Provide information on the choice rules and selected choices to capture the selections in the Option Filter. The worksheet has the following fields:
◦ Variant Specification section:
▪ Name–Enter the name to assign to the variant specification. Name is required to create a new variant specification.
▪ Number–Use this column only when you want to modify an existing variant specification. If a valid number is provided, and the variant specification with this number exists, the persisted name is used. Number is required to update an existing variant specification.
| Either Name or Number must be provided, not both. |
▪ Description – Provide the description for the variant specification (up to 2000 characters).
▪ Location– Specify the location where to store the variant specification. If this field is empty for the variant specification creation, the utility issues an error. However, it does not apply when updating an existing variant specification.
▪ Create Variants– When set to true, the system creates module variants for the variant specification. Because the creation of module variants is asynchronous, the utility only notes the success/failure of the creation of the queue entry for each variant and not the actual variant. When this field is set to false or is blank, the utility only creates the variant specification.
| This information is not required for a variant specification associated with an option set. |
◦ Part section–This section provides details of the part to configure using the variant specification. The utility expects that a part has an assigned option set. The row is not processed if there is no assigned option set.
▪ Number: Number of the part (for example, Bicycle1).
▪ Owning Organization: Owning organization that the part belongs to. This is an optional column when it is not required to uniquely identify a part when the same part number is used by two different organizations.
▪ Revision: Revision of the part (for example, A).
▪ Iteration: Iteration of the part (for example, 1). In this example, the part is Bicycle1 A.1.
▪ View: View for the part version (for example, Design). This is an optional column when it is not required to uniquely identify a part view version (for example, Part 123 A.1 (Design) or Part 123 A.1 (Manufacturing)).
| If you provide information for the part, then the information about the option set may not be defined. You must provide information either for the Part or Option Set field, not both. |
◦ OptionSet section—The variant specification is created using an option set. Such variant specification is created with the utility if a part was not specified for that row.
▪ Name—Name of the option set.
▪ Revision—Revision of the part (for example, A).
▪ Iteration—Iteration of the part (for example, 1).
| If you provide information for the option set, then the information about the part may not be defined. You must provide information either for the Part or Option Set field, not both. |
◦ Navigation Criteria section —This section captures the information specified on the Configuration Specification tab and the Option Filter tab of the user interface:
▪ Apply to Top—Setting this attribute to true is equivalent to checking the same box on the Configuration Specification tab on the Configure window. When Apply to Top is set to true, the system applies the configuration specification to the top-most part on the part structure provided.
| This information is not necessary for a variant specification that is associated with an option set. If this information is provided, the validator reports an error. |
▪ Part Configuration Specification—Provide a list of part configuration specifications that have already been loaded using the worksheet 1. You can provide multiple configuration specifications by separating them with a coma (,). If a part was specified in the column F-H, and this column is left blank, the utility loads the Latest part configuration specification and uses it to create the variant specification.
▪ Option Filter Configuration Specifications —Provide a list of the Option Filter configuration specifications that have already been loaded using the worksheet 2. You can provide multiple configuration specifications by separating them with a coma (,). When this information is left blank, the utility loads the latest Option Filter configuration specification and uses it to create the variant specification.
▪ Disable Rule Checking—By default, this is set to false. When set to true, it is the equivalent of checking the box on the Configure window.
▪ Filter Mode—This is similar to the drop-down list on the Option Filter tab (where Standard or Alternate can be selected).
◦ Start Options column—The empty column represents the starting of the definition of choices to select for the Option Filter.
◦ 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 filling out. Cells under OptionA and OptionB can be left blank for this row.
| If a version of the same variant specification exists in the system, you need to refer to the variant specification by its number. |
◦ End Options—This is the last column on the worksheet. It marks the end of all option and choice selections for the Option Filter.
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 you run the utility 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”.
Spreadsheet Validation
The following validations are performed when a spreadsheet is being processed:
• For the variant specification, either Name or Number must be specified, not both.
• The variant specification with the specified number must exist.
• To modify an existing variant specification, it must continue to use a configurable module or an option set.
• Either the part or the option set can be specified, not both.
• When a part is specified:
◦ The part must exist.
◦ The part must be uniquely identified by using part and, optionally, organization.
◦ The part version must be uniquely identified by using revision, iteration, and, optionally, view.
◦ The part must have an assigned option set.
• When an option set is specified, it must exist.
• The folder must exist and must be specified when creating a new variant specification.
• The specified part configuration specification must be included in the spreadsheet definition.
• The specified Option Filter configuration specification must be included in the spreadsheet definition.
• User must be authorized to read any part version, organization, option set, option, and choice referenced in the spreadsheet.
• User must be authorized to create or modify the variant specification as defined in the spreadsheet.
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>
