How formulas work

A Calculated field works out its value from other fields, so nobody types it and it never goes stale: change an input, and every record that uses it recomputes. This page is the reference for what a formula can say. Each section explains one idea, then works an example; click Try it on one to change its inputs and watch the result follow. For a first formula step by step, see Give fields units and write your first formula.

Admin and Engineering members write formulas. Anyone can read one: hover the small ƒ beside a calculated value in the grid.

On this page: Write a formula · Units · Units SpecLatch can't store · Temperatures · Tests and choices · Linked records · Reference tables · Your own functions · Curves · Blank results · Math functions

Write a formula

A formula is ordinary math written with field keys. A key is a field's short code name, such as cv_rated: lowercase letters, digits and underscores. You rarely type one in full, because the editor finds fields by name.

• Type + and - to add and subtract, * to multiply, / to divide and ^ for a power.
• Type part of a field's name and pick it from the list; its key goes into the formula. Ctrl+Space opens the list.
• Insert lists your fields, your functions, the reference tables and the math functions.
• Spaces and line breaks don't matter, so a long formula can take several lines.
• Write 0.5, not .5, and 1500, not 1,500.
• ^ comes first, then * and /, then + and -. A minus sign in front of a value applies before ^, so -2 ^ 2 is 4; write -(2 ^ 2) for −4.

A formula reads Number, Choice, Yes / no, Calculated and From linked records fields. It can't read text, dates, files, a choice that allows several values, or its own field.

The checker runs as you type. When the formula is sound, the line under the editor says what it returns, such as Returns a plain number; otherwise a red message names the problem and how to fix it. Click Preview on real records to see results before you save.

cv_rated * 0.865

Units

Formulas check units the way an engineer does on paper. SpecLatch converts every value to a common base before the math, so feet and inches mix freely, and it refuses a formula that adds a length to a pressure. A wrong formula fails in the editor, not as a wrong number on a spec sheet.

• +, - and comparisons need the same kind of value on both sides, such as two lengths.
• In a sum or a comparison, a plain number needs a unit first: qty(100, "ft/min").
• * and / combine kinds, and two values of the same kind divide to a plain number.
• Kinds may combine along the way, but the result must be a plain number or a single kind, such as a flow or a pressure.
• ^ takes plain numbers only.

cap_100fpm_50pcf * (max_belt_speed / qty(100, "ft/min"))

The line under the editor reads Returns Mass flow (capacity) · stored in, then a unit you can change. Write / 100 instead and the formula doesn't save: 100 has no unit, so the speeds don't cancel.

Units SpecLatch can't store

Many engineering formulas carry a constant that assumes particular units, such as the 3,960 in hydraulic power, which is right only for gpm and feet. Their in-between units, such as flow times head, are also nothing a field can store. For those, read each value as a plain number in the unit the constant expects, do the math, then give the result its unit:

• num(bep_flow, "GPM") reads the flow as a plain number of gallons per minute, whatever unit the field stores.
• qty(…, "HP") turns a plain result into a power.
• num() takes a value that has a unit; qty() takes a plain number.
• Name units as the unit picker shows them, such as "in", "ft", "psi", "GPM", "HP" or "m³/h". Capitals don't matter, and "m3/h" works too.

Because the units sit in the formula, anyone reading it sees what the constant assumes.

qty(num(bep_flow, "GPM") * num(bep_head, "ft") * sg / 3960, "HP")

Written as bep_flow * bep_head * sg / 3960, it doesn't save: the message says the result would be flow × length, a compound unit SpecLatch can't store. A per-unit result, such as 1 / rated_speed, needs num() too.

Temperatures

A temperature reading and a step on the scale convert differently: 70 °F is 21 °C, but a 10 °F rise is a 5.6 °C rise. Mixing them up gives wrong spans and accuracies, so SpecLatch keeps the two apart:

• Subtracting two temperatures gives a temperature difference, in Δ°F or Δ°C.
• A temperature plus or minus a difference is a temperature.
• A difference scales like any value. A temperature can't be scaled, negated or added to a plain number, so range_max * 2 and range_max + 10 don't save.
• min(), max(), comparisons and num() work on temperatures.
• Write a difference in a formula as qty(10, "Δ°F") or qty(10, "delta °F").

Store spans, accuracies and rises in a unit from the Temperature difference group of the unit picker, so they convert correctly.

range_max - range_min

A second field then adds half a span above the scale: range_max + span * 0.5 gives 375 °F (191 °C).

Tests and choices

A formula can decide as well as compute: compare values, test a yes/no field, and return a label such as Non-overloading instead of a number.

