User Calculations

If clicked, any changes to the record selected will be re-assigned to all BOMs, Jobs, Purchase Orders, or Sales Orders based on the "Retro Since" date defined on the record. In other words, if the expression of the selected User Calculation is updated and this button is used, the lines on all BOMs/Jobs/Orders containing this User Calculation will be updated to reflect the result of the new expression.

• This button changes all Purchase Order Types and Sales Order Types and modifies orders in all statuses including Not Received, Received, Invoiced, Not Shipped, Shipped, Invoiced, and Paid. Historical copies of these orders are updated as well.
• Use this button with caution as the action cannot be undone.
• If User Calculations for Jobs are needed or are added after one or more Jobs have been created, the calculation will need to be made Retroactive via this button.
• If User Calculations for BOMs are needed or are added after BOMs/Formulas have been created in the system, users will need to obtain and run a "RecalcFormulas" External.

Pick list used to select the table where the User Calculations will be stored. Options are:

• BOM
• Jobs
• Purchase Order
• Sales Order (Sales order calculations are enabled in DSD beginning in version 17.02.010. The calcs will fire, and store against the orders the same as in main application. Calculations will also add parts to orders when saved the same as in the main application. This occurs when the device is online. If the device is not online, the system will track what calculations need to be fired, and fire/sync them when the device is back online.)

The name or description of the calculation that will appear on screen when viewing User Calculations.

• Important: For the User Calculation to select the correct item when adding a line to the BOM, Job, Sales Order, or Purchase Order, the "Description" of User Calculation must match the "Part Number" of the item that will be added to the BOM/Job/Sales Order/Purchase Order.

The name that will appear in the database table and also on the variable display when viewing reports. Supports User Defined Fields.

• Recommended format for naming is "uc_description"

Pick list that allows for greater text and character limits, particularly when using SDS forms which may require large amounts of text descriptions and regulatory information. Options are:

• Character
• Date
• Numeric
• Text

Note: When using a BOM User Calc with a "Field Type" of "Character" and a "Type" of "Minimum", "Maximum", or "Sum", the following rules are applied:

• Character BOM User Calc Max takes the max based on "largest" string alphabetically.
• Character BOM User Calc Min takes the min based on "smallest" string alphabetically.
• Character BOM User Calc Sum sums the results.
• Date Calcs are refreshed after the selection/re-selection of a Bill-to or Ship-to Company on the Sales Order header.

Pick list used to determine how the resulting calculation will be presented. Options are:

• Average - takes the average, across all the lines, of whatever expression is in the expression field. Average is valid only for expressions that evaluate to a numeric type of result.
• Maximum - takes the maximum, across all the lines, of whatever expression is in the Expression field. Maximum is valid for expressions that evaluate to numeric, string, date, or logical. The maximum of a string expression will produce the highest alphabetical result, meaning the expression that would be last in an alphabetic list. The maximum of a logical would only be false if all the line expressions are false, but would be true if any were true.
• Minimum - takes the minimum, across all the lines, of whatever expression is in the Expression field. Minimum is valid for expressions that evaluate to numeric, string, date, or logical. The minimum of a string expression will produce the lowest alphabetical result, meaning the expression that would be first in an alphabetic list. The minimum of a logical would be false if any the line expressions are false, and would be true if all were true.
• Sum - takes the sum, across all the lines, of whatever expression is in the Expression field. Sum is valid only for expressions that evaluate to a numeric type of result.
  • For example, a sum of lj_quant would give the total units on the job, across multiple lines.
  • Since each item has a unit weight, and lj_quant is in the stock unit of measure, a sum of lj_quant * pr_unitwgt would give you total weight being made on the job, regardless of the different units being produced.
