I have discovered that it is impossible to filter by certain 'calculated' columns on the API, particularly on the sales_posted_transactions endpoint.
The following API call with a filter to return only Sales Receipts works successfully...
/v1/sales_posted_transactions?$skip=0&$orderby=id&$top=500&$filter=trader_transaction_type eq 'TradingAccountEntryTypePurchasePaymentSalesReceipt'
however as soon as you add any of the 'document_' fields (e.g. outstanding value) into the filter the API returns an error, e.g.
v1/sales_posted_transactions?$skip=0&$orderby=id&$top=500&$filter=trader_transaction_type eq 'TradingAccountEntryTypePurchasePaymentSalesReceipt' and document_outstanding_value gt 0
The error being returned is: Error: {"Invalid column name 'DocumentOutstandingValue'."}
Sage Developer Support have advised that this is due to these columns are 'calculated'. The API documentation makes no reference to any columns that are unfilterable, so it was extremely disappointing to discover this. This limitation makes the API useless for many use cases. For my particular requirement I need to obtain any unallocated/outstanding sales transactions.
I would classify this as a bug rather than a suggestion or feature request.
Guest
Thanks for the idea, some fields are calculated or transient, and others persist in the database. You can only filter on those that persist in the database, not the transient ones. To achieve this, you would need to use a filter on the allocated value (what’s been paid/credited) that is less than or NOT equal to the document value (total). This would work, where the DocumentOutstandingValue is a calculated field and so cannot be used for filters.
We will look to improve our documentation around this, including some fields are calculated, what that means, why you can’t filter etc. and also making sure it’s noted in the API reference as to which fields these are.
Attachments Open full size