Price bands and stock pricing
Page mapped price-band rows
/a/PriceBands_getDataTable
Read the generic CRM2 price-band table, with mapped columns, paging and search.
When to use this endpoint
Use the authenticated mapped finance table rather than legacy positional arrays. Supply native fields; table defaults and contains-search behavior differ from the legacy route.
Authentication and permissions
Authenticate with signed API-key fields or a logged-in CRM session; all normal calls use that tenant. These handlers do not enforce an additional role or finance-page permission.
Use strings for signed numeric values, including values inside native arrays. Only JSON-encode parameters explicitly described as JSON text; see Getting started.
Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
fields | Native array of mapped names | Yes | Mapped fields are id, name, orderNum, isRelative, relativeMultiple, cDate and lastEdited; the latter five map to order_num, is_relative, relative_multiple, cdate and last_edited. Unknown names are ignored; id is always added. Empty arrays select id only; JSON text is not decoded. |
isTable | Integer-compatible scalar | No | Defaults to table mode; value coercing to 0 switches to {data:[...]} without draw or counts. |
start / length / draw | Integer-compatible scalars | No | Table defaults are 0/10/0. start must be a nonnegative integer and length a positive integer; -1 is rejected rather than selecting all records. draw is returned as an integer. |
orderBy / orderDesc | Mapped name / direction text | No | Used together in table mode. orderBy matches a mapped name case-insensitively or falls back to a projected mapped field. orderDesc must be asc or desc (case-insensitive), normalized before querying. |
order[0][column] / order[0][dir] | Native DataTables order | No | When the explicit ordering pair is absent, column minus one indexes fields (negative becomes zero). Only exact desc means descending. With no order, the first valid projected field is ascending; fallback is id. |
searchValue / searchField | String / mapped name or native array | No | Trimmed case-insensitive contains search. searchField defaults to All; recognized names are ORed, with other filters combined using AND. % and _ remain wildcards. |
id / name / orderNum / isRelative / relativeMultiple / cDate / lastEdited | Scalar or native array | No | Mapped direct filters combine with AND. Name scalar is case-insensitive contains; name arrays use IN. Numeric scalars use intval, including relativeMultiple; numeric arrays preserve float values for relativeMultiple. Empty/all-invalid numeric arrays match no rows. ids is not a direct-filter alias. |
limit | Integer-compatible scalar | No | A nonnegative integer; applies only in non-table mode and only when positive. Default zero means no limit. |
groupBy | Native array of mapped names | No | Adds SQL GROUP BY for recognized names. It is not an aggregate/report contract: counts ignore grouping and non-grouped projected columns depend on SQL mode. |
referenceDate / dateFrom / dateTo / offsetFrom / offsetTo / timeRangeFrom / timeRangeTo | Legacy date controls | No | Shared relative-day/hour filters exist, but cDate and lastEdited are mapped as int rather than datetimeUnix. They therefore use datetime-string comparisons/HOUR on integer storage, not a reliable Unix timestamp range. Avoid these controls for pricing sync. Tenant timezone participates in these legacy calculations. |
advanceSearch | Native array (legacy controls) | No | Native structured advanced filters as in mapped details. Raw where SQL and malformed rule shapes are rejected before querying. |
Example request
$table = FM_api('PriceBands_getDataTable', ['fields'=>['name', 'orderNum', 'isRelative', 'relativeMultiple'], 'start'=>'0', 'length'=>'20', 'draw'=>'1', 'order'=>[['column'=>'1', 'dir'=>'asc']]]);Example response
{"data":[{"name":"Trade","orderNum":"2","isRelative":"1","relativeMultiple":"0.9","id":"1042"}],"draw":1,"recordsTotal":"8","recordsFiltered":"8"}Mapped numeric columns and both counts remain database strings; draw is an integer. Selected columns use camelCase names and id is automatically included.
Table mode returns data/draw/recordsTotal/recordsFiltered; non-table mode only data. No matches produces data:[]; total is tenant-wide, filtered count is before paging.
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 omitted. |
{"error":"error in database query"} | The main row query fails, including invalid paging/legacy controls. |
{"error":"unsupported raw advanced search"} | An advanced rule supplies raw where SQL. |
{"error":"missing or invalid parameter: orderDesc"} | Direction is not asc/desc. |
{"error":"missing or invalid parameter: start"} | Invalid/negative offset; invalid length/limit uses the corresponding parameter error. |
Legacy application errors may be returned with HTTP 200. Check the decoded response for error, not only the HTTP status.
Behaviour and edge cases
- No active/archive filter: price_bands has no soft-delete columns. No customer/stock enrichment or price calculation. Unknown projection names are skipped.
- Grouping does not adjust count metadata and can fail under strict grouping SQL modes. Unpaged mode ignores ordinary table sorting; referenceDate supplies its own legacy ordering.
- No date conversion is applied to integer timestamp projections. Neither timestamp field should be treated as a generic datetime field.
- Requires a logged-in, positive tenant context; shared-helper reads use that session’s routing, never a caller-supplied tenant ID.
- The mobile app has no direct caller for this endpoint in the searched JavaScript or
m/wwwintermediary. It downloads price bands throughsyncDownloadsinto its localpriceBandstable.
Side effects
- Read-only; no audit, row writes, timestamp changes or cache invalidation.
See it in Fieldmotion
CRM1
No direct CRM1 caller was found; the legacy finance list uses PriceBands_getDT.
CRM2
Open Settings → Finance → Price Bands and page, sort or filter the table. The display widget synthesizes PriceBands_getDataTable with mapped native fields. Feature flag priceBands controls visibility of this screen.
Related endpoints
PriceBands_getDTis the login-guarded legacy table with positional rows.PriceBand_getRequestedDetailsis login-guarded and returns an unpaged bare mapped array.