Project: Variant Editor

Automatic Deriving of Variants

The Variant Editor possesses an automatism that enables you to automatically create derivations of already existing variant files. These can be both variants with constant and with varying sub-types. Furthermore, you can also change the part type ID, and thus also the classification of a VAA and a PAA file when automatically deriving variants.

The prerequisites for this automatism are as follows:

• The customer manages a list of parts in a file that will be referred to as customer_list.csv in the following paragraphs(instead of customer_list you can also use a different file name of your choice). In this list the parts can, for example, be assigned a material, which is usually not the case with the variants that are supplied together with HiCAD. In such as case the Variant Editor will help create derived variant files with an specific material.
• An attribute_assignmenmt.csv file must exist, determining which values from the the customer_list.csv file are to be assigned to the attributes of a derived variant file (instead of attribute_assignment you can also use a different file name of your choice).

The following paragraphs will explain to you how to proceed and how to structure the files:

• Deriving variants with constant sub-types
• Using existing article masters
• Changing the part type ID / Classification
• Automatic deriving of variants with varying sub-types

Deriving variants with constant sub-types

Call and Procedure

For this automatism the calling parameter autoderive will be used as follows:

VariantenEditor.exe /autoderive path\to\attribute_assignment.csv path\to\customerlist.csv [path\to\material_blacklist.txt]

Furthermore, the following files are required for the call:

• customer_list.csv
  The file customer_list.csv is a list provided by the customer that describes the utilized parts, expanded by one additional column where the variant file matching the entry is indicated. Such an additional column can be created automatically by calling the Variant Editor with the parameter autovaa.

Foe each line of the file customer_list.csv a variant file will be derived. The attributes of the derived variants will be assigned values according to the entries of the file attribute_assignment.csv. This file consists of the following entries:

HELiOS Attribute name; Column name

The HELiOS Attribute name designates an attribute of the variant, the column name designates a column of the file customer_list.csv.

• attribute_assignment.csv
  This file determines which values from the file customer_list.csv are assigned to the attributes of a derived variant file. If none of the columns in the file customer_list.csv is suitable for a source of the file names for the derived variant file, an additional column needs to be added.

The result of a variant derivation includes

• the variants,
• a list of all created variant files (.lst). This file can be used in the DBPlantDataImport.exe program to enter all variants into a HELiOS database.
• a file called customer_list_autoderive.csv, which is identical with the file customer_list.csv, with the difference that each row has an additional column indicating either success or error.

Furthermore, you have the option to specify a file called material_blacklist.txt when performing the command line call with the parameter autoderive. This file contains a list of HEL_IDs belonging to materials. Since Material is a so-called User Type Object (UTO), it may be specified by an ambiguous sub-attribute. An example of this is the material number (MATERIAL::WN). In such cases the file material_blacklist.txt will be used that contains a list with HELiOS material IDs. During deriving, only the material that is not contained in the blacklist will be used. If a material cannot be clearly determined despite the blacklist, no variant will be derived.

Example:

The material number 1.0308 is ambiguous. The HELiOS material list contains the following entries for this material number:

A blacklist could, for instance, look as follows:

ZM1_1I
ZM1_3K
ZM1_40
ZM1_3I
ZM1_3L
ZM1_3J
ZM1_30

Only the material with the ID ZM1_3H would be used here, as it is not contained in the list.

The file attribute_assignment

The file attribute_assignment.csv determines which column of the file customer_list.csv is to be assigned to which attribute of a derived variant.

The file may contain further parameters that control the variant deriving procedure. These are described via attributes beginning with the prefix VAREDIT_.

The lines in the attribute assignment file will be treated equal. This means that the value that follows an attribute will be initially interpreted as a reference to the file customer_list.csv. If you do not want this, you need to precede the value with the character that has been determined via VAREDIT_VERBATIM_MARKER. An example of such a file could look as follows:

Important:

• When exporting material numbers from Excel to a CSV file and import them again, these will become unusable, as Excel will misinterpret the point in the material number.
• Remedy 1:
  Instead of Open, select the Get external data > From Text function.In this way you can specify the format of the columns.
• Remedy 2:
  The line VAREDIT_OUTPUT_FORMAT;#SYLK in the attribute description ensures that the modified data will be written as SYLK file. For such a file Excel will not change the formatting during reading.
• If the Material is assigned to a variant via the material number, the synchronization may fail. The reason for this is that the material number is not necessarily unambiguously assigned to one material. If you want to be notified of such errors already during variant derivation, specify the appropriate value for the pseudo attribute VAREDIT_STOP_ON_UTO_ERROR.

Example

Step 1: Create an extended customer list and convert it into a CSV file

Required is the customer list with one additional column containing the names of the HiCAD VAA files.

This extended list can also be automatically created with the calling parameter autovaa !

ISD_customer_list_1.xlsx

ISD_customer_list_1.csv

