Columns, Grouping, Merging And Calculations

Filtering decides which records a list shows. These four controls decide how that set of records is shaped on screen: which fields you see as columns, whether records are bundled under headings, whether records are collapsed into one line each, and what totals are worked out for you.

What This Is

Above every list screen there is a strip of small controls. Some of them narrow the data down (the Filter button, the saved-view switcher, the on-page search box). Four of them reshape what is already there:

Each is a small bordered button with no text. Hover over one for about a third of a second and its name appears — Columns, Group, Merge, Calculations.

None of them change your data. They change only what you are looking at. You can add and remove them freely without risk to any record.

Note Everything in this chapter depends on how your application is set up. An administrator can remove any of these four buttons from a particular screen, and they never appear on screens whose data comes from a query written by hand by an administrator. If a button described here is missing on your screen, see Why Your Screen Differs.

Why It Matters

A list screen is usually built for a general purpose — "all orders", "all invoices", "all applicants". These four controls let you turn that general list into an answer to a specific question without asking anyone to build you a new screen:

• "I do not care about the delivery address, but I do need the payment terms." — Columns.
• "Show me the same orders, but split by status so I can see where the pile is." — Group.
• "I want one line per customer, not one line per order." — Merge.
• "What is all of this actually worth?" — Calculations.

They also combine with filters and with saved views, so an arrangement you find useful can be kept and reused. See Saved Views And Queries.

How Long Your Choices Last

This matters enough to state before the details, because it surprises people.

Your columns, groupings, merges and calculations are carried in the page address. That means:

• They survive a page reload (F5) and the browser Back and Forward buttons step through them one change at a time.
• You can copy the address from the browser and send it to a colleague. They land on the same arrangement, provided they are allowed to see the same screen and the same fields.
• They live in the address, not in a personal setting. Navigate away to another screen and come back fresh, and the list returns to the arrangement its saved view defines.
• Switching to a different saved view discards all of them at once, without asking.

If you want an arrangement back tomorrow, save it as your own view — the saved view stores the filters and the columns, groupings, merges and calculations together. See Saved Views And Queries.

One related setting behaves differently: column widths that you drag on a table are stored in your browser, not in the address and not on your account. That is covered in Tables.

---

Columns

Opening The Picker

Click the Columns button. A large pop-up window opens immediately — it does not fetch anything from the server, so it shows the columns as of the last time the list was drawn.

The window has two lists side by side:

• Active — the columns currently shown, in the order they appear on screen (left column at the top of the list).
• Available — every other column you are allowed to see, which is currently hidden.

Moving Columns

• One at a time: drag an entry from one list to the other. Entries are draggable by grabbing them anywhere; the cursor changes to a grab hand.
• All at once: the footer has three small icon buttons.
  • Double chevron pointing left — Move all to active
  • Left-right arrow — Swap active and available
  • Double chevron pointing right — Move all to available

Swap active and available exchanges the two lists wholesale: everything you were showing becomes hidden and everything hidden becomes shown. It is useful when a screen shows the ten fields you do not need and hides the ten you do.

Ordering The Columns

Drag entries up and down within the Active list. The order in that list is the left-to-right order of the columns on screen. Dragging a field to the top of Active puts it in the first column position.

Searching The List

The box at the top of the window filters both lists as you type. It is a plain text match on the column names.

Be careful when combining search with the move-all buttons: Move all and Swap act on every entry in a list, including the ones the search box has hidden from view. Clear the search box before using them.

Saving And Closing

Press Save to apply your choice and close the window.

There is no cancel. Closing the window with the ✕, the Close button, Esc or a click outside it also applies whatever you dragged. If you moved something by accident and want it back, move it back before closing — or reopen the picker and put it right.

Which Columns Are Offered

The Available list is not the full set of fields on the record. A field is offered only when:

• your administrator marked it as usable as a column, and
• your role is allowed to see that field, and
• the screen's saved view has not deliberately excluded it.

The second point is the one that causes confusion. Two colleagues opening the same screen can see different entries in the picker, because field visibility is granted per role. If a column you expect is missing altogether, see Roles, Groups And Permissions.

One extra entry can appear here that is not a field at all: Merged count: followed by the name of the merged field, which shows up once a merge is active. It is described under Merge below.

Worked Example: A Column Set For A Chase Call

