Spot thin-margin loads with a margin % formula on orders

The situation

A $150 margin looks the same on every load, and it isn’t. Your order already shows a dollar Margin: Customer Rate minus Carrier Cost. On a $1,000 short haul, $150 is 15%. On a $3,000 cross-country lane it is 5%, and the desk should have re-quoted that load or held out for a cheaper truck. The ops lead usually finds the 5% loads at month end, in a spreadsheet export, after the carriers are booked and the money is gone. A Margin % field on the Order puts that number on every load while you can still do something about it, and because a formula field works it out from the rate and the cost, nobody has to type it or keep it up to date.

The workflow

The whole formula is one line. You insert the two fields from the editor’s field list, so they show up as chips rather than typed text:

toPercent(if(areEqual(toNumber({Customer Rate}), 0), 0, ({Customer Rate} - {Carrier Cost}) / {Customer Rate}))

Read it from the inside out: take the cost from the rate, divide by the rate, and return 0 when the rate is zero. toPercent goes last, around everything. The clip above covers steps 1 to 5 and then cancels, so nothing was saved.

1. Click Settings, then Objects, and open Order. Object Builder opens on Available fields. Find your rate and cost fields first. On a broker order they are often Customer Rate and Carrier Cost, and you may already have a dollar Margin next to them. Keep it, because the percent sits alongside it and doesn’t replace it.
2. Click Create custom field. Type Margin % in Field name, pick Formula in Field type, and click Set formula. The editor opens on an empty formula box.
3. Type toPercent(if(areEqual(toNumber(, then type { and the start of Customer Rate, and press Enter to insert it. Close the check with ), 0), 0, (. This is the guard: on a load with a $0 rate, the field reads 0 instead of failing on a division by zero.
4. Insert Customer Rate, type -, insert Carrier Cost, type ) /, insert Customer Rate again, and close with )). That is (rate − cost) ÷ rate, the share of the rate you keep.
5. When the formula is complete, Output opens below the box. Read Field type: it says Number. That tells you the board can sort the field like any other number, so the thinnest loads can sit at the top of the list instead of turning up in an export.
6. Click Set formula, then Create. In Available fields, click Add beside Margin % so it appears in the Order’s data panel next to Margin, then click Save changes.
7. Open one order where you know the answer. For example, a $2,000 rate and a $1,850 cost should read a margin of 7.5, because toPercent multiplies by 100. Then open one with a $0 rate and check that it reads 0.

From here, every order works out its own margin percent each time it is read. When a rep changes the rate or the cost, the percent changes with it, with no re-keying and no Friday spreadsheet. The formula fields page covers the editor itself: what each function group does, and what to check before a board relies on a formula.

Extend it

• Sort the board by it. On the Orders board, click Configure, then Columns, and move Margin % into Shown fields. Sort it ascending and save the view as Thin margin, so the ops lead opens the list instead of building it. Views are covered in board views.
• Work it backwards. A second formula field, {Customer Rate} * 0.88, gives the most you can pay a carrier and still keep 12%. The rep sees that number before they call a carrier, not after they book one.
• Guard any per-unit number the same way. A rate-per-mile formula, the rate divided by a miles field such as Total Miles, fails on an order whose miles read 0, so wrap it in the same if(areEqual(...), 0, ...). The Order object page shows where new fields go once they exist.