• Compare with <, <=, >, >=, = and <>. The forms ==, !=, ≤, ≥ and ≠ work too.
• Compare like with like, and give a threshold its unit: range_max <= qty(1000, "psi"). Units convert first, so 1 in equals 25.4 mm.
• Compare a choice field to one of its labels in quotes: magnet_material = "Samarium-cobalt (SmCo)". Labels match exactly, capitals included, and compare with = or <> only.
• A Yes / no field is a test by itself: if(stock_item, …).
• Combine tests with and(), or() and not(); &, | and ! aren't operators.
• Comparisons don't chain: write and(ratio > 0.4, ratio < 0.8), not 0.4 < ratio < 0.8.
• if(test, then, else) gives one of two values. Add test and value pairs for bands; the first true test wins.
• Only the branch taken needs its inputs, and every branch gives the same kind of value.
• A choice whose labels are all numbers, such as 2, 4 and 6, also reads as a plain number.

A formula that returns a label in quotes on every branch is a choice. The line under the editor then says Returns a choice: and lists the labels in the order the formula writes them, and the result filters and prints like a Choice field.

if(max_bhp <= std_motor_hp, "Non-overloading", max_bhp <= std_motor_hp * std_motor_sf, "Into the service factor", "Overloads the motor")

⤢ full size

Linked records

A From linked records field brings values over a link, as a lookup or a rollup. A formula reads it like any field of its own table, so specs that live on a linked record never need retyping:

• A lookup set to Each value or Unique values of a number field reads that number, in the source field's unit. If the linked records hold different values, the result stays blank and says why: use a rollup to combine them.
• A rollup set to Sum, Average, Lowest or Highest reads the combined number, in the rollup's Show in unit. Count reads as a plain number.
• Range (lowest–highest) gives two numbers, so a formula can't read it.
• When a linked record changes, the formula recomputes.

The motor check above already reads two linked values: std_motor_hp and std_motor_sf come from the pump's linked motor.

round(std_actuator_torque / required_torque, 2)

required_torque is itself calculated, 330 in·lb of breakaway torque × a 1.3 sizing factor: a formula can read another calculated field.

Reference tables

Values from a chart or a standard, such as a valve's flow by opening angle, live on Reference tables, shared by every table. A formula reads one with lookup(table, x):

• Pass the table's key, then its inputs in order. A number input needs the right kind, such as qty(45, "°") for an angle.
• A choice input picks rows by exact label.
• Between two rows, the value is read on a straight line.
• Outside the rows, the result stays blank with the reason, because SpecLatch doesn't extrapolate.
• When a table has several output columns, name one in quotes last: lookup(steel, t, "modulus").
• Editing a reference table recomputes every formula that reads it.

cv_rated * lookup(ball_characteristic, qty(45, "°")) / 100

Your own functions

When the same math appears in several fields, name it once on Functions, then call it from any formula by its key. A correction to the function then reaches every record that uses it.

• Pass the values in the function's input order. Each one is checked against its input's kind, in any unit of that kind.
• A call with the wrong number of values doesn't save, and the message lists the inputs.
• Editing a function recomputes every formula that calls it.
• A function can't call another function or read a reference table. Pass that value in as an input instead.

hydraulic_power(bep_flow, bep_head, 1)

Define a reusable function shows how to create one.

Curves

A data series can draw calculated lines on every record, from that record's own values, so a curve you can work out never needs typing. Open Fields, click Add series or Edit series under Data series (curves), then Add line under Calculated lines:

• Formula of x draws y from x, the series' X axis. x runs evenly between two values, through a list, or through a reference table's rows.
• From another curve redraws each point of a curve on the same record. Its new x = and new y = formulas read x and y.
• Line formulas can read the record's fields, lookup() and your functions.
• Preview on records draws the lines on real records before you save.

A series holds up to 10 calculated lines, of up to 200 points each.

cap_100fpm_50pcf * x / qty(100, "ft/min")

Performance curves covers series and lines in full.

Blank results

When the math can't finish, a calculated value stays blank rather than showing a guess. In the grid, a blank calculated cell shows a dash and a small ƒ; hover it to see the formula and why it's blank. Preview on real records shows the same reasons before you save.

Common reasons:

• An input is empty.
• A division by zero, or the square root of a negative number.
• A lookup outside the reference table's rows.
• A linked field whose records hold different values.

hydraulic_power(bep_flow, bep_head, 1)

Math functions

Beside the operators, every formula can call the built-in functions below. Built-in functions, with examples works through each family.