• Secondary Total - Calculates once after non-secondary calculations are completed in sort order and can reference other User Calcs and header fields. All of the basic calculation types listed above are calculated in parallel and independent of each other, which means that you can’t have a basic calculation that looks at the results of another basic calculation. This is where the Secondary Total calculation type is used. Unlike the basic calculation types, Secondary Total does NOT look at the lines, but looks at the header, header-related fields (customer and vendor), the results of the basic calculation types, and other secondary total calculations.
  • All basic calculation types are done first, in parallel and independently of each other, so the sort order of basic calculation types doesn’t matter.
  • Secondary total calculations are done one at a time, in the order in which they are sorted. The sort order is used for both display of results and to control calculation order. Each secondary calculation has available to it the results of all the secondary calculations that are ahead of it in the sort order.
  • Secondary Totals don’t have to look at basic calculation results, as the name might imply. Since they look at header level fields they can be used to provide logic based on header level variables. Calculations can force lines onto the sales order, so a secondary calculation could look at the terms code and add a 2% charge to the order if the terms code indicated that a credit card was being used. Users need to be careful in their choice of variables here, because to_totdue is the natural total of all the lines on the sales order, including the added credit card charge. So you need another total that ignores the credit card charge and perhaps freight, which would be a Sum type calculation with a condition to return a zero in those cases. The secondary total would then be conditional (IIF statement) on certain terms codes or the credit card checkbox on the terms code, and return 2% of the result of the sum calculation.
  • A Secondary Total can be used inside the Secondary Total after it.

Examples:

• Assume a User Calculation exists with a "Type" of "Secondary Total" and an "Expression" of "1+7 (total11)". Another User Calc can be added with a "Type" of "Secondary Total" and an "Expression" of "total11 + 14 (total22)".
• Calculate average density of a blend. One basic BOM calculation can sum the weights of the lines on a BOM (bo_quant * pr_unitwgt) or already multiplied for you as totwgt. Another basic BOM calculation can sum the volumes of the lines on a BOM (bo_quant * pr_unitwgt) or already multiplied for you as totvol. A secondary total type calculation can take the result of the total weight calc and divide it by the result of the total volume calc, resulting in the density of the blend.

Note: When applying user calcs to child orders, the appropriate value of the weight of the order is pulled from the specific child, rather than the master order.

Defines the format or mask that will be used to display the calculation result. Used for numeric and calculation field types.

Memo field used to define the expression, which will be evaluated to produce a calculation result.

• Expressions may contain a combination of functions and field references.
• Calculated User Calcs may reference other User Calcs in their calculation.
• BOM Calcs will not calculate if the Part is not flagged as "Stocked" on the Item Master Properties tab.
• Bill of Material User Calculations are only re-calculated when saving a line item or any other action that causes the batch yield to be re-calculated. Revision fields (i.e. re_yield) are available to BOM calculations but must be prefixed by "cREV."
• For Sales Engineering information, the engineering values related to a given order line are available to use in the expression for a calculated, order line User Field.
• Calculations may make use of the log() function by using the keyword LOG(a, b) where a is the number the logarithm is for and b is the base of the logarithm.
• The basetotwgt and basetotvol variables are available to this field. These variables contain the same calculations as the totwgt and totvol variables less overage, scrap, and overissue.

When Table is set to BOM – The following fields are available to the expression. Revision header (dmrev) fields, which must be prefixed by “cRev.”. BOM Line Fields (dmbom) including BOM Line User Fields. Item Master fields (dmprod), including Item Master User Fields. Additional fields, prfact - pricing factor, bo_prid, bo_subtot, un_type, un_name, bocount, dispcnt, totwgt, totvol, totcub, exten, futext, wgtcost, volcost, wgtper, volper, and idfld.

• Secondary BOM Calcs can reference Revision header Item Master (dmprod) and Revision header Item Master User Fields.

When Table is set to Job – The following fields are available to the expression. Job header and Job Line fields (including User Fields), Item Master fields (including User Fields), Routing Groups fields (including User Fields), Sales Order Line and Sales Engineering data for linked orders.