Order number;Part;Assembly;Order designation;Standard EN;DINStandard (old);Description;Material number;HiCAD-VAA
W3-ISD-01;BT0011;BG-W3;Elbow, 90° Typ 3D;EN 10253-2;DIN 2605;Design B;1.0308;EN10253-2-B-3D_ELBOW
W3-ISD-01;BT0015;BG-W3;Elbow, 90° Typ 3D;EN 10253-2;DIN 2605;Design B;1.0308;EN10253-2-B-3D_ELBOW
W3-ISD-01;BT0026;BG-W3;Elbow, 90° Typ 3D;EN 10253-2;DIN 2605;Design B;1.0308;EN10253-2-B-3D_ELBOW
W3-ISD-01;BT0027;BG-W3;Elbow, 90° Typ 3D;EN 10253-4;DIN 2605;Design A;1.0308;EN10253-4-A-3D_ELBOW
W3-ISD-01;BT0028;BG-W3;Elbow, 90° Typ 3D;EN 10253-4;DIN 2605;Design A;1.0308;EN10253-4-A-3D_ELBOW
W3-ISD-01;BT0039;BG-W3;Elbow, 90° Typ 3D;EN 10253-4;DIN 2605;Design A;1.0308;EN10253-4-A-3D_ELBOW
W3-ISD-01;BT0040;BG-W3;Elbow, 90° Typ 3D;EN 10253-4;DIN 2605;Design A;1.0308;EN10253-4-A-3D_ELBOW

Step 2: Create the attribute designation file: ISD_attribute_assignment.csv

VAREDIT_VERBATIM_MARKER;#
VAREDIT_BASE_VAA;HiCAD-VAA
VAREDIT_DERIVED_VAA;Part
VAREDIT_OVERWRITE_EXISTING_VAA;#1
VAREDIT_LANGUAGE_CODE;#1031
VAREDIT_BASE_DIR;#D:\hicad\PlantParts
VAREDIT_DERIVED_DIR;#D:\temp\derived
VAREDIT_STOP_ON_UTO_ERROR;#1
VAREDIT_LST_FILE_NAME;#AutoDerived
VAREDIT_OUTPUT_FORMAT;#SYLK
MATERIAL::WN;Material number
BESTELLVERMERK;#Elbow-<WINKEL>°-<D_AUSSEN>x<WANDDICKE>

Step 3: Create a blacklist for the material: ISD_material_blacklist.txt

ZM1_1I
ZM1_3K
ZM1_40
ZM1_3I
ZM1_3L
ZM1_3J
ZM1_30

Step 4: Call the Variant Editor:

Now call the Variant Editor in the HiCAD exe directory via command line prompt:

VariantenEditor.exe /autoderive d:\temp\ISD_attribute_assignment.csv D:\temp\ISD_customer_list_1.csv d:\temp\ISD_material_blacklist.txt

The Variant Editor will be started and the variants will be created in the specified directory (here: D:\temp\derived).

The result:

• the variants and the archive file list

AutoDerived.lst
BT0011.VAA
BT0015.VAA
BT0026.VAA
BT0027.VAA
BT0028.VAA
BT0039.VAA
BT0040.VAA

• a file called ISD_customer_list_1_autoderive.csv that is identical with ISD_customer_list_1.csv, with the difference that each row has an additional column indicating either success or error.

Step 4: Start the DBPlantDataImport tool and open the file AutoDerived.lst

To enter the derived variants into the database, first copy the LST and the VAA files to the HiCAD PlantParts directory. Then, start the DBPlantDataImport tool in the HiCAD exe directory, choose the file AutoDerived.LST and start the import.

Article master for BT0011.VAA

Using existing article masters

If Plant Engineering variants with existing HELiOS article masters are to be used for derivation, the Head IDs can also be directly provided in the customer_list.csv file.

For this purpose the new pseudo attributes

• VAREDIT_SUB_ID
• VAREDIT_GENERAL_ID and
• VAREDIT_MOVE_ON_SUCCESS

are available.

An example:

