Using openBoM formulas

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
  • Cutting and pasting formulas (available soon)
  • Formula roll-ups (available soon)

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

How formulas work

The format for formulas involves referencing the Part Number with properties:

Target property = (Part Number) (Property1) <arithmetic operator> (Part Number)(Property2)

Let’s see an example:

Let’s say you want to create a formula to calculate the Total Cost of Part Number 100-1.  The formula would consist so: Total Cost = (100-1)(Quantity) * (100-1)(Cost).  Here’s what it would look like using the Formula Builder:

In the Formula Builder, as you enter values, you’ll be presented with available Part Numbers and Properties.  Here’s another example showing the Total cost formula for Part Number 100-2:

Total Cost for Part Number 100-2 = (100-2)(Quantity) * (100-2)(Cost)

Notice how you access the Formula Builder with a mouse right click in the target property, e.g. Total Cost. Make sure to click Save to ensure the formula is applied.

Pro tip: Use “@pn” in Formula Builder as a shortcut to replace the current line item Part Number in the formula. Here’s an example:

Arithmetic operators available for formulas are:

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

Copy & paste

To apply a formula across desired items, you can (1) click “Apply for all rows” in the Formula Builder window:

or (2) cut and paste formulas across the desired items:

Formula rollups (summing values in a column)

To create a formula roll-up, (1) click “Enable Rollup“.  A (2) “Totals” row will automatically appear where by you can edit SUMs across all the values in the chosen property (i.e., column).

Here’s a short video showing you how to edit a formula rollup.  Currently, only the “SUM” function is available.