Creating formulas and rollups

Formulas in openBoM are equations you create in a cell which performs simple arithmetic operations using the designated cells.  The basic principle to understand is that a formula equation needs to reference a specific Part Number along with the chosen property.  This document has three topics:

  • How formulas work
  • Formula roll-ups

Note: Formulas are currently only supported for BOMs, not for inventories.

How formulas work

The format for formulas include number property types and arithmetic operators:

Target property = (Property1) <arithmetic operator> (Property2)<aritmetic operator> (Property3)….

Let’s build a formula step-by-step:

(1) Right-click on the desired number property type, e.g. Total Cost

(2) Enter the first desired number property type into the Formula Builder, e.g. “Quantity”. Then select the desired arithmetic operator, e.g. “*”. Arithmetic operators available for formulas are:

Multiplication (*), Division (/), Addition (+), and Subtraction (-)

(3) Add the next desired number property type, e.g. “Cost”. Continue adding properties and operators as desired

(4) Click “Apply for all rows” if you want the formula to be applied to all the components in the BOM. When finished building the formula, clicked “Save”

Here are the steps for creating a rollup:

(1) Click “Enable Rollup” and right-click on the desired rollup cell to “Edit Formula”

(2) Start with “SUM” in the formula builder and add the corresponding number type property, e.g. “Total Cost”. Click “Save” when done.

(3) The roll-up returns the sum of the column with the Property, Total Cost