Query the CRM2 Customers table

When to use this endpoint

Use Customers_getDataTable when reproducing CRM2’s Customers page or its export-selection pass. It returns named fields from the shared customer field map rather than CRM1’s positional arrays.

For a stable external integration contract, prefer Customers_list and Customers_getById. This endpoint exposes CRM2 layout/search conventions.

Authentication and permissions

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

For a non-client-admin user, the departmentBasedFilter permission on pageMain restricts results to the user’s department IDs (or no rows when the user has none), and accountManagerBasedFilter restricts results to the logged-in user as account manager. These restrictions are added even when the caller supplies other filters.

Parameters

Parameter	Type	Required	Description
`fields`	Array	Yes	Mapped response field names. This must be a native request array. Unknown names are silently skipped and `id` is always added. Common names include `id`, `name`, `accountNumber`, `contactName`, `email`, `mobile`, `parentId`, `isActive`, `marketingSource`, and `areaID`.
`isTable`	Integer string	No	Defaults to table mode. Exact integer `0` returns only `{"data":[...]}` without table counts and allows optional `limit`.
`draw`	Integer string	No	Table counter copied as an integer; defaults to `0`.
`start`	Integer string	No	Table offset; defaults to `0`.
`length`	Integer string	No	Table page size; defaults to `10`. The shared engine does not reject zero or negative values before SQL.
`limit`	Integer string	No	Positive row limit used only when `isTable=0`. Zero means no limit.
`orderBy`	Mapped field name	No	Preferred sort field when `orderDesc` is also present. Matching is case-insensitive as a fallback; an unknown value falls back to a requested field.
`orderDesc`	String	Conditional	SQL direction text used with a valid `orderBy`. CRM callers send `asc` or `desc`.
`order[0][column]`	Integer string	No	DataTables column position when explicit ordering is absent. The engine subtracts one before indexing `fields`, preserving the leading action-column convention.
`order[0][dir]`	String	No	Exact `desc` selects descending; other values select ascending.
`searchValue`	String	No	Case-insensitive substring searched across `searchField`; with no field it searches all mapped searchable fields.
`searchField`	String or array	No	Mapped field name(s) or `All`. Scalar `country`, `leadAcc`, and `areaID` use special display-name searches before the generic engine.
`advanceSearch`	Array	No	CRM2 condition objects with `id` or `column`, `condition`, `value`, and optional `andOr`/`special`. Supported conditions are equal, contains, not-equal, greater/greater-or-equal, and lesser/lesser-or-equal. The special ID `customer_parentOnly` selects referenced parent IDs.
`referenceDate`	Mapped field name	No	Enables shared relative date/time filtering and non-table ordering for a mapped date field.
`dateFrom / dateTo`	Integer day offsets	Conditional	Inclusive day offsets relative to today in the tenant timezone when `referenceDate` is valid. Both must be present.
`offsetFrom / offsetTo`	Objects	No	Optional `hour`/`minute` adjustments used by the shared reference-date filter.
`timeRangeFrom / timeRangeTo`	Integer hours	No	Optional inclusive local-hour range for the reference field. Reversed bounds are swapped.
`groupBy`	Array	No	Mapped field names to add to SQL `GROUP BY`; unknown names are ignored.
`customerFiles`	Array	No	One value: `[0]` requires a customer file and `[1]` requires none. Two selected values apply no file filter.
`hasSite`	Integer string	No	Presence applies a parent-ID filter: integer `0` selects IDs not referenced as a parent; any other value selects referenced parent IDs.
`hasJob`	Integer string	No	Presence applies stored job-counter filtering: truthy requires either counter; zero requires both counters to be zero.
`callbackStatus`	Array	No	OR-combined callback states: `-2` overdue, `-1` yesterday, `0` today, `1` tomorrow, `2` future.
`childJobAndAsset`	Any nonzero value	Conditional	With `name`, includes rows whose own name contains the value or whose direct parent name contains it, then suppresses the normal own-name filter.
`parentOnly`	Any	No	Presence selects IDs referenced as a parent; the value itself is ignored.
`Mapped field filters`	Scalar or array	No	Any mapped key with a database ID can also be supplied directly. Text scalars use case-insensitive substring matching; numeric scalars use equality; arrays use `IN`. Examples: `id`, `name`, `isActive`, `departmentId`, `accountManagerID`, `type`, `priceBand`, `marketingSource`, and `areaID`.

