Articles in this section

Create and configure a Custom Extract

  • Updated

Custom Extracts let you build and configure your own data extracts in the format you need, without raising a request with Lumary Support.

With a Custom Extract, you choose the records to pull, decide how each value is calculated, and define exactly how the resulting CSV file is laid out.

The result is a repeatable, self-service export you can feed straight into your accounting system, reporting tool, or business intelligence platform. Once an extract is configured and activated, your team can run it whenever they need to, and the output will be consistent every time.

Before you begin...

Know which record type holds the data you need. The record type you select determines which fields are available to extract. It also helps to have your target file format ready, including column headers, column order, date formats, and the file name.

You will be working with records, fields, and filter conditions, so some familiarity with your organisation's data model is assumed.

Required packages and permissions

Available with: Lumary 2.15 or Lumary 26 and later

Updated in: Lumary 2.20 and Lumary 26 minor release 1

Permission: System administrator

Move through the pathway with Mark Stage as Complete at the bottom of each stage, and go back with Back. 

You can click Save at any point to store your progress without advancing to the next stage. An extract does not need to be finished in one sitting, and your configuration is only available to your team once Is Active is switched on. Save before you switch between Sort By and Group By, move to another stage, or leave the page, because unsaved changes are discarded without warning.

Create the extract record

There are two ways to start, and the one you choose changes what you configure afterwards.

  • New Custom Extract - build an extract from scratch against any record type. You choose the data, the filters, the field mapping, and the CSV layout.
  • Existing Extract Custom CSV - keep the data and logic of a standard Lumary extract, and change only how its CSV file is laid out.
new custom extract options

New Custom Extract

  1. Open Custom Extracts and click New.
  2. Select New Custom Extract.
new custom extract name and object name
  1. Complete the fields in the New Custom Extract modal.
Field Purpose
Name Name of the custom extract.
Object Name The record type that will be extracted. Choose carefully - Object Name is locked once the extract record is created, so changing it means starting again with a new record.
Is Active Switch on when the extract is ready to use.
  1. Click Save. The extract record opens and the guided pathway begins at the Extract Configuration stage.

On this path you configure the extract end to end, from the records it pulls through to the layout of the file. Your stages run Extract Configuration, then Extract Item Field Mapping, then CSV Column Mapping, then Review & Complete.

Existing Extract Custom CSV

This option re-formats the CSV output of a standard Lumary extract. It does not define a new data source. The records, the filters and the values still come from the standard extract, and you control only how the file is laid out.

  1. Open Custom Extracts and click New.
  2. Select Existing Extract Custom CSV. The Name and Object Name fields are replaced by an Extract Type picklist.
  3. Select the Extract Type you want to re-format. The record takes its name from the extract type you choose, so you do not name it yourself.
extract type picklist
  1. Switch Is Active on when the custom CSV is ready to use.
  2. Click Save. The extract record opens at the Extract Configuration stage.

Not every extract type appears in the Extract Type list. An extract type is hidden once another Custom Extract record uses it, because each standard extract can have only one custom CSV.

On this path you configure the CSV layout only. Filter conditions and field mapping are not available, because the filters are set on the standard extract page and the values are already populated by the standard extract. Your stages run Extract Configuration, then CSV Column Mapping, then Review & Complete.

Back to links

Configure the extract

In the Extract Configuration stage, you define what data your extract will pull.

  1. Set any Additional Filters you want available at run time. Each filter has a corresponding variable that you use to reference it in your filter conditions.
additional filters with site selected

Selecting an additional filter here only makes it available on the extract creation screen. It has no effect on its own. You must also reference its variable in the Filter Condition Builder for it to actually filter your results, for example Site__c = :extractSite.

Filter Purpose Variable
Site Limit the extract to a specific site. :extractSite
Client Limit the extract to a specific client. :extractClient
Account Limit the extract to a specific account. :extractAccount
Service Agreement Limit the extract to a specific service agreement. :extractAgreement
Region Limit the extract to a specific region. :extractRegion
NDIS Registration Number Limit the extract to a specific registration number. :extractNDIS
  1. Build your filter conditions in the Filter Condition Builder.

