Formulas

Formulas are how Grindstone works things out: what a view’s column shows, which entries a view lets through, what a status says, and what an action writes. This topic covers starting from a pattern that asks you questions, building a formula piece by piece, and typing one with suggestions.

Where formulas appear

You’ll find formula fields in several places:

• A view’s Filter and Visibility, and each column’s Display, in Create View and Edit View. See Views.
• Each status’s Formula and Visibility, in Statuses. See Statuses and alerts.
• Each action’s Target, Only Where and the Value of each write, in Actions. See Actions.
• Timing formulas, in Timing Formulas. See Timing readout and label.

A formula is worked out for one thing at a time: a view’s formulas for each row, a status for each person, an action’s values for each thing it writes to, and a timing formula for the time slice being timed.

A formula field shows the formula’s parts. Click it, or its Edit button, to open the formula dialog. A field that can be left empty has a Clear button, and a field you’ve changed has an Undo button. If a field is outlined as an error with “Parts of this formula don’t fit together,” open it to see why.

Starting from a pattern

When you choose Add Column in a view (see Views), Add Status in Statuses, or add a timing formula in Timing Formulas, Grindstone first offers patterns: formulas that ask you a few questions instead of making you write anything. The list has three parts:

• Patterns, the ones that come with Grindstone.
• Saved Patterns, the ones saved in your workspace with Save as Pattern.
• Write a Formula, which starts from an empty formula in the builder.

Choose a pattern, read its description, and answer its questions. Grindstone shows the name the new column, status or timing formula will get; you can rename it afterward. Then choose Add Column (or Add Status, Add Readout or Add Label) to use it as it is, or Customize to open the result in the builder first. Back returns to the list.

If no pattern suits the kind of view you’re editing, Add Column opens the builder directly.

Patterns that come with Grindstone

Which patterns you’re offered depends on what the view shows. Statuses are offered the patterns for people.

Patterns that ask about a period offer All Time, Today, This Week and This Month. Patterns that ask about rounding offer Exactly, Up to the Tenth of an Hour, To the Nearest Tenth of an Hour, Up to the Quarter Hour and To the Nearest Quarter Hour.

A question about a trait lists only the traits that fit. If none fit yet, Grindstone says so; add one in the traits view and come back. See Traits.

Changing a pattern’s answers

When Grindstone recognizes a formula as one a pattern makes, a Change Answers button appears beside it. It reopens the pattern’s questions with your earlier answers filled in, so you can change them without touching the formula. Change Answers is offered for columns, statuses, timing formulas and actions.

Saving your own pattern

Save as Pattern appears beside a formula that’s valid, wherever patterns are offered. It opens a dialog where you give the pattern a Name and an optional Description, both shown where patterns are offered.

The dialog then lists each value, trait, parameter and specific item in the formula. For each, turn on Ask to make the pattern ask about it, and write the Question it asks. For a trait or parameter you can also fill in In the Name: where that text appears in the pattern’s name, the answer takes its place. Anything not asked about stays as it is. A parameter is always asked about, because a parameter belongs to its view.

Choose Save. The pattern is offered under Saved Patterns from then on, and it’s included in presets you make with Create Preset. See Presets. To remove one, use its Delete Pattern button in the list. Formulas already made from it are kept, but they no longer offer Change Answers.

Building a formula

The formula dialog normally opens in Build mode, where a formula is laid out as chips you click. An empty spot shows a Choose chip; click it to fill the spot.

Beneath the formula, the dialog shows each function the formula uses, with its arguments and a description. Until every empty spot is filled, it says “Fill in every empty spot to finish,” and OK stays unavailable.

To change a part, click its chip and choose again. To clear a part, use the close mark on its chip. A function that takes a varying number of arguments shows Add Argument and Remove Argument buttons.

Choosing a part

Choose opens a list of what fits that spot, with a Search box. It’s divided into sections, showing only those that fit:

