Skip to main content

Queries

Queries are for reading data. We typically support filtering, sorting, pagination and aggregation where we return lists of data.

We will not change any entities from a query.

note
  • Using fragments is recommended
  • For more responsive queries we recommend requesting only the necessary data as we will only read the requested data from the database.
  • For better performance always use variables instead of adding parameters directly in the query body

Schema​

The Dimensions and Financials APIs are versatile, accommodating a variety of query types. Depending on the specific requirements of the use case, different segments of the API schema can retrieve varying degrees of data from the database.

Here are some of the most frequently utilised query categories within our API:

Lists​

This is where we keep smaller versions of master record entities which are optimised for faster reads and expose a smaller amount of data

Examples of List queries include:

  • Customer/Supplier Record Summaries
  • Price Records
  • BACS Formats
  • Currency Rates
note

We recommend using a list wherever possible as they are less complicated than their master record equivalents

Example List Query
query ($filter: CustomerSummaryFilterInput) {
    debtors {
        lists {
            customers(filter: $filter) {
                items {
                    primaryKey
                    code
                    name
                    country
                    currencyCode
                    currencySymbol
                    dateEdited
                }
            }
        }
    }
}

Master Records​

Master records are entities which are mostly static, these are typically accounts and settings. They contain a complete set of data.

Examples of Master Records queries include:

  • Customer/Supplier Records
  • Stock Records
  • Terms
  • Analysis Records
Example Master Record Query
query ($filter: NominalAccountFilterInput) {
  financials {
    masterRecords {
      accounts {
        records(filter: $filter) {
          items {
            details {
              primaryKey
              accountCode
              accountName
              accountType
              compulsoryCosting
              majorHeadingCode
              compulsoryCosting
              ...
            }
            summaryData {
              balance
              turnoverYtd
              currencyBalance
            }
            profile {
              nominalProfile
              updateFromNominalLedger
              updateFromPurchaseLedger
            }
          }
        }
      }
    }
  }
}

Transactions​

Transactions are entities which relate to master records such as invoices or remittances.

Examples of Transaction queries include:

  • Nominal Summaries
  • Live & Batched Transaction Data for all core ledgers
  • Currency Rate history
  • Notes
  • Outstanding Transactions and Credit Control data
Example Transaction Query
query (
  $filter: SalesTransactionDetailFilterInput!
  $sort: [SalesTransactionDetailSortSortByInput!]
  $first: Int
) {
  debtors {
    transactions {
      transactionEntry {
        details(filter: $filter, sort: $sort, first: $first) {
          items {
            primaryKey
            detail
            analysis {
              code
              name
              nominalUpdate {
                debit {
                  code
                  name
                }
                credit {
                  code
                  name
                }
                tax {
                  code
                  name
                }
              }
            }
            tax {
              code
              rate
              type
            }
            values {
              net
              tax
              gross
              currencyNet
              currencyTax
              currencyGross
            }
            costing {
              project {
                code
                name
              }
              costCentre {
                code
                name
              }
            }
            accrual {
              accrueOverPeriods
              defer
              account {
                code
                name
              }
            }
          }
        }
      }
    }
  }
}

Filtering​

We provide complex filtering for almost every property you can select suited to the given data type. We also typically provide a summary filter which will perform a search on multiple key fields.

You can provide as many filters to as many fields you like and we will combine each filter using AND. Please see examples for more information.

Summary​

The summary filter will perform a begins with check on core string fields such as customer Code and Name. The begins with search applies to each word within the value of the field. For example, if the customer name is My Company, searching by my or company will find the record.

Actions​

Ne - Field is not equal to filter value

Eq - Field is equal to filter value

Le - Field is less than or equal to filter value

Lt - Field is less than filter value

Ge - Field is greater than or equal to filter value

Gt - Field is greater than filter value

Between - Field is greater than or equal to the first filter value and less than or equal to the last filter value

NotBetween - Field is less than the first value or greater than the second value

