At Bommer, we’re all about automating “the boring stuff”: the work you have to do to build your bill of materials into an accurate and complete representation of your parts and their metadata. Many times, that includes having to compute a value that changes based on other data, like a cost that changes based on the type of part, type of material, or type of process you specify to produce that part. If this sounds familiar, I’ll bet you have battle scars from writing Excel formulas to accomplish this. Well good news, spreadsheet warrior: now you can build all of that logic right into your Bommer bill of materials. That’s right, if-then-else conditional statements are now supported in Bommer formulas. Read on to learn how to leverage this new feature and scratch those calculations off your “I need to do this in Excel after exporting my data” list, forever.
What are conditionals?
Conditionals are IF/THEN statements that allow you to set up some logic to check if a property is a certain value and then do something depending on the result. A real-world example would be “IF this item at the market is on sale THEN buy 3 ELSE check another market”, or “IF you are running by a store THEN buy groceries ELSE we’ll order delivery”.
These are what are also sometimes referred to as “branching statements” because they branch off based on the true/false value of the "if" part. Simple enough concept, right? Sure, but this gives Bommer users some pretty cool powers when it comes to automating their bill of materials, and what you can accomplish with it is borderline limitless.
How do I use conditional statements in Bommer?
To dive right in, we recommend checking out our quick start guide on conditional statements, which should be enough to get you using conditionals right away.
If you want a bit more explanation first, let’s get started. Bommer formulas support the use of three keywords in Bommer formulas - IF, THEN, and ELSE - to enable you to build branching statements that look like this:
=if ("Make/Buy"=="Make") then "Make Price" else if ("Make/Buy"=="Buy") then "Vendor Price" else 0
This is a common formula for determining the per-item cost for a part where the cost calculations for a made part and a bought part are different. The formula looks at the Make/Buy property: if it is set to Make, it outputs the value in a Make Price column, and if it is set to Buy, it outputs the value in a Buy column. This also makes use of Bommer’s support for chaining conditionals together to provide a default value of 0 if Make/Buy is not set to either value (which, in this author’s humble opinion, is a good best practice to avoid any surprises in your computed values). And because Bommer lets you set a default formula for a Bommer property, you can set this to compute for each of your line items automatically in a column that you can then export into your bill of materials. Powerful.
Express yourself
Without a doubt, the above formula is useful, but let’s see what we can do with more complex Bommer formulas. In this example, we’re going to build a board feet calculator for wood parts directly into a Bommer formula. And to do this right, we’re going to need to use… math.
Don't worry, not that much math
Board footage for a part made of wood is used to order lumber for any wood project. It is a common calculation used by furniture builders and wood workers to compute the amount of material they need to buy to manufacture their parts. It’s not a hard calculation, but it does require specific units, and it only applies to specific wood parts. In other words, this is a perfect job for Bommer formulas with conditionals.
The equation for board feet for some quantity of a wood part in your BOM is:
quantity * width (in) × thickness (in) x length (ft) / 12
Which can be expressed in the following Bommer formula:
= Quantity * (Middle Dimension :: in * Shortest Dimension :: in * Longest Dimension :: ft) / 12
But, we only want this formula to apply if Make/Buy is Make and the Material is Lumber. Using Bommer’s conditionals, we can write this like:
=if ("Make/Buy" == "Make" and ("Material" == "Lumber")) then (Quantity * (Middle Dimension :: in * Shortest Dimension :: in * Longest Dimension :: ft) / 12) else 0
Now that’s a calculation! Like above, this can also be built into your Bommer configuration to automatically run for any parts that satisfy the if criteria, giving you key material ordering details. You can even add a cost term into that calculation or multiply by a factor to order extra material. Take that, tedious manual tasks!
No limits to what you can do
If we wanted, we can keep expanding this formula, e.g. to create a “material quantity” quantity that produces a different number based on the material or the process of the part:
= if ("Make/Buy" == "Make" and ("Material"=="Lumber")) then
(Quantity * (Middle Dimension :: in * Shortest Dimension :: in * Longest Dimension :: ft) / 12)
else if ("Make/Buy" == "Make" and ("Process"=="Injection Molded")) then
(Quantity * "Runner Weight Per Shot" / "Mold Cavitation" + Quantity * Mass ::g)
else 0
We can restructure this formula to pull out the “Make/Buy” check, which can be clearer if we’re going to chain a lot of else if statements for different part types:
= if ("Make/Buy" == "Make") then (
if ("Material"=="Lumber") then
(Quantity * (Middle Dimension :: in * Shortest Dimension :: in * Longest Dimension :: ft) / 12)
else if ("Process"=="Injection Molded") then
(Quantity * "Runner Weight Per Shot" / "Mold Cavitation" + Quantity * Mass ::g)
else 0
)
else 0
Or, we can go the other direction, and instead add a checkbox to our BOM that indicates whether we should perform this calculation:
=if ("Compute Board Feet?") then (Quantity * (Middle Dimension :: in * Shortest Dimension :: in * Longest Dimension :: ft) / 12) else 0
where “Compute Board Feet?” is a True/False property added via Bommer settings.
As you can see, Bommer formulas become very powerful when you can branch on a true/false statement. All of this translates to a BOM that does more automatically, which means fewer things you have to do in Excel to get your documentation ready. And because this is Bommer, all of your conditional formulas (well, any formulas) are stored in the same place as your property data and your Fusion 360 part data, so you always have your single source of truth for both data AND calculations.
Getting the goods
If you’re already a Bommer user, update to the latest version and read our quick migration guide to get up and running with conditional statements. If you’re not yet a user of our BOM software, head over to our installation guide to get started. And if you’ve already upgraded and are wondering how to spend all your new free time, consider checking out our other blog posts, watching videos in our YouTube channel, sending us examples of your awesome formulas (we may even feature you in our content), or recommending us to a friend/colleague.
For now however, have an awesome day!
Autodesk Fusion 360
SOLIDWORKS