This article explains how to group the output of a Custom Extract so related records are kept together, labelled with a generated identifier, and presented in the layout your file needs.

By default every row in a Custom Extract output file is a standalone record, and the file has no sense of which records belong together. Grouping changes that. You choose up to three fields to group by, and records that share the same values across those fields form a group. Lumary orders the file so each group's records sit together, and adds a generated identifier column that labels each group.

Two separate settings control how the file looks. The display mode controls how the identifier column behaves. Compact Grouping controls how the group-by field columns behave. The two are independent, so any display mode can be combined with Compact Grouping switched on or off.

Grouping is set up in the ordering and grouping section of the CSV Column Mapping stage. This article explains how to:

• understand how grouping affects row and column order
• add group-by fields
• choose a display mode
• configure the identifier column
• use Compact Grouping, and
• regenerate a grouped file.

Grouping is a configuration-time setting. You set it up once on the Custom Extract, and every run of that extract applies it.

How grouping affects row and column order

Grouping changes both the order rows appear in and the order columns appear in.

Group-by fields always lead the ordering, and your Sort By rules are applied after them. This means a group's rows can never be split apart by sorting. Within each group, rows are ordered according to your sort rules.

If a field appears in both Group By and Sort By, the direction from Sort By is preserved. Grouping on Client while sorting Client descending gives you descending client groups, not ascending.

The generated file lays columns out in a fixed order.

If you leave ID Field (Header) Name blank, no identifier column is added to the file at all. Grouping still affects row order and Compact Grouping still works, but nothing labels the groups.

Back to links

Add group-by fields

1. Open the Custom Extract record you want to configure.
2. Go to the ordering and grouping section of the CSV Column Mapping stage.
3. Switch the toggle from Sort By to Group By.
4. Click Add field and select the field to group by.
5. Repeat step 4 for any additional fields, up to a maximum of three.
6. Reorder the fields as required. The first field is the outermost grouping level.
7. Click Save.

Switching the toggle back to Sort By hides the Group By panel but does not delete your grouping configuration. Both configurations are stored separately, and both are kept when you save.

To remove grouping entirely, delete every group-by field. The extract returns to its standard ungrouped output with no identifier column.

You can select a maximum of three group-by fields. Once three are configured the Add field button is hidden, so remove an existing field before adding another. Only direct fields on the extract's record type can be used. Fields from related records are not offered in the field picker.

The ordering section always opens on the Sort By view, even when only a Group By configuration exists. If your grouping settings appear to be missing, switch the toggle to Group By. The configuration is still saved.

Back to links

Choose a display mode

The display mode controls how the identifier column behaves.

1. In the Group By panel, locate the display mode control.
2. Select First Row Only, Every Row, First Row With Blanking, or Sequential Number.
3. Click Save.

Changing the display mode does not clear your ID Field (Header) Name, Template Pattern, or Compact Grouping settings. They are retained and applied to the new mode.

All examples below group by Client, with an ID Field (Header) Name of Invoice Number, a Template Pattern of INV-{0000}, and Compact Grouping switched off.

First Row Only

The identifier appears on the first row of each group, and rows after that leave it blank. The identifier increments at each group boundary. Use this when a person will read the file and you want the group breaks to be visually obvious.

Every Row

The identifier repeats on every row within the group and increments at each group boundary, with no blanks. Use this when the file goes to another system, because import tools, pivot tables, and reporting platforms generally need a value on every row.

First Row With Blanking

A dedicated header row is inserted at each group boundary. That header row contains only the identifier and the group-by field values, and every other column is written as an empty cell. The detail rows beneath it have a blank identifier. This is the most report-like of the four modes, because each group reads as its own block with its own heading.

Sequential Number

The identifier numbers the rows within each group, starting at 1 and counting up. When a new group begins the counter resets to 1. The identifier appears on every row. Use this when you need a line number within each group, such as a line item count on an invoice or a position reference within a batch.

The Template Pattern still applies, so with INV-{0000} the first row of each group is INV-0001.

Sequential Number does not give each group a unique reference. The same numbers reappear in every group. If you need a reference that identifies the group itself, use First Row Only or Every Row.

Back to links

Configure the identifier column

The identifier is the generated value that labels each group.

1. In ID Field (Header) Name, enter the column heading for the identifier, for example Invoice Number. Leave it blank if you do not want an identifier column.
2. In Template Pattern, enter the identifier format using braces around the zeros, for example INV-{0000}.
3. Check the live example shown beneath the field. It updates as you type and shows what the first identifier will look like.
4. Click Save.

The following table explains the purpose of the fields.

The template pattern is applied the same way across all four display modes, and renders correctly at both low and high group numbers without breaking the padding.

If the pattern does not match the required format, a validation message appears and the live example is replaced by an error. Correct the pattern before saving. Clearing the field resets the example.

Back to links

Use Compact Grouping

Compact Grouping is a separate setting that works with any display mode. When it is switched on, the group-by field values appear only on the first row of each group, and rows after that leave those columns blank. Detail columns are unaffected and stay populated on every row.

1. In the Group By panel, switch Compact Grouping on.
2. Click Save.

Both the Compact Grouping setting and the display mode are stored together and reloaded the next time you open the configuration. Turning Compact Grouping off restores the group-by values on every row.

The example below uses First Row Only with Compact Grouping switched on. Both the identifier and the Client column blank out after the first row of each group.

Compact Grouping has no effect if your group-by fields are not mapped as CSV columns, because it only blanks columns that are actually in the output. It does still work when no identifier column is configured, which makes it a useful way to tidy a grouped file without adding an extra column.

The following table explains how Compact Grouping combines with each display mode.

Back to links

Regenerate a grouped file

Each time you click Generate File, a new file is created rather than the previous one being overwritten. Records already picked up by an earlier run are not re-processed.

Changing the template pattern between runs affects only the next file generated. Files already produced are untouched. Regenerating after a display mode change produces output matching the newly selected mode, with no leftover behaviour from the previous file.

The generated file always uses the same filename. Rename or move files as you download them if you need to keep several runs side by side.

Back to links