Custom Sections
Formula Fields
A formula field works out a number from other fields on the record. You write the sum once, and ORCA keeps the answer current. Nobody types into it.
Good for a margin, a unit cost, hours worked between a start and a finish, days since something happened, or a 1 or 0 that says "yes, this is overdue". Formulas give numbers. If you want a label such as "Large" or "Won", use a picklist or an automation, not a formula.
Adding a Formula Field
In your template editor, add a field and set its type to Formula. A Formula Expression box appears, with a short guide and some examples underneath.
When you save, ORCA reads the formula first. If it can't be read, it tells you why ("There's no function called ROUDN.", "A { has no closing }.") and doesn't save, rather than leaving a field that is blank on every record.
If you later rename a field that a formula uses, ORCA rewrites the formula to match, so it keeps working.
Tokens
A token is a field's name in curly braces: {Ticket (EUR)}, {Unit Cost}. Capital letters don't matter, and a space or a hyphen reads the same as an underscore, so {Unit Cost}, {unit_cost} and {unit-cost} are the same field.
{Ticket (EUR)} * 1.08
Two tokens are always there, whatever the template has:
{created_at}: when the record was created{updated_at}: when the record was last updated
What a token gives you depends on the field:
- Numbers and currency give the number. Pound signs and commas are dropped, so "£1,200" reads as 1200.
- Blank fields, and anything that isn't a number, count as 0 in sums and arithmetic.
- A picklist reads as its option, in words.
{Status}is "Closed", not a code, so you can compare it to text. - Another formula on the same record gives its result. Formulas can build on each other.
- A roll-up gives its total (see Line Items).
A formula that reads itself, directly or through another formula, shows blank. Formulas reading formulas can go about six deep.
Reading a Linked Record: {Link.Field}
If the section has a Linked Section field, you can read a field on the record it points to. Write the link's name, a full stop, then the field on the other end:
{Qty} * {Item.Unit Rate}
Here Item is a Linked Section field on this record, and Unit Rate is a field on the item it points to. The linked record's own formulas and roll-ups work too, and links can chain, up to about six deep. If nothing is linked, the answer is 0 for that part.
If the linked record is behind a barrier, the formula reads it only when both records share that barrier: they're in the same section, or this section follows that link. Otherwise it treats the link as empty, because a formula's answer shows to everyone who can open the record.
Operators
+ - * /and brackets, as you'd expect. A minus sign in front of a number is fine:-{Discount}.- Dividing by zero gives a blank, not an error. Use
IFto guard against it (below). - Comparisons:
=,<>(not equal),<,<=,>,>=.==and!=work too. A comparison gives 1 if it's true and 0 if it isn't. - Text goes in quotes, single or double:
"Closed". Text is for comparing, so{Status} = "closed"works and capitals don't matter. Numbers compare as numbers, so "£1,000" is equal to 1000. TRUEandFALSEare 1 and 0.
There is no & for joining text, because a formula can't give text back.
Functions
| Function | What it does |
|---|---|
IF(test, then, else) | If the test is true, gives the first value, otherwise the second. Leave the else off and it gives 0. |
AND(a, b, ...) | 1 if every one is true |
OR(a, b, ...) | 1 if any one is true |
NOT(a) | 1 if it's false, 0 if it's true |
ISBLANK(a) | 1 if the field is empty |
ROUND(x, places) | Rounds to that many places. Leave places off for a whole number. A negative number rounds to tens or hundreds: ROUND(1270, -2) is 1300. |
ROUNDUP(x, places) | Always rounds away from zero |
ROUNDDOWN(x, places) | Always rounds towards zero |
MIN(a, b, ...) | The smallest |
MAX(a, b, ...) | The largest |
ABS(x) | Drops the minus sign |
SUM(a, b, ...) | Adds them up |
DAYS_SINCE(date) | Whole days from that date to today. A date in the future gives a minus number. |
DAYS_BETWEEN(from, to) | Whole days from one date to another |
HOURS_BETWEEN(start, finish) | Hours from one time to another |
Anything true is "not 0". A word with something in it counts as true, and an empty one as false.
IF only works out the side it takes
IF doesn't touch the side it doesn't need. That makes this safe:
IF({Qty} = 0, 0, {Cost} / {Qty})
When Qty is 0, the division never happens, so you get 0 instead of a blank.
HOURS_BETWEEN goes past midnight
Give it two times and a finish earlier than the start counts as the next day. A shift of 22:00 to 06:00 is 8 hours.
HOURS_BETWEEN({Start}, {Finish}) - 0.5
That is the hours less a half-hour break. You can give it dates with times too, and it works out the gap.
Examples
Weighted deal value
{Deal Value} * {Probability} / 100
Margin percentage, safe when revenue is blank
IF({Revenue} = 0, 0, ({Revenue} - {Cost}) / {Revenue} * 100)
A line total
{Qty} * {Unit Cost}
Add a fee only above a threshold
IF({Value} > 1000, {Value} * 0.05, 0) + 450
Never go below zero
MAX(0, {Budget} - {Spent})
1 for a live job that is overdue, 0 otherwise
IF(AND({Status} = "Live", DAYS_SINCE({Due Date}) > 0), 1, 0)
Days a job ran
DAYS_BETWEEN({Start Date}, {End Date})
Where They Show Up
On the record, a formula shows as a read-only value, and in the edit form too.
In a section's list view, tick Show In List View on the formula field and it gets a column. Formula columns sort as numbers, so 9 comes before 10.
As money. If a formula uses a currency field ({Qty} * {Unit Cost}), ORCA treats the answer as money. In Line Items and Totals it shows with your currency sign and two decimal places, and negative amounts show with a minus. A formula with no currency field in it ({Hours} * 1.5) stays a plain number.
In Totals and reports. Totals can total, average or find the lowest or highest of a formula, worked out record by record.
Notes
- Formula values are worked out when you look at them, so they are always current. Nothing is stored for you to edit.
- A blank value on a record usually means a division by zero, a formula reading itself, or a field that doesn't exist any more. Check the token names first.
- If you build sections through the API, formulas are checked there too, and a formula that can't be read is refused.