The following CSV file describes two VAAs with three sub-types each , and one PAA. Upon each entry HiCAD will check whether a corresponding VAA file exists. If it does not exist, it will be assumed that the entry is a PAA. The sub-types of the VAAs are identified here via their Type ID here (HELiOS attribute HEL_TYPNAME. For each sub-type, one Head ID is provided for the general type, and one Head ID for the sub-type itself.

In the attribute mapping file attributzuordnung.csv (=attributemapping.csv) these data can be mapped to the VAA attributes as follows:

The pseudo attribute attributes VAREDIT_SUB_ID and VAREDIT_GENERAL_ID ensure that the Head IDs from the customer_list.csv file are transferred into the VAAs.

The pseudo attribute VAREDIT_MOVE_ON_SUCCESS ensures that, after successful generation of all files, these files will be moved from DERIVED_DIR to BASE_DIR. For files that already exist in BASE_DIR, a backup with the file extension .bak will be created.

To transfer the VAA files prepared in this way to HELiOS, the tool DBPlantDataImport.exe must be called with the parameter /X. This allows a Plant Engineering compliant adjustment of existing article masters.

• It is assumed that document masters may be created. These are created for each VAA.
• The links between the article masters and to the document masters will be added.
• Article attributes will be overwritten. The values for attributes that are not relevant for Plant Engineering will remain unchanged.

The tool DBPlantDataImport.exe allows the removal of already transferred variants. When DBPlant-DataImport.exe is called with the parameter /X, only the document master will be removed. Article masters will remain unchanged.

Changing the part type ID / Classification

From HiCAD 2019 SP1 onwards, you can also change the part type ID, and thus also the classification of a VAA and a PAA file when automatically deriving variants.

The attribute assignment file (attribute_assignment.csv) supports the pseudo attribute VAREDIT_KEEP_DB_IDS for this purpose.

If this attribute has the value 1, the previous HELiOS IDs will be preserved during automatic deriving. i.e. no "actual" deriving takes place. From a HELiOS perspective, the resulting VAA or PAA file refers to the same parts. This is helpful for the adjustment of existing VAAs or PAAS, especially in conjunction with VAREDIT_MOVE_ON_SUCCESS.

Also, it is now possible to map the attribute ARTSCHLUESSEL (Type key) to a column in the file customer_list.csv. In this column the part type ID of the VAA or PAA file must then exist in the format AAAAA_BBBBBBB, with AAAAA being the coding for the industry according to anbtlken_top.dat and BBBBBBB the ID according to anbtlken.dat.

If the format should not be correct, the message

Classification not in the format AAAAA_BBBBBBB, with A = Industry and B = ID

will be issued in the output column.

Automatic deriving of variants with varying sub-types

It is also possible to modify sub-types with regards to the automatic derivation. The variants call and procedure take largely place in the same way as the automatic deriving of variants with constant sub-types. However, additional attributes are available for varying sub-types:

• • VAREDIT_CLUSTERING_KEY
    One list of columns of the CSV-file is being assigned to the attribute, which is checked for equality.
  • VAREDIT_SUBTYPE_KEY
    This attribute can be assigned as value to a series of HELiOS attributes, which determine the columns to be compared. By contrast to VAREDIT_CLUSTERING_KEY, the CSV file's names of columns are not considered here.

Example:

The CSV file with VAA assignment should look as follows:

The derived VAA files should receive the file name from the column Part. But here, the description appears twice. The derivation automatism is able to assort these columns and treats them as separate sub-type of a variant. For this, it needs to be specified which attributes may be considered for the groupage.

For this example,

VAREDIT_CLUSTERING_KEY;Part

would be an useful assignment.

Another option would be

VAREDIT_CLUSTERING_KEY;HiCAD-VAA.

If you want to apply a derivation for each nominal diameter, you could assign the attribute with

VAREDIT_CLUSTERING_KEY;Part;Nominal diameter

In this case,an additional column with explicit descriptions would be certainly necessary because the column Part does not provide this information.

In case if there are multiple sub-types after a groupage, the attributes of the existing sub-types with the values of the table will be overwritten. For this you need to determine which sub-type of the initial VAA belongs to the sub-type column of the CSV file. This assignment takes places via the attribute VAREDIT_SUBTYPE_KEY. This attribute can be assigned as value to a series of HELiOS attributes, which determine the columns to be compared. By contrast to VAREDIT_CLUSTERING_KEY, the CSV file's names of columns are not considered here.

In this case,

VAREDIT_SUBTYPE_KEY;NENNWEITE

would be a useful assignment.

To ensure a correct assignment to the column,

NOMINAL DIAMETER;Nominal diameter

has to be added to the attribute assignment file.

The overall attribute assignment file for this example looks as follows:

The result of the derivation with this attribute assignment file is a SLK file with the name of the initial CSV file enhanced with autoderive. The content of this SLK file is presented as follows:

The file is assigned in so far as that the columns belonging to a variant stands below. An additional column with a status notification is added.

The status notification consists of three parts:

1. In the beginning, a serial number appears which groups the sub-types of the variant.
2. Then, a notification follows which concerns the whole variant.
3. Finally, a notification in square brackets appear which concerns the sub-type.

The error messages which can occur in correspondence to sub-types are

• Error: An error occurred during sub-type creation. [Sub-type could not be assigned.]

Meaning: This variant has no matching sub-type to VAREDIT_SUBTYPE_KEY

• Error: An error occurred during sub-type creation. [Sub-type has multiple assignments.]

Meaning: VAREDIT_SUBTYPE_KEY is not explicit

• Error: An error occurred during sub-type creation. [Sub-type was skipped.]

Meaning: The processing has been cancelled due to an occurred error in the preceding sub-type

If a derivation does not consist of separate sub-types, the initial VAA will be derived with all sub-types. In this case, only constant attributes are modified. You recognise such derivation in the result table for its status notification which does not include anything in terms of the sub-type, meaning that the notification status does not contain squared brackets.

If a derivation contains sub-types, only these ones are being overtaken in the VAA file. In the above-mentioned example, all derivations consist of only two sub-types. There are only two exceptions, which either could not be created on account of not assignable sub-types or the derivation has been completed.

The User Interface • Derive New Variant • Settings