Quote a US shipper from CAD costs with a currency formula

The situation

Your costs come in Canadian dollars, and the shipper in Ohio wants the quote in US dollars. So somebody opens a calculator, multiplies the line haul by whatever rate they looked up this morning, and types the answer into an email. The rep at the next desk used a different rate. On a CA$3,000 line haul, 0.73 quotes US$2,190 and 0.71 quotes US$2,130: $60 apart on the same load, and the shipper notices when two quotes from you disagree. A Line Haul (USD) field on the Order does the conversion once, at one rate your team agrees on, so the US number reads straight off the order. Because it is a formula field, nobody types it. The rate is the one number you type yourself, inside the formula, and that choice is why the warning at the bottom of this page matters.

The workflow

The formula is one line. You insert the line haul from the editor’s field list, so it shows up as a chip rather than typed text:

convertCurrency({Line Haul Rate}, 0.73, 'USD')

Read it left to right: take the line haul, multiply it by 0.73, and label the result US dollars. The rate goes from your cost currency to the quote currency: 0.73 means one Canadian dollar is worth 73 US cents, so CA$1,000 becomes US$730. The clip above covers steps 1 to 5 and ends before anything is saved.

1. Click Settings, then Objects under Builders, and open Order. Object Builder opens on Available fields. Click Create custom field.
2. Type Line Haul (USD) in Field name, pick Formula in Field type, and click Set formula. The editor opens on an empty formula box.
3. Type convertCurrency(, then type { and Line Haul Rate. The list narrows to one entry with a $ beside it; pick it and it becomes a chip. The $ matters. The first argument has to be a money field, and the editor will accept a plain number field without complaint, then fail on every order when it is read.
4. Type , 0.73, 'USD'). Type the rate as a bare number, with no quotes, and put the currency code in single quotes. Check the direction before you go on: typing the inverse, 1.37, would overstate every US quote by almost 90%.
5. When the formula is complete, Output opens below the box. Read Field type: it says Money. That means a board can total the field like any other amount. Output doesn’t name the currency. The US dollars come from the 'USD' you typed.
6. Click Set formula, then Create. In Available fields, click Add beside Line Haul (USD) so it shows in the Order’s data panel next to Line Haul Rate, then click Save changes.
7. Open an order whose line haul you know is in Canadian dollars. For example, a CA$2,400 line haul should read US$1,752. Then open an order already priced in US dollars and check that it comes back unchanged, because there is nothing to convert.

From here, every order carries its US line haul, worked out each time the order is read. A rep quoting the Ohio shipper reads one number off the order instead of doing the sum. The formula fields page covers the editor itself: the function groups, and what to check before a board relies on a formula.

Extend it

• Put it beside the Canadian number on the board. On the Orders board, click Configure, then Columns, and move Line Haul (USD) into Shown fields next to Line Haul Rate, so the desk sees both sides of the quote on one row. Views are covered in board views.
• Total your US book. A money result can be summed on a chart, so a view filtered to your US shippers can show this month’s line haul in US dollars without an export. The chart views page has the clicks.
• Convert the cost side too. Add a second field on your carrier cost with the same rate, then a Margin % field over the two, so the margin you quote and the margin you keep are in one currency.
• Lock the quote you sent. If an order has to keep the amount it was quoted at, copy Line Haul (USD) into a typed money field when the quote goes out. A typed field holds its value when the rate changes, and the formula doesn’t. The Order object page shows where new fields go.