Sneak preview - openBoM formula and calculated properties
Support for calculated values in Bill of Materials is critical. The list of calculated value requirements and use cases is big, examples include, price, mass, configurations and so on.
If you’re familiar with enterprise systems, then you probably know calculations are usually performed using scripts or other programming languages. However, for most people, that’s both a complicated and cumbersome way of doing calculations.
But in a spreadsheet, calculations are easy. You simply create a formula by referencing a cell column and row number and viola, you have your calculation. In Bill of Materials, however, it is not as easy as that. Even though openBoM looks, feels and smells like a spreadsheet, it is in fact not a spreadsheet. The row numbers don’t have persistent numbers and properties aren’t exactly column numbers which can be moved around.
Knowing this, we took a deep breath, put our thinking caps on so that in the coming release of openBoM we will bring you something that might be a first step solution for formulas and calculations in BOMs. This limited first step is an experiment with formulas in openBoM.
First, some basics:
To think about formulas and define them, we need to have rows and columns. We reference an item (think about it as a row) via a Part Number. This is a persistent reference value in openBoM. And we reference a property name as a reference for a column.
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.
Target property = (Part Number) (Property1) <arithmetic operator> (Part Number)(Property2)
Let’s see an example:
In row 1, above, let’s say you want to create a formula to calculate the Total Cost of Part Number 100–1. The formula would consist of:
Total Cost = (100–1)(Quantity) * (100–1)(Cost).
Here’s what it would look like using the Formula Builder:
In our first version of formulas, only basic arithmetic operations will be supported.
You access the Formula Builder UI via a menu you get via a mouse right-click.
Take a look at a following Gif to get an idea of what will soon be available:
Conclusion. We’re taking a first step towards providing calculations in openBoM. More calculation based features will be coming later such as roll-up functions and more. We need your help, though. Tell us what you think about formulas in openBom and what is most important for you. I look forward to hearing from you.
Best, Oleg
PS. We should know each other better. If you live in a Greater Boston, please let’s have a meeting (coffee is on me). If you’re located in other places, let’s have a virtual coffee session — I will figure out how to send you a real coffee for our virtual coffee session.
