Export Power BI contract data

When to use this endpoint

Use PowerBI_getContractData for the Contracts table used by the legacy Power BI connector.

Authentication and permissions

The request must authenticate with signed API-key fields or a logged-in CRM session so that the tenant database and user context are available. The handler performs no page or role-permission check.

This direct dataset route does not consume the unsigned api_id/api_key pair used by the Power BI connector. Use PowerBI_getData for the self-authenticating connector feed.

Parameters

Parameter	Type	Required	Description
`format`	Native array or JSON string	No	Legacy contract layout pages. Each page’s `draggable` entries with `field_name` become custom output columns populated from the contract `custom` JSON.

Example request

FM_api('PowerBI_getContractData', [
	'format' => json_encode([[
		'draggable' => [['field_name' => 'Service Level', 'data_type' => 'text']],
	]]),
]);

Example response

[
  {"contract_id":"72","contract_name":"Annual Cover","active":"Active","address":"","budget":"5000.00","budget_variance":1250.5,"cost":3749.5,"created_by":"18","creation_date":"2026-01-01","customers":"customer_id: 1042","customer_assets":"","expiry_date":"2026-12-31","invoicing_disabled":"","last_edited":null,"notes":"","number":"C-72","manager":"18","price_net":"4500.00","price_gross":"5400.00","profit":750.5,"tax":"900.00","type":"Maintenance","meta":"{}","job_ids":"301,302","Service Level":"Gold"}
]

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.
`Malformed custom columns or PHP warnings`	`format` is not a compatible layout array/JSON structure, or stored custom/financial data is malformed.

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

Behaviour and edge cases

A read can update contract links. If a contract is marked as having stale customer links, this export recalculates them from its jobs, rewrites link rows and contract metadata, updates last_edited, then exports the refreshed value.

Soft-deleted contracts and jobs are excluded. All remaining contracts are returned, with no date filter or pagination, ordered by contract name descending.
active is Active or Completed; false invoicing_disabled becomes an empty string. job_ids is a comma-separated list.
Financial fields are calculated only when the contract has jobs. Cost uses stock, mileage, labour, and Schedule of Rates costs from jobs whose status is nonzero; profit, tax, and budget variance are derived from those totals.
customers is a comma-separated mix of customer_id: N and asset_id: N. The separate customer_assets field is always empty in the current implementation.
The exported last_edited is selected from the joined contract type, not the contract row itself.

Side effects

Recalculating a dirty contract deletes and recreates its customer/asset many-to-many links and updates the contract’s cached customers, metadata, and edit timestamp.

See it in Fieldmotion

CRM1

The legacy connector exposes this result as contract_data; the tenant contract layout supplies custom column names.

CRM2

CRM2 does not use this reporting helper for its contract views.

Related endpoints

PowerBI_getData is the supported aggregate connector feed and supplies tenant settings to this helper.
PowerBI_authenticate establishes the unsigned connector session used by the aggregate feed.