Example request

$table = FM_api('Customers_getDataTable', [
	'fields' => ['id', 'name', 'accountNumber', 'mobile', 'isActive'],
	'draw' => '7',
	'start' => '0',
	'length' => '25',
	'isActive' => '1',
	'searchField' => 'All',
	'searchValue' => 'northwind',
	'orderBy' => 'name',
	'orderDesc' => 'asc',
]);

Example response

{
  "data": [
    {
      "id": "1042",
      "name": "Northwind Service Centre",
      "accountNumber": "NW-1042",
      "mobile": "+353875550100",
      "isActive": "1"
    }
  ],
  "draw": 7,
  "recordsTotal": "250",
  "recordsFiltered": "1"
}

Response keys use field-map names, not database column names. Legacy database scalars normally remain strings. Composite layout fields such as parent or accountManager can return nested value objects.

Selectable mapped keys are accountNumber, billing_target, id, name, notes, email, lastEdited, industry, streetAddress1, streetAddress2, town, county, postcode, landline, mobile, contactName, changes, editedUuid, appId, createDate, appointmentsOpen, appointmentsClosed, customValues, callback, accountManagerID, accountManager, meta, callbackNotes, leadAcc, parentId, parent, latitude, longitude, login, isActive, country, createdBy, files, departmentId, password, passwordSalt, city, state, lastEditedAll, isFlagged, contacts, hasInvoices, type, priceBand, accountsName, accountsNumber, creditTerms, areaID, marketingSource, accountSage, discount, doNotSMS, defaultInvoiceDueDate, defaultInvoiceDueDateMode, defaultNominal, defaultTaxcode, accountingEmail, additionalEmail, additionalAccountingEmail, doNotEmailInvoices, owned, and defaultInvoiceFormID. Layout-only taxType and defaultInvoiceForm have no database ID and are skipped.

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.
`{"error":"missing or invalid parameter: fields"}`	`fields` is absent.
`{"error":"error in database query"}`	A constructed mapped/filter query fails.
`Empty or unexpectedly broad data`	Unknown requested fields and malformed advanced-search items are silently ignored rather than rejected.

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

Behaviour and edge cases

This is a UI query surface, not a fixed export schema. Field-map additions, layout composite behavior, and role-based filters affect its contract. The map also exposes the sensitive legacy fields login, password, and passwordSalt without a field-level permission check; external callers must not request or retain them. Use the narrower list/detail endpoints for an external synchronization.

Soft-deleted customer rows are always excluded from data, total, and filtered counts. Inactive rows remain unless filtered.
Table mode returns data, integer draw, and database scalar counts. Non-table mode omits all three table metadata fields and has no limit unless a positive limit is supplied.
recordsTotal is the non-deleted tenant-wide count; recordsFiltered includes all endpoint, mapped, advanced, and role-based filters.
The customerFiles filter tests actual non-deleted-status-agnostic file rows with customer file type 0; it does not inspect the customer’s legacy files column.
hasSite and parentOnly build their preliminary parent lists without soft-delete filters, although final returned customer rows are non-deleted.
Scalar display-name country search maps partial country names to stored codes. A term matching no country builds a blank-code filter, which can match customers whose stored country is blank.
Legacy advanced-search objects containing a raw where expression are passed through by the shared engine. That internal CRM shape is not safe for third-party construction; integrations should use mapped fields and supported conditions only.
The full mapped field family also retains the exact obsolete identifiers appointmentsOpen and appointmentsClosed for the stored open/closed job counters.

See it in Fieldmotion

CRM1

A production-source search found no CRM1 caller. CRM1’s Customers page uses Customers_getDT.

CRM2

Open Customers. The shared display widget derives Customers_getDataTable from the page’s Customer API prefix; column selection, search, advanced filters, layout settings, sorting, and paging feed this request. CRM2 also calls non-table mode to collect IDs for export.

Related endpoints

Customers_getDT serves the CRM1 positional table.
Customers_getExportData returns export field positions for selected IDs.
Customers_list provides the narrower integration-oriented list contract.