Example You are calling customers about overdue invoices. The invoice list is built for accounts staff and shows Invoice number, Issue date, Due date, Net, VAT, Gross, Cost center and Status — far too wide for a phone call, and it is missing the contact.

1. Click Columns.
2. Drag Net, VAT and Cost center from Active to Available.
3. Type cont into the search box, then drag Contact and Contact phone into Active.
4. Clear the search box and drag Contact to the top of Active so it becomes the first column.
5. Press Save.

The list redraws with five columns you can read at a glance. Nothing about the invoices changed — the hidden fields are still there, still filled in, and still exported if you put them back.

---

Group

What Grouping Does

Grouping keeps every record but arranges them into blocks. Each block gets a heading row that names the value the block shares, and the records sit underneath it.

Think of it as inserting dividers into a list, not as summarizing it. The record count does not change.

Adding A Grouping

1. Click Group.
2. A menu opens with a search box (placeholder "Start typing...") and an alphabetical list of the fields you can group by.
3. Click a field name. The list redraws grouped by that field.

A chip appears in the strip reading Grouped: followed by the field name.

What The Group Headings Show

Each heading row shows:

• a minus button on the left, which collapses that block; the icon becomes a plus and clicking again expands it;
• the text <field name>: <value> — for example Status: Awaiting payment;
• (none) as the value for records where the field is empty — those records are gathered into their own block;
• if you also have calculations active, a small badge per calculation to the right of the heading, showing that group's own figures as <calculation> <field>: <displayed> / <all>. Without an active calculation there is no badge.

Blocks always open expanded. Collapsing is a purely visual convenience and is not remembered — any action that redraws the list (changing a filter, changing the page, sorting, refreshing) expands everything again.

Removing A Grouping

Click the ✕ on the Grouped: chip. There is no confirmation. Removing a grouping also returns you to page 1 of the list. (Adding one does not — you stay on whatever page you were on.)

You can add more than one grouping; blocks then nest, each level indented a little further. There is no way to reorder existing groupings; remove them and add them again in the order you want.

Grouping Works On The Page You Are Looking At

This is the single most misread part of grouping.

The blocks are built from the rows on the current page, not from the whole result. So:

• If a group's records straddle a page boundary, that group appears on both pages, with some of its records on each.
• How many blocks you see depends on your page size.
• If you have a calculation active, its badge in a group heading shows two figures separated by a slash. The left one is worked out over that group's rows on this page only; the right one over every matching record in that group, on every page. The two therefore disagree whenever a group straddles a page boundary, and that is not a fault.

The practical move is to raise the page size before grouping — 100 or 250 rows per page usually brings a whole grouping into one view. Page sizes are covered in List Screens.

Grouping Changes The Order Of The List

Records have to be next to their group mates, so the grouping field becomes the list's first sort. Any sorting you set on a column still applies, but only inside each block. If you sorted by date and then grouped by status, you get blocks in status order, each block sorted by date.

Which Fields Make Good Groupings

Group by a field with a small, stable set of values:

• status, stage, state, priority
• category, type, department, cost center
• owner, assignee, responsible person
• country, region, branch
• a yes/no field

Group by a field with many distinct values — an amount, a date and time, a free-text note, an invoice number — and you get almost as many blocks as records, which is harder to read than the plain list.

Some fields are never offered:

• a relationship field that can hold more than one record is never groupable, and nothing on screen explains why — the field is absent from the menu;
• attachments and images, and a number of other specialized field kinds, are never groupable;
• an administrator can turn grouping off for an individual field.

Tags are not a field, so they never appear in this menu either. To work by tag, use the tag filter — see Tags And Folders.

If nothing at all is groupable on a screen, the Group button does not appear.

Which Layouts Show Group Headings

Boards behave differently enough to have their own chapter — see Boards. If a board shows "No active boards.", it has no grouping and you need to add one.

Worked Example: Orders By Status

Example Your Orders screen lists 340 open orders, newest first, and you want to know where they are piling up before the Monday meeting.

1. Set the page size to 250 so most of the set is on one page.
2. Click Group and choose Status.

The list redraws into blocks: Status: New, Status: In production, Status: Awaiting payment, Status: Ready to ship, plus a Status: (none) block holding the eleven orders where nobody has set a status yet — which is itself the finding you needed.

