Query the CRM2 Marketing Expense table

When to use this endpoint

Use MarketingExpenses_getDataTable for CRM2’s Marketing Expenses screen and its export ID-selection pass.

Authentication and permissions

This shared CRM2 table wrapper is intended for signed API-key requests or logged-in CRM sessions, so the request has an authenticated tenant database.

The wrapper omits the normal login guard and has no page or role-permission check. Without tenant context it can reach the wrong database prefix and return a database-query error instead of a structured authentication error.

Parameters

Parameter	Type	Required	Description
`fields`	Native array	Yes	Mapped fields from `id`, `source`, `expense`, `expenseDate`, `createDate`, and `lastEdited`. Unknown names are skipped and `id` is always added.
`isTable`	Integer string	No	Defaults to table mode. Exact `0` returns only data and enables optional `limit`.
`draw, start, length`	Integer strings	No	Table counter, offset, and page size; defaults are 0, 0, and 10.
`limit`	Integer string	No	Positive non-table limit; zero means unlimited.
`orderBy / orderDesc`	Mapped field / direction	No	Explicit ordering. Sorting by `source` currently produces a database-query error because the shared sorter cannot use this map’s related-name search metadata.
`order[0][column] / order[0][dir]`	Integer / string	No	DataTables ordering fallback. A column resolving to `source` has the same current sorting failure.
`searchValue / searchField`	String / field or native array	No	Case-insensitive substring search. Searching `source` resolves related marketing-source names through a join.
`source, expense, expenseDate, createDate, lastEdited, id`	Scalar or native array	No	Mapped filters. Numeric scalars are exact after conversion; date/text scalars use case-insensitive substring matching; arrays use `IN`.
`referenceDate, dateFrom, dateTo`	Mapped field and integer offsets	No	Tenant-timezone relative-date filtering. Use `expenseDate` or a mapped timestamp and supply both day offsets.
`advanceSearch, groupBy`	Native arrays	No	Shared CRM2 advanced conditions and mapped grouping controls.

Example request

$table = FM_api('MarketingExpenses_getDataTable', [
	'fields' => ['id', 'createDate', 'source', 'expense', 'expenseDate'],
	'draw' => '7',
	'start' => '0',
	'length' => '25',
	'referenceDate' => 'expenseDate',
	'dateFrom' => '-30',
	'dateTo' => '0',
	'orderBy' => 'expenseDate',
	'orderDesc' => 'desc',
]);

Example response

{
  "data": [{"id":"7124","createDate":"2026-10-01 15:20:00","source":"30124","expense":"425.5","expenseDate":"2026-10-01"}],
  "draw": 7,
  "recordsTotal": "40",
  "recordsFiltered": "1"
}

Errors and unusual responses

Response	Condition
`{"error":"missing or invalid parameter: fields"}`	`fields` is absent.
`{"error":"error in database query"}`	A generated query fails, including current source-column ordering.

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

Behaviour and edge cases

Search and sort treat source differently. Source search uses the related source name successfully, but the response remains the numeric ID and source-name ordering is currently broken. Resolve IDs with MarketingSources_list.

Table mode returns data, integer draw, and scalar counts; non-table mode returns data only.
recordsTotal includes every expense row. There is no active/deleted filter.
createDate/lastEdited are formatted datetimes; expenseDate remains YYYY-MM-DD.
CRM2 export requests up to 100,000 IDs in table mode before submitting the comma-separated set to MarketingExpense_export.

See it in Fieldmotion

CRM1

CRM1 uses MarketingExpenses_getDT.

CRM2

Open Marketing → Marketing Expenses in CRM2 and filter browser developer tools for the exact function name. Table display, filters, sorting, paging, and export ID collection use this endpoint.

Related endpoints

MarketingExpense_export streams selected IDs as CSV.
MarketingExpenses_getDT supplies CRM1’s positional table.