• Notes:
  • BOM calculation results are available to Job calculations. To reference a BOM calculation result in a Job calculation expression, use an “m.” prefix and the user-defined calculation field name, such as “m.uc_bottles_per_case”.
  • Note that results for new BOM calculations are not stored in the underlying table and available to the Job calculations until the BOM has been modified and saved, so Job calculations based on them will be empty also. The Deacom Data Conversion team has a program available that can update all existing BOM calculation results at the same time. Because Job calculation results are available to be displayed in Job Reporting reports and calendars, this provides a pathway for the display of BOM values, like the number of bottles in a case or allergen presence, in those reports and their use in further calculations.

Pick list used to determine whether the User Calc will calculate the price or quantity on new lines added to Sales or Purchase Orders. Options are:

• Price
• Quantity - If chosen, the price will be determined based on the Sales or Purchasing pricing hierarchy. Useful in situations where the physical characteristics or assay levels of a product may be different based on production processes or QC testing.
  • Example: A customer sells product at an assumed assay level. The actual assay of the shipped Lot may be different than the assumed assay level. In this case, setting this field to “Quantity” will allow the extended price on the shipped Sales Order to be adjusted up or down based on the actual assay level of the Lot shipped.

Pick list used to determine at what point User Calcs should stop recalculating for a Sales Order or a Purchase Order. Options are:

• Always - User Calcs will continue to recalculate.
• Invoiced - User Calcs will cease recalculating once the Sales/Purchase Order is invoiced.
• Paid - User Calcs will cease recalculating once the Sales/Purchase Order is paid.
• Shipped - User Calcs will cease recalculating once the Sales Order is shipped. This option will only appear when Sales Orders are chosen for user calculation.
• Received - User Calcs will cease recalculating once the Purchase Order is received. This option will only appear when Purchase Orders are chosen for user calculation.

Memo field used to store additional information regarding the purpose or use of the calculation.

Determines the starting date when using the "Retroactive" button.

Pick list used to determine if BOM User Calcs will calculate at only the bottom level, only the top level, or at all BOM levels.

• Example: Assume (1) there is a BOM revision for an item called FINI1 that contains parts SUB1 and RAW3, (2) the default revision for SUB1 contains parts RAW1 and RAW2, (3) the Item Master for SUB1 has a "Current Labor" cost of 10, (4) the Item Master for RAW2 has a "Current Labor" cost of 2, (5) the Item Master for RAW3 has a "Current Labor" cost of 21, and (6) there are three BOM User Calcs configured as follows:
  • Table = BOM
  • Desc/Field Name = Calc1, Calc2, and Calc3, respectively
  • Type = Sum
  • Expression = pr_stanlab for all three User Calcs
  • Level = Bottom, Top, and All, respectively Editing the BOM revision for FINI1 and viewing the Calculations will display the following: Calc1 = 23, Calc2 = 31, Calc3 = 33.

Note: BOM calculations store the calculations done at every sequence so it can be used by the next sequence regardless of level.

If checked, Regulatory Parts will be considered by the system when performing BOM calcs.

If checked, this record is active. Only active records may be used in the system.

If checked, this calculation will only be available for display when printing Part Forms and will not be available for display on grids throughout the system with the exception of the grid on the Calculation tab of the Edit BOM form.

• This field is only available when selecting the "BOM" option in the Table field on this form.
• This field was added to handle a Foxpro limitation of only allowing 255 fields to be displayed on a grid at one time.
• Part Form Only User Calculations are displayed on the Calculation tab of the Edit BOM form in order to allow users to see what the calcs will be on Part Forms at the time that the Formula is edited.

If checked, the User Calculation will be applied to Sales Orders with an "Order Type" of "Master Order" and will calculate based on values from all Child Orders associated with the Master Order.

• For example, if the expression is simply 'or_quant', the calc value should only appear on master orders, no other order type, and should be the sum of or_quant values for all child order line items on the master order.
• Only available when "Table" is set to "Sales Order".