Calculated Columns & Variables
What this does
Teamship has two related tools for working out values from other values, so nobody has to calculate them by hand:
- Calculated columns add a column to a directory grid that is worked out from other numeric columns on that grid. For example, Product Value × 5 or Product Value × Available.
- Variables and Calculate steps in inbound scan sequences capture intermediate values during receiving and use them to fill a stock attribute. For example, Gross Weight ÷ Quantity = Net Weight.
Both use the same simple shape: operand · operator · operand = result, with the operators +, −, × and ÷.
When to use
- Calculated columns — You want to see a derived figure in a grid (a total value, a weight per unit, a difference between two quantities) without exporting to a spreadsheet. The column stays in your saved view and is included when you export to Excel.
- Variables and Calculate steps — A receiving document gives you a total (for example, overall gross weight for a product) but the stock attribute you need is per unit (net weight per unit). The scan sequence captures the total, divides by the quantity and fills the attribute for you.
Calculated columns
Where they're available
Calculated columns can be added from the Choose Column list on directory grids, including:
| Area | Grids |
|---|---|
| Inventory | Inventory directory (all inventory tabs), Inventory Balance Sheet, Adjustment History Report, Product Inventory History dashboard, LPN Lookup |
| Shipping | All Shipping Orders, Bulk Shipping Orders, Ecommerce Orders, Pack Orders, Manifests |
| Products | Products directory |
| Billing | Invoices, billing transactions |
| Warehouse | Warehouse directory, Warehouse locations |
| Users | User directory |
On the inventory tabs and the Product Inventory History dashboard, number-type custom attributes can be used in calculations alongside the standard numeric columns.
Key rules
- Personal to you. A calculated column is visible only to the user who created it. It is never shared with other users, even if they use the same view name.
- Display only. Values are worked out row by row when the grid loads. Nothing is saved back to the orders, products or other records.
- Numeric columns only. Only columns that hold numbers can be used. Text and date columns aren't offered.
- Marked with a calculator icon. The icon appears at the start of the column header and next to the column in the Choose Column list. Hover over it to see Calculated Column, a note that it is created from a formula rather than system data, and the formula itself (for example, Formula: Product Value × Available).
Creating a calculated column
- On the grid, open Columns → Choose Column and select Add Calculated Column.
- Build the expression from left to right:
- First operand — choose Column and pick a numeric column, or choose Value and type a number.
- Operator — +, −, × or ÷.
- Second operand — Column or Value, as above.
- Enter the Calculated Column Name (for example, Total Product Value).
- Choose where to save it:
- Save to View — pick one of your existing views from the dropdown, or
- Create a New View — type a name for a new view. Use Save to Existing View to switch back.
- Select Save Column.
If you saved to the view you're looking at, the column appears on the grid straight away with the calculator icon.
Using a calculated column in other views
Once created, the calculated column is listed in Choose Column on every view of that grid. Tick it while another view is active to add it to that view too. Switching views shows or hides it depending on which views it belongs to.
Editing or deleting a calculated column
Only the user who created a calculated column can edit or delete it.
- Open Columns → Choose Column.
- Select the calculated column's name (it is shown as a link). The Add Calculated Column popup opens with its current settings.
- To edit, change the expression, name or view and save. The grid refreshes with the new formula.
- To delete, select Delete and confirm. The column is removed from every view it was saved to.
How values are shown
| Situation | Result |
|---|---|
| Normal row | Result rounded half-up to 2 decimal places |
| One of the operands is blank on that row | Blank |
| Division by zero | Blank |
Calculated columns behave like any other numeric column: you can sort and filter on them, and Excel export of the view includes them with their computed values.
Examples
| Formula | Name | Use |
|---|---|---|
Product Value × 5 | Total Product Value | Value of five units |
Product Value × Available | Available Stock Value | Value of available stock per row |
Use the numeric columns your grid shows; available columns differ from grid to grid.
Validation
- Both operands, the operator and the name are required.
- Value accepts a number only.
- A view is required: either an existing view or a non-empty new view name.
- The name must be unique within the target view. If you use a name that already exists, you'll be asked whether to overwrite it. Confirming replaces that column's formula in every view it belongs to.
Variables and calculated attributes in scan sequences
Inbound scan sequences can include variables and Calculate steps.
- A variable is a value captured during the receiving run (scanned, shown as an instruction, or filled by a Default step). It is recorded on the receiving for reference but is never written to stock.
- A Calculate step fills a real stock attribute using a formula over variables, attributes captured in earlier steps and the receiving quantity.
→ See: Scan Sequences for how inbound sequences are built and run.
Setting up variables in the sequence builder
- On the sequence form, find the Variables field.
- Type a variable name (for example, Gross Weight) and press Enter. It becomes a chip.
- Quantity is already there as a built-in variable. It can't be renamed or deleted.
- Add steps as usual. When you pick a step's target, the list is grouped into Stock Attributes and Variables. Scan, Instruct and Default steps can target either. A small icon shows whether the target is a variable or a stock attribute.
Variables are numeric. A variable doesn't have to be used in a calculation; capture-only variables are simply recorded on the receiving.
Adding a Calculate step
- Add a step and choose the Calculate step type.
- Build the expression: operand · operator · operand = target stock attribute. Operands can be variables (including Quantity) or stock attributes captured earlier in the sequence.
- Pick the stock attribute the result should fill.
Example sequence:
| Step | Type | Target |
|---|---|---|
| 2 | Scan | Gross Weight (variable) |
| 3 | Calculate | Gross Weight ÷ Quantity = Net Weight (stock attribute) |
| 4 | Scan | Color (stock attribute) |
Builder validation
The sequence can't be saved when:
- A Calculate step uses a value that is set in a later step, or never set
- The expression is incomplete (missing an operand, the operator or the target)
You also can't delete a variable chip while a step or Calculate operand uses it. Remove the reference first.
What happens at receiving
When an inventory order is received with the sequence:
- Quantity is set at the start. Changing it means restarting the sequence, which clears every captured value, so calculated values never go out of date.
- Variable steps prompt like any other step. Once captured, the screen shows Scanned: [value] - scan or edit to change, with the value editable.
- Calculate steps work out the result when the step is reached, using the values captured so far. The result is shown in an editable field captioned Calculated as [expression] - scan or edit to change (for example, Calculated as Gross Weight/Quantity).
- The operator checks or overrides the value and taps through, as with any pre-filled step. Inventory order suggestions still appear, and tapping one replaces the value.
How results are handled:
- Rounding — number results round half-up to the attribute's decimal setting (2 decimal places).
- Validation — the result must pass the attribute's normal validation. If it doesn't, the step is left blank with a message.
- Blank, invalid or divide by zero — the step is left blank with a message, and the operator enters the value manually.
- Multiple LPNs — when stock is split across LPNs, Quantity is the total quantity for the run, and the calculated per-unit value is written to every LPN in the run.
Where captured values appear
- Variables are shown in the Receiving Details popup for each LPN on the inventory order, under Captured values (name and value). Every LPN from the same run shows the same values. Variables don't appear on inventory tabs or the product's tabs.
- Calculated attributes are stored like any other attribute value. The inventory order on web shows the value only, with no formula or calculated marker, because the operator may have overridden it.
→ See: Inventory Orders and Receiving Inventory
Roles and permissions
Warehouse users and administrators
- Add, edit and delete their own calculated columns on directory grids
- Build scan sequences with variables and Calculate steps
- Run sequences that capture variables and calculated attributes at receiving
Customers
- Add, edit and delete their own calculated columns on the grids they can see, including billing transactions
- Number custom attributes their organization uses are available as operands on inventory tabs
Calculated columns are always private to the user who created them.
Common issues
| Issue | Likely cause |
|---|---|
| A column isn't offered as an operand | It isn't numeric. Only number columns (and number-type attributes on inventory grids) can be used. |
| A calculated column shows blank on some rows | An operand is blank on that row, or the formula divides by zero |
| A colleague can't see my calculated column | Calculated columns are personal to the creator and aren't shared |
| The column disappeared when I switched view | It wasn't saved to that view. Tick it in Choose Column to add it. |
| Can't edit or delete a calculated column | Only its creator can change it |
| Saving asks to overwrite | A calculated column with that name already exists in the target view |
| Scan sequence won't save | A Calculate step uses a value set in a later step, or its expression is incomplete |
| Can't delete a variable chip | A step or Calculate operand still uses it |
| A Calculate step shows blank at receiving | An operand was blank or invalid, the formula divided by zero, or the result failed the attribute's validation |
| Variable values don't show on inventory | Variables are never written to stock. See Captured values in the Receiving Details popup. |