How To Use The Rest API End Point - Read Records

This end-point will allow you to return a list of records, a single record, or a list of sub-records.

GET Type End-Point

/api/v1/collect/data/{record}
/api/v1/collect/data/{record}/{rowid}
/api/v1/collect/data/{record}/{rowid}/counts
/api/v1/collect/data/{record}/{rowid}/{sub-records}

Example

The below code only shows the first few lines of the table as an example. For the current schema, please refer to the Printable Field Help Topic.

/api/v1/collect/data/debtor

Response Body

The response will include a status value. 200 is good. Everything else is a failure. If the response is good, then the response will include the Data Type and Data.

{
  "type": {
    "recId": "string",
    "rowId": "string",
    "numRecords": digit,
    "numPages": digit
  },
  "data": {
    "field": "string",
    ...
  }
}

Code	Description
recId	This is the table name in the database.
rowId	This is the column name for the unique identifier for this table.
numRecords	This is the total number of records in the results for all pages.
numPages	This is the number of pages that the results have been divided in to. For example, if you use the Limit parameter and set it to 100, and 450 records are in the results, then you would have 5 pages. You would then use the Page parameter to navigate the pages.

Example

{
  "type": {
    "recId": "debtor",
    "rowId": "de_rowid",
    "numRecords": 15398,
    "numPages": 16
  },
  "data": {
    "de_rowid": "123",
    ...
  }
}

Return A Single Record

To return a single record, add the rowid after the record type.

/api/v1/collect/data/debtor/123

Useful Note If you do not have the rowid, you can use filters using data that is available to obtain the rowid of the record that you need.

Counts

To return the counts of a record's sub-records, use the counts option.

/api/v1/collect/data/debtor/123/counts

The response will contain the record type, then the data will be an list of the tables and number of records in each table.

{
  "type": {
      "recId": "debtor",
      "rowId": "de_rowid"
  },
  "data": {
      "bank_account": "0",
      "email": "1",
      "phone": "3",
      "address_lookup": "1",
      "notes": "0",
      "attachment": "4",
      "cosigner": "2",
      "contact": "8",
      "de_transaction": "4",
      "blobs": "15"
  }
}

Useful Note In our schema, notes are stored in the blobs table, not the notes table. Each blob entry is a single note line.

Return Sub-Records For A Single Record

To return a list of sub-records for a particular record, you can add the table name to the URL.

/api/v1/collect/data/debtor/123/address_lookup
/api/v1/collect/data/debtor/123/attachment
/api/v1/collect/data/debtor/123/blobs
/api/v1/collect/data/debtor/123/contact
/api/v1/collect/data/debtor/123/cosigner
/api/v1/collect/data/debtor/123/de_transaction
/api/v1/collect/data/debtor/123/email
/api/v1/collect/data/debtor/123/phone

Parameters

After the end-point, you can add a question mark, then parameters.

/api/v1/collect/data/{record}?limit=1000&page=3
/api/v1/collect/data/{record}/{rowid}/{sub-records}?filter=de_status__in=('NEW','ACT','PPA'),de_owing__gt=1000

The following parameters are available:

Limit

Specifies how many records to return in a result. If no limit is specified, then 1000 is used.

limit=1000

Page

If the total results is greater than the limit, then you can use page to navigate the results.

page=1

Offset

As an alternative to paging, you can use offset to navigate the results. For example, with 1500 results and a limit of 500, an offset of 1000 would be similar to requesting page 3 as it would skip the first 1000 results.

offset=1000

Fields

By default, all available fields in the table are returned. This option allows you to control which fields are returned in the results.

fields=field_1,field_2,...

Sorting

This option will sort the results by the specified fields. The default sorting is ascending. Descending can be specified with the "desc" attribute or with a "-" following the field.

sort=field_1,field_2 desc,field_3-,...

Text Search

This option uses SQL Server's full text searching. This feature must be enabled in the database engine to work.

search="text"

Joins

By default, only the specified record is returned with this end-point. If you need related records, you can use a join.

join=({table},{type},{field1},{field2}),...

Warning Note WARNING: You must use the correct fields for the joins as Collect! will not automatically fill in the foreign keys. This is to allow for flexibility for joining certain records where you would not want to use the foreign key.

Supported Join Types:

inner
outer
left
right

Examples

join=(client,inner,cl_rowid,de_rowid_client)
join=(client,inner,cl_number,de_to_office)
join=(client,inner,cl_rowid,de_rowid_client),(debtor_status,left,ds_code,de_status),(nx_operator,left,op_id,de_collector)

Filters

Filters are parameters that can be used to filter data based on values in fields.

filter={field_name}__{operator}={value}

Useful Note Between the field and operator is a double underscore.

Less Than

filter=de_owing__lt=1000

Less Than or Equal To

filter=de_owing__lte=1000

Greater Than

filter=de_owing__gt=1000

Greater Than or Equal To

filter=de_owing__gte=1000

Equal To

filter=de_owing__eq=1000,de_status__eq='NEW'

Not Equal To

filter=de_owing__neq=1000,de_status__neq='NEW'

filter=de_type__in=(1,2,3),de_status__in=('NEW','ACT','PRA')

Not In

filter=de_type__nin=(1,2,3),de_status__nin=('NEW','ACT','PRA')

As Percent Symbols cannot be in the URI, you have to escape it as %25.

filter=de_name__like='Doe%25'

Not Like

As Percent Symbols cannot be in the URI, you have to escape it as %25.

filter=de_name__nlike='Doe%25'

Bit Value On

Bit values are typically used for flag fields where bit values (1, 2, 4, 8, 16, ...) are set. The "bits" option can also be used on number fields to obtain the values that are odd.

For bitwise fields, the value specified will return the results where the applicable flag (checkbox) is on.

filter=de_number__bits=1,de_flags__bits=16

Bit Value Off

Bit values are typically used for flag fields where bit values (1, 2, 4, 8, 16, ...) are set. The "nbits" option can also be used on number fields to obtain the values that are even.

For bitwise fields, the value specified will return the results where the applicable flag (checkbox) is off.

filter=de_number__nbits=1,de_flags__nbits=16

Is Null

filter=de_listed_date__null=1

Is Not Null

filter=de_listed_date__null=0

Example

/api/v1/collect/data/debtor?limit=1000&page=3&fields=de_number,de_name,de_status&filter=de_status__in=('NEW','ACT','PPA'),de_owing__gte=1000&sort=de_number

/api/v1/collect/data/client/111/debtor?de_status__in=('NEW','ACT','PPA'),de_owing__gt=1000

Using OR Operations

By default, filters use the AND operation. This is indicated when each field is separated with a comma. If the field ended with a semi-colon, then that will indicate an OR.

The below example will return Debtors that are in Status (NEW, ACT, PPA), OR where the Owing is greater or equal than $1,000.00.

/api/v1/collect/data/debtor?limit=1000&page=3&fields=de_number,de_name,de_status&filter=de_status__in=('NEW','ACT','PPA');de_owing__gte=1000&sort=de_number

/api/v1/collect/data/client/111/debtor?filter=de_status__in=('NEW','ACT','PPA'),de_owing__gt=1000

Was this page helpful? Do you have any comments on this document? Can we make it better? If so how may we improve this page.

Please click this link to send us your comments: helpinfo@collect.org