Date range is not one of the Additional Filters selections. The :extractStartDate and :extractEndDate variables are always available, so you can filter on a date range in the Filter Condition Builder without selecting anything here. Filtering on a date range is strongly recommended for high-volume record types such as Service Delivered.

Back to links

Build filter conditions

The Filter Condition Builder defines which records are included in the extract. Conditions work in a similar way to filters on a report. Only records that satisfy your conditions are extracted.

  1. Select the field you want to filter on.
  2. Select an operator.
  3. Enter a static value, or reference one of the extract variables listed above. For example, use :extractStartDate to filter on a date supplied at run time, or :extractNDIS to filter on the registration number the user selects.
  4. Combine conditions using logical operators where needed.
  5. Click Check Syntax to validate your condition before saving.
filter condition builder with no syntax errors
  1. Click Mark Stage as Complete to continue.

Always run Check Syntax before you save. An invalid condition will prevent the extract from returning records.

Field names in the examples below are shortened for readability. Fields in your org carry a namespace prefix, for example enrtcr__Site__c. Use Check Syntax to confirm the exact name.

Comparison operators

Operator Description Example
= Equal. True if the value in the field equals the value in the expression. Status = 'Approved'
> Greater than. Amount > 500
< Less than. Amount < 500
>= Greater than or equal. Service_Date__c >= :extractStartDate
<= Less than or equal. Service_Date__c <= :extractEndDate
!= Not equal. True if the value in the field does not equal the specified value. Status != 'Cancelled'
LIKE Search for a pattern. Use % as the wildcard. Support_Item_Number__c LIKE '01_%' matches all support items beginning with 01_, but not those where 01_ appears later in the number.
IN Specify multiple possible values for a field. Status IN ('Ready to Claim', 'Claimed', 'Paid')

Logical operators

Logical operators combine field expressions.

Operator Description Example
AND True if both expressions are true. Status = 'Approved' AND Region__c = 'NSW'
OR True if either expression is true. Region__c = 'NSW' OR Region__c = 'VIC'
NOT True if the expression is false. NOT Status = 'Cancelled'

Parentheses are the round brackets ( and ). Use them to group expressions whenever you mix AND, OR, and NOT in the same condition, so it's clear which parts belong together. Without them, Status = 'Approved' AND Region__c = 'NSW' OR Region__c = 'VIC' returns every VIC record regardless of status. Write Status = 'Approved' AND (Region__c = 'NSW' OR Region__c = 'VIC') instead.

Back to links

Map fields to Extract Items

In the Extract Item Field Mapping stage, you capture values from your source record onto each Extract Item. An Extract Item is a single row of extracted data. Create one mapping for every piece of data you want in the extract.

These mappings feed the next stage. A value travels from a field on your source record, onto a field on the Extract Item, and then into a CSV column - so a field you map here is the field you select in the Line column when you build the CSV layout. Map everything you want in the file, including values you plan to calculate or combine.

  1. Click + Add Mapping.
  2. Complete the mapping.
Field Purpose
Mode

Basic to map a field straight across, or Advanced to build a calculated value in the Formula Builder.

Choose the mode before you set the target field. Switching between Basic and Advanced clears the Extract Item Target Field, and you will need to select it again.
Extract Item Target Field The field on the Extract Item that will store the extracted value.
Source Field Where the value comes from. Search for a field on the extract's record type, or use the arrow beside a relationship to drill into a related record, for example Service Delivered to Client to First and Last Name. A breadcrumb at the top of the list shows where you are.
  1. Choose your mapping approach.
  • Basic - map the source field directly to the target field with no calculation.
    basic extract item field mappings
  • Advanced - open the Formula Builder to construct a calculated value using standard formula syntax. Click Validate to check the formula. A confirmation or a plain-language error message appears beside the button.
    advanced formula builder with a valid formula
  1. Repeat for each value you need.
  2. Click Mark Stage as Complete to continue.

The Extract Item Target Field and the Source Field must be compatible data types. Text fields and multi-select picklists cannot be mapped to each other, and Date and Date/Time fields are distinct. The field type is displayed alongside each field name to help you choose correctly.

If the field you are expecting is not listed, check two things. The available fields depend on the record type selected in Object Name, and on data type compatibility with the field you are mapping to.

Advanced Mode

