Script a dashboard metric

Before you start

• Show a number with a metric block

A metric is one script that ends in a value. This page is for the scripts that go past a plain count: rates, totals with money formatting, and figures that depend on who is looking. It assumes you can already add a metric. If not, start with Show a number with a metric block.

Which helper should I pick?

Press Ctrl + Space in the Value editor and type FunctionManager. to list them.

○ ○ ○Beezr

×

The editor opens larger from the expand button. The hint says Ctrl + Space suggests modules, fields and functions.
Suggestions list each helper with its arguments.
Done closes the editor. The script is already on the block.

You want	Use	Why
How many records match	evaluateCount	One call, no list in memory
A total or an average	evaluateSumFunction, evaluateAverageFunction	They work on the whole module
One value from the first match	loadValueByClause	Returns an empty text when nothing matches
Something per record	loadRecordsByClause	Returns a list of records, or null

Prefer the aggregates. A list loop is the last resort, and it is the slowest option.

Behind the scenes

Every block on a dashboard is calculated on its own, one request each, and each block displays its result as it arrives. Run test shows the same result the block will show.

How do I show a rate?

This shows the share of invoices that are paid, and falls back to 0% when there are no invoices.

1. In the Page builder, add a Metric and set the Label to Invoices paid.
2. In Value, type the script below.
3. Click Run test. A result such as 64% means it ran.
4. Click Save.

var paidId = FunctionManager.resolveLookupValue("invoice_status", "Paid");
var paid = parseInt(FunctionManager.evaluateCount("cm_invoices", "WHERE cm_invoices.status = " + paidId), 10);
var all = parseInt(FunctionManager.evaluateCount("cm_invoices", "WHERE 1 = 1"), 10);
var rate = all > 0 ? Math.round(paid / all * 100) : 0;
isNaN(rate) ? '0%' : rate + '%';

Three details keep it safe. The count is text, so each one goes through parseInt. The division only happens when all is above zero. A missing lookup value makes paid a NaN, and the last line turns that into 0%.

How do I format a total as money?

evaluateSumFunction returns text formatted with 2 decimal places. Convert it, then format it.

var openId = FunctionManager.resolveLookupValue("po_status", "Open");
var total = parseFloat(FunctionManager.evaluateSumFunction("total_amount", "cm_purchase_orders", "WHERE cm_purchase_orders.status = " + openId));
isNaN(total) ? '0.00' : FunctionManager.convertToMoneyFormat(total);

convertToMoneyFormat gives the number with two decimals. The currency code goes in the Label, for example Open orders (USD).

How do I show only my own records?

The user token gives the person who opens the dashboard. It arrives in quotes, so joining it to a clause text gives a clean number. This counts the tasks assigned to the viewer. It assumes assignee is a User field.

var mine = parseInt(FunctionManager.evaluateCount("cm_tasks", "WHERE cm_tasks.assignee = " + $this$.$user$.$id$), 10);
isNaN(mine) ? 0 : mine;

Each person sees their own number, because the script runs for the viewer.

What are the rules for clauses?

• Start with WHERE and use the full name, cm_invoices.status. Never use a bare status.
• Text values need quotes: cm_vendors.country = 'Egypt'.
• Compare lookup fields with the lookup’s id. Get the id with resolveLookupValue(lookupTable, name), and the name back with resolveLookupIdValue(lookupTable, id).
• evaluateCount, evaluateSumFunction and evaluateAverageFunction use your clause as written. loadRecordsByClause and its relatives tidy it first.
• End the script with an expression. Write total;, not return total;.

loadRecordsByClause returns null when nothing matches, so wrap the loop in if (records).

How do I know it works for everyone?

Run test runs the script as you. A metric that uses the user token or a lookup can give a different answer to someone else, so open the saved dashboard in App space with a second account before you rely on it. People see changes the next time they open the page.