SAP Cloud Platform Open Connectors

How to Query a Ledger-Account by FullName

When trying to query a Ledger-Account by “FullName”, you will often receive a 400 response code with the following message:

"message":"Invalid field name provided in where clause."

Unfortunately, this happens due to a limitation on QBE’s query management. In order to enable a user to query ledger-accounts by its name, QBE demands that we either filter the name by using a “Name Filter” or something called a “Name Range”:

"<NameFilter> <!-- optional -->
<!-- MatchCriterion may have one of the following values: StartsWith, Contains, EndsWith -->
<MatchCriterion >ENUMTYPE</MatchCriterion> <!-- required -->
<Name >STRTYPE</Name> <!-- required -->
</NameFilter>
<!-- OR -->
<NameRangeFilter> <!-- optional -->
<FromName >STRTYPE</FromName> <!-- optional -->
<ToName >STRTYPE</ToName> <!-- optional -->
</NameRangeFilter>"

Therefore, in order to filter a Ledger-Account based on the “FullName”, one should use the following query within Cloud Elements CEQL tool:

where Name='{myLedger-AccountName}'

Here is a full CURL to give you a clear example:

curl -X GET "https://console.cloud-elements.com/elements/api-v2/hubs/finance/ledger-accounts?where=Name%20%3D%20'Accounts%20Receivable'" -H "accept: application/json" -H "Authorization: User {cloudElementsUserToken}, Organization {cloudElementsOrgToken}, Element {cloudElementsElementToken}"

And based on the CURL given above, the “response.body” should look like this:

[
  {
    "CurrencyRef": {
      "ListID": "80000096-1484939752",
      "FullName": "US Dollar"
    },
    "IsActive": "true",
    "EditSequence": "1484940218",
    "AccountType": "AccountsReceivable",
    "AccountNumber": "11000",
    "Name": "Accounts Receivable",
    "TimeCreated": "2017-01-20T12:23:38-07:00",
    "TimeModified": "2018-01-02T16:45:05-07:00",
    "Desc": "Unpaid or unapplied customer invoices and credits",
    "TotalBalance": "81.52",
    "SpecialAccountType": "AccountsReceivable",
    "Sublevel": "0",
    "ListID": "80000027-1484940218",
    "TaxLineInfoRet": {
      "TaxLineID": "1174",
      "TaxLineName": "B/S-Assets: Accts. Rec. and trade notes."
    },
    "FullName": "Accounts Receivable",
    "CashFlowClassification": "Operating",
    "Balance": "81.52"
  }
]