The formula editor in Advanced Mode uses standard Salesforce formula syntax. If you're new to writing formulas, or you want to see the full range of what's available, the following Salesforce Help articles are a useful reference:

Back to links

Define the CSV output

The CSV Column Mapping stage controls the structure of the file that gets produced.

  1. Enter a File Name for your CSV output. It must end in .csv, or the file will not generate.
csv file name and column mapping
  1. Click Add Row to add a column, then complete the details.
Field Purpose
Order Position of the column in the file.
Header The column heading that appears in the CSV.
Selection Mode Field to pull a value from the Extract Item, or Static Text for a fixed value.
Type When Selection Mode is Field: Basic or Date. When Selection Mode is Static Text: the static value type.
Line The Extract Item field this column pulls its value from. The list contains every Extract Item field, so choose one you mapped in the previous stage. An unmapped field produces an empty column. Available when Selection Mode is Field.
Select Date Format Available when Type is Date.
  1. Add a column for every field you want in the output file.
  2. Set your sorting and grouping rules.
  3. Click Mark Stage as Complete to continue.

Back to links

Sort the output

Sort rules control the order rows appear in within the output file.

  1. Go to the ordering and grouping section.
  2. Click + Add Sort Rule.
  3. Select the field to sort on. Fields are listed by their label, so you can type the first letter of the label to jump to it.
  4. Select the order, either Ascending (smallest to largest) or Descending (largest to smallest).
  5. Choose whether empty or blank values appear last or first.
  6. Add further rules as needed, up to five. Use the arrow buttons to change their order.
two sort rules with plain language summary

As you add rules, the sorting sequence is described back to you in plain language, so you can confirm the file will be ordered the way you expect.

Back to links

Group the output

Grouping keeps related records together in the output file and adds a generated identifier column that labels each group. You choose up to three fields to group by, and records that share the same values across those fields form a group. Grouping by Client, for example, puts all of one client's rows together.

Lumary then does two things with those groups. It orders the file so each group's rows sit together, and it can add a column at the front of the file that labels each group with a generated reference, such as INV-0001. This column is optional. Your groups may not include a reference ID, but the EXIs will still be grouped together in the CSV.

Two settings then control how the file reads:

  • Display Mode sets where the label appears: once at the top of each group, on every row, as its own header row above each group, or as a running count that restarts with each new group.
  • Compact Grouping sets whether the group-by columns repeat their values on every row, or show them only on the first row of each group.

Switch the toggle from Sort By to Group By, then complete the fields below.

group by fields with template pattern preview
Field Purpose
Group By fields The fields that decide which records belong to the same group. Add up to three. The order sets the grouping hierarchy, so the first field is the outermost level. Only direct fields on the extract's record type are available.
Display mode How the identifier column behaves. First Row Only shows the identifier once per group. Every Row repeats it on every row. First Row With Blanking adds a dedicated header row for each group. Sequential Number numbers the rows within each group and resets at every new group.
ID Field (Header) Name The column heading for the generated identifier, for example Invoice_No. Leave it blank if you do not want an identifier column at all.
Template pattern The format of the generated identifier. Use braces to mark the number and its padding, for example INV-{0000} produces INV-0001, INV-0002, and so on. Template Pattern is required once you enter an ID Field (Header) Name, and it must contain a zero-padding token - braces containing only zeros, such as {00} or {0000}. The number of zeros sets the padding length.
Compact grouping When switched on, the group-by field values appear only on the first row of each group. Detail columns stay populated on every row. It can be switched on independently of the display mode you choose.

Grouping and sorting work together, and your group-by fields always lead the ordering. Sort rules are applied within each group, so a group's rows are never split apart. If a field is used in both, the direction from Sort By is preserved.

Find out more about how to group the output of a Custom Extract.

Back to links

Review and activate the extract

The Review & Complete stage shows a summary of your configuration.

  1. Check the summary - name, active state, record type and CSV file name.
  2. Click Back to return to an earlier stage if anything needs correcting. Each stage you pass back through reverts to incomplete, so mark them complete again on your way forward.
  3. Go to the Extract Configuration stage and switch Is Active on to make the extract available to your team.
custom extract created summary

Switching Is Active on makes the extract available to your team immediately. Generate the extract once yourself from Lumary Extracts and check the output file before activating it.

Back to links

Powered by Zendesk