Collapse the blocks you are not discussing so the meeting sees only the two that matter. Every order is still there, still clickable, still editable; you have only rearranged the page.

---

Merge

Merge is the least obvious of the four, and the one most worth reading carefully.

What Merging Does

Merging collapses records. Pick a field, and every record that shares the same value in that field is folded into a single line. A list of 340 orders placed by 62 customers, merged by Customer, becomes 62 lines.

That is the difference from grouping, and it is a big one:

Adding A Merge

1. Click Merge.
2. The menu is the same shape as the Group menu — a search box and an alphabetical list. In fact the list of fields offered is identical to the grouping list, with the same exclusions (no relationship field that can hold more than one record, and nothing an administrator has switched off for grouping).
3. Click a field name.

A chip appears reading Merged: followed by the field name. Its ✕ removes the merge and returns you to page 1.

Adding The Count Column

Merging is much less useful without knowing how many records went into each line. That count is available as a column, but it is not shown automatically:

1. Click Columns.
2. In Available, find the entry beginning Merged count: and naming the merged field — it only exists while the merge is active.
3. Drag it into Active, position it where you want it, and press Save.

The column then shows, on every line, how many records were folded into that line.

That column is a summary figure rather than a real field, so it has no sort arrow and no filter funnel, and it disappears from the picker again when you remove the merge.

What The Other Columns Show On A Merged Line

Only the merged field is guaranteed to be meaningful on a merged line.

Every other column shows the value from one of the records that went into the line, and which one is not something you choose or can predict. On a customer-merged order list, the Order number, Order date and Amount columns each show the value from a single arbitrary order belonging to that customer — not a total, not the latest, not the first.

The same applies to clicking through: opening a merged line opens that one arbitrary record, not the set.

Caution A merged line is a summary, not a record. Do not read the values in its other columns as facts about the customer, and do not use a merged list as the basis for editing, selecting or acting on records. If you need to work on the underlying records, remove the merge first.

Merging And Calculations Together

Calculations are worked out on top of the merged lines, not on the records underneath them.

So on an order list merged by Customer, a Sum on the Amount column adds up one arbitrary order per customer. That number looks plausible and is wrong for almost any question you would want to ask.

If you want a genuine total across all the orders, take the calculation with the merge removed. If you want a per-customer total, that is a different kind of report — ask your administrator, since it is not something the filter bar can produce.

Worked Example: One Line Per Customer

Example Your Orders screen shows 340 orders for the current quarter. You want to know how many distinct customers ordered, and which of them ordered most often.

1. Click Merge and choose Customer. The list collapses to 62 lines and the counter at the bottom left now reads something like 20 / 62 — a page of twenty against sixty-two lines in total. Sixty-two is your answer to "how many customers".
2. Click Columns, drag Merged count: Customer from Available into Active, and press Save. Each line now shows how many of the 340 orders belong to that customer.
3. Read down the count column to find your heaviest customers.

What you must not do at this point: read the Amount column as that customer's spend (it is one arbitrary order), or add a Sum on Amount and quote it as quarterly revenue (it would add 62 single orders, not 340). Remove the merge before doing anything with values.

---

Calculations

The Calculations Available

Sum, Sum & round, Maximum, Minimum and Average are presented using the field's own display style — a money field's total carries its currency formatting, a duration field's total reads as a duration. That also means a total over an unusual field kind, such as a star rating, is drawn in that field's style and is rarely meaningful. The three counts are plain whole numbers.

Adding A Calculation

1. Click Calculations.
2. The menu lists the fields that can be calculated, alphabetically, with a search box at the top.
3. Hover over a field name. A submenu opens beneath it listing the calculations available for that field.
4. Click the calculation you want.

The menu stays open while you type in its search box and move across the field names — it does not close the moment you click inside it. Choosing a calculation, however, redraws the list, so add calculations one at a time and reopen the menu for the next one.

Note The submenu opens on hover only, with no tap equivalent. On a tablet or phone the calculation kinds are difficult or impossible to reach; use a computer for this control.

Each calculation adds a chip to the strip reading <calculation>: <field>, for example Sum: Amount. Clicking the chip's label opens a short description of what that calculation does. The ✕ removes it.

Nothing stops you adding the same calculation to the same field twice. You get two identical chips, two identical results, and a slower list. If you see duplicates, remove one.

Which Fields Offer Calculations

This is narrower than people expect:

