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.