Compound Columns of Product Composition

Data of the product composition column can be filled by taking data from other columns and applying to it various mathematical and logical operators. To do so, you have to select the Compound column data assignment method in the Data Assignment drop-down list of the column properties dialog. Upon selecting this method, the column expression box appears below the drop-down list. The expression may consist of various records: names of other columns, arbitrary text strings, operators, parentheses. In the expression box records are shown as rectangles of different colors. Results of the expression will be used as column data in the Product Composition and Record Parameters windows. Records in the column expression box can be selected using . You can Delete the selected record or Add a new record after selected one, using buttons located at the right side of the box. If no records are selected, the new record will be added to the end of the expression.

Pressing the Add button invokes the drop-down list, where you have to select a type of new record. Upon placing a textual cursor into a rectangle of one of the existing records, you can add new records using hot keys. Following types of records are available:

•Column                                <Ctrl>+<P>
A content of another column will be used in expression. Click the button in the right side of the record, in order to select a name of the source column. Upon clicking a record of this type, it gets highlighted. Then you can start typing a name of a column. A column, whose name starts with the inputted symbols, will be selected in the list.

•Text                                <Ctrl>+<T>
An arbitrary text string will be used in expression. The content of the string should be inputted via keyboard.

•Parentheses:
  (        opening                <Ctrl>+<9>
  )        closing                <Ctrl>+<0>
Parentheses allow to set order of mathematical and logical operations.

•Mathematical operations:
+        Addition                <Ctrl>+<+>
  -        Subtraction                <Ctrl>+<->
x        Multiplication                <Ctrl>+<8>
/        Division                <Ctrl>+</>
%        Division remainder        
Mathematical operations with numerical records. Order of operations can be controlled by parentheses.

•Concat                Concatenation                <Ctrl>+<D>
Merging text strings. E.g. merging string "1" with string "2" results in "12".
CondConcat        Conditional concatenation        <Ctrl>+<S>
Merging is performed if both elements are not empty. The result of CondConcat operation for the Compound column is shown below.

•->        Conditional operator (if A, then B)        <Ctrl>+<I>
The B will be included in the column if A is not empty, not equal “0”, is “True”.  In particular it is useful to use this operator to control separation symbols,
In the example below the Name column is defined without conditional operator, so the separation symbol "-" is always displayed.

   

We may create condition to not display separations symbol when the column Part No is empty.

       

•Logical operators:
  &&                And                <Ctrl>+<6>
  ||                Or                <Ctrl>+<7>
  !                Not                <Ctrl>+<Q>
These operators allow to perform logical operations on the neighboring elements.
For example, with logical operation && you can output the separation symbol only when both Description and Material columns are not empty.

•Operators of substring search:
  StrSubstr                Contains substring                <Ctrl>+<Y>
  StrNotSubstr                Does not contain substring        <Ctrl>+<U>
  StrStartWith                Starts with substring                <Ctrl>+<L>
  StrEndsWith                Ends with substring                <Ctrl>+<O>
Check if the second textual string contain first textual string.
Example: If the Description column contains abbreviations of standards, they are displayed in the compound column Standard.

•Operators of string comparison:
  StrEq                        A equal B                <Ctrl>+<1>
  StrNotEq                A is not equal B        <Ctrl>+<2>
These operators perform comparison of text values. The result is “True” or “False”.
For example, you can set the condition: display value of Quantity if the field Parameter 1 is equal to Parameter 2.

If the condition is satisfied, the composite column contains data. If not - it will be empty.

•

•Operators of numerical comparison:
  ==        А equal В                <Ctrl>+<3>
  !=        А not equal В                <Ctrl>+<4>
  >        А greater than В        <Ctrl>+<>>
  <        А less than В                <Ctrl>+<<>
The operators perform comparison of number values. The result is “True” or “False”.

•Round                <Ctrl>+<R>
Rounds decimal number the nearest integer.

•Floor                <Ctrl>+<W>
Largest integer that is not greater than the argument.

•Ceil                <Ctrl>+<E>
Smallest integer that is greater than or equal to the argument.

•Convert        <Ctrl>+<M>
Converts a value to another unit of measurement. The right argument is the name of the unit to which you want to convert the value.

       

•Format                <Ctrl>+<F>
Converts a number to a string. Allows you to output any resulting value with the required number of decimal places. The text after the Format operator must consist of two numbers: the first number specifies the number of decimal places, the second - to discard the last zeros or not. A value of 1 is to discard zeros, a value of 0 is not.

The picture below shows the result of formatting the numbers 1.23400 and 32.00.

•Unit                <Ctrl>+<B>
Displays the units of the measurement. As the right argument, you can set the type of the displayed name - ShortName/GlobalName/FullName. The default is ShortName.