ContainsValues - Field is equal to a value within the list of filter values

NotContainsValues - Field is not equal to any value within the list of filter values

IsBlank - When filter value is true, field is null (or blank for a string)

IsNotBlank - When filter value is true, field is not null (and not blank for a string)

BeginsWith - String field begins with filter value

DoesNotBeginWith - String field does not begin with filter value

EndsWith - String field ends with filter value

DoesNotEndWith - String field does not end with filter value

Contains - Filter value matches part of the string field

DoesNotContain - Filter value matches no part of the string field

DayX - Day number x in any month in any year

NotDayX - All dates except where the day is x

LastXDays - Today and up to x days prior

NextXDays - Today and up to x days hence

TodayInAnyYear - Todays day and month in any year

Today - Today

NotToday - Everything but today

Yesterday - Yesterday

NotYesterday - Everything but yesterday

Tomorrow - Tomorrow

NotTomorrow - Everything but tomorrow

MonthX - Month number x in any year

NotMonthX - All dates except where the month number is x

LastXMonths - Today and up to x months prior

NextXMonths - Today and up to x months hence

ThisCalendarMonthInAnyYear - This month in any year

ThisCalendarMonth - This month, in this year

NotThisCalendarMonth - Any date except one within this month in this year

LastCalendarMonth - Last month, in this year

NotLastCalendarMonth - Any date except one within last month in its year

NextCalendarMonth - Next month, in its year

NotNextCalendarMonth - Any date except one within next month in its year

YearX - Year x

NotYearX - All dates except where the year is x

LastXYears - Today and up to x years prior

NextXYears - Today and up to x years hence

ThisCalendarYear - This year

NotThisCalendarYear - Any date except one within this year

LastCalendarYear - Last year

NotLastCalendarYear - Any date except one within last year

NextCalendarYear - Next year

NotNextCalendarYear - Any date except one within next year

Types​

StringNumericDateEnum/Boolean/Guid
Ne✅✅✅✅
Eq✅✅✅✅
Le✅✅✅
Lt✅✅✅
Ge✅✅✅
Gt✅✅✅
Between✅✅✅
NotBetween✅✅✅
ContainsValues✅✅✅✅
NotContainsValues✅✅✅✅
IsBlank✅✅✅✅
IsNotBlank✅✅✅✅
BeginsWith✅
DoesNotBeginWith✅
EndsWith✅
DoesNotEndWith✅
Contains✅
DoesNotContain✅
DayX✅
NotDayX✅
LastXDays✅
NextXDays✅
TodayInAnyYear✅
Today✅
NotToday✅
Yesterday✅
NotYesterday✅
Tomorrow✅
NotTomorrow✅
MonthX✅
NotMonthX✅
LastXMonths✅
NextXMonths✅
ThisCalendarMonthInAnyYear✅
ThisCalendarMonth✅
NotThisCalendarMonth✅
LastCalendarMonth✅
NotLastCalendarMonth✅
NextCalendarMonth✅
NotNextCalendarMonth✅
YearX✅
NotYearX✅
LastXYears✅
NextXYears✅
ThisCalendarYear✅
NotThisCalendarYear✅
LastCalendarYear✅
NotLastCalendarYear✅
NextCalendarYear✅
NotNextCalendarYear✅
note

We provide advanced numeric filtering for the following filter types

  • DecimalFilter
  • DoubleFilter
  • IntFilter
  • LongFilter
note

We provide advanced date filtering for the following filter types

  • DateFilter
  • DateOnlyFilter
  • DateTimeFilter
note

For the DateTimeFilter we offer a way to filter ignoring time using the DateOnly property

Examples​

If we want to find all the categories for number 3 where the codes are alphabetically after D but before H we can filter using the following

query NominalCategories($filter: NominalCategorySummaryFilterInput) {
  financials {
    lists {
      categories(filter: $filter) {        
        ...NominalCategory
      }
    }
  }
}

fragment NominalCategory on NominalCategorySummaryConnection {
  items {
    code
    name
    number
  }
}