• Value: a value you enter, such as Yes or No, Integer, Decimal Number, Floating Point Number, Text, Date & Time, Time Span or Date & Time Range. Time spans are entered as hours, minutes and seconds, as in 1:30:00.
• Parameter: one of the view’s parameters. Only user views have parameters.
• Function: each with its description.
• Trait: one of your traits.
• Entity: a specific person, work item, list value and so on.
• Intrinsic Value: a built-in fact about an entry, such as when a time slice started.
• Scope and Subject: the kinds of things some functions look across.
• Formula: a formula worked out for each entity the function looks at.

When the spot is a function’s argument, the top of the list names which argument it is.

When parts don’t fit

If a part doesn’t fit where it is, its chip is highlighted and the dialog says “Parts of this formula don’t fit together. Point at the highlighted parts to see why.” Pointing at a highlighted chip tells you what’s wrong, such as “These arguments don’t fit this function” or “This needs a different number of arguments.”

Typing a formula

Switch the toggle at the top of the formula dialog from Build to Type to write the formula as text. Type is available once every empty spot is filled, and the text starts as the formula you built. You can switch back to Build whenever the text reads as a formula. Until you close Grindstone, the next complete formula you open starts in whichever mode you used last; a new or unfinished one starts in Build.

Problems are underlined as you type and listed beneath the text, such as “There’s no function by this name” or “This name needs a closing bracket.”

What you can type

Function names aren’t case sensitive. If more than one thing goes by a name, choose it from the suggestions and Grindstone uses its id instead.

Suggestions

Press Ctrl+Space for suggestions of what fits where your cursor is. Suggestions also appear as you type an opening bracket, @, an opening parenthesis, a comma, an opening brace or a space. When your cursor is inside a function’s parentheses, Grindstone shows the function’s arguments and highlights the one you’re on.

Formulas inside formulas

Functions that add up, count or pick, such as Sum, Count, Average, Min, Max and Value, look across many things at once. Normally they look at the things related to the entry the formula is worked out for: a work item’s time slices, a person’s assignments, and so on. Given a Scope, they look at every entity of that kind instead.

Two of their arguments are worked out once for each thing they look at, not once for the entry:

• Measure is what’s added up, counted or picked: a built-in fact such as Time Slice Range, a trait, or a formula of its own.
• Condition is a Yes or No formula. Only the things where it’s Yes are counted.

In Build, choose these from the Formula section of the list. In Type, write them in braces. Inside the braces, the formula is about the thing being looked at, not the entry, and Value reads that thing’s own value: inside braces, Value(TimeSliceRangeStart) is when that time slice started. A formula in braces can use any function, including other functions with braces of their own.

To see how a pattern does it, choose the pattern, answer its questions, choose Customize, and switch to Type.

Example: time on certain days of the week

To add up a work item’s time on Mondays, Wednesdays and Fridays, add a column to a work item view with Write a Formula, switch to Type and enter:

Sum(TimeSliceRange, {Or(Equal(DayOfWeek(Value(TimeSliceRangeStart)), 2), Equal(DayOfWeek(Value(TimeSliceRangeStart)), 4), Equal(DayOfWeek(Value(TimeSliceRangeStart)), 6))})

• Sum(TimeSliceRange, …) adds up the lengths of the work item’s time slices.
• The part in braces is worked out for each time slice. Value(TimeSliceRangeStart) is when that time slice started, DayOfWeek turns it into a day number in the workspace time zone (1 for Sunday through 7 for Saturday), and Or and Equal keep days 2, 4 and 6.
• A time slice counts toward the day it started on, even if it runs past midnight.

The same shape counts only your own time: Sum(TimeSliceRange, {Equal(Value(AssignmentPerson), Perspective())}).

Functions

Functions do the work in a formula: arithmetic, comparisons, text, dates and times, and totals across related things, such as adding up the time on a work item’s time slices.

Every function, with the description Grindstone shows for it, is listed in Formula functions.

If you’d like help with a formula, Speak with a specialist.