Export Power BI purchase-order data

When to use this endpoint

Use PowerBI_getPurchaseOrderData for the connector’s Purchase Orders and Purchase Order Line Items tables.

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
`from`	String	Yes	An exact lower-bound fragment such as `>="2026-09-01"`. It is not a plain date.
`to`	String	Yes	An exact upper-bound fragment such as `<="2026-09-30 23:59:59"`. It is not a plain date.
`dataset`	String	No	`purchase_order_data` or `purchase_order_lineitem_data`. Blank returns both; an unsupported direct value silently falls back to both.

Example request

FM_api('PowerBI_getPurchaseOrderData', [
	'from' => '>="2026-09-01"',
	'to' => '<="2026-09-30 23:59:59"',
	'dataset' => 'purchase_order_data',
]);

Example response

{
  "purchase_order_data": [{"purchase_order_id":"44","po_num":"PO-44","supplier_id":"9","supplier_name":"Parts Ltd","status":"Open","link_id":"301","link_type":"Job","date_created":"2026-09-03 10:00:00","total_net":"100.00","total_tax":"20.00","invoice_num":"SUP-1","invoice_date":"2026-09-04","invoice_received":"Yes"}],
  "purchase_order_lineitem_data": [{"purchase_order_id":"44","amt":"2","cost_net":"50.00","date_created":"2026-09-03 10:01:00","nominal_code":"5000","stock_id":"12","stock_text":"Filter","stock_type":"Stock Item","taxtype":"T1","total_net":"100.00","total_tax":"20.00"}]
}

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: from"}`	`from` is absent.
`{"error":"missing or invalid parameter: to"}`	`to` is absent.
`{"error":"invalid parameter: from"} / {"error":"invalid parameter: to"}`	A bound has the wrong exact shape.

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

Behaviour and edge cases

Order rows are bounded by purchase-order creation time; entry rows are independently bounded by each entry’s creation time. Both joins exclude soft-deleted parent orders, and orders are newest first.
Status codes map to Open, Part-Received, Complete, Over-Received, or Cancelled. Link types map 0–6 to blank, Contract, Customer, Quote, Job, Department, or Location.
Invoice number/date are extracted from the stored invoice-info object. invoice_received becomes Yes whenever invoice_recd exists, even when its value is false; otherwise it is No.
Line-item stock_type maps 0–2 to blank, Stock Item, or Freetext. The line shape contains only the fields shown in the example.
The current order query joins the form table on purchase-order ID instead of form_id; form_name can therefore be blank or refer to an unrelated form whose ID happens to match the order.
There is no pagination. A projected dataset skips the unrequested query where implemented.

See it in Fieldmotion

CRM1

The legacy connector exposes orders and line items as two navigation tables.

CRM2

CRM2 does not use this export route for purchase-order screens.

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.