We have achieved this by performing 3 filters (combined using AND)

  • Code greater than or equal to D to ensure it either matches or is after D alphabetically
  • Code less than H alphabetically to ensure we don't include anything beginning with H nor after it
  • Number is equal to 3

Sorting​

We support multi-field sorting.

Performance​

The choice of sorting can have a large impact on performance as some fields are calculated.

note

We recommend always filtering to the specific data you require. If this filter is quite vague then we would advise either not sorting or sorting by key fields such as code, name or audit number.

Schema​

Each sort looks like an array of the following

  • field an enum for choosing the field to apply sorting to
  • order an enum indicating the direction of the sort

Examples​

If we want to get Nominal Category codes in order of number with code descending we can do the following

query NominalCategories($sort: [NominalCategorySummarySortSortByInput!]) {
  financials {
    lists {
      categories(sort: $sort) {
        ...NominalCategory
      }
    }
  }
}

fragment NominalCategory on NominalCategorySummaryConnection {
  items {
    code
    name
    number
  }
}

Aggregation​

Aggregation allows you to group by a specific field and aggregate other fields.

Schema​

field an enum for choosing the field to apply grouping to aggregates an array of fields to aggregate

  • field an enum for choosing the field to apply aggregation to
  • type an enum for choosing the type of aggregation
    • Supported types are Max, Min and Sum
      note

      Sum is only supported for numeric types

Examples​

If we want to find out the total outstanding balance vs. total value for transactions for a given supplier we can

  • Group by supplier code
  • Get the max supplier name, as this will be unique per supplier
  • Sum the outstanding to get the total outstanding value
  • Sum the gross to get the total value
  • Filter to a given period
query PurchaseTransactions(
  $filter: PurchaseTransactionEnquiryHeaderFilterInput!
  $aggregate: PurchaseTransactionEnquiryHeaderSortGroupByInput
) {
  creditors {
    transactions {
      allTransactions {
        header(filter: $filter, aggregate: $aggregate) {
          ...Transaction
        }
      }
    }
  }
}
fragment Transaction on PurchaseTransactionEnquiryHeaderConnection {
  items {
    accountCode
    accountName
    gross
    outstanding
  }
}

Pagination​

Pagination brings the benefits of transferring smaller chunks to keep the load on the database low and the responsiveness high.

The page size is set to 100. When data is returned we also include whether there is another page and the cursor to use to get that page.

Inputs​

after is the name of the input variable used to indicate the page of data to request. It expects a cursor which is returned after a request or null if this is the initial request.

Outputs​

pageInfo is the node in the response which contains information about pages

  • hasNextPage a boolean indicating if there is another page of data
  • hasPreviousPage a boolean indicating if there is a previous page of data
  • endCursor the value to send into after to request the next page, null if no subsequent page
  • startCursor the value to send into after to request the previous page, null if no prior page

Examples​

The following shows a typical example of the requests to use when paginating

First Page​

query NominalCategories($after: String) {
  financials {
    lists {
      categories {
        ...NominalCategory
        ...Pagination
      }
    }
  }
}

fragment NominalCategory on NominalCategorySummaryConnection {
  items {
    code
    name
    number
  }
}

fragment Pagination on NominalCategorySummaryConnection {
  pageInfo {
    hasNextPage
    endCursor
  }
}

We can see that there is another page by the hasNextPage property in pageInfo and an endCursor to use to retrieve it.

To select the next page we can use the same query as above but with the provided endCursor as after

Second Page​

query NominalCategories($after: String) {
  financials {
    lists {
      categories {
        ...NominalCategory
        ...Pagination
      }
    }
  }
}

fragment NominalCategory on NominalCategorySummaryConnection {
  items {
    code
    name
    number
  }
}

fragment Pagination on NominalCategorySummaryConnection {
  pageInfo {
    hasNextPage
    endCursor
  }
}

As this was the last page of data we can see that hasNextPage is false and there is no endCursor