• Number fields — whole numbers, decimals, money, ratings, durations — offer all eight calculations.
• Reference fields — fields that point at another record, such as Customer, Owner or Category — offer only Count distinct, Count empty and Count not empty.
• Everything else — text, dates, yes/no, drop-down choices, attachments — offers no calculations at all and does not appear in the menu.

So you cannot count how many records have an empty text field from this menu, and you cannot get "earliest date" from Minimum. An administrator can also switch calculations off for an individual field. If no field on the screen qualifies, the Calculations button does not appear.

Where The Results Appear

Above the list, below the control strip in a standard screen layout. One rounded chip per calculation, each showing a calculator icon, the calculation name, the field name, and then the figures:

Sum: Amount Displayed 84,300.00 All 612,450.00

The box is not there at all until you have at least one calculation active, and it disappears again when you remove the last one. On boards and calendars only the All figure is shown.

In group headings. If a grouping is also active, every group heading carries a small badge per calculation showing that group's own displayed / all pair. Hovering the badge shows the words "Displayed / All" to remind you which is which.

There is no totals row at the bottom of a table. Above the list and in the group headings are the only two places figures appear.

Displayed Versus All — The Two Figures That Matter

Every calculation is worked out twice and both answers are shown:

All ignores paging but honors everything else — your filters, your tag and folder selections, and the records your role is permitted to see. It is the honest total, and it is the one to quote.

Displayed changes when you turn the page; All does not. Two examples of what that looks like in practice:

• On page 1 of 31, Displayed 84,300.00 / All 612,450.00. Move to page 2 and the left number changes while the right number stays put.
• Set the page size to show everything, and the two numbers become identical.

Note that "Find on page...", the box that hides rows as you type, does not affect either figure. It only hides rows visually; both Displayed and All ignore it. See Filtering Your Data.

There is no switch to turn one of the two figures off, and the All figure has a cost, which is the next point.

The Cost Of The All Figure

The All figure is worked out by running a fresh calculation across the entire filtered set — every matching record, not only the page. That happens again on every change you make to the list: every filter you apply or remove, every page you turn, every sort, every column change, every refresh.

On a few thousand records nobody notices. On a large, lightly filtered set it is usually the slowest thing on the page, and the symptom is that the list takes visibly longer to redraw after every click.

Two habits keep this comfortable:

• Filter first, calculate second. Narrow the set down to what you actually want to total, then add the calculation.
• Take the calculation off when you are done reading it. Leaving four calculations attached to an unfiltered list makes the whole screen sluggish for as long as you keep it open.

Grouping multiplies this: with a grouping active, the figures are recomputed for each group as well as for the whole set. Twenty groups with two calculations is a great deal of work per click, and it is the usual explanation behind "the list got slow after I grouped it". See Large Lists And Performance.

Empty Values Are Counted As Zero

For Sum, Maximum, Minimum and Average, a record with the field left empty is treated as if it held zero. This is worth knowing because it changes the answer:

• Minimum over a column with any empty values reports 0, not the smallest real value.
• Maximum over a column where every value is negative reports 0.
• Average is pulled toward zero by every empty record, because those records still count in the division.

If empties are a real possibility, add Count empty alongside your Sum or Average. It tells you immediately how much of the answer is made of nothing.

Also note what Count distinct actually counts: how many different values appear in that field, not how many records. Three orders all worth 1,000 contribute one distinct value between them.

Worked Example: Total Invoiced Value For A Filtered Set

Example Finance asks what is still outstanding on last month's invoices.

1. On the Invoices screen, add the filters you need — Status is not Paid, and Issue date using the Special preset Previous month. See Filtering Your Data. The counter at the bottom reads 20 / 617.
2. Click Calculations, hover Gross amount, click Sum.
3. Read the chip above the list: Sum: Gross amount Displayed 84,300.00 All 612,450.00. 612,450.00 is the figure to report — it covers all 617 invoices, not the 20 on screen.
4. Add Count empty on Gross amount as a sanity check. It reads All 0, so no invoice is missing its amount and the total is trustworthy. Had it read All 34, the total would have been understated by whatever those 34 invoices are worth.
5. Optionally, click Group and choose Customer to see the same total broken down per customer in each heading. Expect the list to redraw more slowly from here on.
6. When you are finished, remove both calculation chips with their ✕ so the screen is quick again for the next person who opens it — or use the bookmark button above the list to keep the whole arrangement as your own saved view.

