Build the CRM2 contracts table

When to use this endpoint

Use Contracts_getDataTable when reproducing CRM2’s Contracts list, Kanban, or export-selection request.

For an external integration prefer Contracts_list and Contracts_getById; this endpoint exposes CRM2 field-map and layout conventions.

Authentication and permissions

The request must authenticate with signed API-key fields or a logged-in CRM session. The endpoint has no explicit Contracts feature-setting or page-view check.

For non-client-admin users, enabled departmentBasedFilter and accountManagerBasedFilter permissions on pageMain restrict contracts through their cached customer/asset relationships.

Parameters

Parameter	Type	Required	Description
`fields`	Array	No	Native array of mapped response names. Omission is accepted because `id`, `meta`, and `customers` are always appended. Other keys are `name`, `cDate`, `notes`, `lastEdited`, `createdBy`, `active`, `customValues`, `budget`, `expiryDate`, `number`, `address`, `manager`, `typeID`, `dateCompleted`, `dateStarted`, `metaHoursBudget`, `metaNotInvoiceable`, and `metaStage`.
`isTable`	Integer string	No	Defaults to table mode. Exact integer `0` returns only `{"data":[...]}` and enables optional `limit`.
`draw / start / length`	Integer strings	No	Table counter, zero-based offset, and page size; defaults are `0`, `0`, and `10`.
`limit`	Integer string	No	Positive maximum used only with `isTable=0`; zero means no limit.
`orderBy / orderDesc`	Mapped name and direction	No	Explicit mapped sort. CRM callers send `asc` or `desc`.
`order[0][column] / order[0][dir]`	DataTables values	No	Fallback ordering. The shared engine subtracts one from the column position before indexing `fields`, preserving CRM2’s leading action column. Exact `desc` selects descending.
`searchValue / searchField`	String and mapped name(s)	No	Shared case-insensitive search. `All` searches mapped searchable fields. The special scalar `customerAssetID` instead finds customer and asset names and searches their bare numeric IDs in cached relationship text.
`advanceSearch`	Array	No	CRM2 condition objects using `id`/`column`, `condition`, `value`, and optional `andOr`/`special`. The shared engine supports equality, containment, inequality, and greater/lesser comparisons.
`referenceDate / dateFrom / dateTo`	Mapped date and integer offsets	No	Shared relative-date filtering in the tenant timezone. Both inclusive day offsets must accompany a valid mapped reference date; optional `offsetFrom`/`offsetTo` and `timeRangeFrom`/`timeRangeTo` refine it.
`groupBy`	Array	No	Mapped field names for SQL grouping; unknown names are ignored.
`expiryStatus`	Array	No	First item `0` requires null expiry; first item `1` requires non-null expiry. A scalar is mistakenly treated as zero. Legacy zero dates count as non-null.
`customerAssetID`	Array of objects	No	Each object’s `id` is merged into a regular expression against cached relationships; any supplied type is ignored. An empty array builds an empty expression, which broadly matches rows.
`customers / type / childCustomerAndAsset`	ID, type, array	No	With all three present, starts with the requested relationship. Child option `0` adds direct child customers, `1` adds customer-owned/located assets, and `2` resets the set to only those assets. Matching then uses bare IDs without type distinction.
`hasFiles`	Array	No	One item: `[0]` requires any contract-file row and `[1]` requires none. Selecting both applies no file filter. The existence query does not exclude soft-deleted files.
`Mapped field filters`	Scalar or array	No	Mapped keys may be direct filters: text scalars use case-insensitive substring matching, numeric scalars equality, and arrays `IN`. Common examples are `id`, `name`, `active`, `typeID`, `expiryDate`, `metaStage`, and `metaNotInvoiceable`.

Example request

$table = FM_api('Contracts_getDataTable', [
	'fields' => ['id', 'name', 'number', 'active', 'expiryDate'],
	'draw' => '8',
	'start' => '0',
	'length' => '25',
	'active' => ['1'],
	'orderBy' => 'name',
	'orderDesc' => 'asc',
]);

Example response

{
  "data": [
    {
      "id": "720",
      "name": "North Region Service Contract",
      "number": "CT-720",
      "active": "1",
      "expiryDate": "2026-12-31",
      "meta": "{}",
      "customers": "[[0,1042]]",
      "labourCost": "1200.00",
      "mileageCost": "90.00",
      "stockCost": "325.50",
      "sorCost": 240,
      "miscCost": "65.00",
      "netPrice": "4200.00",
      "grossPrice": "5166.00",
      "jobs": [8801, 8802],
      "customerAssetID": "[{\"customerID\":1042,\"customerType\":0}]",
      "sorCostPerJob": "[{\"id\":8801,\"sorCost\":240}]"
    }
  ],
  "draw": 8,
  "recordsTotal": "48",
  "recordsFiltered": "1"
}

Requested stored fields use field-map names and database scalars usually remain strings. id, meta, and customers are always returned, followed by the derived values shown above.

customerAssetID and sorCostPerJob are JSON strings inside the outer JSON response, whereas jobs is a native array.

Errors and unusual responses

Response	Condition
`{"error":"not logged in"}`	No authenticated tenant context is available. Invalid signed credentials can be rejected by the dispatcher before the handler runs.
`Authentication error`	No valid API signature or logged-in CRM session is available.
`{"error":"error in database query"}`	A mapped, advanced, relationship, or ordering query fails.
`Unexpectedly broad or empty rows`	Unknown fields/advanced items are silently ignored, and bare-ID relationship regular expressions can overmatch.

Legacy application errors may be returned with HTTP 200. Check the decoded response for error, not only the HTTP status.

Behaviour and edge cases

Job relationships come from two incompatible sources. The returned jobs, base costs, net, and gross use only type-6 legacy relationship rows. Stock, vehicle processing, and schedule-of-rates costs use only jobs whose direct contract_id matches. Migrated data can therefore produce internally different totals and job sets.

Soft-deleted contracts are excluded. Table mode returns counts; non-table mode omits table metadata. Inactive/completed rows remain unless filtered.
recordsTotal is the non-deleted tenant-wide count. recordsFiltered includes mapped, endpoint-specific, advanced, and non-admin row restrictions.
Customer/asset special filters interpolate IDs into cached JSON-like text without preserving relationship type. Equal customer and asset IDs can collide; the name-search variant also does not restrict its preliminary customer/asset lookups to non-deleted rows.
The per-job schedule-of-rates calculation can understate multiple stock components because its per-job stock subtotal is replaced for each component, although the contract-wide sorCost keeps accumulating.
Raw where expressions in legacy advanced-search objects are passed through by the shared engine. That internal CRM shape is not appropriate for third-party construction.

Side effects

When a returned contract is marked customersDirty, this read rebuilds its cached customer/asset relationships, metadata, and last_edited. The derived customerAssetID uses the rebuilt value, but the already-selected customers response field can remain stale for that response.

See it in Fieldmotion

CRM1

A production-source search found no CRM1 caller. CRM1’s Contracts page uses Contracts_getDT.

CRM2

Open Contracts in list or Kanban view. The shared display widget derives this route for columns, search, filters, ordering, paging, and export ID selection.

Related endpoints

Contracts_getDT is CRM1’s positional table.
Contracts_getExportDataTable expands CRM2 export batches.