API guide - export run results to spreadsheet in custom format

Param

Description

bucket

Correspond to the left hand side menu options in the app, each corresponds to a subset of the result data. Available options:

Generic reconciliation process:

• all
• unreviewed
• under-review
• pending
• closed
• unmatched
• partially-matched
• matched
• manually-matched

Cash process:

• all-transactions
• recent-transactions
• unreviewed
• under-review
• pending
• resolved
• unmatched
• auto-matched
• manually-matched
• ungrouped

columns

List of columns in the order to be exported and filters to be applied. 

This is optional - if not provided, all columns will be exported in the default order.

columnId:    The name or Id of the column

type:           The data type of the column, not needed for system columns

filters:         Filter criteria for the items. Multiple options are available, see below for more details.

Filters can be set depending on the data type. Here are the options with examples:

Filter by values

Use include or exclude, with actual values.  E.g.

"filter": {
          "exclude": ["d78f6e4ddd0354c419834bdecd6239f76df298df", "GBP"]
        }

The long value is specifically for empty value. The above will exclude items with no value or value is GBP.

You can use this for filtering boolean values using “true” or “false".

Filter by text

Contains or does not contain a certain text.  E.g.

"textFilter": {
          "excludeMode": true,
          "text": "some value"
        }

Set excludeMode to false for contain, and to true for does not contain. The example above filters for items which does not contain the text “some value”.

Filter by age

Only applicable to the exception age system column. Filter by exceptions where age is older than (olderThan) or newer than (newerThan) certain number of days. E.g.

"ageFilter": {
          "newerThan": 3
        },

The above example filters exceptions where age is 3 days or younger.

Filter by break field

Filter for the partially-match items which break on this field, or do not break on this field.

"breakFilter": {
          "haveBreakFilter": true,
          "whereBreakIs": true
        }

The above example filters items which break on this field.

Set “whereBreakIs” to false for items that do not break on this field.

Filter for roll ups

To filter by items with or without roll ups.

“boolFilter”: {
  “active”: true,
  “value”: true
}

Active is mandatory. The above example filters by items with roll ups. If you want to filter by items with no roll ups, then set value to “false”.

Numeric value filter

You can either use condition (less than “lt”, greater than “gt”, less than or equal “le”, greater than or equal “ge”, equal “eq”, not equal “ne”) or range (“is-between” or “is-not-between”) 

"amountFilter": {
          "condition": {
            "type": "lt",
            "value": 0
          }
        }

"amountFilter": {
          "range": {
            "type": "is-between",
            "from": 0,
            "to": 10000
          }
        }

The first example above is filtering by items which is less than 0.

The second example is filtering by items where the value is between 0 and 10,000 inclusive.

Date value filter

The date filter can be used on any column of date type. You can use it to filter by before or after a certain date, between or not between a date range, or on a specific date. All dates need to be given in ISO 8601 format i.e. yyyy-MM-dd, such as 2020-10-19.

"dateFilter": {
          "afterDate": true,
          "beforeDate": false,
          "cutOffDate": "2020-10-19",

}

"dateFilter": {
          "insideRange": true,
          "outsideRange": false,
          "fromDate": "2020-10-09",
          "toDate": "2020-10-19",

}

"dateFilter": {
          "specificDate": "2020-10-19"
        }

The 3 examples above will filter the data by items with the date field value:

1. after 19th Oct 2020

2. between 9th Oct 2020 and 19th Oct 2020, inclusive

3. equal to 19th Oct 2020