Understanding CALC

Many calculated fields in reports will need to perform some mathematical function between two sets of data. More than likely, multiplying a quantity by some cost. Because quantity values in MAPCON have a different conversion rate than cost values, it is recommended to use the MAPCON ToolBox subroutine CALC.

The CALC subroutine takes two values, regardless of conversion, performs the mathematical function, and returns the value in the correct conversion. The CALC subroutine also makes sure multi-valued data is properly calculated. Without this ToolBox utility, the burden of checking precision and multi-values would fall on the user.

For example, the quantity of parts on a work order has a two decimals format while the cost of those parts has a four decimal format. Numbers in OpenInsight are kept as internal values with an implicit decimal. If the quantity is 2 then the internal value is 200 (2.00). If the price is $1.50 then the internal values is 15000.  The quantity multiplied by the cost is 30000000 (a very large value)!  Doing this manually would require trying to figure out how many decimal places were kept for each value, a rather tedious task. Instead, the CALC statement takes care of it for us, returning the value with either two decimals (30.00) or four (30.0000).

For all this to happen, the CALC statement requires four things:

1) The two values to be calculated  (val1, val2)

2) The conversion format (precision) of each value (conv1, conv2)

3) The conversion format (precision) of the result (resultconv)

d) The mathematical function: add, multiply, subtract or divide (command)

The format of the CALC statement is:

CALC(val1, val2, conv1, conv2, result, resultconv, command)

The values to pass, the result, and the commands that the CALC statement requires are straightforward. The conversion formats require a little more explanation.

MAPCON uses three standard conversion formats: MD0, MD2 and MD4. The first implies a whole number, no decimal. The second implies two decimal precision on the numeric value, and the third implies four decimal precision on the numeric value. Throughout the system, columns containing a quantity (such as quantity on hand) have an MD2 conversion, and columns containing a cost (such as unit price) have an MD4 conversion. Any value in MAPCON which cannot be divided (such as task number on a work order) have an MD0 conversion.

Heres an example of a CALC statement:

$INSERT DICTIONARY_INCLUDES, WO_MSTR

$INSERT DICTIONARY_INCLUDES, INV_MSTR

* get the part numbers  on the work order            

PARTS = @RECORD<WO_MSTR_PART_NO>

*get the qty planned for each part on the work order 

QTY = @RECORD<WO_MSTR_QTY>

* get the price for each part on the work order

PRICE = XLATE(INV_MSTR, PARTS, INV_MSTR_A_UNIT_COST, 'X')

CALC(QTY, PRICE, 'MD2', 'MD4', RESULT, 'MD4', '*')

@ANS = RESULT

If the conversion for the column is not known, then use the $INSERT for the column conversions. Like the FMC equate, the column conversion include statements equate readable variables to the conversion format of the column.

For example, the CALC statement above could read:

CALC(QTY, PRICE, WO_MSTR_QTY_CNV, INV_MSTR_A_UNIT_COST_CNV,

            RESULT, INV_MSTR_A_UNIT_COST_CNV, '*')

The following $INSERT commands would need to be added at the top of the symbolic program:

$INSERT DICTIONARY_INCLUDES, WO_MSTR_CNV

$INSERT DICTIONARY_INCLUDES, INV_MSTR_CNV

AddCalc

Because of the complexities of the CALC statement, the push-button Add Calc is available on the MAPCON Symbolic Editor. When this button is clicked, the MAPCON Symbolic Calculation Creation Window will appear to assist in creating a CALC statement. The fields on this window are explained below:

Calculation Operator - Select the type of calculation to perform by clicking on the down arrow: Add, Subtract, Multiply, Divide.

Decimal Places in Final Result - Select how many decimal places should be in the final result. If you're not sure, then leave the setting as Auto and MAPCON will determine the conversion format for the result. You may choose Auto, 0 decimal places, 2 decimal places, or 4 decimal places.

Equation Left/Equation Right - This determines what is on the left or right side of the equation. This can be a column in the current record being printed (numeric columns only), a variable used in the symbolic program, or a static hard-coded value. When this value changes, the display to the right of the value will change to reflect what can be entered.

Column - The list of Available columns contains all the columns in the table used by the report that are numeric. To select a column, double click on the desired column name. The Equation on the bottom of the screen will display the formula.

Variable - If selected, the display to the right will change to a combo box. Click on the down arrow to see what variables are in the symbolic program. Chose the desired variable and watch the Equation change on the bottom of the screen.

Note: Because variables are not known to be numeric or not when designing the symbolic, be sure the variable selected is equated to a number, or another variable that is equated to a numeric value. Otherwise, the symbolic program will break with a 'non-numeric when numeric required' error.

Static - If selected, the display to the right will change to allow a number to be entered. Enter any numeric value, including decimals, to create a static hard-coded value in the CALC statement.

When the OK button is clicked, a CALC statement is added to the bottom of the symbolic program. If any $INSERT records are required, they are added to the top of the symbolic program.

Note: To perform a calculation on two values from different tables, use the XLATE command to retrieve the value into a variable. Then, use the variable in the CALC statement.