---

How The Four Work Together

Columns and the rest. Grouping, merging and calculations do not require the field to be an active column. You can group by Status without showing the Status column, and total a field you have hidden. The one exception is the Merged count column, which exists only in the Columns picker.

Group and Merge together. Both can be active at once. The merge is applied first — records are collapsed into lines — and then those lines are grouped under headings. It is legitimate but hard to read, and the caution about merged lines applies to every line in every block.

Filters. Filters decide the set; these four decide its shape. Every figure and every group is computed over the filtered set, including filters your administrator has fixed on the screen that you cannot see or remove.

Paging. Grouping is built from the rows on the current page, so a group can appear on two pages. The Displayed figure is the current page; All is not. When a merge is active, the row counter and the number of pages both count merged lines, not underlying records. Adding a grouping, merge or calculation leaves you on your current page; removing a grouping or a merge sends you back to page 1.

Sorting. A grouping takes over as the list's first sort. Your own column sort still applies inside each block. Sorting is covered in Tables.

Refresh. The circular-arrow button re-runs everything — same filters, same columns, same grouping, same merge, same calculations, same page — against current data. It is the supported way to pick up changes colleagues have made, because lists never update themselves.

Export. Exports contain rows, not analysis:

• The file contains the columns you have made Active, in your order — so the Columns picker is also how you shape an export.
• Calculation figures are never in the file. There is no totals row and no group subtotal in an exported spreadsheet.
• Group headings are not in the file either; a grouping is a screen device only.
• A merge changes which rows the list produces, not merely how they are drawn. Do not assume an export taken while a merge is active contains every underlying record — remove the merge before exporting if you need one row per record.

Exporting itself is covered in Action Buttons And Exporting.

Saved views. Saving a view with the bookmark button stores your filters together with your columns, groupings, merges and calculations. Switching to a different saved view throws all of them away without warning.

---

Tips And Gotchas

• Closing the Columns window saves. ✕, Close, Esc and clicking outside all apply your drags. There is no cancel.
• Move all and Swap ignore the search box. They act on entries the search has hidden too. Clear the search first.
• A relationship field that holds several records cannot be grouped or merged. It is absent from both menus, with no message explaining the absence. Tags are not a field and never appear there.
• A missing button means nothing qualified. If no field on a screen can be grouped, or none can be calculated, the button is not shown rather than showing an empty menu. It is not a fault.
• Groups and figures you did not add. An administrator can fix a grouping, merge or calculation onto a screen. It takes effect, group headings and result chips appear, but no chip shows in the strip and there is nothing to click to remove it.
• Collapsed groups do not stay collapsed. Any redraw of the list expands them all again.
• In a non-English interface, group heading badges stay English. The chip above the list and the chip in the strip are translated; the small badge inside a group heading shows the original English calculation name. The figures are the same either way.
• Duplicate calculations are allowed. Two identical chips mean you clicked twice; remove one.
• The calculation submenu needs a mouse. It opens on hover, with no tap alternative.
• The list of fields you are offered depends on your role. A colleague may be able to group, merge or total by a field that is not in your menus at all.
• Merged lines are summaries. Their non-merged columns show one arbitrary member's values, and clicking one opens that one record.
• A long address is normal. The page address carries your whole arrangement, which is why it can become long and unreadable. It still works when pasted to a colleague.

---

What To Ask Your Administrator For

• "Make field X available as a column" — when a field you need never appears in the Columns picker for anyone.
• "Give my role permission to see field X" — when a colleague can add a column, group or total that you cannot.
• "Make field X groupable / calculable" — when a field appears on records but not in the Group, Merge or Calculations menus, and it is not a relationship field that holds several records.
• "Add the Columns / Group / Merge / Calculations button back to this screen" — when the strip above a list is missing a control described here.
• "Build me a proper per-group report" — when what you actually need is a total per customer or per month rather than a single figure. Merging cannot do this, and calculations on a merged list give a misleading answer.
• "Raise the default page size on this screen" — when you routinely group and need more rows per page to see a whole grouping.

See also What To Ask Your Administrator For.

---

Related Chapters

• Filtering Your Data
• Saved Views And Queries
• Tables
• Action Buttons And Exporting
• Large Lists And Performance